having trouble with QueryDef fields

  • Thread starter Thread starter kitty
  • Start date Start date
K

kitty

I have a subroutine where I would like to list the fields in a query
which resides in the database but I get an error: "Object variable as
woth block variable not set"

Code:
Public Sub test()
Dim db As Database
Dim qdf As QueryDef
Dim fld As Field

Set db = DBEngine.OpenDatabase("C:\Event.mdb")
Set qdf = db.QueryDefs("qrycodes")
For Each fld In qdf.Fields
Debug.Print fld.Name
Next fld
qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing
End Sub

Do you see anything I'm doing wrong in this code
 
kitty said:
I have a subroutine where I would like to list the fields in a query
which resides in the database but I get an error: "Object variable as
woth block variable not set"

Code:
Public Sub test()
Dim db As Database
Dim qdf As QueryDef
Dim fld As Field

Set db = DBEngine.OpenDatabase("C:\Event.mdb")
Set qdf = db.QueryDefs("qrycodes")
For Each fld In qdf.Fields
Debug.Print fld.Name
Next fld
qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing
End Sub

Do you see anything I'm doing wrong in this code
Click to expand...

No, it looks okay, provided you have a reference to DAO and don't have a
reference to ADO, or else the DAO reference has a higher priority. The
code, modified to fit my test database, ran fine for me. What version
of Access are you running? Is the code running inside "C:\Event.mdb"
itself? If so, try using

Set db = CurrentDb

just in case you're locking yourself out.
 
On which code line does the error occur? Are you using ACCESS 2000 or higher
version? If yes, do you have a reference to DAO library set?
 
Dirk Goldgar said:
No, it looks okay, provided you have a reference to DAO and don't have a
reference to ADO, or else the DAO reference has a higher priority.
Click to expand...

Just to tag along on Dirk's post, if you do have references to both DAO and
ADO, change

Dim fld As Field

to

Dim fld As DAO.Field
 
I think you are right about the ADO and DAO. I believe that's my
problem. I will try your suggestions. I am getting the error on the
"For each fld In qdf.Fields" line. As if there is a problem with "fld"
so I will check on the ADO and DAO. THANKS!!
 
Back
Top