Solved Delete all records from all tables in a database

[zelarra821]

Hello.

I found a code to delete all records from a database:

Public Function empty_tables() As Boolean
'======================================================
' Author: wongm003
' Use to empty all visible tables in current database.
'
' Note: if you do not want to prompt the user for each
'       table comment out the second if statement (if msgbox...).
'======================================================
On Error GoTo hndl_err
  
    Dim tbl As Variant
    Dim strSQL As String
  
    DoCmd.SetWarnings False
  
    For Each tbl In CurrentDb.TableDefs
        If Not Application.GetHiddenAttribute(acTable, tbl.Name) Then
            If MsgBox("Empty " & tbl.Name & "?", vbYesNo) = vbYes Then
                strSQL = _
                    "DELETE " & tbl.Name & ".* " & _
                    "FROM " & tbl.Name & ";"
                DoCmd.RunSQL strSQL
            End If
        End If
    Next tbl

    empty_tables = True
  
normal_exit:
    DoCmd.SetWarnings True
    Exit Function

hndl_err:
    empty_tables = False
    Resume normal_exit
  
End Function

However, when I run it, and enter a form, the data source is not updated.

I don't know if I might be missing something in the code, such as updating the tables, that's why I'm asking, or is there another way to do it.

Thank you so much.

 

[Mike Krailo]

However, when I run it, and enter a form, the data source is not updated

That code operates directly on tables, not forms. What data source are you referring to?if form was open while you empty the tables, then me.refresh should update the form.

 

[cheekybuddha]

Do you have spaces in your table names?

This is a problem with turning off the warnings - you don't get to see any errors that could help you troubleshoot.

Try adjusting like:

Public Function empty_tables() As Boolean
On Error GoTo hndl_err

    Dim db As DAO.Database, tbl As DAO.TableDef
    Dim strSQL As String

    Set db = CurrentDb
    For Each tbl In CurrentDb.TableDefs
        If Not Application.GetHiddenAttribute(acTable, tbl.Name) Then
            If MsgBox("Empty " & tbl.Name & "?", vbYesNo) = vbYes Then
                strSQL = _
                    "DELETE [" & tbl.Name & "].* " & _
                    "FROM [" & tbl.Name & "];"
                db.Execute strSQL, dbFailOnError
            End If
        End If
    Next tbl
    empty_tables = True

normal_exit:
    Set db = Nothing
    Exit Function

hndl_err:
    empty_tables = False
    Resume normal_exit

End Function

 

[Josef P.]

Delete all tables​

If Not Application.GetHiddenAttribute(acTable, tbl.Name) Then

Is this a good variant to bypass the system tables?
I just assume that this is the idea.
Of course, it could also be that all hidden tables are intentionally not to be processed.
Comment in code #1:

Use to empty all visible tables in current database.

I would rather be in favor of this variant:

If (tbl.Attributes And DAO.TableDefAttributeEnum.dbSystemObject) = 0 Then

Referential integrity​

It will also be difficult during table processing when the order is undefined to delete the records that are still needed because of referential integrity (without cascade delete).
Therefore, I would tend to define the tables to be processed via an array or an extra table if this is to run repeatedly.

 

[zelarra821]

Wait a moment, let me explain.

I have taken that code as a reference, but I have only taken the lines where it gives value to the SQL statement and then executes it. I have removed everything else, even Next. I have done the latter because of the relationships between tables. Thus, I have replicated the code for each table that I want to delete the records, respecting an order that does not give errors due to possible relationships.

Of course, I execute this code from a form that I have in Configuration. Then I close it and open another form based on a table that I have deleted the data from by code, and it is not updated. I know this because I have a message to create a new record, and it does not appear.

 

[CJ_London]

Suggest show the code you are actually using

 

[jdraw]

What exactly do you mean by "all tables"? Even system tables?
Can you tell readers in plain English (no database jargon) what you are trying to accomplish?
Agree with CJ --seeing some code might help with context.

 

[Josef P.]

I tried something:

Public Sub DeleteFromTables(ByVal TableNamesEnumerable As Variant, Optional ByVal db As DAO.Database = Nothing, _
                   Optional ByVal FixReferentialIntegrityErrors As Boolean = False)

   Dim TabName As Variant
   Dim ErrTabNames() As String
   Dim ErrTabMaxIndex As Long
   
