user1688194 user1688194 - 4 years ago 235
SQL Question

VBA Script to check if table exist on MS ACCESS, Delete if it does

I need to implement a check to see if TableA exists, if it does, drop the entire table. If it doesn't, i will create the table. I couldn't really find out if this is possible to implement on VBA / MS Access.

In SQL we can use:

DROP TABLE IF EXISTS dbo.TableA


Anybody has any idea how this can be implemented? Thank you!

Answer Source

Consider using the TableDefs collection where you iterate through items and conditionally drop or create using same DDL SQL statements passed in code.

Dim db As Database 
Dim tbldef As TableDef

Set db = CurrentDb

For each tbldef in db.TableDefs 
   If tbldef.Name = "TableName" Then 
      db.Execute "DROP TABLE " & tbldef.Name, dbFailOnError
   End if 
Next tbldef

db.Execute "CREATE TABLE TableName (...rest of SQL...);", dbFailOnError    

' UNINITIALIZE OBJECTS 
Set tbldef = Nothing 
Set db = Nothing
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download