Merging databases to form a master

Question

Good day,

I have multiple MS Access databases, which are split databases, the front end is split from the forms, etc, this is due to the Access runtime application being run, these databases are located in multiple locations, which can not be joined through the internet due to connectivity restrictions and security.

These databases are all exactly the same in structure and layout, the clean database is distributed to the various sites, each site will hold its own site data, due to security reasons. which we have to abide by. the goal is to transport these databases back to the main site, with secure thumb drives and then merge this information into a single database, for historical and trend analysis,.

I need to create a method which is simple for the individual databased to be added to a master database so that I am not responsible for the maintenance of the system, and that any member of the team can do it, as I may be deployed to a site and not be available to perform the merge.

Any suggestions would be appreciated.

Thank you

Answer 1

Thank you both for this information, I am going back to the drawing board and going to confirm I have unique keys on each database and will then work forward from there.

Regards

Answer 2

As an example of what Scott has described the following UNION ALL query is from one of my online demo files and returns the rows from two tables, Credits and Debits, giving the formatted primary key values from each a prefix of C and D respectively:

SELECT "C" & Format(CreditID,"000000000") As TransactionID,

CustomerID, TransactionDate, 0 As Debit, Credit

FROM Credits

UNION ALL

SELECT "D" & Format(DebitID,"000000000"),

CustomerID, TransactionDate,Debit, 0

FROM Debits;

This can easily be converted into a 'make table' query to insert the rows into a new table as follows:

SELECT * INTO NewTable

FROM

    (SELECT "C" & Format(CreditID,"000000000") As TransactionID,

     CustomerID, TransactionDate, 0 As Debit, Credit

     FROM Credits

     UNION ALL

     SELECT "D" & Format(DebitID,"000000000"),

     CustomerID, TransactionDate,Debit, 0

     FROM Debits);

Once the new table has been created the TransactionID column could be made its primary key or, if the table is not referenced by any other tables in a one-to-many relationship type, a new autonumber column could be added.  If possible I'd recommend the latter. The TransactionID column could then be deleted in table design view, and other non-key columns indexed as appropriate.

Answer 3

The key to this issue is how you set your primary keys. You CANNOT use Autonumber PKs since there will be no guarantee of uniqueness once merged. You need to devise some other method to ensure uniqueness both within the local versions and then when merged. One possibility is to use a Location code. Include a single record table with the location code that will be unique to each installation. Then concatenate the location code to some other unique feature.

Once this is setup. Writing a VBA module using the TransferData method to select and link to the tables on the thumb drive (or wherever) and execute ann Append query to add them to your master will be simple.