Syncronize data from site to office

[moi]

Hello all,

Is there an easy way to syncronize record?. Situation, the main db is on the office (desktop) , 1 laptop is on site/off office..

My problem now is office desktop is updating/entering records, site is also updating/entering records, at the end of office work, site needs to upload her records to main (desktop).

Pls i need help badly..

 

[GaP42]

In simple terms - you have two separate databases. They may be structurally the same (table and relationships), I assume. You add/edit/delete data in each. Then you want some kind of merge function where changes made in each (or in one - the laptop) are applied to the other. Then if successful the updated database is copied to the laptop? Good luck with that - a nightmare in the making as you are subverting the foundational concept of DBMS to maintain a single source of data available to multiple users. I cannot provide any real knowledge / experience, but it would have to involve generating a log of the changes that need to be applied to the master (office) db - but you would have to deal with situations where the laptop copy records a transaction which when synchronisation (application of the log file) occurs finds the record against which that has been performed has been deleted or at least marked inactive/archived, and vv. The capacity to deal with the application of the changes would apply across all updateable tables.

 

[ebs17]

Is there an easy way

No. Replication, which did something like this, was eliminated with Access 2007.

Synchronizing means updating in both directions. This creates conflicts that need to be resolved. For example, if a certain value is changed differently in both databases, a decision must be made as to whose change will be adopted.

With some organizational measures you can defuse something like this, but overall it is not that easy and foolproof. You have to know what you're doing and everyone involved has to consistently follow the rules.

 

[Simon_C]

It will depend very much on moi's circumstances but it may be that rather than try to synchronize two (or more?) databases via updates to specific records, the record updates need only be pushed in one direction (i.e. from site to main office) and that a copy of the whole newly-updated 'clean' data can then be sent back to the site(s). Size of data and whether there's any downtime (i.e. are the databases aren't being operated 24-7) may make this impractical.

As ebs17 says, there may almost certainly be some conflicts where records have been updated in more than one location. If data can be created (not just updated) at both sites then there'll need to be a way of handling the unique IDs the various records have so that either new IDs are guaranteed unique across all sites or there's a way of identifying and handling a duplicate ID when it comes in.

 

[MajP]

I have built my own "replication" before. It is doable. It takes some techniques to be able to consider doing this.
1. You cannot use incrementing autonumbers for your IDs. You must use random autonumbers or roll your own on all tables.

In that video he links to a demo showing how to build a replicated database.

2. Every table needs a date time stamp. I do two. Creation date and modification date.
3. I also log changes and deletions. Something like this

Allen Browne - Creating an Audit Log

How to create an audit log to record the history of changes to records in a Microsoft Access database - edits, inserts, and deletions.

allenbrowne.com

If you are simply merging data daily then all you really need is the random autonumbers and some append queries / update queries. If you are never working in the same system at the same time that is simple. At the end of the day you update the home computer, in the morning you update the work computer. But if it is possible that both systems are modifying the same records and you want the most current changes then you need something more advanced.
Back when there was replication and you merged databases it would tell you that the record was modified on two more more databases before the last merge. It would show you the differences and you would pick what to keep. If you are doing that it is much more complicated then simply appending new records from one db into another.

 

[GPGeorge]

Hello all,

Is there an easy way to syncronize record?. Situation, the main db is on the office (desktop) , 1 laptop is on site/off office..

My problem now is office desktop is updating/entering records, site is also updating/entering records, at the end of office work, site needs to upload her records to main (desktop).

Pls i need help badly..

Have you considered a remotely hosted database (e.g. SQL Server or SQL Azure) that doesn't require synchronization between two locations, on-site and remote? All locations, on-premises and remote, are using the same data all the time.

I know there is some reluctance to implement Access/SQL in the Cloud designs because of the latency and transfer factors, but a properly designed interface minimizes that.

 

[The_Doc_Man]

The U.S. Navy's personnel system addressed this problem by building everything through transactions. We had a "transaction player" program that took a given set of transactions and "played them into the database" one record at a time. Want to add a new person? We had an ADDPERS transaction. Wanted to note that a person was transferred to a new site and billet? We had a TRNPERS transaction. Someone retired from the service? We had a DELPERS transaction. In total there were perhaps a couple of hundred meaningful transaction types. Our rule was simple: For each department, their server was authoritative for their transactions, but some other department's servers were authoritative for THEIR specialty transactions. We didn't copy finished records from system to system. We sent - and played back - transactions that would fill in whatever was needed to make THAT TRANSACTION completely recorded.

Synchronization can be a real problem since - as noted above - that feature is no longer part of Access.

 

[moi]

The U.S. Navy's personnel system addressed this problem by building everything through transactions. We had a "transaction player" program that took a given set of transactions and "played them into the database" one record at a tie. Want to add a new person? We had an ADDPERS transaction. Wanted to note that a person was transferred to a new site and billet? We had a TRNPERS transaction. Someone retired from the service? We had a DELPERS transaction. In total there were perhaps a couple of hundred meaningful transaction types. Our rule was simple: For each department, their server was authoritative for their transactions, but some other department's servers were authoritative for THEIR specialty transactions. We didn't copy finished records from system to system. We sent - and played back - transactions that would fill in whatever was needed to make THAT TRANSACTION completely recorded.

Synchronization can be a real problem since - as noted above - that feature is no longer part of Access

In short this thing is impossible to newbie like me.. considering that this is my first project..

Thanks for all your feedback..

 

[arnelgp]

My problem now is office desktop is updating/entering records, site is also updating/entering records, at the end of office work, site needs to upload her records to main (desktop).

you should add a "utility" on your db that will save all tables to an excel/text or csv at the end of the day.
then let the site email you this files.
on your end, you also add utilty on the db on your office that will consolidate these files to your office db.
it is very easy as i can see it.
after consolidating, you give them back the updated db (again via email or any file hosting).

 

[Solo712]

you should add a "utility" on your db that will save all tables to an excel/text or csv at the end of the day.
then let the site email you this files.
on your end, you also add utilty on the db on your office that will consolidate these files to your office db.
it is very easy as i can see it.
after consolidating, you give them back the updated db (again via email or any file hosting).

I have built my own "replication" before. It is doable. It takes some techniques to be able to consider doing this.
1. You cannot use incrementing autonumbers for your IDs. You must use random autonumbers or roll your own on all tables.

In that video he links to a demo showing how to build a replicated database.

2. Every table needs a date time stamp. I do two. Creation date and modification date.
3. I also log changes and deletions. Something like this

Allen Browne - Creating an Audit Log

How to create an audit log to record the history of changes to records in a Microsoft Access database - edits, inserts, and deletions.

allenbrowne.com

If you are simply merging data daily then all you really need is the random autonumbers and some append queries / update queries. If you are never working in the same system at the same time that is simple. At the end of the day you update the home computer, in the morning you update the work computer. But if it is possible that both systems are modifying the same records and you want the most current changes then you need something more advanced.
Back when there was replication and you merged databases it would tell you that the record was modified on two more more databases before the last merge. It would show you the differences and you would pick what to keep. If you are doing that it is much more complicated then simply appending new records from one db into another.

There is more than one way of doing this. My preferred method was a consolidation table which recorded all changes (inserts-updates-deletes) and sent via e-mail as excel spreadsheet. The two sides then merged the table by the date stamp and a common algorithm. My tables in the db's did not have PK autonumbers but assigned values (via DMAX) which allowed them to be updated during the merge. This allowed the crossreference for later updates to be made via PK obviating the need for date stamps in all tables.

Best,
Jiri

 

[moi]

you should add a "utility" on your db that will save all tables to an excel/text or csv at the end of the day.
then let the site email you this files.
on your end, you also add utilty on the db on your office that will consolidate these files to your office db.
it is very easy as i can see it.
after consolidating, you give them back the updated db (again via email or any file hosting).

Hi arnelgp,

Many thanks. Can you please help me what utilities these are.. I can do manually exporting to excel what are tables affected, how can i consolodate those tables.. To tell you honestly i can't even imagine the process..

I need help pls..

 

[The_Doc_Man]

To tell you honestly i can't even imagine the process..

OK, we have to be honest here... neither can we, really. Oh, we can come close, but YOU have to be the subject matter expert. YOU have to decide the right way to synchronize data between two places. It is YOUR business rules that will apply.

In a nutshell, here is the problem: You have data in a central location and you have data in some remote location. Your problem is compounded by your phrase:

office desktop is updating/entering records, site is also updating/entering records

In order to merge the work done on the two databases, you have to come to a conclusion regarding what is called "authoritative data" - which is to say, if you have different data values in two places but relating to or describing the same exact thing, only one of those values can be right. How do you tell which one? (The one you pick is the "authoritative site.")

You could not use an auto-number on the related tables at the two sites as a way to merge things, because they would potentially overlap. They would be generated in two independent places at once so without a connection, they could not be guaranteed to NOT overlap. Therefore, a direct merge with overlapping keys would probably lead to key violations.

If it were only ENTERING records, it would be easier. If there is no limitation on which records would be updated at each site, your trouble is in deciding which value in the two databases is correct. But independent updating makes it impossible to properly reconcile the two records if BOTH of their actions can occur in essence simultaneously. This is why I mentioned transactions earlier. With a transaction, you have a record that describes the change made because of the update (or perhaps explaining the update.)

I have to tell you that with 28 1/2 years of Navy experience, I wouldn't want the problem you have right now, because you are going to HAVE to control / limit how data entry and data update occur. If you could somehow set up a central database with a web-based front end, then have a shared back-end, the problem essentially goes away because the shared back-end becomes the authoritative site. But if the two machines are not connected and operate independently AND UPDATE independently, you are going nowhere.

In essence, if you can't use a transactional method, I don't think your problem is solvable by computer until you first solve it by hand - to decide how you would do it if you only had paper receipts of what was done. Because IF you can define how you would do this on paper, THEN you would be able to do it by computer. If you CAN'T define the process-by-hand rules, you can't do this at all.

 

[moi]

OK, we have to be honest here... neither can we, really. Oh, we can come close, but YOU have to be the subject matter expert. YOU have to decide the right way to synchronize data between two places. It is YOUR business rules that will apply.

In a nutshell, here is the problem: You have data in a central location and you have data in some remote location. Your problem is compounded by your phrase:



In order to merge the work done on the two databases, you have to come to a conclusion regarding what is called "authoritative data" - which is to say, if you have different data values in two places but relating to or describing the same exact thing, only one of those values can be right. How do you tell which one? (The one you pick is the "authoritative site.")

You could not use an auto-number on the related tables at the two sites as a way to merge things, because they would potentially overlap. They would be generated in two independent places at once so without a connection, they could not be guaranteed to NOT overlap. Therefore, a direct merge with overlapping keys would probably lead to key violations.

If it were only ENTERING records, it would be easier. If there is no limitation on which records would be updated at each site, your trouble is in deciding which value in the two databases is correct. But independent updating makes it impossible to properly reconcile the two records if BOTH of their actions can occur in essence simultaneously. This is why I mentioned transactions earlier. With a transaction, you have a record that describes the change made because of the update (or perhaps explaining the update.)

I have to tell you that with 28 1/2 years of Navy experience, I wouldn't want the problem you have right now, because you are going to HAVE to control / limit how data entry and data update occur. If you could somehow set up a central database with a web-based front end, then have a shared back-end, the problem essentially goes away because the shared back-end becomes the authoritative site. But if the two machines are not connected and operate independently AND UPDATE independently, you are going nowhere.

In essence, if you can't use a transactional method, I don't think your problem is solvable by computer until you first solve it by hand - to decide how you would do it if you only had paper receipts of what was done. Because IF you can define how you would do this on paper, THEN you would be able to do it by computer. If you CAN'T define the process-by-hand rules, you can't do this at all.

Yes i have to admit, i can't do this at all.. if there is no easy way.

When we sat and gather info, i was not told about this and it didn't came to my mind either .. so i will set aside this for now..

Many thanks to all..

 

[The_Doc_Man]

First, you are welcome. We help where we can.

Second, this illustrates a MAJOR factor that should be part of every project - the (project) data gathering phase, where you investigate the problem and see what information you will need to address it, both immediately and ongoing. Chalk this one up to experience. Every project MUST begin with an in-depth analysis (with plenty of note-taking) so that you can actually lay out a plan of attack.

Third, just to clarify things, this analysis would have revealed the problem to be procedural in nature, with "the left hand not knowing what the right hand is doing." It would have revealed that the business model was itself flawed rather than having any single program error.

Good luck on your next project, whatever it is.

 

[moi]

First, you are welcome. We help where we can.

Second, this illustrates a MAJOR factor that should be part of every project - the (project) data gathering phase, where you investigate the problem and see what information you will need to address it, both immediately and ongoing. Chalk this one up to experience. Every project MUST begin with an in-depth analysis (with plenty of note-taking) so that you can actually lay out a plan of attack.

Third, just to clarify things, this analysis would have revealed the problem to be procedural in nature, with "the left hand not knowing what the right hand is doing." It would have revealed that the business model was itself flawed rather than having any single program error.

Good luck on your next project, whatever it is.

Thanks the-doc-man..