How to Migrate an MS Access Database to an SQL Server Database

Insights and opinions from a Microsoft-stack focused software development firm.

Microsoft Access is one of the lesser-known applications in Microsoft’s Office Suite, but its low profile is no 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, while also letting experienced developers leverage VBA (Visual Basic for Applications) for more advanced coding. As a result, everything from simple office inventory tools to full-featured ERP and accounting systems has been built in MS Access to run businesses since its release in 1992.

Microsoft Access offers the following objects for developing applications:

• Tables: Data storage tables, including indices and foreign key relationships.
• Queries: Access employs its own dialect of SQL that is 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 an application’s “screens.” Access offers a WYSIWYG development tool with extensive control options and both macro and VBA code modules triggered by user actions or events.
• Reports: Reports in Access let developers 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 give more seasoned developers the ability to write traditional VBA code 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 capable tooling and a user-friendly interface built for Rapid Application Development (RAD), Access is not without limitations. As a result, 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 lead you to choose a different platform from the start:

• Corruption: Microsoft Access files can become corrupt from time to time, rendering the data or the file unusable until the corruption is resolved. Severity ranges 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 limit may not be a concern for smaller databases with low transaction volumes, but it becomes a hard ceiling as data grows.
• Performance: While a powerful database tool, Access is not an enterprise-level database system, so it does not leverage the power of many server-based systems. The result can be slower performance than more powerful database platforms.
• Desktop only: MS Access is a desktop application, and the programs written in it are meant to run on desktops. Desktop applications often lack some of the modern functionality of web applications and can be complicated to maintain.
• Trimmed function set: Access offers tools for database development (Tables), data querying (Queries), UI development (Forms), reporting (Reports), and coding (VBA) — but each is a watered-down version of its more robust counterpart, which can limit what an Access application can do.
• 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 Access 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 already be exploring ways to migrate away from MS Access. Before you do, there are remedies that may alleviate three of the most common problems:

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 to try:

1. Repair & Compact: The built-in Repair and Compact tool in Microsoft Access can resolve most basic corruption in .mdb and .accdb files, correcting the corruption and “shrinking” the database. Some forms of corruption are beyond this tool’s reach — in those cases, third-party tools may help you recover your data if restoring from backup isn’t an option.
2. Split the database and front end: If corruption is somewhat frequent and you find yourself running Repair and Compact often, you may benefit from splitting your front end and back end into two (or more) separate files (see Microsoft’s guide to splitting an Access database). One file should contain all of your front-end UI objects (Queries, Forms, Reports, Macros, Modules) and the other your back-end database objects (Tables). After splitting, link the tables in the back-end file to the front-end file and distribute the front-end file to your users. Access includes an embedded Split Database Wizard to assist with this process.
3. Avoid connecting to your data over a VPN: Running a Microsoft Access application over a VPN is a frequent cause of corruption. Whether you access the entire application over the VPN, or run the front end locally against a back end across the VPN, the VPN overhead can cause performance issues that lead to corruption. If you must reach your Access application remotely, the best approach is to connect to a remote desktop session on a workstation that sits on the same local network as your Access data.

Size limitations

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

1. Repair & Compact: The normal cycle of adding, deleting, and editing records can cause an Access data file to become “bloated.” The Repair & Compact tool shrinks the database and removes that bloat.
2. Split the database and front end: Because MS Access files contain not only data storage objects like tables but also UI and business-logic objects (Queries, Forms, Reports, Macros, and Modules), some of a file’s size is consumed by non-data objects. If your application isn’t already split into a front end and back end, splitting it may free up meaningful space.

Performance

If you’re experiencing performance issues in your MS Access database, consider these steps:

1. Repair & Compact: MS Access files are flat files residing on a drive, so any application accessing them must load the entire file to process or query the data. As the file grows, more data must be loaded into memory and/or transferred across the network. Shrinking the data file with the Repair & Compact tool can deliver real performance gains.
2. Split the database and front end: If all of your Access objects (tables, queries, forms, reports, macros, and modules) live in one file that every user opens from a network share, 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 reduces the burden of multiple simultaneous users hitting one file.
3. Review your indexes: Database indexes are a sort of catalog of your data — they tell the database system how to find what you’re looking for quickly, and good indexes are designed around how end users actually search. For example, if a database built to look up customers by phone number begins to slow as the customer list grows, the lag could stem from a missing index on the phone number field. Ensuring your tables have solid indexes on frequently searched fields can make the application significantly faster.

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

If the suggestions above don’t solve your issues, or you still prefer to migrate away from MS Access, your first step is to identify which core function you want to move: the database, the user interface, or the entire application. Match the problems and limitations outlined above to the core function that needs to move. If you need help identifying which parts of your application to migrate, feel free to start a conversation.

Note: There are other relational database systems besides MS SQL. Platforms like MySQL and PostgreSQL, as well as many NoSQL platforms, are available and may be worth considering.

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 is whether to host 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 it 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 run on one of a few major providers (Microsoft Azure, Amazon Web Services, Google Cloud, etc.), though many other cloud providers are available.

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

1. Support: Both options require some level of support, but because on-prem solutions run on hardware in your facility, they are better suited to organizations with knowledgeable staff who can install, configure, maintain, and support the servers and software the application depends on.
2. Accessibility: Will your database be accessed only by users in your office, or by users spread across different geographic regions? On-prem solutions work best when all 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 fit.
3. Security: Security must be considered with both options. If you host your data on-prem, you’ll need to be diligent about protecting your data and network from external threats — especially if on-prem data is exposed to outside applications or users. With cloud hosting, you’ll still want to follow best practices and grant access only to the users and devices that need it.

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

Steps to migrate your database from Access to MS SQL

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

