Place multiple DROP TABLE statement into FOR LOOP

[skydivetom]

Experts:

I'd like some assistance with streamlining a DROP TABLE procedure based on # (n) number of data source tables.

At the present time, I have 5 data sources tables. Their names are shown below.

tbl_Source1
tbl_Source2
tbl_Source3
tbl_Source4
tbl_Source5

Please note that all tables have a prefix of "tbl_". This is important as future source tables may be named as follows:

tbl_Florida
tbl_Illinois
tbl_Maine
tbl_Texas
tbl_Virgnia

Now, based on the current table naming convention (i.e., tbl_Source1 through tbl_Source5), I use the following code to DROP all 5 tables prior to importing them.

        'Declare variables for DROP TABLE statements
        Dim strSQL_Drop1 As String
        Dim strSQL_Drop2 As String
        Dim strSQL_Drop3 As String
        Dim strSQL_Drop4 As String
        Dim strSQL_Drop5 As String
        
        'Declare variables for generic strCriteria#
        Dim strCriteria1 As String
        Dim strCriteria2 As String
        Dim strCriteria3 As String
        Dim strCriteria4 As String
        Dim strCriteria5 As String
        
        '*** Update here (if additional data sources are utilized)
        'Assign source tables names to strCriteria#
        strCriteria1 = "tbl_Source1"
        strCriteria2 = "tbl_Source2"
        strCriteria3 = "tbl_Source3"
        strCriteria4 = "tbl_Source4"
        strCriteria5 = "tbl_Source5"
        
        'Define strSQL_Drop# statements (for all data sources)
        strSQL_Drop1 = "DELETE " & strCriteria1 & ".* FROM " & strCriteria1 & ";"
        strSQL_Drop2 = "DELETE " & strCriteria2 & ".* FROM " & strCriteria2 & ";"
        strSQL_Drop3 = "DELETE " & strCriteria3 & ".* FROM " & strCriteria3 & ";"
        strSQL_Drop4 = "DELETE " & strCriteria4 & ".* FROM " & strCriteria4 & ";"
        strSQL_Drop5 = "DELETE " & strCriteria5 & ".* FROM " & strCriteria5 & ";"
        
        'Execute all strSQL_Drop# statements
        CurrentDb.Execute strSQL_Drop1
        CurrentDb.Execute strSQL_Drop2
        CurrentDb.Execute strSQL_Drop3
        CurrentDb.Execute strSQL_Drop4
        CurrentDb.Execute strSQL_Drop5

Although the code executes, this entire section appears to be lengthy & maybe even cumbersome.

My question: Is there a ways to change the code above and, e.g., put it into a For Loop so that every table starting with "tbl_" will be dropped? Naturally if new tables will be added to the DB, those also would have to be dropped. Any new source tables would also have the "tbl_" prefix.

If so, what would that VBA code look like?

Thank you,
Tom

[Welshgasman]

FYI When you delete you only need DELETE * FROM TableName ?

[skydivetom]

Welshgasman... hmh, note entirely tracking here. So, what would the combined VBA look like to DROP all tables with a "tbl_" prefix?

[Welshgasman]

Welshgasman... hmh, note entirely tracking here. So, what would the combined VBA look like to DROP all tables with a "tbl_" prefix?

No, I mean there is no criteria for the Delete command, just DELETE * FROM tablename.?
If your tables are all like tbl1, tbl2, then you can append the loop number to the table name in a For next loop.

However your structure seems way off to me. For the state tables, I'd expect one table and a field to indicate which state?, not a table for each state.?

You could store the states in a table and read each record and use in a Do Until EOF loop when they are named as such.?

Also you are not DROPping a table merely deleting the records? See https://docs.microsoft.com/en-us/off...oft-access-sql

[skydivetom]

Ok... I'm not sure if we're fully in sync. Please allow me to recap.

- I have a subfolder where I stored n number of spreadsheets to be imported.
- The tab names on each XLS could be either "Source1", "Source2", "Texas", "XYZ", or "SomethingElse".
- Now, my existing *IMPORT routine* imports all spreadsheets (from that folder) into the DB. As part of the import routine, all tables are prefixed with "tbl_".
- So, I would end up with: "tbl_Source1", "tbl_Source2", "tbl_Texas", "tbl_XYZ", or "tbl_SomethingElse".

Now, during the next import routine, I want to make sure that I remove all records from the existing tables. That's really it.

However, instead of having to define every spreadsheet/table in my current code, I merely want to loop and delete ALL records but only for those with a "tbl_" prefix. All other tables should be left unchanged.

How can that be accomplished? I don't want to create another table where I'd have to add all spreadsheet names or tab names. I hope this makes sense.

Thank you,
Tom

[Welshgasman]

Something along these lines?

Sub DeleteRecordsFromTables()
Dim tdf As TableDef
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
For Each tdf In db.TableDefs
    If Left(tdf.Name, 3) = "tbl" Then
        strSQL = "DELETE * FROM " & tdf.Name
        Debug.Print strSQL
        db.Execute strSQL
    End If
Next

End Sub

Comment out the Execute until you get the sql working, then comment out the debug.print instead.

[skydivetom]

Ok, that works great! Just one comment though. To ensure no other table gets wiped out

From:

If Left(tdf.Name, 3) = "tbl" Then

To:

If Left(tdf.Name, 4) = "tbl_" Then

I think this works but I'll do some double-checking. Thank you thus far for the help.

[Welshgasman]

Well I expect you to change the test to suit?
I do not have any tables named tbl_ and was not going to change a pile of them just for the test?

All I was trying to show was one method of doing what you ask?

[skydivetom]

Yup... that works great! Thousand thanks, Welshgasman.