Is there a way in MS-Access to delete the data in all the tables at once. We run a database in access, save the data every month and then delete all the data in access. But it requires deleting data from a lot of tables. Isn't there a simpler/easier way to do so?
You can use an asterisk (*) to select all fields in a table.
Hello, Yes, you can. You need to create logic to validate if the record is selected and delete that record (inside of for each) or store what records are selected and do a bulk delete with SQL.
Why don't you keep an empty copy of the database on hand. At the end of the month, save the existing database, then copy the empty database in its place.
Craig's answer is simple and sensible. If you really want a programmatic solution, the following VBA script will clear all the data from every table excluding the hidden tables. It requires DAO to be enabled - in Visual Basic Editor, go to Tools -> References, and tick Microsoft DAO 3.6 Object Library, then OK:
Public Sub TruncateTables()
'Majority of code taken from a data dictionary script I can no longer source nor find the author
On Error GoTo Error_TruncateTables
Dim DB As DAO.Database
Dim TDF As DAO.TableDef
Dim strSQL_DELETE As String
Set DB = CurrentDb()
For Each TDF In DB.TableDefs
If Left(TDF.Name, 4) <> "MSys" Then
strSQL_DELETE = "DELETE FROM " & TDF.Name & ";"
DB.Execute strSQL_DELETE
End If
Next
MsgBox "Tables have been truncated", vbInformation, "TABLES TRUNCATED"
DB.Close
Exit_Error_TruncateTables:
Set TDF = Nothing
Set DB = Nothing
Exit Sub
Error_TruncateTables:
Select Case Err.Number
Case 3376
Resume Next 'Ignore error if table not found
Case 3270 'Property Not Found
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Error_TruncateTables
End Select
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With