Deleting Tables

[Guest]

Some help needed for a VBA novice.

My code starts by deleting some tables. That's OK so long as the table has
not already been deleted. So I need some code which deletes a table but only
if the table exists - something like........

IF my table exists THEN
Delete my table
END IF

Any help much appreciated - as ever!

Jim Jones
Botswana

 

[Guest]

IF my TableExists("MyTable") THEN
DoCmd.DeleteObject acTable, "MyTable"
END IF

Public Function TableExists(strTableName As String) As Boolean
Dim tdfs As TableDefs
Dim tdf As TableDef

TableExists = False
Set tdfs = CurrentDb.TableDefs
For Each tdf In tdfs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Set tdfs = Nothing

End Function

 

[Allen Browne]

Access keeps a list of the database objects in a table called MSysObjects.
The local tables in there are are type 1, and the Name column contains the
table name. DLookup() returns Null if the table does not exist.

So:
If Not IsNull(DLookup("ID", "MSysObjects", _
"([Name] = ""Table1"") AND ([Type] = 1)")) Then
dbEngine(0)(0).Execute "DROP TABLE [Table1];", dbFailOnError
End If

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

 

[Guest]

Allen

Many thanks - again!

Jim Jones

Access keeps a list of the database objects in a table called MSysObjects.
The local tables in there are are type 1, and the Name column contains the
table name. DLookup() returns Null if the table does not exist.

So:
If Not IsNull(DLookup("ID", "MSysObjects", _
"([Name] = ""Table1"") AND ([Type] = 1)")) Then
dbEngine(0)(0).Execute "DROP TABLE [Table1];", dbFailOnError
End If

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Some help needed for a VBA novice.

My code starts by deleting some tables. That's OK so long as the table
has
not already been deleted. So I need some code which deletes a table but
only
if the table exists - something like........

IF my table exists THEN
Delete my table
END IF

Any help much appreciated - as ever!

Jim Jones
Botswana

 

[Guest]

Klatuu

Many thanks

Jim Jones

IF my TableExists("MyTable") THEN
DoCmd.DeleteObject acTable, "MyTable"
END IF

Public Function TableExists(strTableName As String) As Boolean
Dim tdfs As TableDefs
Dim tdf As TableDef

TableExists = False
Set tdfs = CurrentDb.TableDefs
For Each tdf In tdfs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Set tdfs = Nothing

End Function