• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 936
  • Last Modified:

Delete multiple tables in Access

I copied the code below from a solution someone posted on here to delete a single table.  Can someone show me how to modify this to delete multiple tables?  For example, let's say I have tables named Table1, Table2, and Table3.  Thanks.

Set db = CurrentDb()
 db.Execute "DROP TABLE Table1;"
 db.Close
0
dbfromnewjersey
Asked:
dbfromnewjersey
  • 3
  • 3
  • 2
  • +3
4 Solutions
 
JohnK813Commented:
You can set up this Sub:

Private Sub DeleteTable(myTable as String)
 Set db = CurrentDb()
 db.Execute "DROP TABLE " & myTable
 db.Close
End Sub

Then, in another Sub or Function, you would call DeleteTable with the appropriate table name:

Call DeleteTable("Table1")
Call DeleteTable("Table2")
Call DeleteTable("Table3")
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You can't delete more than one table with one statement.  So...

Set db = CurrentDb()
 db.Execute "DROP TABLE Table1;"
 db.Execute "DROP TABLE Table2;"
 db.Execute "DROP TABLE Table3;"
 db.Close
0
 
JohnK813Commented:
Another option.  Simpler, but not flexible:

Set db = CurrentDb()
 db.Execute "DROP TABLE Table1;"
 db.Execute "DROP TABLE Table2;"
 db.Execute "DROP TABLE Table3;"
 db.Close
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Great minds tend to think alike.
0
 
dbfromnewjerseyAuthor Commented:
I had already tried the code jimhorn and JohnK813 suggested but it does not work if one of the tables already does not exist.  Looks like once it doesn't find one of the tables,  it doesn't execute the rest of the code to delete the tables that do exist.  Am I wrong?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
On Error Resume Next
Set db = CurrentDb()
 db.Execute "DROP TABLE Table1;"
 db.Execute "DROP TABLE Table2;"
 db.Execute "DROP TABLE Table3;"
 db.Close
On Error Goto YourErrorHandling
0
 
Jokra_the_BarbarianCommented:
You can also do this:

delTables("Table1")
delTables("Table2")
delTables("Table3")

Public Sub delTables(tbl As String)
  On Error Resume Next
    Dim dbs As Database, tbl As TableDef, tdfs As TableDefs
    Set dbs = CurrentDb
    Set tdfs = dbs.TableDefs
   
    ' See if the name is in the Tables collection.
    If Len(tdfs(tbl).Name) Then tdfs.Delete (tbl)
    tdfs.Refresh
End Sub
0
 
Jim P.Commented:
Try this function. And it will even take wildcards:

Public Function DelTables(InputTableName As String)

Dim DB As database
Dim RS As Recordset
Dim SQL As String

SQL = "SELECT NAME " & _
        "FROM MSysobjects " & _
        "WHERE NAME <> 'MSys' " & _
        "AND TYPE = 1 " & _
        "AND NAME LIKE '" & InputTableName & "'"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = False Then
    RS.MoveFirst
    Do Until RS.EOF
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable, RS!name
        DoCmd.SetWarnings True
        RS.MoveNext
    Loop
End If
RS.Close

End Function

Then just put execute
DelTables("Table*")

Note that it should not be case-sensitive so if you have a bunch of tables such as
NAME
DIALED_030_DAY_CALL_TABLE
DIALED_060_DAY_CALL_TABLE
DIALED_090_DAY_CALL_TABLE
DIALED_120_DAY_CALL_TABLE
DIALED_120_PLUS_DAY_CALL_TABLE
Table1
Table2

They will all be deleted.
0
 
Simon BallCommented:

docmd.DeleteObject acTable, "table name1"

or for your 3 tables....

dim i as integer
dim tbl as string

for i = 1 to 3
tbl = "table" & i
docmd.DeleteObject acTable,tbl
next i


0
 
dbfromnewjerseyAuthor Commented:
Thanks folks. Lots to choose from.
0
 
dbfromnewjerseyAuthor Commented:
Just a note.........A couple of the last solutions I believe delete all tables.  I needed code where I specify the tables to be deleted because while I want to delete multiple tables, I do not want to delete ALL of the tables in my database.

Thanks
0
 
Simon BallCommented:
mine was just a loop to hoghlight the alternative code...
docmd.DeleteObject acTable,tblname

which in my loop would have deleted only your 3 tables...table1..table3
0
 
Jim P.Commented:
Mine could be specific or use wildcards

DelTables("Table?")

It will delete anything that is named Table and 1 character after such as
Table1
Table2
...
TableZ

But DelTables("Table*") would delete anything that started with Table.

But thank you for the points, and have a great day.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now