Migration of Access database to SQL Server

Using SQL Server Management Studio we can migrate access database to sql server

Before starting the migration process the steps to get into:

Before you run SSMA a few issues you can address in order to streamline the migration process successfully:

Add table indexes and primary keys: Each table which you require to import in SQL Server must have an index and a primary key. Make sure each Ms Access table has an index and a primary key. SQL Server requires all tables to have at least one index and requires a linked table to have a primary key if the table can be updated.
Check primary/foreign key relationships The relationship between tables must be on fields with consistent data types and sizes. Joined columns with different data types and sizes in foreign key constraints is not supported by SQL Server.
In order to migrate the access database, the database should be in the same system, where the SQL server is installed.
Attachment column is not supported in SQL Server, so before migrating if your tables has attachment column remove the Attachment column from the access backup database, which we will be using for our migration purpose.

To have the functionality of attachment column (What I did in an accesss database applicaton front-end for this functionality to work) I will post this info soon as Attachment_ in_SQL Server.

Migration steps are as follows:

Open SQL Server

Create a new database

Name a database

Click on OK. A blank database is created. Now you can import access database tables to this SQL Server database

As you can see below the database TestINV is created and no tables in this.

Right click on the database “TestINV” and click on tasks

You may find Import Data option in the Tasks list

Click on Import Data

Import and Export Wizard will open as below

If you check on <Do not show this starting page again>, the next time when you import data this will not be displayed. Click on Next

You may find a dialog box asking for Data source and Database information

Select Microsoft Access (Microsoft Access database engine)

Note: Access Database must be in the server pc in order to import. If your db is on other pc then copy first to server pc and then proceed with import process

Click on Browse and select the access database which required

Select a destination from the list for SQL Server 2012 click below destination

a . SQL Server Native Client 11.0

Select Server and then click on Windows Authentication if you want to import without username and password. If your database is SQL Server authenticated, then provide username and password.
Select Database in which you require the tables to be imported.
Click on next

Select option – Copy data from one or more tables or views

Click on next

All the tables in the source database is listed as below

Select those tables required for import

Select all tables

In the destination as you can see the tables as [dbo].[tablename], [dbo] is the default schema in sql server and the imported tables will be under this schema

You can see Edit Mappings option wherein you can select different schema for the data tables to reside in.

Check the datatype of the source and destination is matching

Let leave dbo as our schema for imports

Click on ok

Then

Click on Next

Select Run Immediately and click on Next

Click on Finish

Imports runs and you can see the result Success / Failed if the data is not imported

You can now see the imported tables under schema dbo

Note:

a. Login as administrator in order to import access database

b. Data in Tables must include the data matching the data types

I have to deal with an access database table of data type double, has been stored with 1.#INF, i.e the infinite numeric value, which was causing trouble in migrating data to sql server.

These types of values fails the import process

The following table (tblItemMaster3) is an example of table having hyperlink field. To update the hyperlink part one must create a select query as below and extract hyperlink part, then store the info in temporary table with table id in order to run update query for the required info to update sql server table.

SELECT tblItemMaster3.ID, tblItemMaster3.DWGLink, HyperlinkPart([DwgLink],0) AS Display, HyperlinkPart([DwgLink],1) AS Name, HyperlinkPart([DwgLink],2) AS Addr, HyperlinkPart([DwgLink],3) AS SubAddr, HyperlinkPart([DwgLink],4) AS ScreenTip FROM tblItemMaster3;

Also wrong entries will not allow to migrate

In another database table has wrong entries. This table has wrong entries in field with date data type, found error in Year entry something like 103, 113 etc Ex: 11/10/2001 as 11/10/103

Note: Imposing a validation rule for the database will avoid wrong entries while data input in access database

Conclusion:

Following best practices before migrating access database to sql server will help you migrate the access database successfully within minutes. Many struggle to migrate and fail without tracing out the issues that acts as barrier while migrating.

Points to note:

Must have Index and Primary keys
Relationship with foreign keys to be with the same data type
Removal of attachment field
Avoid error values stored in a tables, rather use validation rule while on inputting data into access database
Hyperlink to be stored in two different fields in SQL server, one with front text and the other for back links.