Microsoft Access is one of the lesser-known applications in Microsoft’s Office Suite, but its lack of notoriety is not a reflection of its usefulness.   

Access offers a variety of powerful tools that allow for the rapid development of data-driven desktop applications. 

Access caters to beginners and DIYers with its intuitive UI, Wizards and No code Macro development tools but also allows experienced developers to leverage VBA (Visual Basic for Applications) for more advanced coding applications. 

As a result, applications from simple office inventory, all the way to full-featured ERP and accounting systems have been developed using MS Access to run businesses since its release in 1992.

Microsoft Access offers the following object to develop applications:

• Tables: Data storage tables including indices and foreign key relationships
• Queries: Access employs its own SQL (Standard Query Language) that’s very similar to MS SQL or MySQL formats along with an easy-to-use UI for building queries.
• Forms: Access Forms enable the development of the application’s “screens”.  It offers a WYSIWYG development tool with extensive control options and both macro or VBA code modules triggered by user actions or events.
• Reports: Reports in Access allow the developer to create data-driven reports with functionality that can rival many larger reporting applications like Crystal Reports.
• Macros: Access Macros offer no-code options for screen automation and functionality
• Modules: Modules allow more seasoned developers the ability to write traditional code in VBA to implement business logic.

The limitations of Access – reasons you might want to migrate from Access

If Microsoft Access is so powerful, why isn’t it used more often?

Despite its powerful tools along with its user-friendly interface that allows for Rapid Application Development (RAD), Access is not without limitations.  As such it is not frequently used to develop larger mission-critical systems.

Here are some of the limitations of MS Access that may push you away from it, or choosing a different platform from the start:

