HOW CAN I CLEAR MULTIPLE TABLES WITH ONE QUERY?

[Guest]

HOW CAN I CLEAR MULTIPLE TABLES WITH ONE QUERY?

 

[Todd Shillam]

I usually just create several 'delete' queries. Afterwards, I just create a macro to run the delete queries in whichever sequence I need. In the macro you can also disable warnings too.

Best regards,

Todd
HOW CAN I CLEAR MULTIPLE TABLES WITH ONE QUERY?

 

[John Vinson]

HOW CAN I CLEAR MULTIPLE TABLES WITH ONE QUERY?

first off... please lay off the CAPS LOCK. It's hard to read and looks
like you're SHOUTING at us.

You can't, not in one query. You'll need as many queries as you have
tables; but they can all be run from a Macro (using the RunQuery
action) or from VBA code. If this is a repetitive activity, just
program it so the user just needs to click a button or perform some
other appropriate action.

John W. Vinson[MVP]

 

[Nick Coe \(UK\)]

In cc typed:

HOW CAN I CLEAR MULTIPLE TABLES WITH ONE QUERY?

Further to Todd's and John's posts;

Here's some _untested_ code you could base a macro or vba
call on to empty a series of tables. You would call the
function thus ClrTbl("tblMyOne").

Again - I haven't tested it and it's early for me use
with caution.

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

 

[Nick Coe \(UK\)]

In cc typed:

HOW CAN I CLEAR MULTIPLE TABLES WITH ONE QUERY?

Damn, forgot to paste the code... )

Public Function ClrTbl(strTblName As String) As Boolean
On Error GoTo ClrTbl_Err

'Nick Coe, 1, Untested, 081205

ClrTbl = False

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM " & strTblName & ";"
DoCmd.SetWarnings True

ClrTbl = True

ClrTbl_Exit:
Exit Function

ClrTbl_Err:
MsgBox "Delete contents of " & strTblName & " error" &
vbCrLf & Err.Description
Resume ClrTbl_Exit

End Function


--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

 

[Douglas J. Steele]

Since it's possible (although certainly not advisable!) for table names to
include embedded spaces, it would be better to use:

DoCmd.RunSQL "DELETE * FROM [" & strTblName & "]"

There's no need for the semi-colon at the end of the SQL statement.

 

[Nick Coe \(UK\)]

In Douglas J. Steele typed:

Since it's possible (although certainly not advisable!)
for table
names to include embedded spaces, it would be better to
use:

DoCmd.RunSQL "DELETE * FROM [" & strTblName & "]"

There's no need for the semi-colon at the end of the SQL
statement.

Thanks Doug...


--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----