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.
Code:
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