• Corruption: Microsoft Access files can become corrupt from time to time.  This corruption renders the data or the file unusable until the corruption is resolved.  The corruption can range in severity from: (1) easily resolved to (2) resolved with data loss to (3) unable to be resolved.
• Size limitations: MS Access files (.accdb and the older .mdb) are limited to 2GB in size.  That limitation may not be a concern for smaller databases focused on data with low transactions.
• Performance: While a powerful database tool, Access is not an enterprise-level database system.  As a result, it does not leverage the power of many server-based systems.  This can result in slower performance than more powerful DB systems.
• Desktop only:   MS Access is a desktop application. The programs written in it are meant to run on desktops.  In many cases, desktop applications lack some of the modern functionality of web applications and can sometimes be complicated to maintain.
• Trimmed function set: Microsoft Access offers tools for database development (tables), date querying (Queries), UI development (Forms), reporting (Reports) and coding (VBA). But each of these is a watered-down version of their more robust counterparts. This can limit the capabilities of an application developed in Access.
• Limited developer network: The pool of skilled developers who are experts in MS Access is small relative to more modern platforms (C#, React, MS SQL, etc.). That can make updating or maintaining the applications difficult or costly.

Careful though…. Are you sure you need to migrate yet?

If you’re struggling with some of the issues above, you may be exploring ways to migrate away from MS Access. Before you do, here are some suggestions that may help alleviate some of the issues above, namely those related to:

1. Corruption
2. Size limitations
3. Performance

Corruption:

If you’ve run a Microsoft Access database for more than a few years, chances are you’ve experienced occasional database or file corruption.   If you’re dealing with data corruption in your MS Access file or tables, here are some things you can try:

1. Repair & Compacting: The built in Repair and Compact tool in Microsoft Access can resolve most basic corruption in .mdb and .accdb files. This tool will help correct the corruption and “shrink” the database.  There are some forms of corruption that this tool can’t address.  In these cases, there may be third-party tools to assist in accessing your data if restoring from backup isn’t an option.
2. Splitting the DB and front end: If you’re experiencing somewhat frequent corruption and using the Repair and Compact tool often, you may benefit from splitting your front end and back end into two (or more) separate files (see https://support.microsoft.com/en-us/office/split-an-access-database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc ).  One file should contain all of your front-end UI objects (Queries, Forms, Reports, Macros, Modules) and the other your backend database objects (Tables).  After doing this you’ll need to link the tables in the back-end file to the front-end file and distribute the front-end file to your users. Microsoft Access has an embedded Split database Wizard to assist with this process.
3. Avoid connecting to your data over a VPN: Another suggestion to prevent frequent database corruption is to avoid running your Microsoft Access application over a VPN.  If you’re accessing the entire application (front end and back end combined) over a VPN, or accessing your front-end locally but your backend across a VPN, the VPN overhead can cause performance issues that can result in corruption.  If you need to access your Access application via a VPN, the best approach is to connect to a remote desktop session on a workstation that is on the same local area network as your Access data.

Size limitations: 

If you’re approaching the 2GB file size limit on MS Access Files you can try the following:

1. Repair & Compacting: The normal use of adding, deleting and editing records in a Microsoft Access database can cause the data file to become “bloated”.  When this happens, the Repair & Compact tool in MS Access can be used to shrink the database to remove the bloat.
2. Splitting the DB and front end: Since MS Access files contain not only data storage objects like tables but also UI and business logic objects like Queries, Forms, Reports, Marcos and Modules, some of the size of a file can be used by these non-data objects.  If your Access application isn’t already split into a front-end and back-end, you might be able to free up some space.

Performance:

If you’re experiencing performance issues in your MS Access database, you can try the following:

1. Repair & Compacting: Since MS access files are flat files residing on a drive, the applications accessing them must load the entire file to process or query the data.  As the data file size increases, more data needs to be loaded into memory and/or transferred across a network.  Some performance gains can be realized by shrinking the size of your data files.  The Repair & Compact tool in Access can help shrink the DB file.
2. Splitting the DB and front end:  If all of your Access Objects (tables, queries, forms, reports, macros and modules) are stored in one file and all users access that file from a network share, your performance may improve if you split the application into front-end and back-end files and deploy the front end to local user workstations.  This will reduce the burden on the file system accessing multiple simultaneous users on one file.
3. Review your Indexes: database indexes are a sort of catalog of your data.  They tell the database system how to find the data you’re looking for quickly. Good indexes are designed to meet the needs of the end users.  For example, if a database built to look up customers by phone number begins to slow down as the number of customers grows over time, the performance lag could be the result of a lack of index on the phone number field.  Ensuring your tables have solid indexes on the fields that are searched frequently can result in significantly faster application performance.

Migrating away from MS Access – some of it, or all of it?

If the suggestions above don’t solve your issues, or if you still prefer to migrate away from MS Access, your first step will be to identify which core function (database, user interface or the entire application) you want to move. If you need help identifying which parts of your application you would like to migrate, feel free to reach out to  start a conversation. 

Here is a chart that associates the problems or limitations outlined above with the core function you want to move:

Problem(s)	Core Function	Migrate to (Examples)
Corruption, Database Size, Performance	Database	Microsoft SQL*
Accessibility (Desktop only), Trimmed function set	Front end (UI)	Web applications, React, C#, …
Limited developer network, Trimmed function set or All of the above	Entire Application	All of the above

* NOTE: There are other relational database systems besides MS SQL.  Platforms like: MySQL, PostgreSQL as well as many NO SQL platforms available that you may want to consider.

Database

If you want to migrate the database functionality of your application away from MS Access, but keep the:

1. business logic,
2. user interface
3. and reporting

in Access then your primary decision will be to choose between hosting your database on-prem or in the cloud.

A brief description of the two options:

• On-prem (or On Premises): Hosting an application on-prem means the application is installed and running on equipment in your facility.  Generally, on a server running a server operating system.
• Cloud: Cloud hosting means your application is installed and running on a cloud service.  Traditionally, cloud-hosted applications are hosted on one of a few major providers of cloud services (Microsoft Azure, Amazon Web Services, Google Cloud etc.) but there are many other cloud providers available.

Here are some things to consider when choosing between on-prem and cloud:

1. Support: Both options require some level of support, but since on-prem solutions are installed on hardware in your facility, it’s better suited to organizations that have knowledgeable staff to install, configure, maintain and support the servers and software needed to run the application.
2. Accessibility: Will your database be accessed by users in your office, or will your data be accessed from users spread across different geographic regions? On-prem solutions work best if all of your application’s users access the application from your local office network.  If you have remote users or external users such as customers, hosting your data in the cloud may be a better solution. 
3. Security: Security must be considered with both options, but if you choose to host your data on-prem, you’ll need to be diligent in ensuring that your data and network are secure from external threats.  This is especially important if you are making data hosted on-prem available to outside applications or users.  In the case of cloud hosting, you will still want to ensure that best practices are followed to only allow access to the users and devices that need it.

Once you’ve decided where to host your database, here are some steps to migrate your database from MS Access to MS SQL:

Steps to migrate your database from Access to MS SQL

1. Convert database
2. Verify database properties
3. Validate the data
4.  Link MS Access front end to new database

1. Convert: To migrate from MS Access to an MS SQL database, you’ll need to create an SQL database that contains the same tables with the same structure (schema) and data as the MS Access database.  There are a few approaches to this:
   • Manual: Either entirely manually or through scripting the database structure, create the same DB structure in the SQL database.  Once the structure is in place, you can link the new empty SQL tables to the MS Access file using the linked table manager, and then create MS Access Append Queries to push the data from MS Access into your new SQL database.
   • Conversion tools: Microsoft SQL Migration Assistant for Access: Microsoft offers the Microsoft SQL Migration Assistance for Access as a free download from their website.  This tool will allow you to attach to an MS Access DB file and an SQL server database, and will migrate the data tables schema (structure of the table) and data from the Access DB to the SQL database. 
2. Verify: When converting from MS Access to MS SQL there are some database properties that you’ll need to verify either before or after the conversion is done.  Here are some:
   • AutoNumber Fields: Database systems allow for fields to be auto-incremented or auto-numbered.  Fields with that data type need special attention when being pushed into another table.  Autonumbering should be disabled on the destination table before importing, and then re-enabled after the import is complete.
   • Foreign Keys: You will want to ensure that all Foreign Key constraints were properly imported into the new database.
   • Defaults: If there are default values set for any database table fields you will want to ensure they are set up in the new system.
   • Data Types: MS Access and MS SQL have similar but slightly different database types.  You want to make sure that the new SQL data tables store the data correctly and no data is truncated or lost during the migration.
   • Indexes: Indexes improve performance on database tables.  You will want to ensure that any indexes in MS Access tables are migrated to SQL (Note: SQL has improved tools to help developers set up helpful indexes).
3. Validate: Perhaps the most important validation step is to ensure that ALL of the data was accurately migrated from Access to SQL. You can use data compare tools like the one in Visual Studio or develop Queries to join and compare the data in the source and destination databases to confirm all data was accurately transferred.
4.  Linking MS Access front end to new database platform: Once the database has been migrated and the data validated, you’ll need to establish an ODBC connection to the new database and use the Linked Table Manager in MS Access to link the data tables into your Access front end.  To keep the functionality of the front end components in your MS Access database you will need to ensure that the name of the linked tables are the same as they were named in MS Access (Note when linking tables from SQL into MS Access you can change the name so that Access uses a different table name than SQL does).

Front End

If you want to migrate the front-end functionality of your application away from MS Access, you’ll have to make some decisions about the type of front end you want:

1. desktop,
2. web app or
3. native app

and you’ll have to identify where you want to host your application:

1.  on-prem
2. or in the Cloud.

Some details regarding front-end options:

1. Desktop Application: Desktop applications run on a local device or workstation.  They require the software to be installed on the device in order to run.  This requires that all workstations running the application run a supported operating system.  In addition, remote access to these applications can usually only be done via remote desktop.  Desktop Applications are traditionally hosted on prem. These applications tend to work best when only a small group of users connected to an office network are going to be accessing the application.
2. Web Application: Web apps use platforms and technologies that run on web servers and are accessed using common web browsers (Google Chrome, Microsoft Edge, Firefox, Safari etc.).  Applications with this architecture can run both on-prem or in the cloud.
3. Native Application: A native application or native app is an application designed to run on mobile devices usually running IOS (iPhone) or the Android (Samsung, Google etc.) operating system.  This development approach works best for applications that are accessed on mobile devices.  Deployment of these applications is usually done through app stores which have requirements that must be met for an app to be listed.  Additionally, the apps that run on IOS leverage a different code base than those running on Android so you must account for those differences when developing

Here are some things to consider when deciding which type of application you want to develop and where to host the application front end:

Support:

All of the application architectures and hosting options require some level of support, but each requires different skillsets.

For example, desktop applications require more knowledge of workstation operating systems and technologies, whereas web apps require knowledge of web servers and browsers. 

When deciding which architecture and hosting solution works best for you, you’ll want to factor your team’s ability and experience in supporting it.

Accessibility:

Who will be using your application and where they will be located when accessing it are all factors when making hosting and architecture decisions. 

If you have a small group of users who will only access the application from their workstation in the office, an on-prem desktop application may work well, but if you’re users are spread across the country and are accessing the application from a variety of device types you may want to look at a cloud-hosted web or native app.

Security:

Security must always be considered, but if you plan to host your application’s front end in the cloud, you want to be sure to limit access to only those users who are authorized.  If those users all work from a static location, you may be able to prohibit access by whitelisting the static IP addresses of the fixed locations.  However, if your users are accessing the application from different locations or from locations without static IP addresses, you’ll want to ensure best practices are followed and tested as it relates to authentication and penetration testing.

Steps to migrate your front end from Access

1. Identify architecture and development stack
2. Select a deployment approach 
3. Redevelop
4. Testing / UAT
5. Deploy
6. Post deployment support

1. Identify architecture and development stack: In today’s software development environment there are a plethora of options for how to develop an application.  Many of these decisions are “in the weeds” and can be left to the development team, but here are some things that you may want to confirm are addressed with the choices:
   • Network of developers: You will want to be sure that your application is being developed in a development stack that has broad support.  This will ensure that you will be able to find developers who can support and enhance the application when needed.
   • Expected support timeframe: Like any other technology, software languages and platforms evolve over time.  Some are constantly updated to ensure they stay relevant, while others become dated and get replaced.  You want to be sure the languages, frameworks, and platforms your application is being developed in are well-supported and have no known upcoming deprecation or sunset dates scheduled.
   • Possible integrations: If you intend to integrate with other third party applications, you want to be sure that your new application employs technologies to make that possible and efficient.
2. Select a deployment approach: Software can be deployed in phases or all at once.  You will want to think about which approach works best for you and your team.  When making this decision you will need to be sure that all required functionality is kept during the transition.
3. Redevelop: Once the desired development stack and hosting environment have been selected, you’ll want to develop the requirements for the new application.  The functionality of the existing front end is a good starting point, but be sure not to limit yourself to that functionality.  Since you’re migrating away from Access, there is a good chance Access was preventing you from doing something you wanted or needed.  Now is a good time to enhance the current function set and take advantage of modern technology.
4. Testing / UAT: Whether you decide to deploy in phases or all at once your team will need to do thorough testing to ensure the new application performs as expected before deploying the application into production.  It is very useful if not required to have a testing environment, sometime referred to as a “sandbox” for testing.
5. Deploy: Once the application or phase of the application has passed UAT, it’s time to deploy the application into production. 
6. Post deployment support: Even the most well-organized and executed migrations have glitches, bugs or hiccups.  It’s important to have a plan in place to support the application immediately after the deployment and beyond.

Next Steps

If you’re thinking about migrating from Access to SQL or have determined you need to but don’t know where to start, feel free to reach out here and we can discuss the following topics:

• What functions your Access application performs.
• Your pain points or the problems you’re experiencing with Access.
• Requirements for what you want the replacement to be able to do.
• How you want to support a new application or database system including if you would like it to be hosted on-prem or in the cloud.

Want to see a few examples? Check out these Access Conversion Case Studies