DAO qdfs does not delete my queries

[Ben]

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

--

 

[Douglas J. Steele]

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.

 

[Klatuu]

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

 

[Stuart McCall]

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

Hey! I like that. Very neat. I'll be using that to empty a collection
tomorrow. Thanks for posting.

 

[Ben]

Wow, that was great explanation. I didn't realize that. Thank you so much
Doug.

Ben

 

[Ben]

Klatuu,

I am not quite sure what do make of your code below, can you help clarify?

Thank you,
Ben

 

[Ben]

Klatuu,

I think what threw me off are the "."s, can you clarify please?

Thank you.

Ben

 

[Douglas J. Steele]

Dave's using a With construct, which allows you to perform a series of
statements on the specified object without requalifying the name of the
object.

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

is the equivalent of

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

(watch for word-wrap in that middle expression: there's only supposed to be
3 lines of text...)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Klatuu,

I think what threw me off are the "."s, can you clarify please?

Thank you.

Ben

 

[Douglas J. Steele]

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.

 

[Ben]

Doug,

Thanks so much, especially for showing the long hand equivalent code.
MVP, all the way!

Ben