Macro to Delete all Tables

[bethabernathy]

Hi - Is there a simple macro or query that can be run to delete all the tables in a database? Thanks, Beth

beth@integratedresourcemgmt.com

 

[PaulBricker]

Queries will only delete records from Tables and not the Tables themselves. In a macro, you could use the DeleteObject Action and delete a (singular) table
In a module, you could write a function and then call it from a Macro that would delete all tables in your Db. It would look like this.

Function delTables()
Dim tbl as TableDef
DoCmd.SetWarnings False
For Each tbl in CurrentDb.TableDefs
DoCmd.DeleteObject acTable, tbl.Name
Next tbl
DoCmd.SetWarnings True
End Function

You would put this in a module and then call it from a Macro using the RunCode Action.

Paul

 

[bethabernathy]

Hi and Thank You! I created the module and added the run code in the macro. I ran the macro and got a compile error

at:

tbl as TableDef

"user defined type not defined"

I named my module: "deletetables"

and put the following in the run code's (macro)function:

deltables ()

I must be doing something wrong. Thank you for your help. -Beth



beth@integratedresourcemgmt.com

 

[PaulBricker]

Beth, it sounds like a reference problem. Open your module in design view. On the menu bar got to Tools...References and look for the
Microsoft DAO 3.6 Object Library
now that's if your are using A2000. If your are using 97 then it would be DAO 3.51 and if your are using XP, it will be DAO 3.6 or 3.61. Not really sure but you should be able to find it. Then put and check mark in the box next to the reference and close the window. Then try running the code again. That should do it.

Paul

 

[bethabernathy]

Hi Paul - Thanks I set the Microsoft DAO 3.6 Object Library. When I run the function, I am getting a "run-time error 3211" that says "The database engine could not lock table "MySysAccessObjects" because it is already in use by another person or process."

It highlights this line of the code

DoCmd.DeleteObject acTable, tbl.Name

I don't have a table in the database named "MySysAccessObjects" do you know what I am doing wrong here? Thanks alot. I appreciate your help. -Beth


beth@integratedresourcemgmt.com

 

[PaulBricker]

OK, don't want to delete those. My sincere apologies. We need to test for your system tables or else we are going to delete some important stuff.

Function delTables()
Dim tbl as TableDef
DoCmd.SetWarnings False
For Each tbl in CurrentDb.TableDefs
If Left(tbl.Name,4) = "MSys" Then
'Do Nothing
Else
DoCmd.DeleteObject acTable, tbl.Name
Next tbl
DoCmd.SetWarnings True
End Function

I just ran it thru and it will leave the MSys tables alone.

 

[bethabernathy]

Hi Paul - Thanks I did finally find those MSys tables by changing the view. Anyway, thank you for your help. I ran the module and I got a compile error "next without for" highlighting this line:

Next tbl

I am trying to read everything I can find. So, i'll look around but if you know of a fix that would be great. Thanks again. -Beth



beth@integratedresourcemgmt.com

 

[bethabernathy]

Hi - I am getting closer:

Function DeleteImportErrorTablesNew()
Dim db As DAO.Database, t As DAO.TableDef, i As Integer
Set db = CurrentDb()
For i = db.TableDefs.Count - 1 To 0 Step -1
Set t = db.TableDefs(i)
If t.Name Like "ML*" Then
db.TableDefs.delete t.Name
End If
Next i
db.Close
End Function



I have tables that begin with ML & P so I created two functions and set them both to run in the macro using the run code and all gone. bye bye no more tables. Thank you very much. -Beth

beth@integratedresourcemgmt.com

 

[Ed2020]

I have to disagree that a query will only delete data from a table.

Using a Data Definition query the following SQL will delete a tabledef from the database:

"DROP TABLE TableName;"

Ed Metcalfe.

Please do not feed the trolls.....

 

[PaulBricker]

Ed, you are right. You can use the Drop Table statement in SQL.

Paul