Append *table names* into table (via VBA function)

[skydivetom]

Hello:

I am using a VBA function to append values from multiple tables into a single table.

With the exception of the 1st field, the structure of my tables are identical.

At this time, my VBA routine successfully appends all records (i.e., *values* from 3 tables) for field #2 ([TARGET_VALUE]), field #3 ([SOURCE]), and field #4 ([DATE_UPDATED]).

The first field (i.e., field name), however, differs as follows:
Table [LK_ACTIVITY_UIC_CODE] where 1st field name = ACTIVITY_UIC_CODE
Table [LK_BLS_INJURY_TYPE] where 1st field name = BLS_INJURY_TYPE
Table [LK_EMERGENCY_ROOM] where 1st field name = EMERGENCY_ROOM

So, while I want to append the *values* for 2nd through 4th fields {[TARGET_VALUE], [SOURCE], [DATE_UPDATED]}, I want to append the *tables names* (or fields names if necessary) for the 1st field.

My question:
How do I modify the VBA function below to also append the associated table names into [00_ALL_DISTINCT]?

Private Sub cmdAppendAllDistinct_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim TotalAppendedRecords As Long
  
    DoCmd.SetWarnings False
    'Delete all records
    DoCmd.RunSQL "DELETE * FROM 00_ALL_DISTINCT;"
    DoCmd.SetWarnings True
    
    Set db = CurrentDb()
  
        For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) <> "Msys" _
                And Left(tdf.Name, 3) = "LK_" Then
               
                'Mass APPEND query
                CurrentDb.Execute "INSERT INTO 00_ALL_DISTINCT ( TARGET_VALUE, SOURCE, DATE_UPDATED ) " & _
                                  "SELECT TARGET_VALUE, SOURCE, DATE_UPDATED " & _
                                  "FROM " & tdf.Name & " " & _
                                  "ORDER BY TARGET_VALUE, SOURCE, DATE_UPDATED;"
                
            End If
        Next tdf

    'Throw message box to indicate record append status
    MsgBox "All records were successfully appended to table [00_ALL_DISTINCT].", vbInformation, "Update Status"

End Sub

P.S. See attached JPG illustrating the desired output.

[kd2017]

Remove the outer conditional that checks for msys tables, seems redundant given the inner conditional.

Add the table name column's name, TABLE_NAME, to the insert part of the sql statement.

Add table name value, tdf.name, from the loop to the select statement at the appropriate place.

Easy peasy lemon squeezy

https://www.w3schools.com/sql/sql_in...nto_select.asp

[skydivetom]

kd2017 -- thank you... sounds like "easy peasy lemon squeezy", but apparently I'm still missing some parameters (see attached).

Updated VBA is shown below:

        For Each tdf In db.TableDefs
            If Left(tdf.Name, 3) = "LK_" Then
               
                'Mass APPEND query
                CurrentDb.Execute "INSERT INTO 00_ALL_DISTINCT ( TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED ) " & _
                                  "SELECT " & tdf.Name & ", TARGET_VALUE, SOURCE, DATE_UPDATED " & _
                                  "FROM " & tdf.Name & " " & _
                                  "ORDER BY " & tdf.Name & ", TARGET_VALUE, SOURCE, DATE_UPDATED;"
                
            End If
        Next tdf

What am I missing?

[kd2017]

The issue is in your select statement. Anytime I build sql statements in code like this I will build them in a string variable so that if there are errors I can print the attempted sql statement and inspect it for any syntax issues.

Run time error 3061 is usually caused by incorrect spelling in a sql statement, ie you've got a typo in a field name or table name somewhere.

In this case the problem is with this part: "SELECT " & tdf.Name & ", ..."

Interpolating the table name here is telling the sql engine to select tdf.name field from the tdf.name table but that field doesn't exist in that table. What you want to do is select a string of tdf.name.

You want:
SELECT "some string" AS some_field, some_other_field FROM some_table;

vs what you tried:
SELECT some string, some_other_field FROM some_table;

[CJ_London]

"SELECT " & tdf.Name & ",

tdf.name will be a string? What do you surround strings with?

And there little point in applying an ORDER BY - a) it will slow the process down (although perhaps not enough to matter) and more importantly b) data is stored in tables in random order, so your order by will be ignored anyway. If you do happen to see data in the right order, that is a happy coincidence

[skydivetom]

kd2017 -- the info about "Run time error 3061 is usually caused by incorrect spelling in a sql statement" was helpful. For testing purposes, I added "LK_" prefixes in front of each 1st field's fieldname. So, while this executed, it appended the *values* (vs. table names) into the table. Hence, I removed the "LK_" prefixes from the fieldnames again.

Ok, I added a string "sField". Also, added a temporary msgbox. During execution, the 1st loop, the message box displayed: "LK_ACTIVITY_UIC_CODE", so I presume the sField stores the correct value.

