How to delete all records from multiple tables with VBA loop? Access 2010

Question

I want a VBA loop function that will delete all records from any table with a name like "d2s_*".

I've found code to delete all records:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM NameOfTable"
DoCmd.SetWarnings True

I've also found a loop to call each table in the database:

Dim T As TableDef
For Each T In CurrentDb.TableDefs
Debug.Print T.Name
Next T

So what I want to do is combine them:

Dim T As TableDef
For Each T In CurrentDb.TableDefs
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM NameOfTable"
DoCmd.SetWarnings True
Next T

But as I understand, the SQL query has to reference a specific table name. Is there a way to make this relative to any table starting with the name "d2s_"?

Monty Wild · Accepted Answer · 2013-12-11 21:33:31Z

5

Try:

Dim T As TableDef
DoCmd.SetWarnings False
For Each T In CurrentDb.TableDefs
    If T.Name Like "d2s_*" Then
        DoCmd.RunSQL "DELETE * FROM " & T.Name
    End If
Next T
DoCmd.SetWarnings True

answered Dec 11, 2013 at 21:33

Monty Wild

3,9991 gold badge26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to delete all records from multiple tables with VBA loop? Access 2010

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related