How to Upgrade Access Database to MSSQL2005?

[Frank100]

Hi Guys,

I have a Database in Access I want to upgrade it to MSSQL2005.
Could you please give me few tips to upgrade my database..
Or do I need to rewrite all tables again in MSSQL2005.

I never used DTS before.

Thanks

[George1111]

DTS (SSIS) is not to be feared - it is basically a wizard - just answer the questions and it converts Access to SQL Server quite easily so long as you have SQL Server installed and working properly

ONE MAJOR EXCEPTION

This astounds me greatly, but DTS will not convert when YOU SELECT MORE THAN around 65 Tables to be converted in one go.

It just fails and gives up a message which is totally irrelevant to exceeding the number of files cap.

[Frank100]

Thanks..I have done with that...But its not creating Primarykeys in MSSQL2005 tables..

[George1111]

There must be TWO MAJOR EXCEPTIONS - thanks for that !

If you are converting only once you have some keying to do

If its more regular you need to write some code .....

[Shuja Ali]

This might be helpful
http://support.microsoft.com/default.aspx/kb/237980