ACC2000: Sample Code to Check for Table or Query in a Database

Option Explicit
					

'********************************************************
' FUNCTION: IsTableQuery()
'
' PURPOSE: Determine if a table or query exists.
'
' ARGUMENTS:
'   DbName: The name of the database. If the database name
'           is "" the current database is used.
'    TName: The name of a table or query.
'
' RETURNS: True (it exists) or False (it does not exist).
'
'********************************************************

Function IsTableQuery(DbName As String, TName As String) As Integer

   Dim Db As DAO.Database, Found As Integer, Test As String
   Const NAME_NOT_IN_COLLECTION = 3265

   ' Assume the table or query does not exist.
   Found = False

   ' Trap for any errors.
   On Error Resume Next

   ' If the database name is empty...
   If Trim$(DbName) = "" Then
      ' ...then set Db to the current Db.
      Set Db = CurrentDb()
   Else
      ' Otherwise, set Db to the specified open database.
      Set Db = DBEngine.Workspaces(0).OpenDatabase(DbName)

      ' See if an error occurred.
      If Err Then
         MsgBox "Could not find database to open: " & DbName
         IsTableQuery = False
         Exit Function
      End If
   End If

   ' See if the name is in the Tables collection.
   Test = Db.TableDefs(TName).Name
   If Err <> NAME_NOT_IN_COLLECTION Then Found = True

   ' Reset the error variable.
   Err = 0

   ' See if the name is in the Queries collection.
   Test = Db.QueryDefs(TName$).Name
   If Err <> NAME_NOT_IN_COLLECTION Then Found = True

   Db.Close

   IsTableQuery = Found

End Function
					

?IsTableQuery("","Invoices")