Having done more than a few Access->Sql Server migrations, I've also found it best to create the database(s) on Sql Server manually and then use DTS to get the data over (the Access "Upsizing Wizard" is a POS imNSho).

As far as keeping local copies of the data and updating Sql Server later, if I understand the task at hand, there are basically two techniques. One is to make extensive use of disconnected recordsets and use the ADO UpdateBatch method (don't know if that fits or not) and the other is to use replication.

hth,
Mikem