Delete all records from multiple tables in one shot

[Alireza355]

Dear all,

Is there a way to dalete all records of several tables using one DELETE query?

[NeoPa]

I don't believe so.

It may be possible if you can link the tables together into an updatable query, but even then I think only one of the tables can be cleared.

[Krandor]

Neo is correct. There is no SQL command for deleting records from more than one table.

That said, it can still be done in Access. Kinda klunky but still doable.

Create and save one delete query for each table you want to purge. Then create a macro that triggers each of the queries.

So when you want to purge all of your tables, just run the macro.

I generally don't like macros but this would be the easiest solution for this problem.

[NeoPa]

If a single query is not possible, then I would suggest a VBA loop would probably be easier. It need not repeat each command, as the various tables can be processed within the loop.

[Krandor]

@NeoPa
I agree with Neo. A VBA loop would be a much better solution than creating a macro. I suggested the macro only because it works for people with no VBA programming skills also.

[NeoPa]

@Krandor
Good thinking Krandor :)

[Alireza355]

Dear all,

Thank you so much for your kind support.

I got it this way:

Docmd.runSQL "DELETE * from Table1"
DoEvents
Docmd.runSQL "DELETE * from Table2"
DoEvents
Docmd.runSQL "DELETE * from Table3"
DoEvents
Docmd.runSQL "DELETE * from Table4"
DoEvents

and it works perfect.

[NeoPa]

Hi Ali.

Pleased you found a good way to handle this. I find this such a common requirement in my projects that I have a procedure in one of my modules to handle this.

Private Const conClearSQL As String = "DELETE * FROM [%T]%W;"
 
'ClearTable clears the named table.  A WHERE string is used if passed.
Public Sub ClearTable(strTable As String, Optional strWhere As String = "")
    Dim strSQL As String
 
    On Error GoTo CTError
    strTable = CurrentDb.TableDefs(strTable).Name
    On Error GoTo 0
 
    strSQL = Replace(Replace(conClearSQL, "%T", strTable), _
                     "%W", IIf(strWhere = "", "", " WHERE " & strWhere))
    Call DoCmd.RunSQL(strSQL)
    Exit Sub
 
CTError:
    Call MsgBog(Prompt:="Invalid table {" & strTable & "}", _
                Title:="ClearTable")
End Sub

[ADezii]

@Alireza355
Under certain, precise, conditions a single line of code can DELETE ALL Records in Multiple Tables. This depends on the Relationships between the Tables, Referential Integrity, and whether or not Cascade Deletes are in effect. For example, the following line of code will DELETE ALL Records in the Customers, Orders, and Order Details Tables of the Northwind sample Database. The Relationships are defined below the Code Example:

'Will DELETE ALL Records in 3 Tables
CurrentDb.Execute "Delete * From Customers", dbFailOnError

[Customers].[CustomerID]{1} ==> [Orders].[CustomerID]{MANY}
(CASCADE DELETES in effect)

[Orders.OrderID]{1} ==> [Order Details.OrderID]{MANY}
(CASCADE DELETES in effect)

[NeoPa]

Good point ADezii. This is possibly appropriate in the OP's case, but they would need to determine that themselves.

I ask you this though, Was it worth posting and losing your post-count of 4,567? Was it? I don't know :D

It's gone for good now anyway.

[ADezii]

@NeoPa
Only you would notice something like that (LOL)! I've just started working on a Post Count of 5,678, GOD willing of course!

[NeoPa]

Of course ;)

PS. I missed your turning 60. Happy Birthday my friend. Hope all is well with you and yours.

[ADezii]

@NeoPa
Thanks NeoPa, all is well with me and I hope that the same can be said of you.

[Exstreamliners]

If a single query is not possible, then I would suggest a VBA loop would probably be easier. It need not repeat each command, as the various tables can be processed within the loop.

This is how I do it in a MS Access Database

1. Create a normal query with the following syntax.

Select Distinct Name as Table_Name
From MSysObjects
Where Type in (1,4,6)
and left(name,4) = "tbl_"    <--- use this if you want specific tables

Name the query xQuery_List_of_Existing_Tables and save it.

2. Create a vba module with the following

Function RunDelValuesInTables()
'you can put in error handling later'
 
Dim dbs as doa.Database
Dim qdf as doa.QueryDef
Dim rst as Recordset
Dim strRunQry as String: strRunQry = ""
 
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("xQuery_List_of_Existing_Tables")
Set rst = qdf.OpenRecordSet
 
rst.MoveFirst
 
     Do While Not rst.EOF
      strRunQry = rst.Fields(0)
 
     strRunQry = "Delete from " & strRunQry
 
     dbs.Execute strRunQry
 
     rst.MoveNext
 
     Loop
 
     response = MsgBox ("Data in all tables has been removed ."
End Function

Hope this is helpful to someone.
Kind regards,
Exstreamliners

[NeoPa]

ExStreamLiners:
This is how I do it in a MS Access Database

Fair enough, but be very clear this is my suggestion for handling something similar after realising the original request - to do it in a single QueryDef - was not possible.

I would add that, in view of the original request being to have all done together, I suspect transaction handling would be an essential part of any solution.