mssqltips logo

Import data from Microsoft Access to SQL Server

By:   |   Updated: 2011-09-07   |   Comments (7)   |   Related: More > Microsoft Access Integration

Problem

Tired of using SSIS packages to import data from Access to SQL Server? Is SQL Server Integration Services more complicated than what you are looking for?  Check out this tip to see how to import data from Access (Access 2007 or above) to SQL Server.

Solution

Microsoft has made importing data from Access much easier using Access 2007 or above than previous versions. Running SSIS packages or using the Import/Export wizard seemed time consuming and often would error out requiring more troubleshooting than it's worth. I'm using an Access database with two tables named Customers and Orders for this tip:

Microsoft Access Table Viewer

First, make sure all tables are closed within in our Access database or we'll get an error:

Microsoft Access Upsizing Wizard Error

Once all tables are closed navigate to Database Tools | SQL Server as shown below:

Microsoft Access Database Tools

After clicking SQL Server an Upsizing Wizard dialog box should appear. Since I don't have a database created I will click "Create New Database" option as shown below.

Microsoft Access Upsizing Wizard Create Database

If there was already a database in place clicking "Use existing database" will bring up a few screens to setup a data source to a preexisting database. After clicking next, we need to enter some information to connect to our SQL Server and create our database. When specifying the Login ID make sure this user has CREATE DATABASE permissions on the server. For this example I will use SQL2008 for my server and create a database called Bama:

Microsoft Access Upsizing Wizard Connection String

After clicking next, you should see a dialog box displaying your Access tables in the left column. Simply click the tables you want to import and click the right arrow seen here 6 and click the "Next > " button. In this example we are importing the Customers and Orders tables as shown below:

Microsoft Access Upsizing Wizard Table Selection

The Upsizing wizard can export table attributes i.e. indexes, defaults, etc. as well as data as you can see in the next screen.

Microsoft Access Upsizing Wizard Table Attributes

After choosing your table attributes click "Next >" and you will see a dialog box where you can modify the existing application or create a new application to work with the database. I will choose "No application changes" for this example and click the "Finish" button.

Microsoft Access Upsizing Wizard Application Changes

The Upsizing wizard will then start importing data:

Microsoft Access Upsizing Wizard Progress

...and display the Upsizing Wizard Report:

Microsoft Access Upsizing Wizard Report

To confirm a successful import we can open SQL Server Management Studio and drill down to Databases where we will see our new database Bama, and our two new tables, Customers and Orders.

SQL Server Management Studio Object Browser

To confirm our data was imported we can query our Customers table using:

SELECT * FROM Customers

SQL Server Management Studio Query Results
Next Steps


Last Updated: 2011-09-07


get scripts

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, November 02, 2016 - 4:58:36 PM - Aditya Moitra Back To Top

 Thanks for the article. I do have a question - Once the database in created and all the tables with the data in them are imported, how do you get new data in the access database into the SQL database in a recurring fashion. Say, if I want to get data from the Access database tables into the SQL database tables every 10 minutes, how do I do that? Please let me know. I sincerely appreciate it. Thank you.

 


Friday, October 21, 2016 - 2:59:50 AM - Hamid Back To Top

This post was quite useful. However, i was able to migrate access tables and it's contents to SQL..

However, how do i convert MS access queries to SQL Stored procedures / SQL Functions?

 


Thursday, February 11, 2016 - 2:05:28 PM - Kevin Lobo Back To Top

 Thanks for the excellent tip. However, I dont see the SQL Server icon in my Database Tools tab. I do have SQL Server installed on my computer. What am I missing?

Thanks in advance.

 


Saturday, March 29, 2014 - 3:20:08 PM - Mikrodots Back To Top

I see this post is from 2011 - just an update:  Unfortunately the upsizing tool is removed from Access 2013

REMOVED

Access Upsizing Wizard

The Upsizing Wizard lets you scale up Access database tables to a new or existing Microsoft SQL Server database. This wizard has been removed for Access 2013.

To do this, run the SQL Server Import and Export Wizard (in SQL Server Management Studio) to import your Access tables into a SQL Server database.

Then, create a new custom Access web app, and import the tables from SQL Server into the web app.


Thursday, February 28, 2013 - 1:49:53 AM - muhammad faizan khan Back To Top

Brady really Fine article. import without any software ..really excellent. I dont know why people use third party tools for this work. this is the really easy n nice way.


Thursday, February 28, 2013 - 1:47:54 AM - muhammad faizan khan Back To Top

Brady really excellent. without any third party software..!!!


Wednesday, September 07, 2011 - 5:11:35 PM - BuntyBoy Back To Top

Your article is very very useful. It have made the data import task from Access very simple. Much apprciated on your effort. Thanks.

===========================================
Better try and fail, instead of not trying at all...

Database Best Practices



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools