Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Importing MS Access 2003 database into MS SQL Server 2008

Posted on 2012-08-30
6
Medium Priority
?
413 Views
Last Modified: 2012-09-07
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
0
Comment
Question by:bejhan
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 38353069
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
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38359156
which will get you a copy of Enterprise Edition that you mustn't use in production!
Actually to be totally accurate they get the Developer Edition which has the same functionality as the Enterprise Edition.

you should find "Microsoft Access (Microsoft Jet Database Engine)" which should be what you need.
That is providing they are not using SQL Server 64-bit, as there is no JET driver for that.
0
 
LVL 1

Author Comment

by:bejhan
ID: 38366570
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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38367590
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.
0
 
LVL 1

Author Comment

by:bejhan
ID: 38374347
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38374617
I've settled for using the tool and manually reviewing table definitions and making necessary corrections.
Excellent!  It sounds like you have it resolved.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question