LOOPING all table in database and use delete * .....

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

LOOPING all table in database and use delete * .....

Post by sal21 »

How to..
LOOPING all tables in batabase and use a SQL "delete * .....from ecc..." without Table1.
In DAO and vb6 (is and old project)

If is possible get the name of each table during the loop.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LOOPING all table in batabase and use delete * .....

Post by HansV »

Try this, but please test it on a copy of your database:

Code: Select all

Sub ClearTables()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim sql As String
    Set dbs = DBEngine.OpenDatabase("...")
    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
            sql = "DELETE * FROM [" & tdf.Name & "]"
            dbs.Execute sql, dbFailOnError
        End If
    Next tdf
    dbs.Close
End Sub
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: LOOPING all table in batabase and use delete * .....

Post by sal21 »

HansV wrote:
02 Nov 2020, 18:33
Try this, but please test it on a copy of your database:

Code: Select all

Sub ClearTables()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim sql As String
    Set dbs = DBEngine.OpenDatabase("...")
    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
            sql = "DELETE * FROM [" & tdf.Name & "]"
            dbs.Execute sql, dbFailOnError
        End If
    Next tdf
    dbs.Close
End Sub
PERFECT!

NOTE:
Possible to have for each tables the deleted records?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LOOPING all table in batabase and use delete * .....

Post by HansV »

You mean the number of records? Like this:

Code: Select all

Sub ClearTables()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim sql As String
    Set dbs = DBEngine.OpenDatabase("...")
    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
            sql = "SELECT Count(*) FROM [" & tdf.Name & "]"
            Set rst = dbs.OpenRecordset(sql, dbReadOnly)
            Debug.Print "Table: " & tdf.Name & ", Records: " & rst(0)
            rst.Close
            sql = "DELETE * FROM [" & tdf.Name & "]"
            dbs.Execute sql, dbFailOnError
        End If
    Next tdf
    dbs.Close
End Sub
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: LOOPING all table in batabase and use delete * .....

Post by sal21 »

HansV wrote:
02 Nov 2020, 18:55
You mean the number of records? Like this:

Code: Select all

Sub ClearTables()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim sql As String
    Set dbs = DBEngine.OpenDatabase("...")
    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then
            sql = "SELECT Count(*) FROM [" & tdf.Name & "]"
            Set rst = dbs.OpenRecordset(sql, dbReadOnly)
            Debug.Print "Table: " & tdf.Name & ", Records: " & rst(0)
            rst.Close
            sql = "DELETE * FROM [" & tdf.Name & "]"
            dbs.Execute sql, dbFailOnError
        End If
    Next tdf
    dbs.Close
End Sub
GREAT!
Work perfect.