Update VBA routine to process fieldname changes for *multiple* tables

**skydivetom** · 07-12-2021, 12:31 PM

Experts:

I need some assistance with tweaking a working VBA routine (for a single table) in order to now process multiple tables. Please find attached 2 database files:

1. Rename_Fields_Single_Table
2. Rename_Fields_Multiple_Tables

Before I go into the details, I would like to first give credit to Vlad (aka Gicu) who has been instrumental in helping me designing the function for processing one (1) table. Vlad's function works perfectly but it it used for a slightly different process. Now, that applying this process to another routine, the procedure is slightly different given it requires processing of multiple tables (vs. 1 table).

Ok, allow me to provide some background first on file "Rename_Fields_Single_Table":
- It contains the following objects:
** 2 tables: [00_tbl_Source_1] and [01_tbl_FieldMapping]
** Table [00_tbl_Source_1] is the "legacy" table with old (out-dated) field naming convention
** Table [01_tbl_FieldMapping] includes 3 fields: [TABLE_NAME], [FN_LEGACY], [FN_STANDARDIZED]
** The 2 queries are automatically generated by the VBA function (see module)

Process for "Rename_Fields_Single_Table":
1. Open form "F01_MainMenu"
2. Click on command button "Standardize Field Names"
3. Per step #2, a new table [tblMMAC] is created. This table is a copy of [00_tbl_Source_1] but with the new/standardized field names as outlined in table [01_tbl_FieldMapping].

Note per VBA:
* Please note that line

Code:

    strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_Source_1];"

includes the hard-coded table: "00_tbl_Source_1"

Now, let's look at the 2nd file "Rename_Fields_Multiple_Tables"... this is where I need some assistance with tweaking the VBA.

Allow me to recap the differences between the 2 files:
a. I now have 4 tables "00_tbl_Source_1" through "00_tbl_Source_4"
b. Table [01_tbl_FieldMapping] has been updated to reflect the mapping between "legacy" fields and standardized fields for all 4 source tables.
c. Please note that field [TABLE_NAME] has 4 different values (MMAC through POAIRS).

Process for "Rename_Fields_Multiple_Tables":
1. Now, just like with the version containing a single source table, I open up the form and click on the command button.
2. You'll note that the VBA automatically creates now 8 queries (4 sets of 2) for each source.
3. Unfortunately, the routine only generates table [tblMMAC] instead of {[tblMMAC], [tblNNSY], [tblPHNSY], [tblPOAIRS]}
4. And, in review of the make-table and SELECT queries for [_NNSY], [_PHNSY], [_POAIRS] I'm prompted with a dialogue box.

Naturally, I understand as to why the VBA did NOT generate the missing 3 tables... again, as previously mentioned, the code only specifies "00_tbl_Source_1".

So, here's my question/what I need some help with:
- How should the VBA be modified to *loop* through all tables that have a table prefix = "00_tbl_"?
- Doing so should then pass the correct values into the SEL and make-table queries and then generate all 4 tables with the correct standardized field names.

Best,
Tom

**kd2017** · 07-12-2021, 03:14 PM

https://stackoverflow.com/questions/...ss-db/17557544

**skydivetom** · 07-13-2021, 05:25 AM

Experts:

Good morning... I tried the below (i.e., replacing the single strBaseSQL line with the ForLoop) but it now it only produces a table for the last source table (vs. all source tables).

What am I missing?

Code:

    Dim t As TableDef
    For Each t In CurrentDb.TableDefs
        'If t.Name Like "00_tbl_*" Then DoCmd.RunSQL ("DROP TABLE " & t.Name)
        If t.Name Like "00_tbl_*" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM " & t.Name
    Next
       
       
    'Replaced strBaseSQL with For Loop above
    'strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_MMAC];"

**Minty** · 07-13-2021, 05:58 AM

You need to run the StrBsseSQL each time IN the loop?

**skydivetom** · 07-13-2021, 06:05 AM

Minty - thank you... isn't that what I have though?

Below is the original code w/o the commented lines. Am I misinterpreting it?

Code:

    Dim t As TableDef
    For Each t In CurrentDb.TableDefs
        If t.Name Like "00_tbl_*" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM " & t.Name
    Next

**Minty** · 07-13-2021, 06:39 AM

No - Work through the loop

Set strBaseSQL to "fred"
loop
Set strBaseSQL to "Harry"
loop
Set strBaseSQL to "Tom"

etc

You aren't doing anything with it until you end the looping process.

**skydivetom** · 07-13-2021, 07:04 AM

