DATA FROM WORD TO ACCESS

[kcfaa]

I utilize WORD templates driven by a vba front end that creates letters using combination of pulling data from an access data base (name address etc), and user input to complete the letters.

We have another database for project managment, I would like to take the data from the user, e.g. date a contract is appoved and write that back to the project managment data base.

I was trying something like:

SQL$ = "f:\psr\PSR97TEST; SQL INSERT INTO Construction.cop approved VALUES Today WHERE IDGRANT# = IDGRANT"

ChanNum = DDEInitiate("MSAccess", SQL$)
DDETerminate ChanNum

I am trying to put a date into a database called PSR97TEST into the Construction Form into the COP APPROVED FIELD for the record with IDGRANT

Is there a better way to do this??
DJ

 

[Ed2020]

If the code to write to the PM database is running from MS Word whilst the Word document is open I'd look into opening the database using DAO, opening the table as a recordset, and using the methods exposed by the DAO recordset class to write the data to the table.

Ed Metcalfe.

Please do not feed the trolls.....

 

[kcfaa]

Since this is my first time to try DAO having some syntax problems this is what I tried

'///////////////////////////
'test to see if can write back to a table in access
Dim Today As String
Today = Format(Date, "mm-dd-yy") 'date to put into cop approved field in construction table
LOCID = System.ProfileString("microsoft word", "LOCID")
PROJNUM = System.ProfileString("microsoft word", "PROJNUM")
IDGRANT = LOCID + PROJNUM 'key field for construction table to id record to update

'attempt at using DAO 8/14
Dim db As Database, rs As Recordset
Set db = OpenDatabase("f:\psr\psr97test.mdb")
Set rs = db.OpenRecordset("SELECT*FROM CONSTRUCTION WHERE IDGRANT#" + Chr(34) + IDGRANT + Chr(34))
rstConstruction.cop approved = Today
rs.Close
db.Close

In the line where I am trying to update the field idgrant# something is wrong starting with the field name idgrant# (I did not pick that field name that is what I was dealt with the existing key field for the access data base)

any insight as to what is wrong with this line?

 

[Ed2020]

I think Access is probably unhappy about the # in the field name. Try this:

Set rs = db.OpenRecordset("SELECT * FROM CONSTRUCTION WHERE [IDGRANT#]=" & """" & IDGRANT & """" & ");"

Also note that if the IDGRANT# field is numeric (rather than text) the quotes around the value are not required.

Ed Metcalfe.

Please do not feed the trolls.....