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.
LOOPING all table in database and use delete * .....
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LOOPING all table in batabase and use delete * .....
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
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: LOOPING all table in batabase and use delete * .....
PERFECT!HansV wrote: ↑02 Nov 2020, 18:33Try 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
NOTE:
Possible to have for each tables the deleted records?
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LOOPING all table in batabase and use delete * .....
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
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: LOOPING all table in batabase and use delete * .....
GREAT!HansV wrote: ↑02 Nov 2020, 18:55You 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
Work perfect.