asked on

Importing MS Access 2003 database into MS SQL Server 2008

I've read in several places that the MS SQL Server 2008 Import/Export Wizard supports using an MS Access database as a data source.

I have installed MS SQL Server 2008 express to do some testing before moving to the production MS SQL Server. However, I can't seem to find a relevant option for importing a MS Access database.

I'm sure that I'm missing something obvious, but can someone shed some light?
options.png

DcpKing

Use Start All Programs | SQL Server | Import and Export Wizard you'll start it up. If you don't have that option (or something with a similar name!) then it doesn't come with eExpress, so you'll have to shell out the ghastly expense of about $50 for a copy of Developer Edition from Amazon - which will get you a copy of Enterprise Edition that you mustn't use in production!

Anyhow, once you get the wizard started the first main screen should ask you to choose a data source: drop down the source already offered (SQL Server) and you should find "Microsoft Access (Microsoft Jet Database Engine)" which should be what you need.

hth

Mike

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bejhan

ASKER

acperkins, you were correct, after installing the 32-bit version of SQL Server Express I was able to import an Access database.

Now a follow-up question, I've found that the primary keys, relationships, and indexes were not imported. Any way to import these as well?

I increased the points since the original question was already answered.

Anthony Perkins

You may have to get a better tool to migrate your data in order to do that, but I am really not sure. Personally, I would never rely on any tool to do a complete job and would rather do that manually.

bejhan

ASKER

I have to do this for a few databases, so the tool will save me a ton of time. I've settled for using the tool and manually reviewing table definitions and making necessary corrections.

Anthony Perkins

I've settled for using the tool and manually reviewing table definitions and making necessary corrections.
Excellent! It sounds like you have it resolved.