Access database into SQL Server 2000

B

balee

Guest
Hi,

I've an access database needs to be imported into MS SQL 2000. I tried a simply import with Enterprise Manager, but I noticed that primary keys were missing. I haven't played lot with this, but I can imagine that other things will be missing also. I can't spend days just for checking if all the type conversions were fine and all those things.

Is there any other way, or has any of you ever done it somehow?

What do you suggest?
 
Use the Upsizing Wizard that comes with Access:

Tools \ Database Utilities \ Upsizing Wizard

You'll have problems with the boolean types since MS SQL convert them to the type "bit"

Also, in order to import Access relationships, check "Use DRI"
 
balee said:
Hi,

I've an access database needs to be imported into MS SQL 2000. I tried a simply import with Enterprise Manager, but I noticed that primary keys were missing. I haven't played lot with this, but I can imagine that other things will be missing also. I can't spend days just for checking if all the type conversions were fine and all those things.
Click to expand...

I used the SQL EM to migrate my DB to SQL and the only SQL-based change I had to make is to change all the Access AutoNumber fields to Identify fields in SQL, using the EM. Other than that, the EM import worked fine. Of course there were several code changes, but that's another story.
 
the access upsizing wizard does everything for you.. it converts all field types into the sql server types.. at least access 2002 does that
the only thing you need to change is the connection string and some SQL statements
 
DirtBag said:
the access upsizing wizard does everything for you.. it converts all field types into the sql server types.. at least access 2002 does that
the only thing you need to change is the connection string and some SQL statements
Click to expand...

The reason I didn't use the Upsizing Wizard is that I had 200M of data to migrate and I wanted to do it in batches, not all in one 4 hour sitting. I don't think the Wizard allowed for selective migration.
 
You must log in or register to reply here.
Back
Top