Options for Access 2007 Database "Replication"

[dgj32784]

I've read some on the subject and there seems to be a number of different options depending on the version of Access/database file type. We are currently running MS Office 2007 (default .accdb) and collect data in an offline survey database running on TabletPCs. I'm looking for the most efficient way to "synchronize" the offline version of the database to the server "master" when we come back from the field. And by sync, I mean we need new table records from the offline dbase to be brought over/appended to the same tables in the server dbase. No changes will have occurred to queries, forms, or reports during field use (at least not any that we want preserved).

Here are some ideas I've had:

1. Programmatically (VBA) save a copy of the offline and master dbases to (.mdb) and use Microsoft Replication (http://support.microsoft.com/kb/182886) to sister up data then save back to (.accdb)
2. Put together a bunch of update/append queries to accomplish the task for (.accdb) files
3. Upgrade to MS Office 2010 and use "out-of-the-box" Database Tools > Replication
4. Use Python to loop through all tables and records in (.accdb) files and add records to master if in offline version

I'm sure there are other ways to get this done. I don't really know about #3 much because I don't have a copy of 2010. I'd prefer to avoid #2 because fields could change (add, delete, rename, etc.) over time. I pretty much need something that essentially does #4 as painlessly as possible. Any guidance would be much appreciated, including opinions/thoughts on the list above, other options, and/or examples of how you achieve something similar for dbases you manage.

Thanks,
David

[June7]

I have created dbs that programmatically update 'master' file from 'field' file. One did not involve any autonumber PKs so used append queries. Another uses autonumber PKs and in order to maintain referential integrity could not use append queries and code is much more complicated. Fortunately, only 11 tables.

My research led me to conclusion it was easier to program in VBA the data merge than figure out replication utility with Access 2007. Maybe your situation will be different.

I will look at the 2010 Replication tool and see what it offers. This seems to be a fairly good summary http://www.blueclaw-db.com/broad_int...eplication.htm

[Rawb]

You could use VBA to build your SQL INSERT Statement dynamically, based on the current structure of the Tables using the TableDefs Collection.

Then you'd just need to make sure that you always update the Tablet PCs so that their Tables match the structures of "main" DB's Tables.

[dgj32784]

Thanks June7 and Rawb for your thoughts. I've since considered going ahead and converting to a distributed database, which I haven't bothered due to the purpose of the dbase and limited number of users. The back-end would be (.mdb) and would allow for Microsoft Replication. Alternatively, I've also tried to get the replication done using Python, which is close to working but running into issues there (http://stackoverflow.com/questions/1...-python-pyodbc). I'm going to try Rawb's recommendation next. June7, any chance you can provide an example of how you programmatically handled your 11-table scenario?

[June7]

Correction, 8 tables. Here is the procedure:

Function SaveData(strFile) As String
On Error GoTo errProc:
Dim j As Integer, k As Integer
Dim strTable As String, strField As String, lngRec As Long
Dim rsSessions As ADODB.Recordset
Dim rsStations As ADODB.Recordset
Dim rsTransducers As ADODB.Recordset
Dim rsComments As ADODB.Recordset
Dim rsRemarks As ADODB.Recordset
Dim rsDrops As ADODB.Recordset
Dim rsHistories As ADODB.Recordset
Dim rsTimings As ADODB.Recordset
Dim rsDest As ADODB.Recordset
Set rsSessions = New ADODB.Recordset
Set rsStations = New ADODB.Recordset
Set rsTransducers = New ADODB.Recordset
Set rsComments = New ADODB.Recordset
Set rsRemarks = New ADODB.Recordset
Set rsDrops = New ADODB.Recordset
Set rsHistories = New ADODB.Recordset
Set rsTimings = New ADODB.Recordset
Set rsDest = New ADODB.Recordset
SaveData = "Imported"
rsSessions.Open "SELECT * FROM [" & strFile & "].Sessions;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
strTable = "Sessions"
While Not rsSessions.EOF
    lngRec = rsSessions!SessionID
    rsDest.Open "SELECT * FROM Sessions;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    rsDest.AddNew
    For j = 1 To rsDest.Fields.Count - 1
        strField = rsDest.Fields(j).Name
        rsDest.Fields(j) = rsSessions.Fields(rsDest.Fields(j).Name)
    Next
    strField = ""
    rsDest.Update
    rsDest.Close
    CurrentDb.Execute "INSERT INTO SessionStructure(SessionID, StructureID) VALUES(" & DMax("SessionID", "Sessions") & ", '" & Me.lbxStructures & "')"
    strTable = "Comments"
    rsComments.Open "SELECT * FROM [" & strFile & "].Comments WHERE SessionID=" & rsSessions!SessionID & ";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    While Not rsComments.EOF
        rsDest.Open "SELECT * FROM Comments;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
        lngRec = rsComments!CommentsID
        rsDest.AddNew
        rsDest!SessionID = DMax("SessionID", "Sessions")
        For j = 2 To rsDest.Fields.Count - 1
            strField = rsDest.Fields(j).Name
            rsDest.Fields(j) = rsComments.Fields(rsDest.Fields(j).Name)
        Next
        strField = ""
        rsDest.Update
        rsDest.Close
        rsComments.MoveNext
    Wend
    rsComments.Close
    strTable = "Remarks"
    rsRemarks.Open "SELECT * FROM [" & strFile & "].Remarks WHERE SessionID=" & rsSessions!SessionID & ";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    While Not rsRemarks.EOF
        rsDest.Open "SELECT * FROM Remarks;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
        lngRec = rsRemarks!RemarkID
        rsDest.AddNew
        rsDest!SessionID = DMax("SessionID", "Sessions")
        For j = 2 To rsDest.Fields.Count - 1
            strField = rsDest.Fields(j).Name
            rsDest.Fields(j) = rsRemarks.Fields(rsDest.Fields(j).Name)
        Next
        strField = ""
        rsDest.Update
        rsDest.Close
        rsRemarks.MoveNext
    Wend
    rsRemarks.Close
    strTable = "Transducers"
    rsTransducers.Open "SELECT * FROM [" & strFile & "].Transducers WHERE SessionID=" & rsSessions!SessionID & ";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    While Not rsTransducers.EOF
        rsDest.Open "SELECT * FROM Transducers;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
        lngRec = rsTransducers!TransducerID
        rsDest.AddNew
        rsDest!SessionID = DMax("SessionID", "Sessions")
        For j = 2 To rsDest.Fields.Count - 1
            strField = rsDest.Fields(j).Name
            rsDest.Fields(j) = rsTransducers.Fields(rsDest.Fields(j).Name)
        Next
        strField = ""
        rsDest.Update
        rsDest.Close
        rsTransducers.MoveNext
    Wend
    rsTransducers.Close
    strTable = "Stations"
    rsStations.Open "SELECT * FROM [" & strFile & "].Stations WHERE SessionID=" & rsSessions!SessionID & ";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    While Not rsStations.EOF
        rsDest.Open "SELECT * FROM Stations;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
        lngRec = rsStations!StationID
        rsDest.AddNew
        rsDest!SessionID = DMax("SessionID", "Sessions")
        For j = 2 To rsDest.Fields.Count - 1
            strField = rsDest.Fields(j).Name
            rsDest.Fields(j) = rsStations.Fields(rsDest.Fields(j).Name)
        Next
        strField = ""
        rsDest.Update
        rsDest.Close
        strTable = "Drops"
        rsDrops.Open "SELECT * FROM [" & strFile & "].Drops WHERE StationID=" & rsStations!StationID & ";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
        While Not rsDrops.EOF
            rsDest.Open "SELECT * FROM Drops;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
            lngRec = rsDrops!DropID
            rsDest.AddNew
            rsDest!StationID = DMax("StationID", "Stations")
            For j = 2 To rsDest.Fields.Count - 1
                strField = rsDest.Fields(j).Name
                rsDest.Fields(j) = rsDrops.Fields(rsDest.Fields(j).Name)
            Next
            strField = ""
            rsDest.Update
            rsDest.Close
            strTable = "Histories"
            rsHistories.Open "SELECT * FROM [" & strFile & "].Histories WHERE DropID=" & rsDrops!DropID & ";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
            While Not rsHistories.EOF
                rsDest.Open "SELECT * FROM Histories;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
                lngRec = rsHistories!HistoryID
                rsDest.AddNew
                rsDest!DropID = DMax("DropID", "Drops")
                For j = 3 To rsDest.Fields.Count - 1
                    strField = rsDest.Fields(j).Name
                    rsDest.Fields(j) = rsHistories.Fields(rsDest.Fields(j).Name)
                Next
                strField = ""
                rsDest.Update
                rsDest.Close
                rsHistories.MoveNext
            Wend
            lngRec = 0
            rsHistories.Close
            strTable = "Timings"
            rsTimings.Open "SELECT * FROM [" & strFile & "].Timings WHERE DropID=" & rsDrops!DropID & ";", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
            While Not rsTimings.EOF
                rsDest.Open "SELECT * FROM Timings;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
                lngRec = rsTimings!TimingID
                rsDest.AddNew
                rsDest!DropID = DMax("DropID", "Drops")
                For j = 3 To rsDest.Fields.Count - 1
                    strField = rsDest.Fields(j).Name
                    rsDest.Fields(j) = rsTimings.Fields(rsDest.Fields(j).Name)
                Next
                strField = ""
                rsDest.Update
                rsDest.Close
                rsTimings.MoveNext
            Wend
            lngRec = 0
            rsTimings.Close
            rsDrops.MoveNext
        Wend
        lngRec = 0
        rsDrops.Close
        rsStations.MoveNext
    Wend
    lngRec = 0
    rsStations.Close
    rsSessions.MoveNext
Wend
lngRec = 0
rsSessions.Close
exitProc:
    Exit Function
errProc:
    Debug.Print Mid(strFile, InStrRev(strFile, "\") + 1) & " : " & strTable & " : " & strField & " : " & lngRec & " :: " & Err.Number & ":" & Err.Description
    SaveData = "Failed"
    Resume exitProc:
End Function