Troubles with data synchronization (1 Viewer)

[KernelK]

What I am attempting to do is to update records on a remote
database with records from a local database. The two tables are
identical in design, with the same primary keys and the same
information. I want to update the remote database with changes from the
local database and append any new records from the local database to the remote database as well.

I set up two boolean fields in my tables, one called Uploaded that I mark True if this record has been uploaded and it gets marked False by the local DB if the record is modified, and one called NewRecord so I can tell if this record will
need to be appended instead of modified. So, I can run SQL Select querys
against my local DB to tell which records will need to be sent to the
remote DB for modification and/or appending. With me so far?

Now my problem is, I cannot figure out how to send the information there. I
use ADO for most of my VBA data handling. I attempted to create a
recordset by running a SELECT statement against my local table to pull
all records that have Uploaded = False and NewRecord = False (ie the
record has been modified). After retrieving my "modified" records from the local table, I then set the activeconnection property of
the recordset to the remote table location. I then called the updatebatch method to update the records.

The code runs perfectly without returning an error but does not update the records in the remote table. I suspect it is because I never modified any of the records once they were in my recordset and as such are not flagged to update.

Any ideas on making this method work? Or possibly a better way to do this? I was thinking of doing a docmd.runsql statement, but cannot figure out how to use a local table and a remote table at the same time for that to work. And
then after I get the update to work, I should hopefully be able to tweak
that method some to append all the NewRecord = True antries.

Thanks in advance.

 

[KernelK]

One more thing

Oh, and for performance reasons, I do not want to link the tables from the remote DB in the local DB. I have multiple users with a distributed front-end set up and am currently attempting to minimize my connections to the DB. Hence, this batch update of records neccessity. Thanks!

 

[KernelK]

Works like this, but there has to be a faster way.....

I got it to work using this code, but there has got to be a faster way than manually stepping through my local recordset.

Public rsLocalData As ADODB.Recordset
Public rsRemoteData As ADODB.Recordset

Public Function SynchronizeData() As Boolean
Dim cnRemote As ADODB.Connection
Dim mark As Variant

    Set rsLocalData = New ADODB.Recordset
    With rsLocalData
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .Open "SELECT * FROM tblDailyRecords WHERE Uploaded = False ORDER BY DateStamp", CurrentProject.Connection
        .ActiveConnection = Nothing
    End With
        
    If Not rsLocalData.BOF And Not rsLocalData.EOF Then
        Set cnRemote = New ADODB.Connection
        cnRemote.Open gstrDBLocale, gUserCon, gPassCon
        Set rsRemoteData = New ADODB.Recordset
        
        With rsRemoteData
            .CursorType = adOpenKeyset
            .CursorLocation = adUseClient
            .LockType = adLockBatchOptimistic
            .Open "SELECT * FROM tblDailyRecords WHERE DateStamp >= #" & rsLocalData!DateStamp & "# AND Owner = '" & CurrentUser & "'", cnRemote
            .ActiveConnection = Nothing
        End With
        
        Call SaveFields
        rsLocalData.MoveNext
        
        Do Until rsLocalData.EOF
            rsRemoteData.Find "DateStamp = #" & rsLocalData!DateStamp & "#", 1, adSearchForward
            Call SaveFields
            rsLocalData.MoveNext
        Loop
        
        rsRemoteData.ActiveConnection = cnRemote
        rsRemoteData.UpdateBatch
        rsLocalData.ActiveConnection = CurrentProject.Connection
        rsLocalData.UpdateBatch
        
        cnRemote.Close
        Set cnRemote = Nothing
        Set rsLocalData = Nothing
        Set rsRemoteData = Nothing
        SynchronizeData = True
        MsgBox "Done"
    Else
        SynchronizeData = False
        MsgBox "Failed"
    End If
End Function

Public Sub SaveFields()
    rsRemoteData!Source = rsLocalData!Source
    rsRemoteData!SubSource = rsLocalData!SubSource
    rsRemoteData!Tech = rsLocalData!Tech
    rsRemoteData!Site = rsLocalData!Site
    rsRemoteData!Account = rsLocalData!Account
    rsRemoteData!ServiceNum = rsLocalData!ServiceNum
    rsRemoteData!Issue = rsLocalData!Issue
    rsRemoteData!Solution = rsLocalData!Solution
    rsRemoteData!Action = rsLocalData!Action
    rsRemoteData!Comments = rsLocalData!Comments
    rsRemoteData!FollowUp = rsLocalData!FollowUp
    rsRemoteData!FollowDate = rsLocalData!FollowDate
    rsRemoteData!CallOut = rsLocalData!CallOut
    rsRemoteData!RepName = rsLocalData!RepName
    rsLocalData!Uploaded = True
End Sub

 

[Pat Hartman]

Look into replication.

 

[KernelK]

But...

But doesn't replication actually store a replica of the entire DB/Table? I don't actually need any information stored locally after the Back end gets updated. In fact, the next time the user grabs a copy of the front end (updates, lost whatever) there won't be any local data in the tables anyway. The only reason I'm storing information locally at all is to minimize the amount of simultaneous connections to the back end of the DB which is slowing down production. Is there a way to replicate the information from the local copy to the remote copy, but not keep a complete copy of the entire remote table locally?

 

[gemma-the-husky]

why dont you temporaily connect to the remote table, either manually or with code.

then you can run find_unmatched query, find_matched query and use that to append records to the remote table, set values in your own tables etc.

If you are using recordsets you still have a connection to the data. Using stored queries seems a lot easier and safer to me than having to write complex code