Delete all records from multiple tables

H

Helena

Hi

I have a database with 22 tables from which I need to clear down all
of the records before importing fresh data. There is no criteria for
keeping / deleting specific records. I just want to do the equivalent
of opening the table, clicking in the top left and pressing delete.

I know that I can build a delete query to do this, but when I tried to
build a delete query showing more than one table I got a 3086 error.

Is there any way that I can do this other than building 22 different
delete queries and a macro to run them all?

Thanks
Helena
 
A

Allen Browne

You cannot use multiple DELETEs in one query.

You can programmatically delete, e.g.:
Dim db As DAO.Database
Set db = dbEngine(0)(0)
db.Execute "DELETE FROM Table1;", dbFailOnError
db.Execute "DELETE FROM Table2;", dbFailOnError
and so on.

You can avoid some of this where you have cascading deletes (if that's
applicable.) Otherwise, you may need to delete in a particular order. For
example, you can't delete the Orders until the OrderDetails are deleted.
 
D

Douglas J. Steele

Sub DeleteFromAllTables()
On Error GoTo Err_DeleteFromAllTables
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
dbCurr.Execute "DELETE * FROM [" & tdfCurr.Name & "]", dbFailOnError
End If
Next tdfCurr

End_DeleteFromAllTables:
Set dbCurr = Nothing
Exit Sub

Err_DeleteFromAllTables:
MsgBox Err.Number & ": " & Err.Description
Resume End_DeleteFromAllTables

End Sub

Note that if you've set up referential integrity between tables, this will
fail, since the order in which you delete the data will matter.
 
S

Scott McDaniel

Hi

I have a database with 22 tables from which I need to clear down all
of the records before importing fresh data. There is no criteria for
keeping / deleting specific records. I just want to do the equivalent
of opening the table, clicking in the top left and pressing delete.

I know that I can build a delete query to do this, but when I tried to
build a delete query showing more than one table I got a 3086 error.

Is there any way that I can do this other than building 22 different
delete queries and a macro to run them all?

You'd have to use VBA to do this. This assumes you have a reference to Microsoft DAO xx library, where "xx" is the
specific version (like 3.6):

Public Function ClearAllData() As Boolean

Dim tdf As DAO.TableDef
Dim dbs as DAO.Database

Set dbs = CurrentDB
For Each tdf in dbs.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
dbs.Execute "DELETE * FROM " & tdf.Name
End IF
Next tdf

Set dbs = Nothing

End Function

Note that we bypass the system tables by checking the first 4 letters of the name; anything beginning in MSys is not
processed.

You could call this in the Click event of a button, for example:

Public Sub cmdClearData_Click()

If Msgbox("Are you sure you want to clear all data? This operation is NOT reversible!!",vbYesNoCancel +
vbExclamation, "Confirm Data Deletion") <> vbYes Then Exit Sub

If ClearAllData Then MsgBox "Your data has been deleted. Please perform a Compact and Repair (Tools - Database
Utilities - Compact and Repair) to reclaim wasted space.", vbOkOnly + vbINformation, "Data Deletion Complete"

End Sub
Thanks
Helena

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
D

Douglas J. Steele

<picky>
Since it's possible for table names to have embedded blanks, you need

dbs.Execute "DELETE * FROM [" & tdf.Name & "]"
</picky>
 
S

Scott McDaniel

<picky>
Since it's possible for table names to have embedded blanks, you need

dbs.Execute "DELETE * FROM [" & tdf.Name & "]"
</picky>

Picky is good ... thanks for the clarification!

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top