Existence of table

[Laura]

I am in the process of writing a routine that uses CreateTableDef("tblName")
but am unable to figure out how to check if a table already exists - I get
an error message, obviously, if I try to create it a second time.
Laura

 

[Rick Brandt]

I am in the process of writing a routine that uses CreateTableDef("tblName")
but am unable to figure out how to check if a table already exists - I get
an error message, obviously, if I try to create it a second time.
Laura

Function TableExists(strTableName As String) As Boolean

Dim tbl As TableDef
Dim MyDB As Database

Set MyDB = CurrentDb

For Each tbl In MyDB.TableDefs
If tbl.Name = strTableName Then TableExists = True
Next tbl

Set MyDB = Nothing

End Function

 

[Van T. Dinh]

You can simply try to delete the existing Table but make sure you trap the
error if the Table doesn't exist.

Something like:

On Error Resume Next
DoCmd.DeleteObject acTable, "Whatever"

 

[Laura]

Hi shockley,

I appreciate your input, but I'm too new to Access still to be able to
implement your suggestion without having more of the code set out.

I presume you mean something like this:-

Dim dbs As Database
Set dbs = workspaces(0).OpenDatabae("db.mdb") ... not sure what
"db.mdb" is
On Error Resume Next
sErrorText = dbs.TableDefs("customers".Name ... not sure what
sErrorText is
if Err = 0 Then
MsgBox("Database exists")
End if

Sorry, but stuggling with the basics, as you can see. Thanks for your help.
Laura