By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,604 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,604 IT Pros & Developers. It's quick & easy.

Delete all records from multiple tables in one shot

Alireza355
P: 86
Dear all,

Is there a way to dalete all records of several tables using one DELETE query?
Apr 23 '09 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,709
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

P: 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
Expert Mod 15k+
P: 31,709
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

P: 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
Expert Mod 15k+
P: 31,709
@Krandor
Good thinking Krandor :)
Apr 24 '09 #6

Alireza355
P: 86
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
Expert Mod 15k+
P: 31,709
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
Expert 5K+
P: 8,680
@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
Expert Mod 15k+
P: 31,709
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
Expert 5K+
P: 8,680
@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
Expert Mod 15k+
P: 31,709
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
Expert 5K+
P: 8,680
@NeoPa
Thanks NeoPa, all is well with me and I hope that the same can be said of you.
Apr 25 '09 #13

Post your reply

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