Combining identical databases (1 Viewer)

[uniq_usr]

Hello Everyone, the forum has helped me countless times and I am finally posting my own question. I have started working on an existing MS access application (Front-end & Back-end) built with VBA. The frontend linked to a back-end access database which contains only tables. Because of the nature of the work involving vehicles. Each user has their own copy of identical databases with their individual data stored in backend. Right now we just copy each back-end database via ftp and store it as usr_backenddb_date.accdb Back end databases contain around 16 tables with most of them containing a autonumber field as a primary key. Further tables are connected with each other referencing the primary key as a foreign key .I would like ideally create a function in vba that lets me select the database and merge all the data from that database to a identical central database. For the initial part, I am thinking of combining each individual table in each database and changing the autonumber field with SQL with possibly trying to cascade the change so the refrences remain intact.
I wanted to know if this approach is do-able or if any one has any other ideas and suggestion that I can look into. Constant Access to one back end db via internet (or server) is not really possible as the users are able to connect to internet (network) only few times a day. There are over 100 backends (for each vehicle) with over 1000 records in each. If any one has any recommendations for this project to move to something beyond Access, I would really appreciate that as well.
Cheers !!

 

[Anakardian]

One thing you could consider is making a master ID field for each of the tables in the central backend.
When you copy the data from the user backends to the central backend you keep their auto-generated number but also store the user name or id.
This allows you to check if a particular user has any new records by looking up those records that belong to the particular user.
You have a master id for the record as well to make sure the central database does not get hiccups.
It does require a lot of crosschecking of data however you should be able to cut down on this if you are allowed to make changes in the user backends.
You could do this by having a routine make a date/time stamp on each record every time it is updated. Then you can check the update times and find the records with the changes as well as new records.

 

[uniq_usr]

I do have access to backend but there are multiple (around 100) copies of it out there at the moment. The data once entered for the day aren't usually changed much. For the sake of easier implementation at this stage, I can assume that the existing data wont be updated. I am thinking of creating a master update table in each backend which will record the primary key that has been uploaded upto that point and the next update will simply continue from that point onwards.
I could think of another problem. I create a Master backend with a master and import my tables DayHeader and DayItem. DayHeader has dhid as primary key while DayItem has diid as primary and dhid as foreign key. When multiple databases will get merged to the Master backend, I assume the value of dhid in DayHeader and diid in Day item will have to changed with a cascade update sql. I was wondering will the change accurately be reflected in DayItem (dhid) and will it still point the correct DayHeader.
Thank you for the help and as this is my first job out of college, I might be asking few silly questions. Forgive me for that.

 

[Anakardian]

If the backends are unique to a user, you could store it in your master backend in this structure:

--------------------------------------------------------------
| Masterdhid | UserID | Userdhid | Field1 to FieldX|
--------------------------------------------------------------

This will allow you to import the data from each backend into your master backend.
Since each backend starts from scratch, it is likely that you will have duplicates in your primary keys across all the backends. By including the UserID you ensure you know which record belongs to which person (backend).
When you import you can also match the UserID and Userdhid to ensure you do not get duplicates into your master backend.

The Masterdhid could be replaced by a composite primary key but I prefer having a seperate primary key for it.

For sending the information to you Master backend I would mark each record with whether or not is has been sent and probably have a field for a return from the Master backend to indicate whether the record has been received successfully.
This allows you to have a file with the updates go missing and will also allow you to handle updates to previous records.

 

[uniq_usr]

Sorry, I think I mislead you into thinking there are different backend. All the databases for different users have exact same structure. Only the data in them is different.
I really like the idea of marking each record as being sent and if I make that field a boolean and indexable, I think I can just do a search of all the fields with the field marked as false and just update them.
I am working on major updates on front-end so I am hoping to start this very soon and really appreciate all the insides you have given.
Will comeback with more questions when i get back to work next week.

 

[Anakardian]

I was really hoping you had a single structure of the backend and that it was only the data that was different.

The biggest worry I see is the integrity of your data.
When you get updates from the various users they really need to be kept in a way that can make sure you do not overwrite data from another user.
The suggestion I made for some additional fields can actually also be reflected in the users backends if you like. I just means you send the master id for the record to the user and updates his records with that in a separate field. This field should not be the primary key for the users backend, this is only to provide a cross reference for you.

 

[uniq_usr]

Thnak you for the significant insight. The idea of tracking the master id in each user back end is a very good idea but I will have to convince my company of sending data back to user each way. The way it works now is they send us a copy of data and we only send them newer copies of front and/or backend whenever there is an update. Just sending occasional updates is already painful enough for the users coordinator.
Another basic question is can I actually change the value of autonumber in access ?
My initial plan is to start of by combining two backend databases
The approach I am thinking of taking is, finding the highest value of primary key (autonumber) and adding that to the primary key of the same table in database 2. This ensures all primary keys become unique. From my understanding this should change the values of the foreign key in the child tables because cascade update has been enabled, so all relationships become intact as well. Then continuing this step by going down the hierarchy of the tables. I will also need to have a max primary ID feild in the master backedn that records the last value of the primary key that was uploaded for the particular. I am still thinking if this needs to be stored in users backend or the master backed.
The approach I highlighted is a very rudimentary and basic approach, because I couldn't figure out on how the referential integrity remain with the master ID. As the child table's foreign key will have two values for the parent table's primary key in the master backend table. As there are 4 levels of tables with table D refrencing table C's primary key. Table C's foreign key refrencing table b's primary key. Table b's foreign key refrencing table A's primary key.
Thank you for you input once again

 

[spikepl]

IF each db had a composite PK for each record consisting of DatabaseID - a unique number identifying each specific db, and SomeNormalKey - the normal autonumber, and any children would then have those two as foreign key, then you would be able to simply copy the records to your central db and that would be it.



DatabaseID - PK (fixed Long , different for each db)
SomeKey - (PK -autonumber)

 

[uniq_usr]

I have the authority to change the structure of back end so I can certainly institutionalize a unique dB number for each backend database. Should I create a tdbID table with one field in each database just storing the unique database key (long-number) and every table in the database will have a one to many relationship with that table with the dbID as a foreign key. The existing structure and relationships remain as it is.
Then each table will have a composite key consisting of their own primary key(autonumber) and the dbID as foreign key. There will be other foreign key linking them to their parent table.
As parent table A and child table B both has dbID as part of their composite key, I assume child table b wont get confused when there are two entries in parent table A with same primary key (autonumber).
I understand a lot of this is conceptual question and I really appreciate your input and insight into helping me in this.

 

[Anakardian]

I don't know how your policy is but a relatively painless import/export can be made using something like dropbox although it requires you to have an internet connection at least some of the time.
You can make a function for your users backend data to be dropped into an excel file that is placed in a dropbox folder.
This is then synchronised to your office where the master backend can grab it.
If you need data to go back it can go the same way.
It is relatively painless to import and export data this way but the question is how your system is set up and what you are allowed to do.
It could even be that your systems are already set to do this kind of thing.

With regards to the composite key I would tend to use the user id as one part of the composite key.
The reason is that i can then return the data to this particular user if needed. I will not end up with a lot of different keys for the same person and possible doublets.