Auto Append data from another database

[Eagle2012]

I'm trying to find a way to append a table A (from another database, named dbA, chosen each time from different location) into an opened dbB (tables with same structure), while keeping in mind the 2 following options:


1- both databases are located in the same folder (same path)
2- prompt to choose dbA (where db to append is taken from).
With appreciation to your help.

[June7]

Here is example code from my project:

Private Sub btnImport_Click()

Dim strImportDataPath As String
Dim strTable As String
Dim intStep As Integer
Dim strProjNum As String
strImportDataPath = "C:\" & Me.tbxFieldFile
'get proj_num from import file
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection
cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strImportDataPath)
Set rs = New ADODB.Recordset
rs.Open "SELECT proj_num FROM projects;", cn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
    MsgBox "No records to import."
ElseIf IsNull(DLookup("proj_num", "projects", "proj_num='" & rs!proj_num & "'")) Then
    For intStep = 1 To 4
        Select Case intStep
            'need table name to use in the INSERT command
            Case 1
                strTable = "projects"
            Case 2
                strTable = "co_inputs"
            Case 3
                strTable = "OverUnder"
            Case 4
                strTable = "rates"
        End Select
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO " & strTable & " SELECT * FROM [" & strImportDataPath & "]." & strTable & ";"
        DoCmd.SetWarnings True
    Next
    Me.Requery
Else
    MsgBox "Project number already in database."
End If

End Sub