CODEWITHSUNDEEP
sqlvbams-access
user-DY
user-DY

Reputation: 123

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!

Upvotes: 1

Views: 13157

Answers (2)

C Suttle
C Suttle

Reputation: 51

There is a much easier way to do this than the code above.

On Error Resume Next
  currentdb.execute "DROP TABLE TABLENAME;"
On Error Goto 0

What this does is tries to delete the table and skips the line if an error is generated because the table does not exists. Only 3 lines and runs faster.

Upvotes: 4

Parfait
Parfait

Reputation: 107687

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

Upvotes: 0

Related Questions