1. Convert: To migrate from MS Access to an MS SQL database, you’ll need to create an SQL database containing the same tables — with the same structure (schema) and data — as the MS Access database. There are a few approaches:

• Manual: Either entirely by hand or by scripting the database structure, recreate the same schema in the SQL database. Once the structure is in place, link the new empty SQL tables into the MS Access file using the Linked Table Manager, then create MS Access Append Queries to push the data from Access into your new SQL database.
• Conversion tools: Microsoft offers the Microsoft SQL Migration Assistant for Access as a free download from its website. The tool attaches to an MS Access DB file and an SQL Server database, then migrates the data tables’ schema (the structure of the tables) and data from the Access DB to the SQL database.

2. Verify: When converting from MS Access to MS SQL, there are database properties you’ll need to verify either before or after the conversion:

• AutoNumber fields: Database systems allow fields to be auto-incremented or auto-numbered. Fields with that data type need special attention when being pushed into another table — auto-numbering should be disabled on the destination table before importing and re-enabled once the import is complete.
• Foreign keys: Ensure that all foreign key constraints were properly imported into the new database.
• Defaults: If any table fields have default values, confirm they are set up in the new system.
• Data types: MS Access and MS SQL have similar but slightly different data types. Make sure the new SQL tables store the data correctly and nothing is truncated or lost during the migration.
• Indexes: Indexes improve performance on database tables. Ensure that any indexes on MS Access tables are migrated to SQL. (Note: SQL Server has improved tools that help developers set up useful indexes.)

3. Validate: Perhaps the most important step is confirming 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 that join and compare the data in the source and destination databases, to confirm every record transferred accurately.

4. Link the MS Access front end to the new database platform: Once the database has been migrated and the data validated, 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 preserve the functionality of the front-end components in your MS Access database, make sure the linked tables keep the same names they had in MS Access. (When linking tables from SQL into Access, you can rename them so 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 need to decide on the type of front end you want:

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

and identify where you want to host the application:

1. on-prem, or
2. in the cloud.

Some details on the front-end options:

1. Desktop application: Desktop applications run on a local device or workstation and require the software to be installed on the device. Every workstation running the application must run a supported operating system, and remote access is usually only possible via remote desktop. Desktop applications are traditionally hosted on-prem, and they tend to work best when only a small group of users connected to an office network will use the application.
2. Web application: Web apps use platforms and technologies that run on web servers and are accessed through common web browsers (Google Chrome, Microsoft Edge, Firefox, Safari, etc.). Applications with this architecture can run either on-prem or in the cloud.
3. Native application: A native app is designed to run on mobile devices, usually on iOS (iPhone) or Android (Samsung, Google, etc.). This approach works best for applications accessed on mobile devices. Deployment usually goes through app stores, which impose requirements an app must meet to be listed. Additionally, apps that run on iOS use a different code base than those running on Android, so you must account for those differences during development.

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

Support

All of the application architectures and hosting options require some level of support, but each demands different skill sets. Desktop applications call for deeper 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, factor in your team’s ability and experience supporting it.

Accessibility

Who will be using your application, and where will they be when they access it? Both are factors in your hosting and architecture decisions. If a small group of users will only access the application from their office workstations, an on-prem desktop application may work well. But if your users are spread across the country and connect from a variety of device types, a cloud-hosted web or native app may be the better path.

Security

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

Steps to migrate your front end from Access

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

1. Identify the architecture and development stack: In today’s software development environment there are a plethora of options for how to build an application. Many of these decisions are “in the weeds” and can be left to the development team, but you may want to confirm the choices address the following:

• Network of developers: Make sure your application is built on a development stack with broad support, so you can find developers to support and enhance the application when needed.
• Expected support timeframe: Like any other technology, software languages and platforms evolve. Some are constantly updated to stay relevant, while others become dated and get replaced. Confirm that the languages, frameworks, and platforms your application uses are well supported, with no known deprecation or sunset dates on the horizon.
• Possible integrations: If you intend to integrate with other third-party applications, be sure your new application employs technologies that make those integrations possible and efficient.

2. Select a deployment approach: Software can be deployed in phases or all at once. Think about which approach works best for you and your team, and make sure all required functionality is preserved during the transition.

3. Redevelop: Once the development stack and hosting environment are selected, develop the requirements for the new application. The functionality of the existing front end is a good starting point, but don’t limit yourself to it. Since you’re migrating away from Access, there’s a good chance Access was preventing you from doing something you wanted or needed — now is the time to enhance the current function set and take advantage of modern technology.

4. Testing / UAT: Whether you deploy in phases or all at once, your team will need to test thoroughly to ensure the new application performs as expected before it goes into production. A dedicated testing environment — sometimes referred to as a “sandbox” — is very useful, if not required.

5. Deploy: Once the application (or the current phase of it) has passed UAT, it’s time to deploy to production.

6. Post-deployment support: Even the most well-organized and well-executed migrations have glitches, bugs, or hiccups. It’s important to have a plan in place to support the application immediately after deployment and beyond.

Next Steps

If you’re thinking about migrating from Access to SQL — or you’ve determined you need to but don’t know where to start — feel free to reach out and we can discuss:

• 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 whether you’d like it hosted on-prem or in the cloud.

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

Browse our case studies to see how these migrations play out in practice, including:

• Modernizing Business-critical Legacy Software
• Legacy Software Update – Support, then full rewrite – Auditing Software
• Custom Business Process Automation Software – Time Tracking and Reporting

Ready to move off of Access? Keep exploring

For more perspective before you commit to a migration, browse our articles, including:

• Custom software can be a competitive advantage for small businesses, but not always
• When to Rewrite your software
• Why do I need a data warehouse?