Delete rows in different tables

  • Thread starter Thread starter Smigidy
  • Start date Start date
S

Smigidy

I have a database that gets new information put into the tables every month.
I tried to create a delete query that would delete all the rows in all the
tables, but it wanted me to specify a table.

How do I delete all the rows in all the tables without creating a delete for
each specific one? This has been a great forum for me so far, I've had a
great deal of success so far.

Thx
Michael
 
You can't unless you specify the tables.

Of course, you can write some VBA that steps through all the tables (except
the system tables) and deletes all the records. That would look something
like the following.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Public Sub sDeleteAllTableData()
Dim dbAny As DAO.Database
Dim I As Long

Set dbAny = CurrentDb()
For I = 0 To dbAny.TableDefs.Count - 1
If (dbAny.TableDefs(I).Attributes And dbSystemObject) = 0 Then
dbAny.Execute "DELETE FROM [" & dbAny.TableDefs(I).Name & "]"
End If
Next I

End Sub




John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I copied and pasted your lines and I get this:
Invalid SQL statement; expected 'DELETE', INSERT', 'PROCEDURE', 'SELECT', or
'UPDATE'.

John Spencer MVP said:
You can't unless you specify the tables.

Of course, you can write some VBA that steps through all the tables (except
the system tables) and deletes all the records. That would look something
like the following.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Public Sub sDeleteAllTableData()
Dim dbAny As DAO.Database
Dim I As Long

Set dbAny = CurrentDb()
For I = 0 To dbAny.TableDefs.Count - 1
If (dbAny.TableDefs(I).Attributes And dbSystemObject) = 0 Then
dbAny.Execute "DELETE FROM [" & dbAny.TableDefs(I).Name & "]"
End If
Next I

End Sub




John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a database that gets new information put into the tables every month.
I tried to create a delete query that would delete all the rows in all the
tables, but it wanted me to specify a table.

How do I delete all the rows in all the tables without creating a delete for
each specific one? This has been a great forum for me so far, I've had a
great deal of success so far.

Thx
Michael
Click to expand...
Click to expand...
 
Hmm. It works for me.

Access 2003 sp2
Using an MDB or MDE

Perhaps copy and paste introduced some extraneous characters.

You did put this code into a VBA module and save the module with a name other
than sDeleteAllTableData()

That was VBA code it was not a SQL query statement.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
OK, I put it in a query and not a module. It worked.

Is there a way to click on the module then run, or do I have to open the
module itself and hit F5?
 
I had a switchboard, and now the switchboard table data is gone! Not cool,
but I'm learning. How do I delete all tables minus the switchboard stuff?
 
add the switchboard table to the exclude:
I've never & will never willingly use the built-in switchboard *crap* but
you must add something like
If (dbAny.TableDefs(I).Attributes And dbSystemObject = 0) or
dbany.tabledefs(i).name="switchboard" Then

or you could add the dbsystemobject property value to the table (but that's
for the next lesson)

hth
pieter
 
I have a database that gets new information put into the tables every month.
I tried to create a delete query that would delete all the rows in all the
tables, but it wanted me to specify a table.

How do I delete all the rows in all the tables without creating a delete for
each specific one? This has been a great forum for me so far, I've had a
great deal of success so far.
Click to expand...

You're getting some good advice on how to do this but... I'd suggest a simpler
approach that would avoid the chore of emptying the tables and dealing with
the ensuing database bloat.

Keep an empty template database with just the "carried over" data such as your
switchboard table (and any other permanent lookup tables).

Delete your working database .mdb file and just copy the template to the
"working" database name, using Windows Explorer.

Start using the clean, empty database.
 
Back
Top