Minty -- not really tracking here... sorry. Please see entire VBA code (3 functions) where I'm currently trying to correct the UpdateQueries code.

Code:

Option Compare Database
Option Explicit
Public iCounter As Integer

Public Sub UpdateQueries()

    'Declare variables
    Dim rs As DAO.Recordset, sTarget As String, sField As String
    Dim sCurrent As String, i As Integer
    Dim sSQL As String, sFieldAlias As String
    Dim strBaseSQL As String
    Dim v, sOrganizationFilter As String
       
       
    'Filter for data source (MMAC, NNSY, etc.)
    Dim t As TableDef
    For Each t In CurrentDb.TableDefs
        If t.Name = "00_tbl_MMAC" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_MMAC];"
        If t.Name = "00_tbl_NNSY" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_NNSY];"
        If t.Name = "00_tbl_PHNSY" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_PHNSY];"
        If t.Name = "00_tbl_POAIRS" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_POAIRS];"
    Next
              
       
    'Reset counter for new tables
    iCounter = 0
            
    'Delete all queries
    If Forms![F01_MainMenu].chkDeleteAllQry = True Then Call DeleteAllQueries
            
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_FieldMapping Order BY TABLE_NAME, FN_STANDARDIZED;", dbOpenSnapshot)
       
    
    If rs.RecordCount = 0 Then Exit Sub
    sTarget = ""
    sField = ""
    sSQL = ""
    
    sTarget = rs("TABLE_NAME")                                      'First record
    Do Until rs.EOF
        sField = rs("[FN_LEGACY]")
        sCurrent = rs("TABLE_NAME")
        sFieldAlias = Nz(rs("FN_STANDARDIZED"), sField)             'Allows to only add alias where different than original field name (i.e., FN_LEGACY)
        If sTarget <> sCurrent And i > 1 Then
            sSQL = Left(sSQL, Len(sSQL) - 1)
            sSQL = Replace(strBaseSQL, "|FIELDS_HERE|", sSQL)
           
            Call CreateQuery("qry" & sTarget, sSQL)
            sTarget = sCurrent                                      'Reset target
            sSQL = ""
        End If
        If sFieldAlias = sField Then
            sSQL = sSQL & "[" & sField & "],"
        Else
            sSQL = sSQL & "[" & sField & "] as " & sFieldAlias & ","
        End If
    i = i + 1
    rs.MoveNext
    If rs.EOF Then Call CreateQuery("qry" & sTarget, Replace(strBaseSQL, "|FIELDS_HERE|", Left(sSQL, Len(sSQL) - 1)))   'Last one
    Loop

End Sub

Public Sub CreateQuery(sQueryName As String, sQuerySQL As String)

    Dim qdf As DAO.QueryDef, qdfMk As DAO.QueryDef
    
    On Error Resume Next
    DoCmd.DeleteObject acQuery, sQueryName
    DoCmd.DeleteObject acTable, Replace(sQueryName, "qry", "tbl")
    
    Set qdf = CurrentDb.CreateQueryDef(sQueryName, sQuerySQL)
     
    If Forms![F01_MainMenu].chkCreateMK = True Then                 'Copy SELECT queries and makes them MAKE TABLE queries
      Set qdfMk = CurrentDb.CreateQueryDef("mk_" & sQueryName, "SELECT [" & sQueryName & "].* INTO " & Replace(sQueryName, "qry", "tbl") & " FROM [" & sQueryName & "];")
    End If
        
    If Forms![F01_MainMenu].chkExecuteMK = True Then                'Execute MAKE TABLE queries
        CurrentDb.Execute "mk_" & sQueryName, dbFailOnError
    End If
    
    CurrentDb.QueryDefs.Refresh
        
    Application.RefreshDatabaseWindow
    
    iCounter = iCounter + 1                                         'Increment public new tables counter
    
End Sub

Public Sub DeleteAllQueries()

    'Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim n As Integer
 
    'For loop for deleting queries
    Set db = CurrentDb
    For Each qdf In db.QueryDefs
        If Left(qdf.Name, 6) <> "01_qry" Then
            DoCmd.DeleteObject acQuery, qdf.Name
            n = n + 1
        End If
    Next
    
    If n = 0 Then
        'Do nothing
    Else
        MsgBox n & " queries have been deleted.", vbInformation, "Delete Queries"
    End If

End Sub

**Minty** · 07-13-2021, 07:14 AM

Change your code here;

