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
Or
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.