Opening a SQL Server table from within MS Access

[absmienk]

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
app.

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"
rst.Open

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
details.

 

[Brendan Reynolds]

BTW: The ADO help files have a nasty habit of going AWOL, so having advised
you to see the ADO documentation, I guess I should probably tell you where
to find it!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/dasdkadooverview.asp

 

[ab]

Brendan,

this works great. I only need to check for the properties in order to
update the records. I' ll have a lokk at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/dasdkadooverview.asp

Thanks a bunch and kind regards,
Ab Smienk