However,

SELECT " & sField & " AS TABLE_NAME,

still results in the parameter error. Below is the full code. Not as easy peasy as I had hoped... I'm sure the answer stares me right into my face. Anyhow, what am I still missing?

    Dim sField As String
  
    DoCmd.SetWarnings False
    'Delete all records
    DoCmd.RunSQL "DELETE * FROM 00_ALL_DISTINCT;"
    DoCmd.SetWarnings True
    
    Set db = CurrentDb()
  
        For Each tdf In db.TableDefs
            If Left(tdf.Name, 3) = "LK_" Then
            
            sField = tdf.Name
            MsgBox sField
               
                CurrentDb.Execute "INSERT INTO 00_ALL_DISTINCT ( TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED ) " & _
                                  "SELECT " & sField & " AS TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED " & _
                                  "FROM " & tdf.Name & ";"

[CJ_London]

You have not followed the advice provided by kd2017 or myself regarding treatment of strings.

since you continue to ignore me although you have taken my advice re ordering, I'm going to put you on my ignore list. Seems you only want help from one or two specific people.

[skydivetom]

Ajax -- sorry you feel this way.

I did NOT ignore your help! Pls consider that I purely misunderstood the advice given.

Respectfully
Tom

[skydivetom]

kd2017, Ajax:

Thank you for your assistance... it finally works. Appreciate the help.

                CurrentDb.Execute "INSERT INTO 00_ALL_DISTINCT ( TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED ) " & _
                                  "SELECT """ & sTablename & """ AS TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED " & _
                                  "FROM " & tdf.Name & ";"

[kd2017]

That's the ticket!

I would refactor the sub to use some basic error handling, object cleanup, and remove the DoCmds so you don't have to worry about setting warnings like so:

Private Sub cmdAppendAllDistinct_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qry As String
    
    Set db = CurrentDb()
    
    qry = "DELETE * FROM 00_ALL_DISTINCT;"
    db.Execute qry, dbFailOnError
  
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 3) = "LK_" Then
           
            'Mass APPEND query
            qry = "INSERT INTO 00_ALL_DISTINCT ( TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED ) " & _
                  " SELECT """ & tdf.Name & """ AS TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED " & _
                  " FROM [" & tdf.Name & "];"
                  
            db.Execute qry, dbFailOnError
            
        End If
    Next tdf

    'Throw message box to indicate record append status
    MsgBox "All records were successfully appended to table [00_ALL_DISTINCT].", vbInformation, "Update Status"
    
ExitHandler:
    Set tdf = Nothing
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

* EDIT - Note: if there is a possibility that any of these tables could have a space in their names (they shouldn't) it wouldn't hurt to put square brackets around the table names. I've edited the code above to reflect that.

[skydivetom]

kd2017 -- awesome. I will make the proposed changes. Thank you for your help in matter.

[skydivetom]

kd2017:

Again, thank you for the help earlier. With your permission, may I ask you a follow-up question?

Per my original post, I stated the following: "With the exception of the 1st field, the structure of my tables are identical."

Well, while that statement is TRUE for most tables, I realized it is NOT true for all tables. Allow me to summarize in bullet format:
- Most tables contain 8 identical field names with the only exception of the 1st field (which equals its table name minus "LK_" prefix.
- The latest VBA (incl. your proposed error handling, object cleanup, and removal of DoCmds) executes flawlessly.

Now, the caveat to my original statement "... structure of my tables are identical.":
- There are a few tables that only include 6 (vs. 8) fields. 1st field is still named table name minus "LK_" prefix.
- These 2 fewer fields, however, are included in my qry string "INSERT INTO 00_ALL_DISTINCT ( TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED )".

That said, once the For Loop gets to a table that does NOT have these 2 required fields, I get an error. Naturally, this is totally expected.

Here's my question:
Do you know of a way to include, e.g., either an IF statement or CASE statement (maybe inside the For Loop) which would check the following (pseudo):

For Each tdf In db.TableDefs
    ...
    ...
    ... 'Pseudo code...
        If tdf.Name field count = 8 then 
        
            "INSERT INTO 00_ALL_DISTINCT ( TABLE_NAME, TARGET_VALUE, SOURCE, DATE_UPDATED )"
            ...
            ...
        Else
            
            "INSERT INTO 00_ALL_DISTINCT ( TABLE_NAME, DATE_UPDATED )"
            ...
            ...
        End If

... or something like it. Ultimately, it still processes *all* tables but when it gets to a table that contains fewer fields, it then uses a different qry string.

Thoughts/recommendations? Thank you for your help in advance.

[Gicu]

Tom, you were very close, try tdf.Fields.Count
Cheers,

[skydivetom]

Vlad:

Yup... that worked like a charm. Thanks for the tip WRT "tdf.Fields.Count".

Cheers,
Tom