Need VBA routines that merges all (structure identical) tables into single table.

[skydivetom]

Hello:

I need to develop a VBA routine/function that merges n tables (w/ same field structure) into a single table named [T000_MergedData].



Additional information:
- VBA routine needs to scan through DB's TDF and apply merge routine to *only* those tables having table name prefix = "T100_".
- This sample DB includes five (5) tables where 2 tables ("T010_IgnoreInMerge" and "Table1_IgnoreInMerge") do NOT meet the prefix criteria. So, they must be ignored in the merge routine.
- To ensure record count won't be inflated, [T000_MergedData]'s record set must be deleted prior to each execution.
- If VBA runs successful, [T000_MergedData] should contain only 35 records (15 from "T100_Org1_IncludeInMerge" and 20 from "T100_Org2_IncludeInMerge") after each execution.
- Naturally, the record count from any new/additional tables starting with "T100" would need to be added to the total count.

My question:
- What does such VBA routine/loop would look like that, theoretically, merges records from dozens or hundreds of T100_ tables into single table [T000_MergedData]?

[June7]

UNION query has a limit of 50 SELECT lines so not feasible with hundreds of T100_ tables.

Have a structure that loops through tables collection (this is a common topic) and looks for "T100" prefix in table name. If match, run INSERT SELECT action SQL.

Begin procedure with DELETE action SQL.

Why don't you make an effort to write code and let us know how it goes.

[skydivetom]

The VBA code below only executes the query one (1) time. It does NOT replace the table reference in the FROM statement with any subsequent table in the TDF scan.

See attached updated sample DB with VBA (in form 'F01_MainMenu').

Please advise as to how the VBA needs to be modified so that all tables meeting the T100 criteria will have records appended as part of the loop.

Option Compare Database


Private Sub cmdMergeTables_Click()
    
    'Error handling
    On Error GoTo Err_PrematureClose_Click
    
    'Declare variables
    Dim tdf As DAO.TableDef
    Dim sTable As String
    Dim strSQL As String
    
    'Clear the table
    CurrentDb.Execute "DELETE * FROM T000_MergedData;", dbFailOnError
    
    'Get the database and associated query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Q_MergeData")
        
    'Start looping through the table definitions
    For Each tdf In CurrentDb.TableDefs
        sTable = tdf.Name
                
            If Left(sTable, 5) <> "T100_" Then GoTo NEXT_TDF
                                    
            'I need to auto-replace table reference in 'FROM T100_Org1_IncludeInMerge' with TDF table name
            strSQL = "INSERT INTO T000_MergedData ( ORGANIZATION, [MODULE], SOURCE_FIELD, TARGET_FIELD ) " & _
                     "SELECT ORGANIZATION, MODULE, SOURCE_FIELD, TARGET_FIELD " & _
                     "FROM " & sTable & ";"
           
    'Apply the new SQL statement to specified query
    qdf.SQL = strSQL


    'Open query
    DoCmd.OpenQuery "Q_MergeData", acViewNormal, acReadOnly
    
    'Empty memory
    Set db = Nothing
    Set qdf = Nothing
                    
NEXT_TDF:
    Next tdf
    
    MsgBox "All done! The T000_MergedData table has been refreshed.", vbInformation, "T000_MergedData Refresh Completed"
           
'Begin error handling
Exit_PrematureClose_Click:
    Exit Sub
    
Err_PrematureClose_Click:
    Resume Exit_PrematureClose_Click
           
End Sub

[orange]

Where are the T100_*** tables located? How many in total? What is the key difference in these T100_** tables?
I haven't looked at your zip.

[moke123]

This works for me.
Any reason you need a qrydef?

Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strSQL As String
    
    Set db = CurrentDb
    
    db.Execute "DELETE * FROM T000_MergedData;", dbFailOnError


    For Each tdf In db.TableDefs


        If Left(tdf.Name, 5) = "T100_" Then
            
            strSQL = "INSERT INTO T000_MergedData ( ORGANIZATION, [MODULE], SOURCE_FIELD, TARGET_FIELD ) " & _
                "SELECT ORGANIZATION, MODULE, SOURCE_FIELD, TARGET_FIELD " & _
                "FROM " & tdf.Name & ";"
                     
            ' Debug.Print strSQL
                     
            db.Execute strSQL, dbFailOnError
            
        End If


    Next

[skydivetom]

moke123 -- PERFECT!! That works great. I added several new tables including different tables names following the T_100 prefix.

Record count is 100% accurate... many thanks!

//

orange -- thanks for chiming in... tables were/are included in the zip file. VBA routine has been solved. Cheers.