Table exist

[jeanulrich00]

Hi

A simple question

On a form, I have a button that create a table when someone click on
it "DoCmd.open query "
But I want to add something that will stop the code if the table
already exist

Something like

If the table exist then
msgbox "The table already exist"
else
DoCmd.open query "...."

So what is the right sentence for "if the table exist"

thanks

 

[John]

You could use for instance the following function:

Public Function tableExists(strTableName As String) As Boolean
Dim dbClient As Database
Dim tdf As TableDef
Set dbClient = CurrentDb

For Each tdf In dbClient.TableDefs
If tdf.Name = strTableName Then
tableExists = True
Exit Function
End If
Next
tableExists = False
End Function

You can call the function like this:

If tableExists("YourTableName") = True Then
MsgBox "Yes, it exists."
Else
MsgBox "No, it does not exist."
End If

john

 

[Douglas J. Steele]

If IsNull(DLookup("Name", "MSysObjects", _
"Name='xxxx' And Type IN (1,4,6)")) Then
DoCmd.open query "...."
Else
MsgBox "The table already exist"
End If

Replace xxxx with the name of the table (remember to include the single
quotes)

MSysObjects is a system table that exists in all databases (it's normally
hidden). Type 1 refers to tables in the current database, type 4 refers to
tables linked using ODBC, type 6 refers to all other linked tables.

 

[Krzysztof Pozorek [MVP]]

1. TableDefs collection (...)

2. MSysObjects table (...)

3. And one more possibility:
Dim Table_exists As Boolean
WizHook.Key = 51488399
Table_exists = WizHook.GetCurrentView("Table1")

K.P. MVP, Poland
www.access.vis.pl