ACCESS FEED: RE: [MS_AccessPros] Delete records from multiple tables

Kamis, 16 April 2015

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

Best wishes,
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

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

    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
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

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

http://www.viescas.com/

(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: "Graham Mandeno" <graham@mandeno.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic