how do i access the sql property of a saved query (i want to change the 

query definition) in code?

i have code for access97 but it doesnt work in access2000

all help files explaining the differences between 97 and 2000 just confused me more.

please help!

thanx

erez.

erez,



 The following code will look for a particular named query {QueryName} in 

the querydefs collection of the current database, and once located, will 

store the SQL statement associated with that query and print it out in the 

immediate window.



  If you wish to change that SQL statement, then I would suggest you 

delete the query itself (Docmd.DeleteObject)and save it again 

(Docmd.CreateQuery) using the new SQL statement.



HTH



Tony



Function SQL_of_a_Query()



Dim dbsQry As DAO.Database

Dim qryDef As DAO.QueryDef

Dim strQryDef As String

Dim strQryName As String





strQryName = "{QueryName}"



Set dbsQry = CurrentDb()



For Each qryDef In dbsQry.QueryDefs





If qryDef.Name = strQryName Then



strQryDef = qryDef.SQL

Debug.Print strQryDef

End If















> how do i access the sql property of a saved query (i want to change the 

> query definition) in code?

> i have code for access97 but it doesnt work in access2000

> all help files explaining the differences between 97 and 2000 just 

confused me more.

> please help!

> thanx

> erez.

dear tony



thanx alot for helping me, but i tried to use the function and i get "user 

undefined types..." for the DAO.Database and DAO.Querydef DIM statements.



does this code works for you on access2000? cause then it means there's 

something wrong with my access. 



and thanx again for everything.



erez.

You don't have the DAO object library referenced in your Access 2K module. 

The default data access library in 2K is ADO 2.1. Open a module, go to 

Tools>References and scroll down in the available references dialog until 

you find "Microsoft DAO 3.6 Object Library". Check the check box. Uncheck 

ADO 2.1 if you want, but you don't have to. Should work.





>From: erezmor@n... (Erez Mor)

>Reply-To: "Access" <access@p...>

>To: "Access" <access@p...>

>Subject: [access] Re: changing a saved query def in code

>Date: Thu, 12 Jul 2001 14:10:57 -0400

>

>dear tony

>

>thanx alot for helping me, but i tried to use the function and i get "user

>undefined types..." for the DAO.Database and DAO.Querydef DIM statements.

>

>does this code works for you on access2000? cause then it means there's

>something wrong with my access.

>

>and thanx again for everything.

>

>erez.