Code to delete all queries in database

[Robert Valentine]

Group:

I need to write a sub to delete all of the queries in the current database.
I am hoping that someone can steer me in the right direction. I know that
the statement to delete a query is db.QueryDefs.Delete (QueryName). But how
do I go about getting the names of all of the queries and then delete them?
Any sample code would be a great help.

Thanks,
Bob V

 

[Douglas J. Steele]

Somewhat unusual to need to do this, but try:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim intLoop As Integer

Set dbCurr = CurrentDb()

For intLoop = (dbCurr.QueryDefs.Count - 1) To 0 Step -1
dbCurr.QueryDefs.Delete dbCurr.QueryDefs(intLoop).Name
Next intLoop

Note that it's necessary to work backwards. If not, when you delete the
first query, the pointer will move to the next query in the collection
(since the current query no longer exists). Once you get to the Next
statement, the pointer will move to the next query again, meaning that
you'll only be deleting every other query.

 

[chris.nebinger]

many different ways:


Dim dbs as DAO.Database
Dim qdf as DAO.QueryDef
Set dbs=CurrentDB
For each qdf in dbs.QueryDefs
dbs.QueryDefs.Delete(qdf.Name)
Next qdf



Or:

dim dbs as dao.databse
dim rst as dao.recordset
set dbs=currentdb
set rst = dbs.openrecordset("Select [name] from MSYSObjects where type
= 5 and left([name],1) <> '~'")
Do until rst.eof
dbs.querydefs.delete(rst("Name"))
rst.movenext
loop



Chris Nebinger

 

[Robert Valentine]

Doug & Chris:

Thank you for your quick responses. The examples you provided helped me
immensely!

Thanks again so much,
Bob V

 

[Robert Valentine]

Doug:

The reason I want to delete all of the queries in the current database is
because I have made a new version of the application and the underlying
queries have changed quite a bit. I thought it would be easier to delete all
of them and then create new ones through code.

Thanks so much for your help.

Bob V