Recordset not working with linked tables

[ruaridhmac]

I'm having trouble using a recordset in VBA that i have previously used without touble - the only difference is that i'm now using linked tables in SQL Server. Code as follows:

Function BuildtblNodes2()

Dim db As DAO.Database
Dim rsZones As DAO.Recordset

Set db = CurrentDb
Set rsZones = db.OpenRecordset("SELECT txtZone FROM dbo.tblZone")

End Function

All the function is supposed to do so far is set the recordset to the query - this doesn't work and just comes up with error 91:

Object Variable or With block variable not set

and highlights the Set rsZones line when debugging.

I've checked my referencs and there doesn't seem to be a problem there. Is there any differences i don't know about that are required when using linking tables? Or perhaps theres some setting i need to change in SQL Server - i wouldn't know what though.

 

[boblarson]

You need

Set rsZones = New DAO.Recordset

rsZones = db.OpenRecordset("SELECT txtZone FROM dbo.tblZone")

 

[ruaridhmac]

Just tried that but when i type 'New DAO.', 'recordset' does not appear on the drop down list. I typed it in anyway and back came an error. Anyway this is what i now have:

Function BuildtblNodes2()

Dim db As DAO.Database
Dim rsZones As DAO.Recordset

Set db = CurrentDb
Set rsZones = New DAO.Recordset

rsZones = db.OpenRecordset("SELECT txtZone FROM dbo.tblZone")

End Function

which is really begining to make me think i have a problem with referencing because it looks right to me.

 

[boblarson]

Is DAO checked in your references? If you are on Access 2000 it won't be by default, (or possibly 2002 - not sure if it gets checked by default).

 

[RoyVidar]

No, remove the Set New again, that's more ADOish

But I'm wondering if it's the currentdb/db or recordset creating the error. Just for fun, try if

msgbox currentdb.name ' gives path and name of the db and
msgbox db.name ' gives the same

then, I didn't think you used owner prefix on linked tables, but rather

"SELECT txtZone FROM dbo_tblZone", but do check out the actual name of the linked table, as it looks in the database window. I don't use linked SQL tables, but would you need dbSeeChanges?

 

[ruaridhmac]

Now i think i've discovered the problem as when i type

msgbox currentdb.name

it returns the same error as before: error 91
which leads me to believe there is a problem with the database but i'm not sure where to start fixing it?

 

[RoyVidar]

Are you by any chance using ADP?

If so, forget about DAO, use ADO. Even if CurrentDB is a member of the access application object in mdbs, it isn't available in ADPs.

Or, perhaps create an mdb file, and link the tables in stead.

 

[ruaridhmac]

Right, I've dropped DAO for ADO - which someone told me to do a while ago but i ignored them thinking it would be hard work - and everything seems to be working.

Thanks to those who helped!

R.