On Error GoTo HandleErrors

   If db Is Nothing Then
      Set db = CurrentDb
   End If
   
   ErrTabMaxIndex = -1

   For Each TabName In TableNamesEnumerable
      DeleteFromTable TabName, db
   Next
   
   If ErrTabMaxIndex >= 0 Then
      DeleteFromTables ErrTabNames, db, True
   End If
   
Exit Sub

AppendTabNameToFixTabNames:
   ErrTabMaxIndex = ErrTabMaxIndex + 1
   ReDim Preserve ErrTabNames(ErrTabMaxIndex)
   ErrTabNames(ErrTabMaxIndex) = TabName
   Return
   
TryDeleteRecordsWithoutRIerrors:
   If Err.Description Like "*" & TabName & "*" Then
      DeleteFromTable TabName, db, 0
   End If
   Return
   
HandleErrors:
   Const ReferentialIntegrityDeleteErrorNumber As Long = 3200
   If Err.Number = ReferentialIntegrityDeleteErrorNumber And FixReferentialIntegrityErrors Then
      GoSub AppendTabNameToFixTabNames
      GoSub TryDeleteRecordsWithoutRIerrors
      Resume Next
   Else
      Err.Raise Err.Number, "DeleteFromTables->" & Err.Source, Err.Description
   End If

End Sub

Public Sub DeleteFromTable(ByVal TableName As String, _
                  Optional ByVal db As DAO.Database = Nothing, _
                  Optional ByVal ExecuteOption As DAO.RecordsetOptionEnum = DAO.RecordsetOptionEnum.dbFailOnError)

   Dim DeleteSql As String

   If db Is Nothing Then
      Set db = CurrentDb
   End If
   
   If Left(TableName, 1) <> "[" Then
      TableName = "[" & TableName & "]"
   End If
   
   DeleteSql = "delete from " & TableName
   Debug.Print DeleteSql
   
   db.Execute DeleteSql, ExecuteOption

End Sub

Possible usage:

DeleteFromTables Array("Tab1", "Tab2")
DeleteFromTables GetUserTableArray()

Private Function GetUserTableArray(ByVal db As DAO.Database) As String()

   Dim TabNames() As String
   Dim tdf As DAO.TableDef
   Dim tdfs As DAO.TableDefs
   Dim MaxIndex As Long

   Set tdfs = db.TableDefs
   ReDim TabNames(tdfs.Count) ' Count = incl. system tables!

   MaxIndex = -1 ' to start with 0 on first item
   For Each tdf In tdfs
      If (tdf.Attributes And DAO.TableDefAttributeEnum.dbSystemObject) = 0 Then
         MaxIndex = MaxIndex + 1
         TabNames(MaxIndex) = tdf.Name
      End If
   Next

   ReDim Preserve TabNames(MaxIndex)

   GetUserTableArray = TabNames

End Function

 

[The_Doc_Man]

If you are trying to erase the contents of the tables but not the structures thereof, perhaps because you are resetting a database back to its "primal" state, this more or less makes sense I guess. But be sure to perform a Compact & Repair once you do this. Deleting records in bulk is one of the easiest ways to leave yourself with tremendous database bloat AND it doesn't reset incrementing autonumbers back to 1. A carefully-done C&R solves both of those problem.

 

[zelarra821]

Look, this is the tweaked code. As you can see, I have only added the tables that I am interested in, eliminating all the records. It is a database to generate invoices and estimates, to which I have example data entered. Therefore, if I want to give it to someone, I have to delete the data. And this is the case. But I'm not going to be there, because I'm working outside, and my father has to be the one to show him the database with examples so that he can see how it works, and then delete the data. Therefore, I want to prepare a button to delete the records from the tables that you can see.

The order is planned:

I first eliminate TInvoices and TBudgets because in them there is data related to the following tables (in a relationship: several -in these two tables- to one -in the corresponding table-).

Then, I delete the "subtables" of those two previous tables, since there is also data related to the other tables that I want to delete.

Finally, I delete the table that has the relationship.

The only thing I have done is extract the code that interested me and add a Select Case with a question asking if you are sure what you are going to do.

Public Sub VaciarTablas()

