Check if a table exists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to check if a table exists and if not then create it. I know how to
create the table, I dont know how to check if the table exists. How do I do
that?
Thanks
 
in message:
I want to check if a table exists and if not then create it. I know how to
create the table, I dont know how to check if the table exists. How do I do
that?
Thanks
Click to expand...

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

' This function will check to see if a
' table exists within the current database
' Similar to IsLoaded function it will return True or False
' Jeff Conrad - Access Junkie
' Usage:
' If funcTableExists("SomeTable") = True Then
' ' Table Exists
' Else
' ' Table does not exist
' End If
' **Requires DAO Reference be set**

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
Exit For
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
 
Jeff,
What an excellent piece of code, thanks.

Jeff Conrad said:
in message:


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

' This function will check to see if a
' table exists within the current database
' Similar to IsLoaded function it will return True or False
' Jeff Conrad - Access Junkie
' Usage:
' If funcTableExists("SomeTable") = True Then
' ' Table Exists
' Else
' ' Table does not exist
' End If
' **Requires DAO Reference be set**

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
Exit For
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
Click to expand...
 
Shorter/faster way....

Function TableExists(TableName) As Boolean
Dim db as Database
Dim tdf as TableDef

On Error Resume Next

Set db = currentdb()
Set tdf = db.TableDefs(TableName)

TableExists = (err.Number = 0)

Exit Function
 
in message:
Shorter/faster way....

Function TableExists(TableName) As Boolean
Dim db as Database
Dim tdf as TableDef

On Error Resume Next

Set db = currentdb()
Set tdf = db.TableDefs(TableName)

TableExists = (err.Number = 0)

Exit Function
Click to expand...

Yep, lots of ways to solve this issue.
Thanks Paul.
 
Back
Top