VBA ForLoop to rename all tables with same *prefix*

[skydivetom]

Experts:

I need some assistance with streamlining an update routine which strips off/removes a prefix from a range of tables.

Background:
a. I have a staging database that contains nearly 100 tables. Each table has an existing prefix = "LK_"
b. At the present time, I am reviewing the data within each table.
c. Upon finishing the review, I add a *temporary prefix to the existing "LK_" prefix . For instance, I change table "LK_IMPACT" to "complete_LK_IMPACT".


d. Based on c. this merely moves a validated/reviewed table to the top section of the list so that I can continue concentrating only on those starting with an "LK_"

Current re-naming process:
- Once I finalized the review of all of the tables, I need to change the table names back to their original names.
- So, basically, I could either click on "rename" and simply remove the prefix "complete_". This is time-consuming though!
- Now, please see the VBA below. When executing this routine, it successfully renames all table names.
- HOWEVER, if the VBA statement includes 100 DoCmd.Rename lines but it turns out that I decided that one of the tables is no longer needed (and I delete/remove it), the VBA stops and throws an error indicationg "could not find table name".

Here's my question:
Is there a way (e.g., ForLoop) that would scan through all existing tables that have a prefix = "complete_" and then remove that prefix so that the table name would start out with "LK_" again?
I am hopeful that such procedure would continue until n number of tables have been renamed.

P.S. The attached JPG and VBA is an illustration of only 7 table names. Again, in actually, I have nearly 100 tables and that number could grow even further.

Option Compare Database

Private Sub cmdRenameTables_Click()

    DoCmd.Rename "LK_IMPACT", acTable, "complete_LK_IMPACT"
    DoCmd.Rename "LK_INJURY_TYPE", acTable, "complete_LK_INJURY_TYPE"
    DoCmd.Rename "LK_INJURYTYPE", acTable, "complete_LK_INJURYTYPE"
    DoCmd.Rename "LK_JOB_TITLE_ID", acTable, "complete_LK_JOB_TITLE_ID"
    DoCmd.Rename "LK_LOCATION_BASE", acTable, "complete_LK_LOCATION_BASE"
    DoCmd.Rename "LK_MISHAP_CLASS", acTable, "complete_LK_MISHAP_CLASS"
    DoCmd.Rename "LK_NSC_GEN_LOCATION", acTable, "complete_LK_NSC_GEN_LOCATION"
    
    '...
    '... I have nearly 100 tables that require removal of prefix "complete_"
    
End Sub

[Micron]

there are tons of result for this, with code examples. Did you look? You will learn more that way. Or you could adapt this concept, using IF with Left or Instr functions.

Private Sub tblLoop()
Dim tdf As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb

For Each tdf In db.TableDefs
   MsgBox tdf.Name
Next

Set db = Nothing

End Sub

[Welshgasman]

@Micron,

O/P was given code to loop through table names with a prefix a few weeks back, so already has the logic?

[skydivetom]

Micron -- thanks for the feedback. Yes, I searched for solutions but may have not used proper keywords to actually find a working solution.

For testing purposes only, I placed your code in the function. Now, I get the msg box popping up nearly 20 times with *only* 3 tables (for testing only). If I had left the 7 tables (or 100 tables), I probably would see the msg to show up hundreds of times.

I'll continue to look for a solution... any additional thoughts would always be welcome.

[skydivetom]

Micron -- thank you... it appears the following works great.

Private Sub cmdRenameTables_Click()

    On Error Resume Next
    Set dbs = CurrentDb
    dbs.TableDefs.Refresh

    For Each tdf In dbs.TableDefs

        If tdf.Name Like "complete_*" Then
        tdf.Name = "LK" & Mid(tdf.Name, 12, 255)

    End If

    Next
    
End Sub

[Micron]

I get the msg box popping up nearly 20 times with *only* 3 tables (for testing only)

You're overlooking system tables; perhaps hidden ones too.
Get in the habit of destroying objects that you create (e.g. dbs)
Maybe review Mid function and figure out why you don't need 255 argument.
Thanks for the rep point.

[skydivetom]

Actually, I don't need to rename systems or hidden tables. All table objects are visible and non-systems. Again, thanks for the feedback... I appreciate it.

[Micron]

That was an explanation for your quoted comment, which I must have incorrectly interpreted as asking why 20 messages when you only had 3 tables.