On Error GoTo hndl_err
    
    Dim strSQL As String
    
    Select Case MsgBox("¿Seguro que quieres borrar todo la información contenida en la base de datos? Este proceso no podrá revertirse." _
    , vbYesNo, NombreBD)
    
        Case vbYes
    
            DoCmd.SetWarnings False
                              
            strSQL = _
                "DELETE TFacturas.* " & _
                "FROM TFacturas;"
            DoCmd.RunSQL strSQL
            
            strSQL = _
                "DELETE TPresupuestos.* " & _
                "FROM TPresupuestos;"
            DoCmd.RunSQL strSQL
        
            strSQL = _
                "DELETE TFacturasSubtabla.* " & _
                "FROM TFacturasSubtabla;"
            DoCmd.RunSQL strSQL
        
            strSQL = _
                "DELETE TPresupuestosSubtabla.* " & _
                "FROM TPresupuestosSubtabla;"
            DoCmd.RunSQL strSQL
        
            strSQL = _
                "DELETE TClientes.* " & _
                "FROM TClientes " & _
                " WHERE CodigoCliente<>1;"
            DoCmd.RunSQL strSQL

            MsgBox "Se han borrado todas las tablas correctamente.", vbInformation, NombreBD

        Case vbNo
        
            Exit Sub

    End Select
    
normal_exit:
    DoCmd.SetWarnings True
    Exit Sub

hndl_err:
    Resume normal_exit
    
End Sub

 

[zelarra821]

I also give you a video.

1. First, I show you a form that contains data.
2. I exit the form and open the form where I have the button.
3. I have this button in a form that I have made that is accessed previously by entering a password that only I know.
4. I delete the data.
5. I go back to the home form and the data is there.
6. I close the base.
7. I open it again and repeat steps 1 to 5.
8. This time they have been deleted because it asks me, when entering the form, if I want to generate a new invoice.

 

[cheekybuddha]

What happens if you try it like this:

Public Sub VaciarTablas()

On Error GoTo hndl_err
    
    Dim strSQL As String
    
    Select Case MsgBox("¿Seguro que quieres borrar todo la información contenida en la base de datos? Este proceso no podrá revertirse." _
    , vbYesNo, NombreBD)
    
        Case vbYes
    
          With CurrentDb
                              
            strSQL = _
                "DELETE TFacturas.* " & _
                "FROM TFacturas;"
            .Execute strSQL, dbFailOnError
            
            strSQL = _
                "DELETE TPresupuestos.* " & _
                "FROM TPresupuestos;"
            .Execute strSQL, dbFailOnError
        
            strSQL = _
                "DELETE TFacturasSubtabla.* " & _
                "FROM TFacturasSubtabla;"
            .Execute strSQL, dbFailOnError
        
            strSQL = _
                "DELETE TPresupuestosSubtabla.* " & _
                "FROM TPresupuestosSubtabla;"
            .Execute strSQL, dbFailOnError
        
            strSQL = _
                "DELETE TClientes.* " & _
                "FROM TClientes " & _
                " WHERE CodigoCliente<>1;"
            .Execute strSQL, dbFailOnError

            MsgBox "Se han borrado todas las tablas correctamente.", vbInformation, NombreBD

            ' Requery your main form
            Forms!NameOfMainForm.Requery

          End With

        Case vbNo
        
            Exit Sub

    End Select
    
normal_exit:
    Exit Sub

hndl_err:
    MsgBox Err.Description & vbNewLine & vbNewLine & strSQL, vbOkOnly, Err.Number
    
End Sub

 

[zelarra821]

Fails. It says that you have to delete the related data to get the data from the table.

 

[zelarra821]

It is fixed.

She was putting the subtables after the main tables. Consequence? Well, since there were records that relate both tables, it only eliminated the data from the subtable. This is why I saw the form filled out and also explains why I had to do it twice: the first time I deleted the data from the subtables, and the second time from the main tables.

Thank you so much guys.

All the best.

 

[cheekybuddha]

Fails. It says that you have to delete the related data to get the data from the table.

Excellent! So you have determined the cause of the issue, which is what JosefP was pointing at.

So, change the order ofthe deletions to delete data from the child tables first, then the parent tables.

Now do you see why turning off warnings can be a problem!!!

 

[cheekybuddha]

Ah, we crossed replies!