Getting Access records into Word 2003 VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pacmedtran - 22 Oct 2007 01:00 GMT

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.

Reply to this Message

Tony Jollans - 22 Oct 2007 07:09 GMT

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

Signature

Enjoy,
Tony

> Hi,
> I'm having trouble opening a recordset in Word VBA to use Access data in a
[quoted text clipped - 19 lines]
> 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.

Reply to this Message

pacmedtran - 23 Oct 2007 18:49 GMT

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?
[quoted text clipped - 38 lines]
> > 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.

Reply to this Message