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

How do I drop multiple tables in access 2007

Hi,

I have about 40 tables that are created during a process in an MS Access 2007 DB. But, i want the database to delete the tables, not just their contents when i push a button -' Delete All data'

I have made a table zzModelPointFiles that has all the table names of the tables I want to delete. I have a field Drop_Table which is true if I want the table dropped.

I have attached the code:

I have added a line on error Resume Next but this means the code runs through and none of the tables are deleted. i added this line just in case a table didn't exist when access was trying to delete it.

Any tips on how I can do this?

Thanks
Function Delete_Tables()

Dim MyDB As Database '...........................................................'current database
Dim myrec As Recordset '..........................................................' the recordset
Dim strSQL As String '.......................................................'sql string to delete records


Set MyDB = CurrentDb()
Set myrec = MyDB.OpenRecordset("SELECT * FROM zzModelPointFiles WHERE Drop_Table = True ")


strSQL = "DROP TABLE [" & strTableName_pas & "] ;"


DoCmd.SetWarnings False


myrec.MoveFirst
Do Until myrec.EOF

    
  On Error Resume Next
    DoCmd.RunSQL (strSQL)
    
    
    
myrec.MoveNext
Loop



DoCmd.SetWarnings True

myrec.Close




End Function

Open in new window

0
DB_newbie_NZ
Asked:
DB_newbie_NZ
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this


Function Delete_Tables()

Dim MyDB As Database '...........................................................'current database
Dim myrec As Recordset '..........................................................' the recordset
Dim strSQL As String '.......................................................'sql string to delete records


Set MyDB = CurrentDb()
Set myrec = MyDB.OpenRecordset("SELECT * FROM zzModelPointFiles WHERE Drop_Table = True ")


'strSQL = "DROP TABLE [" & strTableName_pas & "] ;"


DoCmd.SetWarnings False


myrec.MoveFirst
Do Until myrec.EOF

  On Error Resume Next
  strTableName_pas=myrec!NameOfFieldThatHaveTheNamesOFTables

strSQL = "DROP TABLE [" & strTableName_pas & "] ;"
    DoCmd.RunSQL (strSQL) 
    
myrec.MoveNext
Loop
DoCmd.SetWarnings True
myrec.Close

End Function

Open in new window


change NameOfFieldThatHaveTheNamesOFTables with the actual name of the field
0
 
Rey Obrero (Capricorn1)Commented:
Add to your references the Microsoft DA x.x object library



Function Delete_Tables()

Dim MyDB As Database '...........................................................'current database
Dim myrec As DAO.Recordset '..........................................................' the recordset
Dim strSQL As String '.......................................................'sql string to delete records
Dim strTableName_pas as String

Set MyDB = CurrentDb()
Set myrec = MyDB.OpenRecordset("SELECT * FROM zzModelPointFiles WHERE Drop_Table = True ")


'strSQL = "DROP TABLE [" & strTableName_pas & "] ;"


DoCmd.SetWarnings False


myrec.MoveFirst
Do Until myrec.EOF

  On Error Resume Next
  strTableName_pas=myrec!NameOfFieldThatHaveTheNamesOFTables

strSQL = "DROP TABLE [" & strTableName_pas & "] ;"
    DoCmd.RunSQL (strSQL) 
    
myrec.MoveNext
Loop
DoCmd.SetWarnings True
myrec.Close

End Function

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:


Add to your references the Microsoft DAO x.x object library
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Nick67Commented:
From the 'newbie' part of your handle I gather that Access is new to you??

<40 tables that are created during a process in an MS Access 2007 >

This is going to eventually give you grief in the form of a corrupted files.
While it seems like adding and dropping tables should have no net effect, things DO accumulate, somewhat.
You'd be well advised to find a different process to accomplish the task that presently requires creating and dropping so many tables.

You'd be definitely well advised to have good backups, and perhaps a 'template' app to deploy when that day arrives.
0
 
Rey Obrero (Capricorn1)Commented:
or you can use this



Function Delete_Tables()

Dim MyDB As DAO.Database '...........................................................'current database
Dim myrec As DAO.Recordset '..........................................................' the recordset
Dim strSQL As String '.......................................................'sql string to delete records
Dim strTableName_pas as String

Set MyDB = CurrentDb()
Set myrec = MyDB.OpenRecordset("SELECT * FROM zzModelPointFiles WHERE Drop_Table = True ")

myrec.MoveFirst
Do Until myrec.EOF

  On Error Resume Next
  strTableName_pas=myrec!NameOfFieldThatHaveTheNamesOFTables

strSQL = "DROP TABLE [" & strTableName_pas & "] ;"
MyDB.Execute strSQL,dbfailonerror
    
myrec.MoveNext
Loop

myrec.Close

End Function

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You could also replace :

strSQL = "DROP TABLE [" & strTableName_pas & "] ;"
MyDB.Execute strSQL,dbfailonerror

Open in new window

with

DBEngine(0)(0).TableDefs.Delete strTableName_pas

Open in new window


I totally agree with the previous advise that adding and deleting table will only cause you grief in the log run if your repeat the process.

If at all possible I would not delete the table but run a delete query to empty them. I would also keep the table in a separate back end. I would not have them local.

I regularly use DAO code to crate a temp/scratch  back end and the the tables. When done I simple delete the temp/scratch back end.

II you are creating your table with Make Table queries you can create the tables in the temp/scratch back end.

Example:
SELECT fieldlist INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename 

Open in new window



0
 
QlemoC++ DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now