Update Access Tables from MS Word

[KillerChairs]

Is there a way to update an Access table form MS Word?

We are trying to track how long a user works on a Word document. I open a document from Access and record the start time in a table. When the user closes the document, I want Word (via a Close macro) to update the table entry with the close time. The amount of time spent in the document is used for billing purposes, which is recorded in our Access application.

Any ideas?

Thanks.

 

[Crowley16]

well, can you not use a ADO recordset?

reference it in your word vba, then just open the relevant table/row/field and set the value, and save the recordset...

--------------------
Procrastinate Now!

 

[Tranman]

KillerChairs,
Here's a snippet.

You should use the UNC path to the database so you don't have to have the same mappings on all computers.

Just build up your own SQL statement with the correct project ID.

Private Function updMDB()
Dim strDB As String
Dim strSQL As String
Dim con As New ADODB.Connection
strDB = "\\dt12ga53\c$\ACCESS_XP_DBs\dummy2.mdb"
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";Persist Security Info=False"
strSQL = "Update Projects set myDate = Now() where myProj = 'X125'"
con.Execute strSQL
con.Close
Set con = Nothing
End Function

Of course, this requires a reference to the ADO in your Word Project.

If you don't want to do that, you can create a "DSN-less" connection object by referring to the Access ODBC driver in your code:

con.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & strDB;"

Hope that helps.
Tranman