How to test for existence then delete a query

[Rob]

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!

 

[Dirk Goldgar]

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

 

[Aaron]

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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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

 

[Aaron]

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

I had a function named ObjectExists

 

[Dirk Goldgar]

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?

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.