Opening a SQL Server table from within MS Access



I created a Microsoft Data Access Project (adp) in MS Access V11. After
linking the tables in MS SQL Server V7 and creating the views, it all
worked fine until I had to open a table in a vb module (VB 6.3). The
code keeps producing this error: "Run-time error '91': Object variable
or With block variable not set."

I downsized my problem to this simple piece of code:

Public Sub test()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("MyTable")
End Sub

These are the libraries which I ticked in the reference list of the

Visual basic for Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
OLLE Automation
Microsoft ActiveX Data Objects 2.8 Libraries

Do I need to change my code and/or do I have to add another lib? Any
help is welcome.

Brendan Reynolds

CurrentDb returns Nothing in an ADP. You need to use ADO instead of DAO in
this context. For example ...

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "MyTable"

Depending on what you're going to do with the recordset, you may need to set
other properties. Unlike a DAO recordset, the default for an ADO recordset
is a read-only, forward-only recordset. See the ADO documentation for

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question