Data Migration 1

[woodJerry]

I am going from Access database to SQL Server 7.0. Are there any problems that I need to know about?

 

[Terpsfan]

I'm not sure what version of Access you have, but I hope it's 97 or better. If so, I've got great news for you, it's pretty simple. If you have Access 2000, it's even simpler. There are many ways of migrating your data from Access to SQL Server, but the best option is to use the Access Upsizing Wizard. For Access 97 you have to download it as an add in. I think you can find it on the microsoft/access home page. The main thing you need to do is make sure each of your tables has a Primary key BEFORE you migrate your data. If you don't you will be able to migrate the data, but you won't be able to update the tables. Another words, you can't add records! After you have designated primary keys for all of the tables you want to upsize, just run your upsize wizard and it does all the work for you. You designate before hand if you want to upsize your table relationships and indexes...please do this. Also make sure you print out a report afterwards to see if any errors occurred. If you need additional help, let me know. I've done it many times.

 

[Terpsfan]

FYI, I found the Microsoft website where you can download the Access 97 Upsizing Wizard to SQL Server. If you have Access 2000 you can find it in Tools, Add ins.

Http://www.microsoft.com/accessdev/prodinfo/aut97dat.htm

 

[link9]

Sorry to intrude on the thread, but I am trying to upsize my Access database, and it is skipping (or had fatal errors) on every single table, so when it's all said and done, there are no user tables in the resulting SQL Server database.

Omega, any ideas on what might be the problem?

thx
paul prewett

 

[harryrich]

I used the Access 97 upsizing wizard on a large database, pushing 1GB. I found it did a good job of converting the table definitions, but the data transfer failed with an ODBC timeout (near the end of the process, of course).

I followed up by truncating the new tables in the SQL Server database, exported them as delimited text from Access, copied them accross to the SQL Server system, and impoorted them using the import wizard.

The good news was not only did this process succeed, but was much faster than the ODBC transfer used by the upsizing wizard.