Tables/Queries Exists() Function

[BrianDP]

I'm hoping someone can help me with an Exists() function.. I want to
know if tables, queries, or reports - and I'm not really even going to
ask about tables.. Just queries and reports.. But I thought this could
be all inclusive, if you ask it about a table, it could return a good
yes or no to that as well.. Here is my first stab that doesn't seem
to work right no matter what I do!

Public Function eXists(objectName As String) As Boolean
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()

Set rst = db.OpenRecordset("select * from [msysobjects] where
[msysobjects].[name] = """ & objectName & """")

If rst.NoMatch Then
eXists = False
Else
eXists = True
End If

End Function


PS. I apologize if this has been answered before. I glanced on the
web search, and I looked again in Groups, and didn't find the answer.
I Aplogize in advance if I"ve ruffled any feathers with this queriy.

 

[David-W-Fenton]

om:

I'm hoping someone can help me with an Exists() function.. I want
to know if tables, queries, or reports - and I'm not really even
going to ask about tables.. Just queries and reports.. But I
thought this could be all inclusive, if you ask it about a table,
it could return a good yes or no to that as well.. Here is my
first stab that doesn't seem to work right no matter what I do!

The NoMatch property is associated only with the Find operations.
That is, you use it after .FindFirst, .FindNext or .FindPrevious. It
has no meaning in any other context.

You should be checking the .Recordcount property, and if it's more
than 0, your object is found.

Public Function eXists(objectName As String) As Boolean
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()

Set rst = db.OpenRecordset("select * from [msysobjects] where
[msysobjects].[name] = """ & objectName & """")

If rst.NoMatch Then
eXists = False
Else
eXists = True
End If

End Function

For more TableExists() functions (but not QueryDefs or any other
objects), see a post of mine on StackOverflow.com, where I post
three alternative methods to implement the function, and compare
performance of the three methods:

http://tinyurl.com/26r847v =>
http://stackoverflow.com/questions/2985513/check-if-access-table-exis
ts/2992743#2992743

 

[David-W-Fenton]

According to VBA Help, NoMatch can be used with the Seek and
the four Find methods.

What's the fourth Find method?

Seek can only be used on Table type recordsets and the Find
methods can be used with Dynaset and Snapshot recordsets.

....which is why I didn't consider it worth mentioning Seek, since
Seek shouldn't be used except in very, very rare circumstances.