querydefs in 2000 vs 2003

[Donald Grove]

I am using Access 2000, but my manual is for Access 2003. In 2003 you
can declare databases and querydefs as follows:

dim qdf as QueryDef
dim dbs as Database

but this doesn't seem to work in 2000. At least, I couldn't make it
work. I managed to create a new querydef, though, using the following
code:

Set querydef = CurrentDb.CreateQueryDef(qdefName, strSQL)

Question 1: is there a way to get the database name and store it as a
variable, so that the code would read:

dim dbs as Database
Set querydef = dbs.CreateQueryDef(qdefName, strSQL)

Question 2: If I create an alternate SQL string, what is the code to
identify the appropriate queryDef and replace the SQL string with the
alternate. Again, the 2003 example I have has me declare the querydef
name as a variable, and then use it in the code:

dim qdf as QueryDef

qdf.SQL = strAlternateSQL

I had declared a string variable as qDefName giving the name of the
query

qDefName.SQL = strAlternateSQL

but I get back the error "Invalid qualifier". So if I have an
existing query, how do I work with it as a queryDef in 2000?

[Allen Browne]

From your code window in Access 2000, choose References on the Tools menu.

Check the box beside:
Microsoft DAO 3.6 Library
The code will then work.

In their wisdom, Microsoft decided to leave out the DAO library reference
when you create a new database in Access 2002 or 2000. Fortunately, it's
back in 2003 as it was in all previous versions.

More on solveing problems with references:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Donald Grove" <do*********@verizon.net> wrote in message
news:an********************************@4ax.com...

I am using Access 2000, but my manual is for Access 2003. In 2003 you
can declare databases and querydefs as follows:

dim qdf as QueryDef
dim dbs as Database

but this doesn't seem to work in 2000. At least, I couldn't make it
work. I managed to create a new querydef, though, using the following
code:

Set querydef = CurrentDb.CreateQueryDef(qdefName, strSQL)

Question 1: is there a way to get the database name and store it as a
variable, so that the code would read:

dim dbs as Database
Set querydef = dbs.CreateQueryDef(qdefName, strSQL)

Question 2: If I create an alternate SQL string, what is the code to
identify the appropriate queryDef and replace the SQL string with the
alternate. Again, the 2003 example I have has me declare the querydef
name as a variable, and then use it in the code:

dim qdf as QueryDef

qdf.SQL = strAlternateSQL

I had declared a string variable as qDefName giving the name of the
query

qDefName.SQL = strAlternateSQL

but I get back the error "Invalid qualifier". So if I have an
existing query, how do I work with it as a queryDef in 2000?

[pietlinden]

umm... if you're staying in the same DB the whole time, you don't have
to specify it. You can use
the properties and methods of CurrentDB
CurrentDB.Execute
CurrentDB.Querydefs("queryname")

Or you can use
DBEngine(0)(0).Execute
DBEngine(0)(0).Querydefs("queryname")