Delete All Rows Each Table

[Joe K.]

I have a MS-Access (2000) database that has approximately 20 tables.

I periodically would like to delete all rows from each table in the database.

Please help me create MS-Access program to complete this task.

Thanks,

 

[Daniel Pineault]

You can loop through the table collection and then perform a sim Query to
wide each of them.


The following will loop through the table collection 1 by 1
'---------------------------------------------------------------------------------------
' Procedure : listTables
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return a listing of all the tables in the database
' Copyright : The following code may be used as you please, but may not be
resold, as
' long as the header (Author, Website & Copyright) remains with
the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bShowSys - True/False whether or not to include system tables in the list
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2008-June-01 Initial Release
'---------------------------------------------------------------------------------------
Function listTables(bShowSys As Boolean) As String
On Error GoTo listTables_Error
Dim db As DAO.Database
Dim td As DAO.TableDefs

Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td 'loop through all the fields of the tables
If Left(t.Name, 4) = "MSys" And bShowSys = False Then GoTo Continue
Debug.Print t.Name
Continue:
Next

Set td = Nothing
Set db = Nothing
If Err.Number = 0 Then Exit Function

listTables_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: listTable" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
Exit Function
End Function

Then you need to replace the Debug.Print t.Name with code to execute an
delete query something like

CurrentDb().OpenRecordset ("DELETE * FROM " & t.Name)

Please check the query code (just off the top of my head).

Oh ya, in this case make sure your input variable for the function is False
as you do not want to blank system table. Make a backup of your db before
proceeding any further!
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

 

[Paolo]

Hi Joe K,
here's the code you need to accomplish your task

Dim i As Long
Dim td As TableDefs

Set td = CurrentDb.TableDefs
end_tbs = td.Count - 1
For i = 1 To end_tbs
'''******* the if is to avoid deleting data in the system tables
If Left(td(i).Name, 4) <> "msys" Then
CurrentDb.Execute ("delete * from " & td(i).Name)
End If
Next i
Set td = Nothing

HTH Paolo

 

[Daniel Pineault]

Here are 2 functins that should do the trick

'---------------------------------------------------------------------------------------
' Procedure : WipeTable
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Empty all the data of the specified table
' Copyright : The following code may be used as you please, but may not be
resold, as
' long as the header (Author, Website & Copyright) remains with
the code.
'
' Imput variables:
' ---------------
' strTblName: Name of the table to delete all the records in.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2008-Feb Initial Release
'---------------------------------------------------------------------------------------
Function WipeTable(strTblName As String) As String
On Error GoTo WipeTable_Error

DoCmd.SetWarnings False 'Turn off confirmation prompt to user

DoCmd.RunSQL ("DELETE [" & strTblName & "].* FROM [" & strTblName & "];")

DoCmd.SetWarnings True 'Turn back on confirmation prompt to user

WipeTable = True

If Err.Number = 0 Then Exit Function

WipeTable_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: WipeTable" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured! "
Exit Function
End Function




'---------------------------------------------------------------------------------------
' Procedure : WipeTables
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Empty all the table in a db of all data (wipe the db clean
(does not
' include system tables)
' Copyright : The following code may be used as you please, but may not be
resold, as
' long as the header (Author, Website & Copyright) remains with
the code.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2008-Feb Initial Release
'---------------------------------------------------------------------------------------
Function WipeTables() As String
On Error GoTo WipeTables_Error
Dim db As DAO.Database
Dim td As DAO.TableDefs

Set db = CurrentDb()
Set td = db.TableDefs
DoCmd.SetWarnings False 'Turn off confirmation prompt to user

For Each t In td 'loop through all the fields of the tables
If Left(t.Name, 4) = "MSys" Or Left(t.Name, 1) = "~" Then GoTo
Continue
DoCmd.RunSQL ("DELETE [" & t.Name & "].* FROM [" & t.Name & "];")
Continue:
Next

DoCmd.SetWarnings True 'Turn back on confirmation prompt to user
Set td = Nothing
Set db = Nothing
WipeTables = True

If Err.Number = 0 Then Exit Function

WipeTables_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: WipeTables" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured! "
Exit Function
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

 

[John Spencer]

One minor change to the delete statement - not everyone names there tables
without spaces and other "special" characters. That being the case it may
be wise to surround the table name with brackets [].

CurrentDB().Execute("DELETE FROM [" & td(i).Name &"]")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[raskew via AccessMonster.com]

Sub EmptyTables2()
' This procedure allows the developer to remove records
' from all tables while retaining the structure.
' It is EXTREMELY DESTRUCTIVE!, since once the
' records are deleted, there's no getting them back!

Dim db As Database
Dim td As Variant
Dim strSQL As String
Dim tName As String
Dim i As Integer

Set db = CurrentDb
Set td = db.TableDefs
' Trap for any errors.
On Error Resume Next
Beep
If MsgBox("Warning! You are about to delete all records in this database.
", vbOKCancel, "Are you sure you want to continue?") = vbCancel Then Exit Sub
For i = 0 To td.Count - 1
If Left(td(i).Name, 4) <> "MSys" Then
tName = td(i).Name
strSQL = "DELETE [" & tName & "].*" _
& " FROM [" & tName & "];"
Debug.Print strSQL
'commented out for safety
'db.Execute strSQL
End If
Next i

End Sub

HTH - Bob

 

[John W. Vinson]

Here are 2 functins that should do the trick

One other niggle: this may fail because of enforced referential integrity
between tables. If you don't have Cascade Deletes specified in the
relationships, *and* if the code happens to try to empty a parent table which
has dependent records in a child table, you'll get records left undeleted and
an error.

If I were doing this, I'd be inclined to create a new database and import all
the tables, design only. No errors and you get a backup of the original
database for free.

 

[Paolo]

Good point John!
I never think that some people could use spaces of other things naming the
tables... 'cause in my mind is not a good idea...

Cheers Paolo

One minor change to the delete statement - not everyone names there tables
without spaces and other "special" characters. That being the case it may
be wise to surround the table name with brackets [].

CurrentDB().Execute("DELETE FROM [" & td(i).Name &"]")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Joe K,
here's the code you need to accomplish your task

Dim i As Long
Dim td As TableDefs

Set td = CurrentDb.TableDefs
end_tbs = td.Count - 1
For i = 1 To end_tbs
'''******* the if is to avoid deleting data in the system tables
If Left(td(i).Name, 4) <> "msys" Then
CurrentDb.Execute ("delete * from " & td(i).Name)
End If
Next i
Set td = Nothing

HTH Paolo

 

[Daniel Pineault]

Thanks John! You do learn something every day.