Mirror one table from 1 db to another

I have 2 jet DBs located on 2 different machines.
The structure of the 2 DBs is identical.
A set of users makes changes to DB A while another set users makes changes
to DB B.
I need to be able to merge data regularly from a certain table on DB B to
the corresponding table in DB A.
I seem to recall that there's a way with UpdateBatch that won't require
having to loop thru the records and comparing them.

Here's the code I came up with. I t doesn't work.

Any help appreciated.
TIA.

Andy

'*********************************************************************************
Public Sub MirrorTable()
Dim oConn1 As Connection
Dim oConn2 As Connection
Dim rs As Recordset
Dim sDB1 As String
Dim sDB2 As String

' Main routine

' Assemble db path's
sDB1 = App.Path & "\db1.mdb"
sDB2 = App.Path & "\db2.mdb"

' Open connection 1
Set oConn1 = New Connection
oConn1.ConnectionString = "Provider=MSDataShape.1;Persist Security
Info=False;Data Source=" & sDB1 & ";Data Provider=MICROSOFT.JET.OLEDB.4.0"
oConn1.Open

' Open table
Set rs = New Recordset
rs.CursorLocation = adUseServer
rs.Open "MyTable", oConn1, adOpenDynamic, adLockBatchOptimistic,
adCmdTable

' Open connection 2
Set oConn2 = New Connection
oConn2.ConnectionString = "Provider=MSDataShape.1;Persist Security
Info=False;Data Source=" & sDB2 & ";Data Provider=MICROSOFT.JET.OLEDB.4.0"
oConn2.Open

rs.ActiveConnection = oConn2
rs.UpdateBatch ' -> No update takes place!!!

End Sub




.

Relevant Pages

  • Re: Updating Access data using SQL / refresh time question
    ... As a test today, for one poarticular recordset, I changed from DAO to ADO to ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ... > ' Set the recordset object each time we load the listview ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ... > ' Set the recordset object each time we load the listview ...
    (microsoft.public.vb.database)
  • Re: making datareaders
    ... ''' This is set in the connection string by setting the ... Dim myAddressReader As SqlDataReader ... Dim myFinalString As String = "" ...
    (microsoft.public.dotnet.languages.vb)
  • Re: making datareaders
    ... You have to close the datareader when you are done with it, or the connection won't be usable again. ... ''' This is set in the connection string by setting the ... Dim myAddressReader As SqlDataReader ... Dim myFinalString As String = "" ...
    (microsoft.public.dotnet.languages.vb)