Getting Access records into Word 2003 VBA

[pacmedtran]

Hi,
I'm having trouble opening a recordset in Word VBA to use Access data in a
macro. Here is the code:

Set objAccess = CreateObject("Access.Application")
objAccess.DoCmd.SetWarnings False
objAccess.OpenCurrentDatabase "PUTR.mdb"
objAccess.DoCmd.OpenTable "ReadyList"

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim varRecordSet1 As New ADODB.Recordset
varRecordSet1.ActiveConnection = cnn1

The trouble comes at the last line, where I get a 3001 error that the
argument is the wrong type, out of range or in conflict. That's as much help
as I get.

I do have the ActiveX Data Objects 2.1 object library referenced. As far as
I can see, this ought to work, but I can't see what's wrong. If anybody
could point out the obvious for me, I'd really appreciate it.

 

[Tony Jollans]

I'm surprised you get as far as the last line. What is CurrentProject in
your Word application?

You don't need to instantiate an Access application to use ADO to read a
database. Try just something like this ..

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;"User ID=Admin;" & _
"Data Source=" & "PUTR.mdb"

cmd.CommandText = "Select * from ReadyList;"
cmd.ActiveConnection = cnn

Set rst = cmd.Execute

 

[pacmedtran]

That did the trick. Thanks so much.

I'm surprised you get as far as the last line. What is CurrentProject in
your Word application?

You don't need to instantiate an Access application to use ADO to read a
database. Try just something like this ..

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;"User ID=Admin;" & _
"Data Source=" & "PUTR.mdb"

cmd.CommandText = "Select * from ReadyList;"
cmd.ActiveConnection = cnn

Set rst = cmd.Execute