DAO qdfs does not delete my queries

  • Thread starter Thread starter Ben
  • Start date Start date
B

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

--
 
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,

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
 
Klatuu said:
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
Click to expand...

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

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

Thank you,
Ben
 
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!)


Ben said:
Klatuu,

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

Thank you.

Ben
Click to expand...
 
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,

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

Ben
 
Back
Top