How to test for existence then delete a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob - 28 Dec 2006 22:58 GMT

I want to run the following command using VBA...

db.QueryDefs.Delete("query1")

But I want to test for existence of the object (the Query) before I do.
How do you do that ?

Thanks!

Reply to this Message

Dirk Goldgar - 28 Dec 2006 23:33 GMT

> I want to run the following command using VBA...
>
> db.QueryDefs.Delete("query1")
>
> But I want to test for existence of the object (the Query) before I
> do. How do you do that ?

You can do it by looping through the QueryDefs collection looking for
it, but ... if you're going to delete it anyway, wouldn't it be simpler
just to delete it without checking, and trap the error that will occur
if it doesn't exist? E.g.,

Dim lngErrNum As Integer

On Error Resume Next
db.QueryDefs.Delete("query1")
lngErrNum = Err.Number
On Error GoTo YourOriginalErrorHandler
If (lngErrNum <> 0) And (lngErrNum <> 3265) Then
Err.Raise lngErrNum
End If

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Aaron - 29 Dec 2006 22:59 GMT

>> I want to run the following command using VBA...
>>
[quoted text clipped - 17 lines]
> Err.Raise lngErrNum
> End If

I also need to determine if a query exists, then delete it if it does exist.
I've learned if I try to delete a query(object) that doesn't exist, I would
get an error. I originally used
If ObjectExists("Query", "qryTempRptQry") Then .....

But it seems that DAO 3.6 in Access 2003 has a problem with Objectexists().

Suggestions?

Thanks

Reply to this Message

Aaron - 29 Dec 2006 23:24 GMT

>>> I want to run the following command using VBA...
>>>
[quoted text clipped - 29 lines]
>
> Thanks

I had a function named ObjectExists

Reply to this Message

Dirk Goldgar - 30 Dec 2006 06:44 GMT

>>> I want to run the following command using VBA...
>>>
[quoted text clipped - 26 lines]
> Objectexists().
> Suggestions?

Obviously, you once had a function named ObjectExists. Such a function
is easy enough to write, but my whole point was that, if all you're
going to do if the query exists is delete it, then why bother testing
first? Just delete it, trap the error, and ignore it.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message