DTS - migrate from access to SQLSERVER2000

[vijayleads]

Hello,

I am trying to migrate data from an access database to sqlserver 2000 using DTS. I am able to create the same tables just like in the source(access) but the relationships and joins are getting lost.

for example if i have two tables, customer and customer_detail in access. there is a join on cust_id on these tables. I am using to DTS to move these two tables to sqlserver2000. I get 2 tables with the same name but they have no join on them.

So my question is do i have to manually create the joins or is there a way to migrate all the tables with the joins between them intact.

Any help will be highly appreciated.

thanks

vijay

 

[timscronin]

Are you going from 97 or 2000 to SQL. I believe for either if you use the upsizing wizard instead that your foreign keys will be upsized. Which ever way you go recheck your data types when you are done, including indexes. None o these tools has proved perfect and spending an hour rechecking everything might save big headaches later.

 

[tlbroadbent]

You should use Access Upsize wizard to upsize to SQL Server. You can select whether to upsize relationships. If you use DTS to create the SQL tables, the relationships will not be upsized as you have seen. In this case, you'll need to create the relationships in SQL Server.

See the following:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q237980

http://msdn.microsoft.com/library/en-us/off2000/html/acconSpecifyWhichTableAttributesToUpsize.asp

http://office.microsoft.com/downloads/2000/Accsql.aspx

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[vijayleads]

where do i find the access upsize wizard. can i download it from somewhere.

 

[tlbroadbent]

Please read the article in the first link I provided. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.