Art,
You could use some DAO code that loops through the TableDefs collection and when it finds a table name beginning with "SC" executes a "DELETE * FROM SC..." SQL statement. If you have related child records with integrity set on, you will delete from the other tables as well.
Something like this should work:
Dim td AS DAO.TableDef
Dim db as DAO.Database
Dim strNameStart as String
Dim strSQLStart as String
strNameStart = "SC"
strSQLStart = "DELETE * FROM ["
Set db = CurrentDb
For Each td in db.TableDefs
If Left(td.Name, Len(strNameStart)) = strNameStart Then
debug.Print "Deleting from table " & td.Name
db.Execute strSQLStart & td.Name & "]", dbFailOnError
End If
Next
Set td = Nothing
Set db = Nothing
MsgBox "Done"
I would run the code at least once with the db.Execute line commented out to make sure the correct tables are being truncated. Once you are comfortable (and after a backup) you can un-comment the line to actually perform the deletions.
Duane Hookom, MVP
MS Access
Date: Thu, 16 Apr 2015 12:03:22 -0700
Subject: [MS_AccessPros] Delete records from multiple tables
Is there a way to purge records in multiple tables via VBA. I have 12 tables that contains the letter SC in their name. I would like to be able to delete all the records in these tables but not effect other tables. Is it doable?
Thank you,
Art Lorenzini
You could use some DAO code that loops through the TableDefs collection and when it finds a table name beginning with "SC" executes a "DELETE * FROM SC..." SQL statement. If you have related child records with integrity set on, you will delete from the other tables as well.
Something like this should work:
Dim td AS DAO.TableDef
Dim db as DAO.Database
Dim strNameStart as String
Dim strSQLStart as String
strNameStart = "SC"
strSQLStart = "DELETE * FROM ["
Set db = CurrentDb
For Each td in db.TableDefs
If Left(td.Name, Len(strNameStart)) = strNameStart Then
debug.Print "Deleting from table " & td.Name
db.Execute strSQLStart & td.Name & "]", dbFailOnError
End If
Next
Set td = Nothing
Set db = Nothing
MsgBox "Done"
I would run the code at least once with the db.Execute line commented out to make sure the correct tables are being truncated. Once you are comfortable (and after a backup) you can un-comment the line to actually perform the deletions.
Duane Hookom, MVP
MS Access
Date: Thu, 16 Apr 2015 12:03:22 -0700
Subject: [MS_AccessPros] Delete records from multiple tables
Is there a way to purge records in multiple tables via VBA. I have 12 tables that contains the letter SC in their name. I would like to be able to delete all the records in these tables but not effect other tables. Is it doable?
Thank you,
Art Lorenzini
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar