sync two tables at different locations across a network

[tamus121]

Have been thinking about this but still haven't a clue how to do it so can't show any progress.

I have a database (database 1) which isn't always accessable to users who need excess to the data in one of the tables so a link to the table isn't possible. I can store a copy of the required table (database 2) at a location which is accessable but rather than cut and paste the data every day I would like to automate this when records are added, deleted or amended in database 1 by a user so that the table in database 2 will reflect the changes made.(and without the user being invloved).

Again I can't link from database 2 to database 1 but I have a known path for the location of database 2. I could link to the required table on database 2 from database 1 but how do I get the updates to both tables. The simple solution would be to move database 1 to the same location as database 2 but I can't do that.

The only thing that I can think might be part of a solution would be to use recordset, but as yet I have no idea how to go about it. Again thanks for any help.

tamus

 

[Triacona]

Dear Tamus,

What aboout having the table in database 2 and link the table to database 1?
Failing that an update query?

Hope this helps...

Thank you,

Kind regards

Triacona

 

[tamus121]

The table along with the rest of the tables forms etc are supposed to be in a designated secure location. I could place and link the table in the way you suggest but it will not meet company policy. If I go down the route of asking for permissions for this exception I would probably ask to move the whole database to the second location. This might have to be the answer or else just copy and paste every few weeks.

I don't think I can use an update query to add or delete records.

 

[foxup]

Hi Tamus121,

Here's TRICK #1. What you need to do is implement a timestamp column in your table, and update the value during your data changes.

The only way to detect if data in the table has changed is to perform a query against the table. There isn't a way without "manually" writing to a column each time you access the table.

You must add a column of type DATETIME to the table e.g. named LastUpdatedDate that indicates the last updated date/time of each row. Make it NOT NULL so that you will have to write an updated DATETIME value to that column for each INSERT or UPDATE. Also, set the column to have a default of DATE() for the current date stamp or NOW() for the current date/time stamp. Then add a Validation Rule or CHECK constraint e.g. CHECK (LastUpdatedDate = NOW()) to ensure the column is actually updated on each UPDATE and INSERT.

Finally, run a MAX(LastUpdatedDate) query and you will get what you need.


TRICK #2: You can simply create a simple DOS batch file that "COPY" ies the desired file in question and put that batch file in a Scheduled Windows Task to run every 15 minutes (or whatever you prefer) from a dedicated computer. Advise your staff that the file in question gets updated every 15 minutes and all is hunky dory.

Hope that helps,
FOXUP!

 

[MazeWorX]

Trick3 Microsoft has solved this problem for us - replication -you create a replicated version of db 1 in location 2. Access adds all of the required fields to your data tables to keep track of and manage changed records. You can use access to sync the 2 or it can be coded in vba. See the code below to automate this process. Replication will also give you the ability to handle conflicts (Where one user updates a record while another is editing)

Sub used to sync our db. As you can see I have added the diff. possibilities to sync a db into one sub

Sub SynchronizeDBs(strDBName As String, strSyncTargetDB As String, intSync As Integer)

    Dim dbs As Database
    Set dbs = DBEngine(0).OpenDatabase(strDBName)
    Select Case intSync
    Case 1    'Synchronize replicas (bidirectional exchange).
        dbs.Synchronize strSyncTargetDB, dbRepImpExpChanges
    Case 2    'Synchronize replicas (Export changes).
        dbs.Synchronize strSyncTargetDB, dbRepExportChanges
    Case 3    'Synchronize replicas (Import changes).
        dbs.Synchronize strSyncTargetDB, dbRepImportChanges
    Case 4    'Synchronize replicas (Internet).
        dbs.Synchronize strSyncTargetDB, dbRepSyncInternet
    End Select
    dbs.Close
End Sub

This code can be called from a cmdbutton or anywhere else in order to fully automate the process.

Public Function Sync()

    Dim strServerPath As String
    Dim intRetry As Integer

    intRetry = 0

SyncStart:
    On Error GoTo Sync_Error
    strServerPath = "TheLocationOfYourReplicateddbHere.mdb"

    Call SynchronizeDBs(CurrentProject.Path & "\Mydb.mdb", strServerPath, 1)

Sync_Error:
    If Err.Number = 70 Then
        Resume Next
    End If

    If Err.Number = 3052 Then
        intRetry = intRetry + 1
        If intRetry = 1 Then
            DAO.DBEngine.SetOption dbMaxLocksPerFile, 175000
            GoTo SyncStart
        ElseIf intRetry = 2 Then
            DAO.DBEngine.SetOption dbMaxLocksPerFile, 200000
            GoTo SyncStart
        ElseIf intRetry = 3 Then
            DAO.DBEngine.SetOption dbMaxLocksPerFile, 250000
            GoTo SyncStart
        ElseIf intRetry = 4 Then
            MsgBox "failed to Sync data 3 times." & vbNewLine & _
                   "Please contact Development team to trouble shoot this issue - MaxLocks error"
            DoCmd.Hourglass False
            Exit Function
        End If
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Sync"
        DoCmd.Hourglass False
        Exit Function
    End If
End Function

Above the code has been modified for this example, it is, in my case a small part of an admin db that updates and syncs data nightly to 39 db's. In my case this is unattended which is why I place the code into a function rather then attached to a cmd button. This way I call the function from a macro. I Add a Scheduled task to windows and start the db with the cmd line switch that runs a macro in turn runs my function all the while I'm tucked cozily in bed

Also to note with replication Access creates a table with any conflicts you can if you want check this table for data to create you own conflict manager although the access conflicts manager works well enough for most applications





HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>