Updating a table in a centralized location to include data from multiple identical databases

[mkltmsck]

Hi,



I'm trying to figure out how to build either a make table query or an append query or some other, preferably simple, method to consolidate data from multiple "branch" databases into a single centralized database.

In each branch database, there is a table, the structure of which is identical across all branches. In the centralized database, the structure is a little different: all columns that exist in the branch dbs also exist in the central db. However, also in the central db is a column to tell me which branch each row came from.

So, I have a few things I'm trying to figure out:

1. What is the best way to pull in the data from the branch databases? It looks like, in this instance, an append query would need to execute in each branch database and PUSH the data to the central db. This is not ideal, as I'd prefer a "single button" experience.

2. As I go branch-to-branch, I need to populate the branch field with data that doesn't exist in the branch database. I could technically modify the branch databases to have this information if that's the only way to go (and doing this is not the end of the world).

An automated, schedulable solution is not required.

I'd give myself a beginner-to-intermediate status as a builder of Access systems, which is to say, if some more savvy users can at least point me in the right direction, it's likely I'll be able to solve the problem.

[June7]

I have a similar situation. Data is entered to db for a single project at remote construction site. File sent to main office and data imported. One decision I made was not to use autonumber generated primary/foreign key in any relationships. The file distributed to the field is essentially the same as the master. The project receives empty tables and the 'Import' button is disabled.

1. I use VBA to execute SQL action for import to 4 tables. Excerpt:

Private Sub btnImport_Click()
On Error GoTo error_proc
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
exit_proc:
    If Not cn Is Nothing Then
        Set cn = Nothing
    End If
    If Not rs Is Nothing Then
        Set rs = Nothing
    End If
    Exit Sub
error_proc:
    MsgBox Err.Number & " : " & Err.Description & vbCrLf & vbCrLf & "Import failed, contact administrator."
    Resume exit_proc
    
End Sub

2. Additional data can populate fields with SQL UPDATE action.