Open access database from Word 2007

[mvesaas]

I had a Word document that was working when it was saved as 97-03 version. I had no problems and have not changed the code. Now that I am working in Word 2007, it is giving a User-defined type error message and stops at....

Dim vConnection As New ADODB.Connection

This code is supposed to open a database and look up a record based on what is in a formfield - then pull back the relevant contact information and populate the Word document.


I think the ADODB may be outdated, but can't find code for the 2007 version of VBA to open a database and retrieve info.

Any ideas?



Private Sub btnUserOK22_Click()
'UNDERWRITER******************************
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vName As String
Dim vTitle As String
Dim vEmail As String
Dim vPhone As String
Dim vFax As String
vConnection.ConnectionString = "data source=J:\pathname\ClientInfo.mdb;" & "Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
'set variable equal to whatever is entered into First & LastName form field
'so you can query the DB with this name to see if a record exists
vName = UserForm2.txtUWName.Text

'open a RecordSet with SQL query results...to see if first/last name matches a record
vRecordSet.Open "SELECT * FROM ClientInfo WHERE ClientInfo!Name = " & Chr(34) & vName & Chr(34), vConnection, adOpenKeyset, adLockOptimistic

'if a match is found, display it to the user in a message box
'you'll get a match if you are NOT .EOF (end of file)
With vRecordSet
If Not .EOF Then
'if yes...set variables from DB fields (the name variables are already set above)
vName = vRecordSet("Name")
vTitle = vRecordSet("Title")
vEmail = vRecordSet("Email")
vPhone = vRecordSet("Phone")
vFax = vRecordSet("Fax")
'set the form field's bookmarks to the results from the database field variables
ActiveDocument.FormFields("bkUWName2").Result = vName
ActiveDocument.FormFields("bkUWName1").Result = vName
ActiveDocument.FormFields("bkUWTitle").Result = vTitle
ActiveDocument.FormFields("bkUWEmail").Result = vEmail
ActiveDocument.FormFields("bkUWPhone").Result = vPhone
ActiveDocument.FormFields("bkUWFax").Result = vFax

'close objects
vRecordSet.Close
vConnection.Close
'clear object to free up memory
Set vRecordSet = Nothing
Set vConnection = Nothing

mFac
Else
'if not 6, then not Yes, so must be NO...remind user to update database!
MsgBox " " & Chr(13) & _
"Please CHOOSE a name!"
End If
End With

End Sub

[Jay Freedman]

There's nothing wrong with the code. All you're missing is a reference to the library (DLL) that supplies the code to define and use ADODB objects.

In the VB editor, click Tools > References, scroll down the list, and put a check next to "Microsoft ActiveX Data Objects 2.5 Library".

You may have other versions of the library installed on your computer (I have 2.0, 2.1, 2.5, 2.6, 2.7, 2.8, and 6.0), and probably any of them will do for a simple macro. For more discussion, read http://allenbrowne.com/ser-38.html and the pages linked from there.