Sincronize two Access Tables in different Dbase

[neideb]

I wish I could read records in two tables (two DBase but same fields) and update missing records.
I already have a module but I am having intermitent errors.
I would appreciate your help. Thanks a lot

 

[arnelgp]

you should first determine what Field holds unique key (primary key).
you then just insert records where the key is missing.
then do an update for missing columns info.

sample code:

Private Sub t()
Dim db As DAO.Database
Set db = CurrentDb
'Insert missing records
db.Execute "insert into [Table1] select * from [Table1] As T in '" & _
    Environ("userprofile") & "\pathName\Externaldb.accdb' where T.PK not in (select PK from Table1]);"

'Insert missing field values    
db.Execute "update [Table1] As A, " & _
    "(Select * From [Table1] In " & _
    "'" & Environ("userprofile") & "\pathName\Externaldb.accdb') As B " & _
    "Set A.Field1 = B.Field1, A.Field2=B.Field2 where B.PK = A.PK;"


End Sub

 

[theDBguy]

Hi. Are we talking bi-directional synchronization or is one db always the master?

 

[ebs17]

but I am having intermitent errors

Do something about the errors.
Did you notice the content of the reports?

 

[Pat Hartman]

@neideb This question always raises lots of red flags since it is really poor practice to store the same data in multiple places which seems to be the situation you are in. How do you know which version is correct? That is what the question about "master" is all about. If one table is the "master", in theory, you don't have to "update" anything. You can just delete the copy and replace it with rows from the "master".

Sometimes we need to do this because we are building an application that will replace one or more old applications and we need to combine two sets of data. This is much more of a challenge because you will almost never know which file is the "master". In fact, you may have trouble even finding a column on which to join the two tables.

We really need a lot more information on what problem you are trying to solve to provide the most rational answer.