Code:

 Dim t As TableDef
    For Each t In CurrentDb.TableDefs
        If t.Name = "00_tbl_MMAC" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_MMAC];"
        If t.Name = "00_tbl_NNSY" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_NNSY];"
        If t.Name = "00_tbl_PHNSY" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_PHNSY];"
        If t.Name = "00_tbl_POAIRS" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM [00_tbl_POAIRS];"
        Debug.Print strBaseSQL
    Next


   Debug.Print "Final string = " &  strBaseSQL

You will see that you are stepping through all the tables, but that strBaseSQL is reset everytime to the next table before you use it, eventually it gets set to the last table and you then use that in the rest of your code.
The For loop needs to be used to set the strBaseSQL then before you issue the NEXT command you carry out all the other shinanigans .

EG

Code:

For each t ....

    All you other code

Next

**skydivetom** · 07-13-2021, 07:29 AM

Ok... seems like I'm now in a never-ending loop...

Code:

Public Sub UpdateQueries()

    'Declare variables
    Dim rs As DAO.Recordset, sTarget As String, sField As String
    Dim sCurrent As String, i As Integer
    Dim sSQL As String, sFieldAlias As String
    Dim strBaseSQL As String
    Dim v, sOrganizationFilter As String
       
             
    Dim t As TableDef
    For Each t In CurrentDb.TableDefs
       
        If t.Name Like "00_tbl_*" Then strBaseSQL = "SELECT |FIELDS_HERE| FROM " & t.Name
        
        

       
       
       
       
    'Reset counter for new tables
    iCounter = 0
            
    'Delete all queries
    If Forms![F01_MainMenu].chkDeleteAllQry = True Then Call DeleteAllQueries
            
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_FieldMapping Order BY TABLE_NAME, FN_STANDARDIZED;", dbOpenSnapshot)
       
    
    If rs.RecordCount = 0 Then Exit Sub
    sTarget = ""
    sField = ""
    sSQL = ""
    
    sTarget = rs("TABLE_NAME")                                      'First record
    Do Until rs.EOF
        sField = rs("[FN_LEGACY]")
        sCurrent = rs("TABLE_NAME")
        sFieldAlias = Nz(rs("FN_STANDARDIZED"), sField)             'Allows to only add alias where different than original field name (i.e., FN_LEGACY)
        If sTarget <> sCurrent And i > 1 Then
            sSQL = Left(sSQL, Len(sSQL) - 1)
            sSQL = Replace(strBaseSQL, "|FIELDS_HERE|", sSQL)
           
            Call CreateQuery("qry" & sTarget, sSQL)
            sTarget = sCurrent                                      'Reset target
            sSQL = ""
        End If
        If sFieldAlias = sField Then
            sSQL = sSQL & "[" & sField & "],"
        Else
            sSQL = sSQL & "[" & sField & "] as " & sFieldAlias & ","
        End If
    i = i + 1
    rs.MoveNext
    If rs.EOF Then Call CreateQuery("qry" & sTarget, Replace(strBaseSQL, "|FIELDS_HERE|", Left(sSQL, Len(sSQL) - 1)))   'Last one
    Loop








    Next










End Sub

**Minty** · 07-13-2021, 07:49 AM

Without delving into your current (Working I assume) process too much, I'm puzzled by iCounter which is set to 0 then never does anything.
Should that be the variable i as that is incremented later in the original rs loop?

I would take a step back and write down the steps you need to achieve (in order) for each table.
e.g.
Do you need to load the record set "SELECT * FROM 01_tbl_FieldMapping Order BY TABLE_NAME, FN_STANDARDIZED" in the loop or is it a one off that needs stepping though for each table?
Does it need to be outside the table loop or inside it?

You need to work out how the diffent parts of nested loops interact with each other.
Pen and paper or post-it's can help here, if your mind doesn't work in a way to "see it"

Not being critical, just some people find this stuff easy, and some don't.
If you don't, it's probably more important to grasp the methodology and slowly work through it than just dive in.

**skydivetom** · 07-13-2021, 10:08 AM

Minty -- thank you... I appreciate the feedback. It's working now... your recommendation worked.

Update VBA routine to process fieldname changes for multiple tables

Thread Tools

Update VBA routine to process fieldname changes for multiple tables

Similar Threads

Dual listbox -- change process to allowing multiple selection acoss multiple other values

Best way to update multiple existing tables in Access with data from multiple excel tables?

Forms that update multiple tables, including junction tables

Notinlist routine preventing following routine from running

How to reference a fieldname with a variable in DAO's rs!FieldName syntax

Posting Permissions