Test Object Existence

[Guest]

*Sigh* I've searched everywhere for what I figured was an easy one that I'm
having a mental block on.

How do I test to see if an object in my database exists, say a table or query.

If I try to test to see if the table exists, I get an error 3265 Object Not
Found in this Collection

If (db.TableDefs("tblMyTable")) Then
' Do Something
Else
'Do Something else
End If

I could easily trap the error, and call a separate function to handle the
else part but that seems like a stupid workaround.

-David

 

[Dirk Goldgar]

*Sigh* I've searched everywhere for what I figured was an easy one
that I'm having a mental block on.

How do I test to see if an object in my database exists, say a table
or query.

If I try to test to see if the table exists, I get an error 3265
Object Not Found in this Collection

If (db.TableDefs("tblMyTable")) Then
' Do Something
Else
'Do Something else
End If

I could easily trap the error, and call a separate function to handle
the else part but that seems like a stupid workaround.

That's the way I've usually done it, though I segregate the code into a
function, "fncTableExists", to simplify its use in multiple locations.
Some people do argue that it's better to loop through the TableDefs
collection, or else CurrentData.AllTables, looking for an object with
the given name. That way, you can avoid raising an error at all.
However, I see no reason to shy away from error-handling, and I
believe -- without actually having done any benchmarking -- that using
the collection's index will be more efficient than looping through all
members.

 

[Guest]

That's the way I've usually done it, though I segregate the code into a
function, "fncTableExists", to simplify its use in multiple locations.
Some people do argue that it's better to loop through the TableDefs
collection, or else CurrentData.AllTables, looking for an object with
the given name. That way, you can avoid raising an error at all.
However, I see no reason to shy away from error-handling, and I
believe -- without actually having done any benchmarking -- that using
the collection's index will be more efficient than looping through all
members.

Ok good then. And I'd also tend to agree, that logically speaking, "failing
fast" by thowing an error and handling it should be faster than iterating
over the entire collection each time. (especially if you are doing a batch
of tables)

Thanks,

-David