469,273 Members | 1,593 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,273 developers. It's quick & easy.

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?
Apr 23 '09 #1
14 41654
NeoPa
32,171 Expert Mod 16PB
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.
Apr 23 '09 #2
Krandor
50
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.
Apr 23 '09 #3
NeoPa
32,171 Expert Mod 16PB
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.
Apr 23 '09 #4
Krandor
50
@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.
Apr 24 '09 #5
NeoPa
32,171 Expert Mod 16PB
@Krandor
Good thinking Krandor :)
Apr 24 '09 #6
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.
Apr 25 '09 #7
NeoPa
32,171 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Const conClearSQL As String = "DELETE * FROM [%T]%W;"
  2.  
  3. 'ClearTable clears the named table.  A WHERE string is used if passed.
  4. Public Sub ClearTable(strTable As String, Optional strWhere As String = "")
  5.     Dim strSQL As String
  6.  
  7.     On Error GoTo CTError
  8.     strTable = CurrentDb.TableDefs(strTable).Name
  9.     On Error GoTo 0
  10.  
  11.     strSQL = Replace(Replace(conClearSQL, "%T", strTable), _
  12.                      "%W", IIf(strWhere = "", "", " WHERE " & strWhere))
  13.     Call DoCmd.RunSQL(strSQL)
  14.     Exit Sub
  15.  
  16. CTError:
  17.     Call MsgBog(Prompt:="Invalid table {" & strTable & "}", _
  18.                 Title:="ClearTable")
  19. End Sub
Apr 25 '09 #8
ADezii
8,800 Expert 8TB
@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:
Expand|Select|Wrap|Line Numbers
  1. 'Will DELETE ALL Records in 3 Tables
  2. 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)
Apr 25 '09 #9
NeoPa
32,171 Expert Mod 16PB
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.
Apr 25 '09 #10
ADezii
8,800 Expert 8TB
@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!
Apr 25 '09 #11
NeoPa
32,171 Expert Mod 16PB
Of course ;)

PS. I missed your turning 60. Happy Birthday my friend. Hope all is well with you and yours.
Apr 25 '09 #12
ADezii
8,800 Expert 8TB
@NeoPa
Thanks NeoPa, all is well with me and I hope that the same can be said of you.
Apr 25 '09 #13
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.
Expand|Select|Wrap|Line Numbers
  1. Select Distinct Name as Table_Name
  2. From MSysObjects
  3. Where Type in (1,4,6)
  4. 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
Expand|Select|Wrap|Line Numbers
  1. Function RunDelValuesInTables()
  2. 'you can put in error handling later'
  3.  
  4. Dim dbs as doa.Database
  5. Dim qdf as doa.QueryDef
  6. Dim rst as Recordset
  7. Dim strRunQry as String: strRunQry = ""
  8.  
  9. Set dbs = CurrentDb
  10. Set qdf = dbs.QueryDefs("xQuery_List_of_Existing_Tables")
  11. Set rst = qdf.OpenRecordSet
  12.  
  13. rst.MoveFirst
  14.  
  15.      Do While Not rst.EOF
  16.       strRunQry = rst.Fields(0)
  17.  
  18.      strRunQry = "Delete from " & strRunQry
  19.  
  20.      dbs.Execute strRunQry
  21.  
  22.      rst.MoveNext
  23.  
  24.      Loop
  25.  
  26.      response = MsgBox ("Data in all tables has been removed ."
  27. End Function
Hope this is helpful to someone.
Kind regards,
Exstreamliners
3 Weeks Ago #14
NeoPa
32,171 Expert Mod 16PB
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.
3 Weeks Ago #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Craig Westerman | last post: by
2 posts views Thread by Jim Devenish | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.