Split access database - will this solve my basic problem in managing a single database in google drive?

Question

So I have an access database, intact, non-split in google drive.

I have two pc's that have access to this intact, non split access database.

I don't have internet access all the time. 

My general problem is this:

1. I add data using pc1 and have it synced to google drive. 
2. I use pc2 this time elsewhere and added data without first syncing/downloading whatever data i added using pc1 (as stated in #1), because i have no internet connection while on pc2. 
3. I get home, sync data on pc2 to google drive and it becomes a mess. I have lost data. 

Question is:

1. Will splitting the database solve my problem of error due to two different changes made on the database?
2. If I do split the database... how many front end copies do I need to make?

    ?1 - Am I correct in thinking I need to make two front ends, one for each pc?

         ?1.1 - If I do make two front ends, one for each pc, and call it FEPC1, and FEPC2. What happens if I do the following?

                Condition: Enter data using pc1 on FEPC1, and failed to sync to drive - due to no internet. 

                                    then enter data using pc2 on FEPC2, and failed to sync to drive - due to no internet

Question:

If i happen to suddenly have internet, and sync BOTH FEPC1 and FEPC2 to google drive given the condition stated above, and syncing happens at the same time, will this still create an error?

Or does the split front end of both PC's update the data to the Backend in the proper order, and that the backend accepts data from both frontends at the same time without creating an error?

I really need help, I previously had errors and the truth is I don't know how many data i have lost because I failed to sync the database first before and after using pc2.

Answer 1

Ken and Scott, 

Thanks for your answers. I guess my case is an unusual one. I was hoping splitting would solve my problems but I just didn't know how my file would change since I can't find a way to un-split the front/back ends if splitting didn't work out. 

I kinda wish access had some sort of a portal for data entry that syncs multiple front ends.

These individual data have different key numbers anyway.

Thanks for your help.

Answer 2

First, its easy to reverse a split. just delete the linked tables and then reimport them using the External Data ribbon.

Second, The way I understood you, you are the only one using the app you just need to access it from 2 different machines. If that is the case, then my initial response applies.

Answer 3

What you are really looking for here is something like the Replication technology which Access supported in the old days.  This allowed separate replicas of a database to be operated completely independently of each other.  When the replicas were then synchronized conflicts were resolved.  It was a cumbersome and not entirely successful solution, however, and not surprisingly was dropped when interconnectivity became more widespread..

However, in the context of a specific database application like yours, it should be possible to design your own replication solution.  I did this myself many years ago with a database operated at three remote locations with no interconnectivity whatsoever.  One of the locations, where I worked, maintained the master back end file and copies of the files from the other sites were periodically received on portable media.  The database was a complex one with many related tables, so synchronizing them reflected this and involved in excess of 100 INSERT and UPDATE SQL statements ('action' queries in Access parlance).  The process was automated by executing these in a specific order.  There were two principle issues to be resolved:

1.  Where existing data in the same row in a table had been updated precedence had to be given to one update.  This was done by date/time stamping each update and giving precedence to that with the latest date/time stamp.  From what you have told us, it sounds like this would be an acceptable principle in your case.

2.  Where the insertion of two or more separate new rows into a referencing table required the insertion of a row into a referenced table, where the new row inserted into the referenced table represented the same entity, this required the insertion of a single row into the referenced table in the master database, and the insertion of two or more rows into the referencing table, with the same foreign key value to reference the single row which had been inserted into the referenced table.  This is not too difficult if your skills in creating 'action' queries are reasonably advanced.

The file in which these inserts and updates were undertaken has long since passed into the dustbin of history, so I can't give you any examples, I'm afraid, but I hope I've given you some idea of the sort of tasks you'd be faced with.  It might sound horrendously complex, but if your database's model is well designed on sound relational principles it might not be as difficult as you think, particularly if the model, unlike the one with which I was faced, is a relatively simple one with a limited number of tables.

Answer 4

No splitting won't solve your problem. Splitting is a technique for when an app is used by multiple concurrent users.

All you are doing is using Google Drive as cloud storage. You MUST synchronize with the cloud storage before and after each use. When you go to add records you need to download the file., when you finish, you need to upload it back.