Avatar of ashercim
ashercim

asked on 

Deleting Queries in Access 2003 using VBA

I've got an Access 2003 application that I'm developing, and it works great except for 1 remaining bug.

I can't seem to delete a QueryDef from the collection.  The code for this follows:

           For Each qdf In db.QueryDefs
                        'MsgBox (qdf.Name)
                        If qdf.Name = "qryTemp2" Then db.QueryDefs.Delete "qryTemp2"
             Next qdf

This temporary query may or may not be created at some point depending on the options that the user has chosen.  What I'm not understanding is that when qdf.name="qryTemp2" I'm getting a runtime 3011 error saying that the Jet Database Engine could not find the object qryTemp2.

It doesn't show up in the database window, but no matter what I do, I can't seem to remove this name from the Querydefs collection.

By the way, i set qdf as:

Dim qdf As DAO.QueryDef

I haven't had any problems like this in any of the other applications I've been developing and I don't understand it.  Any help would be greatly appreciated.
Microsoft Access

Avatar of undefined
Last Comment
harfang
Avatar of harfang
harfang
Flag of Switzerland image

Hello ashercim,

Your code works as expected in my test database. Then the query exists, it's deleted, else nothing happens.

Make sure you see all hidden objects and also that there is no naming conflict (a table with that same name, for example). If all else fails, try it in a new blank database, you will see there is nothing wrong with your code.

(°v°)
Avatar of Rey Obrero (Capricorn1)
try

Set db = CurrentDb
    For Each qdf In db.QueryDefs
     'MsgBox (qdf.Name)
    If qdf.Name = "qryTemp2" Then
    DoCmd.DeleteObject acQuery, "qryTemp2"
    End If
Next qdf
ASKER CERTIFIED SOLUTION
Avatar of harfang
harfang
Flag of Switzerland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ashercim
ashercim

ASKER

Thanks for your insight.

The code used to read with the DoCmd, but I like this version better.

I didn't know that the collection needed to be refreshed.

I was starting to think that I didn't get this anymore...

LOL
Avatar of harfang
harfang
Flag of Switzerland image

Well, this is why CurrentDb is a method that returns a *new* instance of the current database object, unlike DBEngine(0)(0), which is static. The advantage is that all collections are automatically refreshed in the new object. If you hold on to a database object and if there are changes in a collection (e.g. your code adding a new query def), then it's just like a dynaset which you would need to requery.

Cheers!
(°v°)
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo