Re: DAO qdfs does not delete my queries

Show-off! <g>

Of course, since Ben only wanted to delete certain of the queries, not all
of them, I'm not sure how relevant that is.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Klatuu" <david.hargis@xxxxxxxxxxxx> wrote in message
news:O0yNCEtPJHA.4084@xxxxxxxxxxxxxxxxxxxxxxx
Doug,

Just an interesting variation on a theme:

With dbfDestination
Do While .Relations.Count > 0
.Relations.Delete .Relations(.Relations.Count - 1).Name
Loop
End With

Would be the same with querydefs

"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxx> wrote in message
news:OFUrv7rPJHA.3748@xxxxxxxxxxxxxxxxxxxxxxx
When deleting objects from collections such as the QueryDefs collection,
you need to work backwards from the end of the collection.

In other words, rather than:

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

Set dbCurr = CurrentDb
For Each qdfCurr In dbCurr.QueryDefs

Next qdfCurr

you should use

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim lngLoop As Long

Set dbCurr = CurrentDb
For lngLoop = (dbCurr.QueryDefs.Count - 1) To 0 Step -1
Set qdfCurr = dbCurr.QueryDefs(lngLoop)

Next lngLoop

The reason for this is when you delete a particular QueryDef from the
collection, the pointer to the current QueryDef moves to the next
QueryDef in the collection. Since you're then issuing a Next qdfCurr
command, you end up missing the QueryDef.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ben" <Ben@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:56912529-6208-4985-B90D-0FFD124ECE03@xxxxxxxxxxxxxxxx
Hi all,

I have 3 queries I want to delete from my list of queries.
I used a query definition object to cycle through all my queries and
delete
them every time a query's name matches mine listed names, but for some
reason, it does not delete them all, sometimes it would delete them the
first
one and then exit the for each -next loop, sometimes it would delete two
of
the three, but never all three.

I create these queries on the fly in my code and what I had resorted to
do
was at the end of my routine, I manually delete each of them one at a
time
using a separate line of code each time around.

Any idea why the cycling through the query definition wouldn't work?
Thanks for sharing thoughts.

Ben

--







.

Relevant Pages

  • Re: DAO qdfs does not delete my queries
    ... Doug Steele, Microsoft Access MVP ... Dim qdfCurr As DAO.QueryDef ... The reason for this is when you delete a particular QueryDef from the ...
    (microsoft.public.access.modulesdaovba)
  • Re: DAO qdfs does not delete my queries
    ... Dim qdfCurr As DAO.QueryDef ... the pointer to the current QueryDef moves to the next QueryDef ... I have 3 queries I want to delete from my list of queries. ...
    (microsoft.public.access.modulesdaovba)
  • Re: DAO qdfs does not delete my queries
    ... Dim qdfCurr As DAO.QueryDef ... The reason for this is when you delete a particular QueryDef from the ... I have 3 queries I want to delete from my list of queries. ...
    (microsoft.public.access.modulesdaovba)
  • Re: DAO qdfs does not delete my queries
    ... Dim qdfCurr As DAO.QueryDef ... the pointer to the current QueryDef moves to the next QueryDef ... I have 3 queries I want to delete from my list of queries. ...
    (microsoft.public.access.modulesdaovba)
  • Re: DAO qdfs does not delete my queries
    ... Dim qdfCurr As DAO.QueryDef ... the pointer to the current QueryDef moves to the next QueryDef ... I have 3 queries I want to delete from my list of queries. ...
    (microsoft.public.access.modulesdaovba)