Tables/Queries Exists() Function

B

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. :)
 
D

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
 
M

Marshall Barton

David-W-Fenton said:
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.


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

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

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.
 
M

Marshall Barton

David-W-Fenton said:
Marshall Barton wrote


What's the fourth Find method?
FindLast


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

Very true, but in those rare situations, NoMatch does have
an important meaning in the Seek context.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top