how to find if a table is in a database?

  • Thread starter Thread starter Frederick Wilson
  • Start date Start date
F

Frederick Wilson

Currently I am cycling through all the tables in the database to find
out if a particular table is present. Is there a way to directly query
to find out if a table is present?


Thank you,
Fred
 
Query to find out if a table exists in the database???
Not sure about that.

I just use a little function I wrote to check for this type of thing.
Just copy/paste into a new standard module.
Is this what you are looking for?

Public Function funcTableExists(strTable As String) As Boolean
On Error GoTo ErrorPoint

' Jeff Conrad - Access Junkie
' © 2004 Conrad Systems Development
' This function will check to see if a table exists within
' the current database.
' Must have reference set to DAO object library
' Similar to IsLoaded function it will return True or False
' Call like so:
' If funcTableExists("TableNameHere") = True Then
' ' Table Exists
' Else
' ' Table does not exist
' End If

Dim db As DAO.Database
Dim doc As DAO.Document

Set db = CurrentDb()

With db.Containers!Tables
For Each doc In .Documents
If doc.Name = strTable Then
funcTableExists = True
End If
Next doc
End With

ExitPoint:
On Error Resume Next
Set db = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Function
 
Frederick said:
Currently I am cycling through all the tables in the database to find
out if a particular table is present. Is there a way to directly query
to find out if a table is present?
Click to expand...


Even though it hasn't change in twelve years, this is not
guaranteed to true in the future.

You can query the MSysObjects table to retrieve all table
names:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = 1

or determine if a string is the name of an existing table:

SELECT Count(*)
FROM MSysObjects
WHERE [Type] = 1
AND [Name] = "mytablename"

which will return a single record with a single field
containing a 0 if mytablename is not a table name or a 1 if
it is.
 
Back
Top