A simple guide to Microsoft SQL Server Migration Assistant for Access

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 

-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