Check if a table exists

[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

 

[Jeff Conrad]

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

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

 

[Guest]

Jeff,
What an excellent piece of code, thanks.

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

 

[Jeff Conrad]

in message:

Jeff,
What an excellent piece of code, thanks.

You're welcome, glad I could help.
And thank you for the kind words.

 

[Paul Overway]

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

 

[Jeff Conrad]

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

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