Going from Access to SQl Server?

Question

I have to move very large database in Access to SQL Server 2005 because the access is to small. I know there is a migration download that will automatically move everything over but I have read that is not the best way to do it because data can get lost since the field types are different. I was wondering what is the best way to move the data over? Should I jsut make the tables in SQL Server 2005 then write a VB application that will move the data over for me? I know this will take awhile consindering I have about 50 tables but is this my safest bet?

Answer 1

SQL Server Management Studio should allow you to import data from Access directly into a SQL Server database.

Answer 2

Under SQL Server 2000 these types of tasks used to fall under the "Data Transformation Services" in SQL Server.



Now with SQL Server 2005, they fall under "SQL Server Integration Services (SSIS) "



I do not see the benefit of transforming the Data from Access to SQL server through a custom application. I believe you should start reading about SSIS in SQL Server books online.

Answer 3

If you are talking about the freebie SQL Server 2005 Express Edition, then you may be out of luck. The management tool for this (called Management Studio Express) does not have all of the bells and whistles of the pro or enterprise ($$$) versions.

Answer 4

all of that info is included in the documentation with SQL Server