A simple guide to Microsoft SQL Server Migration Assistant for Access

03 March,2022 by Tom Collins

The requirement is to develop a workflow for migrating MS Access objects to SQL Server. 

Reasons for migrating include:

-Access has a size limit of 2 GB

-Access has a concurrent users limit of 255 users

-Require increased capacity 

The SQL Server Migration Assistant for Access (SSMA) is a very useful tool  offered by Microsoft . 

The main objective of these notes is to supplement the Microsoft documentation and to assist in Access to SQL Server journey.      

 

If your plan is to use Access as a front-end and SQL Server as a backend consider these points

-Don't think of SQL Server as just a bigger version of Access. Object models are different

-In reality only the data will be migrated , not the queries or forms that will be migrated.

- Avoid creating queries that join linked tables . See next point 

- This is not best design  but it is possible to link SQL tables and views to your Access front end for a quick migration. 
a. Use the Access object names to Name the linked tables and passthrough queries to keep  modifications to a minimum to forms, reports and VBA code.
b. Views can be linked as tables or as Passthrough queries. Linked views are updatable if they are updatable in SQL server and you add an index to the Access ‘table’ based on a unique identifier.

-   If you simply move all your tables to SQL Server, and run all your queries from Access, each query will retrieve all the data from each table used in the query.The choice of query type will impact the performance 

- Once you have your tables on SQL Server you need to look at how Access works with them. It will either be directly as tables bound into the forms, etc., or else in code, where the code gets the data from the table and then presents it on-screen.

-Recreate the queries in SQL Server , this will allow you to use\exploit the t-sql language and utilise the SQL Server horsepower.You will need to create pass-through queries to use the stored procedures.

-The whole point of splitting up the system is usually to allow more users, and having one Access copy directly binding a display to a table will kill that idea! In addition, Access is very “chatty” with bound tables. Therefore the recommendation that you go with code to obtain/update the database.

-Of course , you don't have to change your queries - make sure the table names stay the same - but your life will be significantly easier if you convert them to SQL Server stored procedures.

-As part of reformatting the queries you'll need to change every reference to the tables.

 

A few points about the set up 

Microsoft Data Access Objects (DAO) libraries not found 

DAO

 

-64 bit or 32 bit - If you have Access components  which are 32 bit , than you need to install 32-bit etc

 

SSMA Assessment Report 

Although not mandatory , strongly recommend the assessment report is utilised. Includes errors,warnings,informational and individual error correction steps. 

SSMA mainly migrates tables and select queries with no parameters. Forms, reports, macros, and VBA modules are not converted

 

Migration process 

-Prepare the target SQL Server Instance & 

To improve the efficiency of the migration consider preparing the Access database , and looking at the Access database with these following points 

  • Add table indexes and primary keys    Make sure each 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    Make sure these relationships are based on fields with consistent data types and sizes. SQL Server does not support joined columns with different data types and sizes in foreign key constraints.

  • Remove the Attachment column    SSMA doesn't migrate tables that contain the Attachment column.

 

 

 

Common warnings & errors

A2SS0024 : Table does not have primary key 

        - The tables will migrate but it is worth making the change before migrating 

A2SS0030: Identifier name might cause problems

 

 

 

Migrate an Access database to SQL Server


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on A simple guide to Microsoft SQL Server Migration Assistant for Access


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer