Recordset Issue

srpathak · December 1st, 2003, 10:15 PM

Hi Friends,
               I am using Access 2002 with a SQL backend for a project. I am trying to access a table in the database using Recordset, and I am popped with the error.

Object variable or WIth block variable not set

Following is the code-

    Dim DbTmp As Database
    Dim rsCity As Recordset

    Set DbTmp = CurrentDb()

    Set rsCity = CurrentDb.OpenRecordset("Select * FROM [ZIPS]") ' This is the source of error

    rsCity.Find "CITY = '" & Me.CITY.Value & "'"

             I have included the DAO library in my project. I tried using DAO.Database and DAO.Recordset but no luck! I am just not able to figure this thing out. Any help in this matter would be really great for me!
             Thanks you.

Sincerely,
Sameer

SerranoG · December 2nd, 2003, 10:33 AM

Hmmm... without seeing more, here's my first guess. Brackets usually surround field names, not table or query names in SQL code. Therefore, try this. Remove the brackets [] from the SELECT and put it in the FIND criterion. That is,

Code:

Set rsCity = CurrentDb.OpenRecordset("Select * FROM ZIPS")
rsCity.Find "[CITY] = '" & Me.CITY.Value & "'"

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

John Anthony · December 2nd, 2003, 11:03 AM

What you need to do, is set the statement to include a dbOpenDynaSet

Set rsCity = CurrentDb.OpenRecordset("Select * FROM [ZIPS]", dbOpenDynaSet)

John

BethMoffitt · December 2nd, 2003, 11:08 AM

And if it's a sql backend, you might need to add dbseechanges.

Beth M

sal · December 2nd, 2003, 12:32 PM

If you are using SQL Server as a backend, do not use currentproject connection to connect to the data, use SQL Server provider, You are bringing the data to Access to crunch.

Also, use ADO instead.

Sal

wscheiman · December 3rd, 2003, 09:12 PM

Sameer,

If you can migrate to ADO, it would be good; however here's what you need to do if you'd like to keep it in DAO - change:

Set rsCity = CurrentDb.OpenRecordset("Select * FROM [ZIPS]")

to

Set rsCity = DbTmp.OpenRecordset("Select * FROM [ZIPS]")

The recordset object needs to be opened under the declared database object, not the CurrentDb. CurrentDb was used to set your database object named dbTmp. This should work as long as the SQL Server table named ZIPS is attached/linked to your database. If you would like help on setting this same scenario using ADO, please let me know.

Good luck.

Warren