Delete All Records From All Tables

[nico3334]

I am trying to delete all records from all tables in Access through VB coding. I would like to do it dynamically in case new tables are added. Here is what I have so far:

I have a form with a command button. When it is clicked, here is the code:

Private Sub Command0_Click() 
 
DoCmd.SetWarnings False 
 
DoCmd.RunSQL "DELETE * FROM [Table];" 
 
DoCmd.SetWarnings True 
 
MsgBox "All Records Deleted.", vbOKOnly, "Sendout Reports" 
 
End Sub 
 

But is there a way to first select all tables and then delete? Or can I delete from all tables using a wildcard character?

Thanks!

[Paul Longtin]

You just need DELETE FROM table, forget the "*"
Here is a sample of finding tables to in a database:

'Using ADO
Private ConDB As ADODB.Connection
Private rs As ADODB.Recordset
Private ProviderType As String
'Make sure you know the Provider versions
'PSI = "True" if there is a password to get into the database else "False"
 
'MS Access: ProviderType = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & MyDatabase
 
'MS SQL Server: ProviderType = "Provider=SQLOLEDB.1" & UserPassword & UserID & ";Integrated Security=SSPI;Persist Security Info=" & PSI & ";Initial Catalog=" & DatabaseName & ";Data Source=" & ServerName
 
Oracle using MS provider: ProviderType = "Provider=MSDAORA.1" & UserPassword & UserID & ";Data Source=" & ServerName & ";Persist Security Info=" & PSI
 
Oracle using MS provider: ProviderType = "Provider=OraOLEDB.Oracle.1" & UserID & UserPassword & ";Data Source=" & ServerName & ";DistribTx=0"
 
ConDB.Open ProviderType
 
Private tempTable As String, tempTableType As String, tempSchema As String
rs.CursorLocation = adUseClient
Set rs = ConDB.OpenSchema(adSchemaTables)
 
'With Oracle, MySQL or if there are owners to the tables
Do Until rs.EOF
    tempTable = rs!TABLE_NAME
    tempTableType = rs!TABLE_TYPE
    If tempTableType = "TABLE" Then
        tempSchema = rs!TABLE_SCHEMA
        'Put you code here using (tempSchema & "." & tempTable) as the table name
    End If
    rs.MoveNext
Loop
 
'Many other databases
Do Until rs.EOF
    tempTable = rs!TABLE_NAME
    tempTableType = rs!TABLE_TYPE
    If tempTableType = "TABLE" Then
        'Put you code here using (tempTable) as the table name
    End If
    rs.MoveNext
Loop