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 Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ben" <(E-Mail Removed)> wrote in message
news:56912529-6208-4985-B90D-(E-Mail Removed)...
> 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
>
> --
>

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@NOSPAM_gmail.com> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:56912529-6208-4985-B90D-(E-Mail Removed)...
>> 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
>>
>> --
>>
>
>

"Klatuu" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

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

Ben

--



"Douglas J. Steele" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:56912529-6208-4985-B90D-(E-Mail Removed)...
> > 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
> >
> > --
> >
>
>
>

Klatuu,

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

Thank you,
Ben



--



"Klatuu" wrote:

> 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@NOSPAM_gmail.com> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:56912529-6208-4985-B90D-(E-Mail Removed)...
> >> 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
> >>
> >> --
> >>
> >
> >
>
>
>

Klatuu,

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

Thank you.

Ben

--



"Klatuu" wrote:

> 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@NOSPAM_gmail.com> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:56912529-6208-4985-B90D-(E-Mail Removed)...
> >> 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
> >>
> >> --
> >>
> >
> >
>
>
>

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
http://I.Am/DougSteele
(no e-mails, please!)


"Ben" <(E-Mail Removed)> wrote in message
news:CFD0B241-E235-447A-AF0B-(E-Mail Removed)...
> Klatuu,
>
> I think what threw me off are the "."s, can you clarify please?
>
> Thank you.
>
> Ben
>
> --
>
>
>
> "Klatuu" wrote:
>
>> 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@NOSPAM_gmail.com> wrote in message
>> news:(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> > news:56912529-6208-4985-B90D-(E-Mail Removed)...
>> >> 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
>> >>
>> >> --
>> >>
>> >
>> >
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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@NOSPAM_gmail.com> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:56912529-6208-4985-B90D-(E-Mail Removed)...
>>> 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
>>>
>>> --
>>>
>>
>>
>
>

Doug,

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

Ben
--



"Douglas J. Steele" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Ben" <(E-Mail Removed)> wrote in message
> news:CFD0B241-E235-447A-AF0B-(E-Mail Removed)...
> > Klatuu,
> >
> > I think what threw me off are the "."s, can you clarify please?
> >
> > Thank you.
> >
> > Ben
> >
> > --
> >
> >
> >
> > "Klatuu" wrote:
> >
> >> 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@NOSPAM_gmail.com> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> > news:56912529-6208-4985-B90D-(E-Mail Removed)...
> >> >> 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
> >> >>
> >> >> --
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>
>