Creating db log from Word Macro

[Mojeaux]

Hi,
I am trying to write to a MSAccess97 from a MSWord 2000 macro usin
VBA. The macro does many things such as save, print, and sent to imag
via another pc of software. I wanted to have the code write out to a d
everytime someone processes a letter... creating a log. I copied th
follwoing code from another web application, but I am getting a ru
time error: Microsoft[odbc MSAccess Driver] syntax error in INSER
INTO statement. Here is the code:

Public Sub TestAdlib()

Dim user
Dim clm
Dim today

Dim conn, rs, strsql

user = SaveFileName
clm = SaveFileNumb
today = Format(Now, "m/d/yy")

strsql = "INSERT INTO ADLTRS (USERID, CLMNO, DATE) VALUES ('" & user
"', '" & clm & "', '" & today & "')"

Set conn = CreateObject("adodb.connection")
conn.Open "DRIVER={Microsoft Access Drive
(*.mdb)};DBQ=\\server1\flder1\SHARED\WORD\AD DB\ADLTRS.mdb"

Set rs = CreateObject("adodb.recordset")
rs.Open strsql, conn

conn.Close

MsgBox ("Your letter has been sent to Image")
End Sub

I've condensed some of the sript (variables and other process) but th
remaining code was used successully in the web page. In debug mode
Word points to the line: rs.Open strsql, conn as the problem.

Are different connections used for different situations... ie, we
page, external application, msAccess(internal)?

Any suggestions would be greatly appreciated. Thanks

 

[Jezebel]

1. Date is a reserved word in SQL, so you need to put square brackets around
it.

2. If the DATE field has a Date/Time data type, you don't want to be quoting
the value. ADO SQL dates should look like %yyyy-mm-dd% (You can use the
USer mm/dd/yy format, but the ISO date format is better.)

3. If the CLMNO field is numeric, you don't want to be quoting that value,
either.

A good way to debug SQL is to print the SQL statement in the immediate
window, copy it, then fire up Access, open the database, and try executing
the statement directly in the SQL Query window. Access will give you a lot
more information about what's wrong.

 

[Mojeaux]

Thank you for the tips. I will give this another whirl using you
suggestions

 

[Mojeaux]

Fyi: You were right on the money with the 'DATE' suggestion. Once
renamed the field, I was in like Flynn!! Thanks again! =