Connect Word with Access using ADO (2002)

Topic

New Reply

Does anyone have some sample code to connect Word to an Access xp database? I want to call up a biography description from a mdb in a form box in Word and then add that bio description to the Word document. Any help is greatly appreciated. Thanks

Reply | Quote

Replies

I don’t have XP, but I suspect it is similar to 2000. Here’s a sample. This is the code for a button on a UserForm that uses a predefined path to the database (global var gstrDBPath). It runs a query that returns a tiny recordset. If the recordset is not empty, it builds a little MsgBox to report the record counts to the user. Hope it makes some sense with that limited context!

Private Sub btnStatusCheck_Click()
Dim conTimeRpt As New ADODB.Connection
Dim cmdTimeRpt As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim strMessage As String
With conTimeRpt
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "Data Source=" & gstrDBPath
End With
With cmdTimeRpt
    .ActiveConnection = conTimeRpt
    .CommandText = "Q4StatusCount"
End With
With rst
    .CacheSize = 2
    .Open cmdTimeRpt, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
End With
If Not rst.EOF Then
NextRec:
    Select Case rst.Fields("TSStatus").Value
        Case 0
            strMessage = strMessage & "Incomplete (editable) slips: " & _
                rst.Fields("Count").Value & vbCrLf
        Case 1
            strMessage = strMessage & "''OK'' (editable) slips: " & _
                rst.Fields("Count").Value & vbCrLf
        Case 2
            strMessage = strMessage & "Sent (uneditable) slips: " & _
                rst.Fields("Count").Value & vbCrLf
        Case Else
    End Select
    rst.MoveNext
    If Not rst.EOF Then GoTo NextRec
Else
    strMessage = "No slips found "
End If
strMessage = strMessage & "for the date range and TimeKeeper you selected"
MsgBox strMessage
Set rst = Nothing
Set cmdTimeRpt = Nothing
conTimeRpt.Close
Set conTimeRpt = Nothing
End Sub

Reply | Quote