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

Question

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!

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

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

Sign up