Variable type not appearing

[Dan29]

Hi guys,

I wonder if you can help? I am trying to create Query Definitions in VBA in Access 2002.

I'm using the book 'Begining Access 2002 VBA', which shows how to do this. On the example database included on a CD with the book, this is possible without problem. However, when I go to declare a variable of type 'QueryDef' in the Access 2002 databases we use at work, the variable type 'QueryDef' just isn't recognised, nor is it listed in the drop down menu you get when declaring a variable.

I was just wondering if anyone could give me any information on this? I really need to be able to create Query Definitions I'm afraid.

One thing that probably is worth mentioning is that the database included with the book utilises DAO, I'm not sure if the Databases we have at work utilise this or ADO. I'm not sure how to check this either to be honest.

If anyone could give me advise on either of these two thing that would be great.

Thanks very much indeed,

Dan.

 

[WayneRyan]

Dan,

Get your code in Design View, then select: Tools --> References from the menu

Check the entry for Microsoft DAO.

Then, when you define a QueryDef:

Dim qdf As DAO.QueryDef

Wayne

 

[boblarson]

You need to set a reference to DAO before using it. Access 2000 and 2002 have ADO set as default and don't have Microsoft DAO 3.xx selected. Go to TOOLS > REFERENCES in the VBA window and scroll through the list and then put a check next to it.

 

[Dan29]

Thanks guys, that's brilliant. I'll certainly give that a go.

Thanks again,

Dan.

 

[gemma-the-husky]

just one thing, to avoid confusion, after you select dao it should appear ABOVE ado in the list of references

 

[boblarson]

just one thing, to avoid confusion, after you select dao it should appear ABOVE ado in the list of references

Although if you are using explicit declaration it really doesn't matter.

 

[Dan29]

Thanks once again guys (& girl ), I really appreciate the help.

Could you tell me, would switching to DAO in this way adversly effect the database I wish to make changes to in any way if it was originally just using ADO?

Thanks,

Dan.

 

[boblarson]

Actually, you can use ADO and DAO all together within the same database; no problems but you will need to make sure all of your code instantiates the objects explicitly (i.e. Dim db As DAO.Database, Dim rst As ADO.Recordset, Dim rst1 As DAO.Recordset).

 

[gemma-the-husky]

orignally (A97) Access used DAO as standard

changed to ADO on A2002, but changed back again in 2003

i think DAO offers some facilities that ADO couldnt do, but then i believe it works the other way round for some things. I use DAO pretty well exclusively for what its worth

 

[boblarson]

changed to ADO on A2002, but changed back again in 2003

Slight correction there -

Access 97 - DAO
Access 2000 - ADO
Access 2002 - ADO
Access 2003 - BOTH checked by default
Access 2007 - DAO

 

[gemma-the-husky]

thanks Bob

does ADO do anything that DAO doesnt

I think ADO is what you use to open Excel directly isnt it? Or am I completely wrong there?

 

[boblarson]

thanks Bob

does ADO do anything that DAO doesnt

I think ADO is what you use to open Excel directly isnt it? Or am I completely wrong there?

Maybe this will help:

http://www.access-programmers.co.uk/forums/showpost.php?p=455820&postcount=3

 

[Dan29]

Hi,

Thanks once again for the help guys and girls. I really appreciate it : )

 

[Dan29]

Hi Bob,

Thanks very much for the info. Could you tell me, would I only need to instantiate variables and do so only when they're declared?

Thanks very much again,

Dan.

 

[boblarson]

Hi Bob,

Thanks very much for the info. Could you tell me, would I only need to instantiate variables and do so only when they're declared?

Thanks very much again,

Dan.

Not sure I understand what you're getting at. You can declare the variables as public in a standard module so that they are accessible throughout the program. You can then instantiate them (Set rst=CurrentDb.OpenRecordset("yourTableOrQueryHere")) wherever, including when the first form opens/loads and then use it throughout.