The following Word VBA code works on my Word 2003 PC but fails on my Word 2010 PC.
The error message is:-
Run Time error 3706
Provider cannot be found. It may not be properly installed.
Both versions of Word have the following references ticked:-
Word (11.0 or 14.0) Object Library
Microsoft Office(11.0 or 14.0) Object Library
Miccrosoft ActiveX Data Objects 2.7 Library
Microsoft Forms 2.0 Object Library
This is the code:
Dim MyDatabase As String
Set cn = New ADODB.Connection
MyDatabase = "C:\psldata.mdb"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & MyDatabase
'cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & MyDatabase
Set rs = New ADODB.Recordset
'open the recordset
rs.Open "People", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs("peoSurname") = "Arney"
rs("peoForenames") = "Richard Neil"
'MyID = rs.peopleID
' MsgBox MyID
If rs.State <> 0 Then rs.Close 'Closes record
cn.Close 'Closes the connection
What have I not done on the 2010 PC to get it to run? I have tried substituting the ACE connection for the JET connection but both trigger the same error message.
What do I need to install to get it to work please?
A supplemental question is why the following line does not work (remarked out in my code). I get a not supported error message. How instead do I return the AutoNumber value of the ID for the record just created please?
MyID = rs.peopleID