• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Deletes all tables in an Access database (hidden system tables are handled correctly)

Deletes all tables in an Access database (hidden system tables are handled correctly)

Deletes all tables in an Access database (hidden system tables are handled correctly)

Rate Deletes all tables in an Access database (hidden system tables are handled correctly)

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'delete tables from

'*******************************************************
'DropTables:
'   Drops all tables in the database.
'*******************************************************
Public Sub DropTables()

Dim rs As DAO.Recordset, tdf As TableDef

If MsgBox("Warning! All tables will be deleted.", vbYesNo, "Database = " & CurrentDb.Name) = vbNo Then
    Exit Sub
End If

For Each tdf In CurrentDb.TableDefs
    If IsSystemTable(tdf.Name) = False Then
       Call CurrentDb.Execute("DROP TABLE " & tdf.Name)
    End If
Next
End Sub

'*******************************************************
'IsSystemTable
'   Checks if the named table is a system table.
'   System tables begin with "MSys" and sometimes
'   with "~sq_" when the database hasnt been compacted.
'*******************************************************
Public Function IsSystemTable(TblName As String) As Boolean
IsSystemTable = IIf(InStr(1, "MSys ~sq_", Left(TblName, 4), vbTextCompare) <> 0, True, False)
End Function