asked on

ACCESS VBA to merge tables with additional field

Hi ,

I have 10 tables of the identical structure Tb1, Tb2 ..TB10

The TB_merge is a table to merge all the rows in the 10 tables . But with an additional column called Origin . This is to identify the origin of the row ( This For each row from Tb1, the column Origin should be filled with Tb2 .. For each row from Tb2 the column should be filled with Tb2 )

How can I do this in a VBA ( or any other means )

Thanks

SOLUTION

Barry Cunney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sam OZ

ASKER

Thanks for the comments . I was looking for a way of automation than manual . Because the number of Tables can change later ( But I can assume it will be tables starting with TB_)

Infact, My real need is to have a column by name Origin to identify from which Table the row is originated from

Dale Fye

Or another option, since you don't know how many tables you are going to have, write a VBA subroutine which loops through the tables (you could either pass the table names in as a parameter array or if they all have the same name with the exception of the value 01-10, then you could just build the table name at run time.

Adding the Origin is actually the easy part.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

Comments above are fine.
You mentioned: " I was looking for a way of automation than manual . "

Here is a comment to try.
Assume in a form 3 text boxes (prefix, noFrom, noTo) and a command button (processDo

Private Sub processDo_Click()
Dim prefix As String
    Dim sql As String
    Dim i As Integer
    'validate fields
    If Me.prefix <> "" Then
    Else
        Me.prefix = "TB_"
    End If
    If Me.NoTo < NoFrom Then
        Me.NoTo = Me.NoFrom
    End If
    'loop to process
    DoCmd.SetWarnings off
    For i = Me.NoFrom To Me.NoTo
        sql = "INSERT INTO " & Me.prefix & "merge" _
             & " SELECT * FROM " & Me.prefix & i
        DoCmd.RunSQL sql
    Next
    DoCmd.SetWarnings True
End Sub

Open in new window