SQL Server: import a MS Access database

Tweet

As I wrote in a past article, MS Access can be used to manage databases for a dynamic web site. However, some times, it is not enough and its features can be not enough. In those cases we really need to move to a more performing software like SQL Server.
Especially in situations were large quantities of information are managed by a dynamic web site, SQL Server can be a great solution.
If you are a reader of the web thought, you know how much I am a fan of SQL Server. so in this short post I will explain how to import an Access database to SQL Server... a few steps and we will ready to go.

First things first
There are two different ways of converting an Access database into a SQL Server database. One is from the MS Access point of view: upsizing a database (export). The other way consists in importing an Access database into SQL Server.
In this article we are going to see the second way, which in my opinion is much more secure and easy to execute.

Import
We are going to use the import feature in SQL Server Management Studio. To do so we need to select our database, right click and then select "Tasks/Import Data...".
Doing so, the import wizard will open.
First, select as data source "Microsoft Access". After that, we need to select the Ms Access file name, by pressing browse.
After clicking on the next button, we need to decide where we want to  import the database. Destination will be "SQL Server". Select the SQL server name and the database. Now, be careful when completing the dialog box section about user authentication: decide if you want to use Windows or SQL Server authentication.
The next import procedure step is about the way to import data. We can copy data from one or more table or views, or we can write a query to specify the data to transfer. We assume we are going to import data from a table, so we select the first option.
In the next step, we can select the sources by ticking next to the table/s we would like to import.
After clicking on the next button, and after just a moment (or maybe more than a moment depending on the size of the imported table/s), we will find the MS Access tables just inside the Tables section of our SQL Server database.

Fast and easy.

A few more tips
After importing the tables, we need to check if everything went the way we wanted. There are some basic differences between SQL Server and Access that we should be taking into account. The import wizard is a great tool and it should take care of most of the dirty work. A good look at what is now inside SQL Server, before using the database, is advisable in any case.

Ok folks... that's all for today.