Rename field names (multiple tables) based on lookup table information

[skydivetom]

Experts:



In a previous post (https://www.accessforums.net/showthread.php?t=83606), I required some assistance with VBA code development in order to:
a) dynamically generate queries based on a lookup table "01_tbl_TargetTables_String"
b) copying the SELECT queries and them into MAKE TABLE queries and
c) finally executing all MAKE TABLE queries and generating the respective tables

Expert Vlad Cucinschi (aka Gicu) provided some superb assistance and his solution works perfectly!!

Upon opening the attached DB, please open form "F01_MainMenu" and click on both command buttons "Delete..." and "Generate". Doing so, will create 2 SELECT queries and 2 MAKE TABLE queries... where the latter 2 are automatically executed to generate tables [tbl_Demographics] and [tbl_Other].

Again, this process work as originally envisioned!


<break><break>


At the present time, I need some additional assistance in order to slightly tweak the existing code.

Allow me to recap the changes I made to the existing version:
#1. Use a differnt form (than the one included by Vlad). The form includes merely changes to formatting but all functions in same manner.
#2. I added field [ALIAS_STRING] to table [01_tbl_TargetTables_String].

Based on #2, this is where I need some help with. Again, please allow me to provide some background first:
a. The existing table "00_tbl_Master_String" is generated from legacy data (with legacy field naming convention).
b. In order to migration to a target system, different field names are required. These new field names are referenced in [01_tbl_TargetTables_String].[ALIAS_STRING]

Envisioned process:
Option #1: Either tweak the existing VBA (module "modGenerateQueries") so that e.g., "LastName" is somehow translated into "PERS_LAST" (while the source table refers to it as "LastName".
Option #2: Execute/generate the two (2) tables "tbl_Demographics" and "tbl_Other" as is. However, then execute function (non-existing right now) which would rename all fields in [tbl_Demographics] and [tbl_Other] based on the information listed in [01_tbl_TargetTables_String].

Please note that the "key" to the entire process is the matrix in the lookup table [01_tbl_TargetTables_String]. That is, in my actual DB, I have additional target tables and dozens of fieldnames. So, any form of VBA must be dynamic enough to interpret the renaming based on that lookup table (in order words, I do NOT want to hard-code the renaming of the few example fields).

My question: How can I either achieve the so that my "product" tables (tbl_Demographics & tbl_Other) are generated OR renamed with the field names such as "PERS_LAST", "PERS_FIRST" (vs. "LastName", "FirstName"), etc.?

Cheers,
Tom

P.S. The attached GIF illustrates the "What I need instead" with different values in the 3rd field. Ultimately, nothing changes given that I still want to end up with "PERS_LAST" (listed as "Alias_LastName" in the GIF). I had created this view earlier but didn't keep the file in order to update the field naming convention and values.

[Gicu]

Hi Tom,

Wasn't this exact issue solved in the other thread (https://www.accessforums.net/showthr...83606&page=2)? I had a look at the db you posted in this thread and is missing all that I've done this morning. You don't need to have a button to delete the tables as the code does it just before running the make table.

So I am not sure why this post was necessary, could you please explain?

Cheers,