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