This book is in a very early phase. You should only book mark this page (http://www.manifestation.com/datamigration/) for now, because any other page is likely to move...
If you need a data migration solution, you can hire me and some my ultra-talented coworkers through Abel Solutions, Inc. Just make sure you tell them who sent you. :)
REAL rough here:
128k jpeg of my thoughts on the subject. 18k PNG of the same thing.. (beats me how that happened - I'd shrink it but this is trash anyway..)
What happens when you want to migrate self-referencing table, and it's going to get assigned new autonumbers?!? Somehow, you've got to pass the old key value along into the new record.. but what if there's not room? There are several solutions to this problem. One is to simply create another column on the destination table to store the old ID. One is to convert the old key into a text value and piggy back it in another field, especially a text or memo field... There are other ways to it outside of straight SQL.. for example, you could use a routine to loop through the recordset and insert each record individually, keeping track of the returned autonumber (if your SQL supports that)...
If you're storing new types of data in the destination database, what are you supposed to do? It probably means you're going to be creating some business logic to generate values. Or it could mean that someone will supply you with a second data source.. Or it could mean that people will manually enter the data.
Once users start to manually clean up destination data, there's no going back, unless you're willing to get them angry by overwriting their hard work. Manual clean up must be done before hand, even if it means creating "filter" tables that overwrite old data with new data. It may add another layer to the data migration, but who cares?
sometimes access screws up and tells you it can't insert records but does it anyway, and drops some fields. the only way to check this is to compare your query against the end table and make sure each and every field is the same.
