Without quite giving all of the relevant issues that got you to the
point of considering the specific solution, you ask...
There is a feature for Access and SQL Server called Replication. Not
for the faint of heart but it does work well at the expense of having
someone around who takes responsibility for the processes. I suppose
that most of the issues can be resolved with current levels of
automation. Replication is a more credible solution than ad-hoc
designs because it is supported by knowledgeable professionals. It is
my first recommendation for a solution.
One huge fly in the ointment of your proposed solution is the
assumption that you can set an Autonumber value and that it will
thereafter behave only in the manner you desire. Autonumbers are not
guaranteed to be sequential. They can go negative and they can go
random.
I've done a similar kind of function before. It tested flawlessly
in-house but there has been no feedback from the field. In that case,
The export process created a new MDB from scratch and allowed the
selection of subsets of related data. The host's Autonumber PKs
traveled along for that part of the ride. The resulting MDB was then
made available to the system that would import it. Subsets of related
data could be selected for import. At the top of the hierarchy, the
old PK wasn't kept. As each record was added to the existing table,
the newly generated Autonumber PK was captured and used as the FK in
the related tables. The lower level PKs were tossed and the lower
level records were appended to an existing table and received new PKs.
The FK derived above was written as the new FK.
In the example above, the data being Exported/Imported had to do with
Medical Practices, Medical Practitioners (related), and Patient
Accounts (related). Lest someone panic about HIPPAA, his had to do
with information needed to create Medical Transcripts, it didn't
handle the transfer of medical records. Several people working
semi-independently had to be able to create Medical Transcripts that
were identical in the administrative elements although their team was
creating them independently.
Back to your case: It would make sense to do something similar to the
example but there would be no requirement for manual intervention. I
would add a field in the highest level of table being transferred that
holds the Office Name. Do that and you can handle unlimited disparate
offices. In your case, I would also have a table tracking the Export
and Import that has a DateSerial value for the date and time that this
export was begun and another DateSerial value that holds the date and
time that the last prior export was begun. Your export candidates
wood be everything between those two dates. There would be similar
logic and tracking in the Import functionality. Let the Autonumber
PKs remain in the exported tables and strip them out and regenerate
them intelligently on the Import side.
HTH
