Drop queries

[Tim]

Hi folks,

Is there a way to drop all queries from my DB which were
prefixed by “Temp”?

Any help will be appreciated.

Thanks.

Tim.

 

[Van T. Dinh]

Using DAO, you can check the name of each of the QueryDef (Query in GUI) in
the QueryDef Collection. If the name is what you are looking for, use the
Delete Method of the QueryDefs object to delete the QueryDef.

Remember to delete from the "last" QueryDef first since the Collection is
re-indexed on each deletion.

 

[Van]

Hi Van,

I have following queries in my DB:

qry1
qry2
qry3
qry4
test

I also have the following code:

dim dbs as database
dim qdf as querydef

set dbs = currentdb()

For Each qdf in dbs.querydefs
if left (qdf.Name,3) = "qry" then
dbs.querydefs.delete qdf.name
end if
Next

dbs.close

When I ran the code, it will delete qry1 and qry3 only
not all four queries. Could you tell me what is the
probelm and how to fix it?

Thanks.

Tim.

 

[Tim]

Hi Van,

Sorry, I typed my name incorrectly. I just retyped my
question again.

I have following queries in my DB:

qry1
qry2
qry3
qry4
test

I also have the following code:

dim dbs as database
dim qdf as querydef

set dbs = currentdb()

For Each qdf in dbs.querydefs
if left (qdf.Name,3) = "qry" then
dbs.querydefs.delete qdf.name
end if
Next

dbs.close

When I ran the code, it will delete qry1 and qry3 only
not all four queries. Could you tell me what is the
probelm and how to fix it?

Thanks.

Tim.

 

[Van T. Dinh]

That's what I meant by the last paragraph. When the 1st QueryDef is
deleted, the 2nd becomes the first, the 3rd becomes the second. Thus in
next iteration, the (original) 3rd Query is deleted and the (original) 2nd
remains. Similar for the 4th QueryDef.

Use For loop like: (***Untested***)

Dim dbs As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.Querydef
Dim intCount As Integer
Dim intIndex As Integer

set dbs = currentdb()
Set qdfs = dbs.QueryDefs
intCount = qdfs.Count

For intIndex = intCount -1 To 0 Step -1
Set qdf = qdfs.Item(intIdex)
if left (qdf.Name,3) = "qry" then
dbs.querydefs.delete qdf.name
end if
Next intIndex

dbs.close
Set qdf = Nothing
Set qdfs = Nothing
Set dbs = Nothing

 

[Tim]

Van,

The code works great.

Thanks a lot.

Tim.

-----Original Message-----
That's what I meant by the last paragraph. When the 1st QueryDef is
deleted, the 2nd becomes the first, the 3rd becomes the second. Thus in
next iteration, the (original) 3rd Query is deleted and the (original) 2nd
remains. Similar for the 4th QueryDef.

Use For loop like: (***Untested***)

Dim dbs As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.Querydef
Dim intCount As Integer
Dim intIndex As Integer

set dbs = currentdb()
Set qdfs = dbs.QueryDefs
intCount = qdfs.Count

For intIndex = intCount -1 To 0 Step -1
Set qdf = qdfs.Item(intIdex)
if left (qdf.Name,3) = "qry" then
dbs.querydefs.delete qdf.name
end if
Next intIndex

dbs.close
Set qdf = Nothing
Set qdfs = Nothing
Set dbs = Nothing



--
HTH
Van T. Dinh
MVP (Access)





.