Journey from MS Access 2016 to Sql Server 2016

Introduction

Sometimes we find ourselves in a situation where we may want to dive into the SQL Server Ocean after creating a few numbers of tables in MS Access. This happens like that due to some spicing materials or conversations we normally have with our colleagues, well that sounds out of this world to some readers, but it happens. Due to some challenges, I went through when I was upsizing my MS Access 2016 to SQL Server 2016, I’m now compelled to share my experience to help those who are in the process of starting the said journey above. 

Common Method of Upsizing MS Access 

SSMA is a more generic tool than the Upsizing Wizard (Not present in MS Access 2016) and is designed to migrate data from various databases into SQL Server. SSMA is free and can be downloaded from the Microsoft sites. After you download and install SSMA on a 64-bit version of Windows, you will find two program shortcuts, one for 32-bit Access and the other for 64-bit Access. If you try to run the wrong version, you will see a message warning that you are running the incorrect version of SSMA for the available Access libraries.

Prerequisites

Before you install SSMA, make sure that your system meets the following requirements:

• Windows 7 or a later version, or Windows Server 2008 or a later version.
• Microsoft Windows Installer 3.1 or a later version.
• The Microsoft .NET Framework version 4.0 or a later version. The .NET Framework version 4.0 is available on the SQL Server product disc, and by using information in the Microsoft .NET Guide.
• Access to and sufficient permissions on the computer that hosts the target instance of SQL Server/SQL Azure DB to which you will be migrating database objects and data.
• Microsoft Data Access Object (DAO) provider version 12.0 or 14.0. You can install DAO provider from Microsoft Office 2010/2007 product or download it from Microsoft web site.
• The SQL Server Native Access Client (SNAC) version 10.5 and above for migration to SQL Azure. You can obtain the latest version of SNAC from Microsoft® SQL Server® 2008 R2 Feature Pack
• 4 GB RAM (recommended).

Pitfalls

This is where the first killer punch is! I emphasize, please be sure of which Microsoft Data Access Object (DAO) provider version is suitable for your situation. To help our novice programmers, please use the following:

(1)  If you are migrating an MDB, then use Microsoft Data Access Object (DAO) provider version 12.0, to get this version you need to install MS Access run-time 2007.

(2)  If you are migrating an MS Access 2016 ACCDB, then use Microsoft Data Access Object (DAO) provider version 14.0. To get this version you need to install MS Access run-time 2013.

• If by mistake you choose Version 12.0, then rest assured that all your child tables will be rejected and your app will not be linked
• Because in principle you cannot install MS Access run-time 2016 together with the full version 2016, then you still have some teething problems here, your closet candidate will be MS Access run-time 2013, but again be prepared to the read-only or uneditable sub-forms after linking your back- end.

Check sub-form forms with the following symptoms after linking

• Grayed
• Not editable
• Error with formulas
• Invisible datasheet
• Uneditable record source queries

Solution

Open your record sources for your affected sub-forms:

• Go to those queries properties and change the property called all out-put – from No to Yes

Then save all changes, this will sort out the sub-form problems and everything will be back to normal as usual. The reasons why this error occurs is due to the MS Access 2013 we use which is lower than 2016.

Procedure 1

Before you go ahead using the above tool, ensure that your application is thoroughly tested or error-free, for example, check the following:

• All your queries are not using calculated fields stored from the local tables, if you allow that, the Sql- server will not recognize those control fields, they will just be assigned data type like float, which will distort your reports because nothing will be pulled to your reports by your queries.
• Check any malfunctioning reports from your users and clear them first
• Ensure the entity relationship is tested and sound
• Ensure that you have a thorough understanding of the SSMA project creation
• Ensure that you have the capacity to handle the errors that sometimes occur during migration, if you don’t, please get back to your books and update yourself on that knowledge gap.

Procedure 2

After the above procedures are done, then you can now migrate your MS Access table to your SQL Server 2016.

When linking the tables use the correct ODBC drivers for example for MS Access 2016:

Use Microsoft ODBC Driver 13.1 for SQL Server. Always Encrypted is a new SQL Server 2016 and Azure SQL Database security feature that prevents sensitive data from being seen in plaintext in a SQL instance. You can now transparently encrypt the data in the application so that SQL Server or Azure SQL Database will only handle the encrypted data and not plaintext values. 

If an SQL instance or host machine is compromised, an attacker can only access ciphertext of your sensitive data. Use the ODBC Driver 13.1 to encrypt plaintext data and store the encrypted data in SQL Server 2016 or Azure SQL Database. Likewise, use the driver to decrypt your encrypted data. Azure Active Directory (AAD)

Advice

Please ensure that the data type is correct if you decide to migrate your tables manually, otherwise, I strongly recommend to novice programmers to use the SSMA tool described above as long as you can handle the 10% remaining work. If you decide to ignore this, it is as good as trying to cross the Atlantic Ocean with a bicycle, and you know what it means! 

Be warned I rest my case here.