That worked great. Thank you.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
From: "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, April 16, 2015 3:25 PM
Subject: RE: [MS_AccessPros] Delete records from multiple tables
Hi Art
It seems your requirement is to delete all records from all tables that start with "tbl_SC_". Try this:
Private Sub lblPurgeRecords_Click()
Const cPrefix = "tbl_SC_"
Dim db As DAO.Database, tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, Len(cPrefix)) =cPrefix Then
db.Execute "DELETE * FROM [" & tdf.Name & "]", dbFailOnError
End If
Next tdf
Set db = Nothing
Set tdf = Nothing
End Sub
Const cPrefix = "tbl_SC_"
Dim db As DAO.Database, tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, Len(cPrefix)) =cPrefix Then
db.Execute "DELETE * FROM [" & tdf.Name & "]", dbFailOnError
End If
Next tdf
Set db = Nothing
Set tdf = Nothing
End Sub
Best wishes,
Graham
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 17 April 2015 07:24
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Delete records from multiple tables
Sent: Friday, 17 April 2015 07:24
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Delete records from multiple tables
I think I missed something. the name of the tables I am after have names like tbl_SC_audit or tbl_SC_permissions. I tried to insert the wild card but it didn't work.
Private Sub lblPurgeRecords_Click()
Dim db As DAO.Database, tdf As DAO.TableDef
Dim db As DAO.Database, tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If InStr(tdf.Name, "*SC*") <> 0 Then
db.Execute "DELETE * FROM [" & tdf.Name & "]", dbFailOnError
End If
For Each tdf In db.TableDefs
If InStr(tdf.Name, "*SC*") <> 0 Then
db.Execute "DELETE * FROM [" & tdf.Name & "]", dbFailOnError
End If
Next tdf
Set db = Nothing
Set tdf = Nothing
End Sub
End Sub
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, April 16, 2015 2:13 PM
Subject: Re: [MS_AccessPros] Delete records from multiple tables
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, April 16, 2015 2:13 PM
Subject: Re: [MS_AccessPros] Delete records from multiple tables
Art-
Easy.
Dim db As DAO.Database, tdf As DAO.Tabledef
Set db = CurrentDb
For Each tdf In db.Tabledefs
If Instr(tdf.Name, "SC") <> 0 Then
db.Execute "DELETE * FROM [" & tdf.Name & "]", dbFailOnError
End If
Next tdf
Set db = Nothing
Set tdf = Nothing
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Apr 16, 2015, at 9:03 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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
Sioux falls, SD
__._,_.___
Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar