Database synchronization problem..

[MarQ]

As for carelessness, i made this mistake on the database. I made a program and installed it on a single computer with a database of MSAccess. then after a month, they told me they want to install it to another computer but not LAN. so i installed without thinking that if I installed the program to another computer, the data on PC1 is not on PC2 and vice versa. so in making reports lets say for month of November, they have separate instead of 1. So the problem is, i want to put all the records of PC2database which are not in PC1databse..thanks in advance.

 

[jebenson]

Just a few thoughts on the subject:

1) If the PCs have Internet access (and the data are not too sensitive), post the DB from one of them to an online storage site (e.g., [URL unfurl="true"]http://www.xdrive.com/[/url], only cost $10 for a month's use) then download it to the other PC and perform a merge....

3) Burn the data from one PC to a CD and copy it to the other PC....

4) Set up a temporary LAN.

All of these will require you to write some sort of code to merge the tables. Also, you need to make sure that there are no duplicate records between the two DBs. Finally, whichever PC has its DB copied should have the DB cleared of all records, to avoid duplication if the DBs are ever merged again in the future.

I'm sure there are some other solutions. Anybody?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[MarQ]

1.just askin...what is the purpose of having Xdrive? in our place..$10 is too expensive..
2. I can setup a temporary LAN..
the real problem is, how can i transfer the PC2database to PC1database without erasing the data on PC!database, just inputting all records in PC2database to PC1database that are not present.
3. any idea how i ca merge the table?

 

[jebenson]

Something like this:

Dim Conn1 as New Connection
Dim Conn2 As New Connection

Conn1.ConnectionString="connection string for source database"
Conn2.ConnectionString="connection string for destination database"

Conn1.CursorLocation=adUseClient
Conn2.CursorLocation=adUseClient

Conn1.Open
Conn2.Open

Dim rs1 As New Recordset
Dim rs2 As New Recordset

Dim SQLStr as String

SQLStr = "SELECT * FROM SourceTableName"

rs1.Open SQLStr, Conn1
rs1.MoveFirst

Do While Not rs1.EOF
    SQLStr = "SELECT * FROM DestinationTableName WHERE SomeField1=" & rs1!SomeField1 & " AND SomeField2=" & rs1!SomeField2

    rs2.Open SQLStr, Conn2

    If rs2.RecordCount = 0 Then
        'Record in source table not found in destination
        'table, so perform an insert with the source data.
    EndIf

    rs2.close
    Set rs2=Nothing
   
    rs1.MoveNext
Loop

The key is using a field or fields that will uniquely identify each record. I would NOT use any ID field or autonumber field as they could have duplicate values between the two tables but actually be identifying different records.

Finally, be sure to make backup copies of both DBs before you start trying to merge the data, so if it doesn't go well at least you haven't destroyed the data.

I hope this helps.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[MarQ]

i will be trying the code...thanks for the help.