Migrate from MS Access to SQL Server 2005 database
Posted on September 16, 2008 at 5:39 am
Recently, I had to migrate the data from an Access database to SQL Server 2005 because my database was getting too large for Access to handle. I decided that since it’s a fairly simple process, but something that many people might be doing, that I would write it up here as an article.
I’ve also written two other migration articles that you might be interested in: Migrating pictures from Flickr to Picasa and migrate from iPhone to iPhone 3G. Now back to Access to SQL.
First, you need to make sure you have SQL Server 2005 or SQL 2005 Express installed on your computer along with the SQL Server Management Studio. If so, you’re ready to export your Access database to SQL 2005.
Transfer Access database to SQL Server 2005
Open SQL Server Management Studio and connect to the database server you want to import your Access database into. Under Databases, right-click and choose New Database.
Give it a name and configure the other settings as needed and click OK. Now we need to right-click on the database we just created and choose Tasks and then Import Data.
On the Data Source dialog box, scroll up until you see Microsoft Access.
Next to File, click on Browse and navigate to the Access database you want to import and click Open. Note that the database cannot be in Access 2007 format as SQL Server 2005 does not recognize it! So if you have a 2007 Access database, first convert it to the 2002-2003 Access format by going to Save As.
Go ahead and click Next to choose the destination. Since you right-clicked on the database you wanted to import the data into, it should already be picked in the list.
Click Next and then specify how you want to transfer the data from Access to SQL by choosing either Copy the data from one or more tables or Write a query to specify the data to transfer.
In most cases, if you want to just transfer the entire database, you should choose the first option. If you want only a portion of the data from a particular table, you can write out the SQL query. Click Next.
By default all the tables should be selected and if you click the Edit button under Mapping, you’ll see the the destination database will create all of the tables to match what’s in the Access database.
Here I have only one table in my Access database. Click Next and then choose whether to execute immediately or to save it as a file to perform later.
Click Next and then click Finish. You’ll then see the progress of the data transfer as it occurs.
After it is completed, you’ll see the number of rows transferred for each table in the Message column.
Click Close and you should now have all of your Access data nicely imported into a SQL Server 2005 database, which you can then use for whatever you like.
Any problems importing your data from Access to SQL 2005? If so, post a comment and I’ll try to help!
» Filed Under MS Office Tips
Related Posts
- How to query webpages using Excel
- How to create an Access database from templates
- SQLyog – MySQL database manager
- How to map a drive in Windows
- Excel Basics – How to use the Conditional Sum wizard























Thank you for yo help……….
I’m having an issue with an Access 2007 database being downconverted to 2002/3 and imported. I need to use this for InfoPath, and one of my tables (a repeating table) is not importing as such and causing issue in my form. Any advice?
Thank you,
Larry
Hello Sir!
i am getting one error while executing this. It says AcquireConnection Failed. please Help
After I move my access tables and views to sql 2005. How can I make web-based front end instead of Access front end? Thanks.
I have no import tab on my sub menu!!!
I had no problem importing data I made changes to connection string also.I am getting login screen but when I try to login through admin it is giving me error Error connecting to database. Error reads: Operation is not allowed when the object is closed.Please suggest some help.
very useful
a similar guide is available on mysql.com to migrate from access to Mysql(open-source)
I was importing database from MS-Access .it was successful but few data in the tables are not inserted instead null value was inserted.
Can any one sugges me why I faced this problem?What is the solution for this?Is there any store procedure to solve such problem?
There is no “Import Data …” option on my Tasks Sub-Menu lists.
Very useful
I don’t have the option “Import Data” under Tasks
I don’t have the option “Import Data” under Tasks
Thanks! Really very helpful!
hello,
while i am importing database MS-Access to sql server 2005 its could not importing
its Showing like this:
The operation could not be completed.
Additional information:
-> Not a valid password.(Microsoft JET Database Engine)
What to do? can you give the solution.
Thanks,
Deepa
I don’t have the option “Import Data” under Tasks, too
other way:
Open your db in microsoft access and then choose a table you want to export.
You can`t choose all tables, only one by one.
Go to Export->more->ODBC data base and create a new data source.Choose a SQL Native Client driver
from the menu. Type a name->Next->Finish.
Then choose a Server from the drop down menu->Next->Next. If you have a data base created in SQL
in which you want to import the tables check “Change the default database to” and choose the name of your db.
Next-> Finish. When you create it, it`s not necessary to do it for every table.
Good luck
Hi,
I tried migration from MS access 2002-03 format to MS SQL SERVER 2008 using the same procedure above and im getting some errors like
“* Error 0xc0202009: Data Flow Task 5: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Invalid character value for cast specification”.
(SQL Server Import and Export Wizard)”
can anyone help on this ?
thank you
Hi!
I need to transfer Access 2007 db to SQL Server Express 2008 the Sql Server Import and Export Wizard doesn’t recognized abccdb format 2007 only 2000-2003 mbd format. I have to keep my database in 2007 can’t convert it format this a requirement. Is it possible somehow open access 2007 in Sql Server Express 2008?
Best regards,
Lilian
It worked. Very good instructions! Thanks so much for your help!!!!
Sir,
I want to migrate MS Access 2003 to SQL Server Express 2005. I tried your above reply but I am using SQL Server Express 2005 and I don’t have the option “Import Data” under Tasks. I very much thankful i will get response.
Best Regards
Om Prakash
Hi,
I’m trying to import ms access tables to sql with the help of query which is as follows:
EXEC sp_addlinkedserver
@server = ‘ACCESS_TRAINING_DB’,
@provider = ‘Microsoft.Jet.OLEDB.4.0′,
@srvproduct = ‘OLE DB Providr for Jet’,
@datasrc = ‘\\argon\data\training\training.mdb’
To query use the complete 4 part definition
i.e. Server.Database.schema.table
This works:-
SELECT * FROM ACCESS_TRAINING_DB…Employee
or
SELECT * FROM ACCESS_TRAINING_DB..dbo.Employee
plz help
Thanks for the info. It worked just fine!
Wow, this is super helpful. I totally have to do this tomorrow.
Thanks dude/ Very helpful
I tried to transfer database created in access 2003 to
sql server 2005 version 9, i could not beacuse their is no
import option in sql server managment.Please help me in this matter
Hi every body
there is not need to convert access2007 to 2003 for migration from MS Access 2007 to SQL server 2005.
you can select “DataBase Tools” panel.In “Move Data” section you can choose “Sql Server” and simply follow wizard for moving your data.
Unable to find Import tab at sub menu
It worked for me great. I have SQL server Developer’s Edition 2005. Those who are facing the issue “No Import Option”. Plz check ur version and edition.