Merging 2 Databases (1 Viewer)

PatAccess

Registered User.
Local time
Today, 08:47
Joined
May 24, 2017
Messages
284
Good day,

I've created a database for data entry (with lots of tables and forms) on our network. A copy of that database is on a surface which was taken on the field. The surface is back in the office but I've added new fields to the original tables and forms (just added, I didn't change anything else)
What is the best way to bring the surface data into my network database without changing anything?

Thank you,
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:47
Joined
Jul 9, 2003
Messages
16,244
I note your question has yet to receive a reply, so I thought I would give it a bump....

From your comments I don't think there is a simple way, particularly as you've made design changes between the two databases.

Assuming you've used ID fields for your tables then the biggest problem is that if you've added new records to both sets of separated data, you could very well have the same ID for a different record in each copy. The other problem is sort of similar, if you have lookups, then again, the lookup may not lookup the correct entry. A lookup created in one set of data could be different to one created in the other set of data. So it is possible to match two databases together, but a little more difficult because you've made design changes, and theres a minefield of mistakes that you can innocently make! It's not something I would be willing to do myself unless I had to!
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2013
Messages
16,553
as Unc says, you have a problem, but one that can be solved with care.

Strongly recommend you take a copy of the main database and work on that to test everything works OK after merging.

Also strongly recommend you split your database.

In addition to ID fields, do you also have timestamp fields you can use to indicate when records were added and/or changed?

Assuming you are working from the main database and importing the surface data, link to the surface db for each table which will have changed.

You need to match records on the surface which are identical to the records on the main db. These can be flagged in each db as 'no change' (create a temporary field for each table) and subsequently ignored. You can then focus on the remaining records in the surface db to determine whether they are new or amended and create insert or update queries as required.

those
 

Solo712

Registered User.
Local time
Today, 08:47
Joined
Oct 19, 2012
Messages
828
Some questions to consider:

1) Does the data on the surface supersede data in the main database. In other words, if records with IDs exists in both dbs, which are the records that prevail ?

2) Do you have the cutoff points for all tables (IDs) after which the databases were separated ?

3) What are the new/amended relationships between records created in the surface db after separation? This is crucial point! If eg. the copied db created records which also act as new FKs, then you must flag them and "repair" them as the IDs will (most likely) not be the same after you added the records back to the original db.

Best,
Jiri
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:47
Joined
Feb 19, 2002
Messages
42,970
Whenever you update copies of a database, you open yourself up to data errors.

If this database is one where you need to take it into the field to capture data and then bring it back to the mothership for upload, that can be managed. If you make a copy of a database and allow random updates over time, it is virtually impossible to reconcile the two automatically since a person is always going to have to decide which version should rule when there is a difference.

You've already been advised to split the database so start with that. If all you did was to add a few columns to some existing tables, make the same changes to the Surface version. Then run your sync code.
 

PatAccess

Registered User.
Local time
Today, 08:47
Joined
May 24, 2017
Messages
284
Hello Guys,
Thank you for all the responses!
To respond to some of the questions...
I've made a copy of the same database on the surface for them to take on the field, then a few days later, I've added a few new fields/columns to certain tables for data entry. The main database on the network is still blank. I want to know the best way to proceed to bring both together without removing the new fields I've added. I've set the IDs on both to start at 10,000. The ID on each forms match on both database. So each time they come back from the field, I would like to just upload the data without messing with the new field/columns I've added to the main database tables?

Is the best way still splitting the database or is there some type of append query I can use?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 19, 2013
Messages
16,553
The main database on the network is still blank.
In that case, add your new fields to the surface db and then replace the main db with the surface db.

Is the best way still splitting the database
splitting the db is not a cure for your problem. It is to minimise the risk of problems in the future. any db should be split into front end and backend, failure to do so significantly increases the risk of corruption at which point you will lose everything - forms, reports, code, queries and most importantly the data.

if this is going to be a common occurrence, requiring synchronising data between a field db and a master db, you need to develop a strategy. Relying on ID's is insufficient - particularly incremental ones. You may have more success by using random ones.

What that strategy is depends on what is required, which you have not explained except in the broadest of terms. But typically each table will require a replicationID field and two timestamp fields, one to indicate when a record is created and one to indicate when it was last amended.

you will then need a number of queries for each table comparing these three fields to see what has changed and in which db, then appending or updating as required.

Alternatively if all updates and amendments are done in the field and the master is a repository where data is not changed, just copy the field db backend to the overwrite the master db backend.
 

PatAccess

Registered User.
Local time
Today, 08:47
Joined
May 24, 2017
Messages
284
Thank you Guys,
I'll try all of that and let you know!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:47
Joined
Feb 19, 2002
Messages
42,970
We still don't know what your plan is.

If you export specific records to a database for people to take to the field to update, then it is possible to manage integrating those changes automatically. If the remote users take the whole database and can update anything, you're in for a world of hurt.

To do a controlled sync, you will need a LastUpdateDT field on each table. When you export the data to the remote tables, you need an extra field because you will want to track when the update happened in the field so you can change the LastUpdateDT to reflect that but you MUST have the original value so you can tell if the record was updated in the main database after it was exported to the field table. Your sync code can apply changes automatically when the two LastUpdateDT values are = but the sync will have to be manual when the field values are different because a human will have to decide for each and every different field value, which should take priority.
 

Users who are viewing this thread

Top Bottom