Drop MS Access table if it exist

[tdrBates]

Can I run code similar to the example below?

If tablename exist then
Run.SQL "DROP TABLE tablename"
End if

 

[evalesthy]

Assuming that the table exists then the following deletes it.
You should check for table first and, depending on what you are
doing you may want to ask user whether they want to delete it.

db.Execute "DROP TABLE [Archive2];"

 

[mp9]

One, admittedly inelegant, way of checking a table exists is to make use of the hidden system file MSysObjects, thus:

If DCount("*", "MSysObjects","Name='Archive2' AND Type=1") = 1 Then
    DBEngine(0)(0).Execute "DROP TABLE [Archive2];"
End If

Another way that doesn't depend on the tiresome DCount is just to turn error-handling off before the DROP TABLE so that it doesn't fall over when the non-existant table can't be deleted. Don't forget to turn it back on again afterwards...

http://www22.brinkster.com/accessory/

 

[evalesthy]

Agreed. Or, you can use the tabledef approach:

For Each tdf In db.TableDefs()
If tdf.Name = "Archive2" Then
'drop table here
Next tdf