I have staff doing work in different .accdbs.
Sometimes I need to synch the records from different tables in their .accdb.
All the tables in question have a last update field with a date and time.
What I would like to do is write a routine that goes to all the .accdbs and pulls up the records for a given table to an "aggregator" table, each time the iteration runs, it should only pull up (for merge, append or replace) records that have a date newer than the one in the aggregator table. When it's all done, the aggregator should have only the most recent records.
Since it's multiple tables in different .accdbs, I'm sure I will need some VBA code, surely avoiding macros.
The question is it best to do it with SQL statements somehow, or write down and dirty VBA DAO code to do it?
Then when it's done, I need to do the reverse, sending the most recent records back down to the individual .accdbs.
In the future, I expect to have millions of records being moved up and down to the various .accdbs, so it makes sense to somehow do this efficiently over a network.
I could import each table in some manner, and then let the code on the aggregator do the work. I'm not sure if the import, with big tables would be efficient, common sense tells me yes, since VBA code would have to run SQL to bring up the records anyways. Alternatively, the aggregator could read just the update field in the remote .accdb, record by record to see what to then import, but there might be a big efficiency penalty for doing it this way.
Anybody got any ideas as to dos and don'ts in such a scenario?