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.
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.
Last Comment
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
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
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.
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
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
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°)
Cheers!
(°v°)
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
TRUSTED BY
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°)