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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Helena" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Helena" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On Wed, 18 Jul 2007 11:58:08 -0000, Helena <(E-Mail Removed)> wrote:

>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

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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Scott McDaniel" <scott@NoSpam_Infotrakker.com> wrote in message
news:(E-Mail Removed)...
>
> 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

On Wed, 18 Jul 2007 09:25:24 -0400, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote:

><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