For Loop (Append Query) must automatically read-in/parse table name into master table

[skydivetom]

Hello:



I would like to get some assistance with modifying a working VBA routine (append records across multiple tables into single 'master' table).

Attached DB contains the following:
- 1 table with object prefix "T200_" (master table into which I append all records)
- 4 tables with object prefix "T201_" (which records will be appended into the master table)
- The field structure is identical across these 5 tables.
- For demo purposes only, all "T201_" tables contain only a single record.

Existing Process:
- Open form and click command button.
- This will invoke the function (in module) and loop through all "T201_" table and, in this case, insert 4 records into master table "T200_".
- As part of the execution, table "T200_" will always be emptied so I will never have more than the total sum of records across the "T201_" tables.

... so far so good.

What I Envision:
- Given the APPEND routine works fine, I later on want to delete all "T201_" tables.
- The problem is that -- presently -- I won't know the source file (from which table the record what appended).
- Thus, **without having to modify any of the "T201_" tables**, I would like to tweak the existing VBA and "read" the table name ...
- and then insert the table name into a new field [T200_Syslog_Merged].[SYSLOGNAME].

My question:
How does the VBA need to be modified so that the ForLoop reads in the table name and then, for each record in the processed "T201_" table inserts, e.g., "T201_Syslog_xxx" into [T200_Syslog_Merged].[SYSLOGNAME]?

Thank you,
Tom



Below is the proposed (but not working) VBA:

Option Compare Database
Option Explicit
  
Public Sub MergeSyslogs()
    
    'Declare variables
    Dim tdf As DAO.TableDef
    Dim sTable As String
    Dim sField As String
    Dim SQL As String
    Dim i As Integer
    
    If MsgBox("All existing messages will be deleted. Are you sure you want to refresh the T200_Syslog_Merged table?", _
        vbOKCancel + vbDefaultButton2 + vbQuestion, "Confirm T200_Syslog_Merged Refresh") = vbCancel Then Exit Sub
        
    'Clear the table
    CurrentDb.Execute "DELETE * FROM T200_Syslog_Merged;", dbFailOnError
    
    'Start looping through the table definitions
    For Each tdf In CurrentDb.TableDefs
        sTable = tdf.Name
            If Left(sTable, 5) <> "T201_" Then GoTo NEXT_TDF
            
            
            MsgBox tdf.Name
                    
                'Regular LK_ tables with TARGET_VALUE field
                sField = tdf.Fields(0).Name
                
                'Create SQL string
                SQL = "INSERT INTO T200_Syslog_Merged ( SYSLOGNAME, AUTHENTICATION, DATETIMESTAMP, DTS_MILLISECONDS, MESSAGE )" & _
                       "SELECT " & sTable & ", " & sTable & ".AUTHENTICATION, " & sTable & ".DATETIMESTAMP, " & sTable & ".DTS_MILLISECONDS, " & sTable & ".MESSAGE " & _      '** Not exactly certain how "sTable" variable needs to be utilized for SYSLOGNAME
                       "FROM " & sTable & ";"
                         
                'Execute query
                CurrentDb.Execute SQL, dbFailOnError
                
                'Increment counter
                i = i + 1
            
NEXT_TDF:
    Next tdf
    
    MsgBox "All done! The T200_Syslog_Merged table has been refreshed. " & _
           DCount("*", "T200_Syslog_Merged") & " messages have been added from " & i & " tables.", vbInformation, "T200_Syslog_Merged Refresh Completed"
           
End Sub

[skydivetom]

Solution:

                'Create SQL string
                SQL = "INSERT INTO T200_Syslog_Merged ( SYSLOGNAME, AUTHENTICATION, DATETIMESTAMP, DTS_MILLISECONDS, MESSAGE ) " & _
                       "SELECT '" & sTable & "' AS SYSLOGNAME, " & sTable & ".AUTHENTICATION, " & sTable & ".DATETIMESTAMP, " & sTable & ".DTS_MILLISECONDS, " & sTable & ".MESSAGE " & _
                       "FROM " & sTable & ";"