When and How to Upsize Microsoft Access Databases to SQL Server

Mid to large organizations may have hundreds to thousands of desktop computers. Each desktop has standard software that allows staff to accomplish computing tasks without the intervention of the organization’s IT department. This offers the primary tenet of desktop computing: empowering users to increase productivity and lower costs through decentralized computing.

As the world's most popular desktop database, Microsoft Access is used in almost all organizations that use Microsoft Windows. As users become more proficient in the operation of these applications, they begin to identify solutions to business tasks that they themselves can implement. The natural evolution of this process is that spreadsheets and databases are created and maintained by end-users to handle their day-to-day tasks.

This dynamic allows both productivity and agility as users are empowered to solve business problems without the intervention of their organization’s Information Technology infrastructure. Microsoft Access fits into this space by providing a desktop database environment where end-users can quickly develop database applications with tables, queries, forms and reports. Access is ideal for low-cost single user or workgroup database applications.

But this power comes with a price. As more users use Microsoft Access to handle their work, issues of data security, reliability, maintainability, scalability and management become acute. The people who built these solutions are rarely trained to be database experts, programmers or system administrators. As databases outgrow the capabilities of the original author, they need to move into a more robust environment.

While some people consider this a reason why end-users shouldn't ever use Microsoft Access, we consider this to be the exception rather than the rule. Most Microsoft Access databases are created by end-users and never need to graduate to the next level. Implementing a strategy to create every end-user database "professionally" would be a huge waste of resources.

For the rare Microsoft Access databases that are so successful that they need to evolve, SQL Server offers the next natural progression. Without losing the existing investment in the application (table designs, data, queries, forms, reports, macros and modules), data can be moved to SQL Server and the Access database linked to it. Once in SQL Server, other platforms such as Visual Studio .NET can be used to create Windows, web and/or mobile solutions. The Access database application may be completely replaced or a hybrid solution may be created.

For more information, read our paper Microsoft Access within an Organization's Overall Database Strategy.

Microsoft Access is the premier desktop database product available for Microsoft Windows. Since its introduction in 1992, Access has provided a versatile platform for beginners and power users to create single-user and small workgroup database applications.

Microsoft Access has enjoyed great success because it pioneered the concept of stepping users through a difficult task with the use of Wizards. This, along with an intuitive query designer, one of the best desktop reporting tools and the inclusion of macros and a coding environment, all contribute to making Access the best choice for desktop database development.

Since Access is designed to be easy to use and approachable, it was never intended as a platform for the most reliable and robust applications. In general, upsizing should occur when these attributes become critical for the application. Fortunately, the flexibility of Access allows you to upsize to SQL Server in a variety of ways, from a quick cost-effective, data-moving scenario to full application redesign.

Access provides a rich variety of data architectures that allow it to manage data in a variety of ways. When considering an upsizing project, it is important to understand the variety of ways Access may be configured to use its native Jet database format and SQL Server in both single and multi-user environments.

Access and the Jet Engine

Microsoft Access has its own database engine—the Microsoft Jet Database Engine (also called the ACE with Access 2007's introduction of the ACCDB format). Jet was designed from the beginning to support single user and multiuser file sharing on a local area network. Databases have a maximum size of 2 GB, though an Access database can connect to other databases via linked tables and multiple backend databases to workaround the 2 GB limit.

But Access is more than a database engine. It is also an application development environment that allows users to design queries, create forms and reports, and write macros and Visual Basic for Applications (VBA) module code to automate an application. In its default configuration, Access uses Jet internally to store its design objects such as forms, reports, macros, and modules and also uses Jet to store all table data.

One of the primary benefits of Access upsizing is that you can redesign your application to continue to use its forms, reports, macros and modules, and replace the Jet Engine with SQL Server. This allows the best of both worlds: the ease of use of Access with the reliability and security of SQL Server.

Microsoft Access and SQL Server Comparison

To better understand the upsizing decision process, take a look at the following comparison table:

Now that you have seen the various architectures and database engine options available, you’ll want to explore the decision points and parameters for making the upsizing decision.

The most important part in this process is understanding that not all Access databases need to be upsized. In fact, a majority of Access applications should not be upsized—the cost and disruption to business is simply not a cost-effective use of your resources. These databases work fine on a day to day basis and do not need attributes such as scalability, security and 100% reliability. Of all the Access databases in your organization, only a few are candidates for upsizing.

Additionally, from the list of candidates for upsizing, a majority can be upsized using a cost-effective process where only the data is moved to SQL Server. All of the application’s functionality in terms of forms and reports is kept in Access. Only the smallest percentage of upsizing projects involve rewriting the Access application in a new environment such as .NET.

The following section examines each of the key areas involved in database planning and discusses how Access performs in each area.

Security

Microsoft Access offers three different security mechanisms.

1. Database Passwords: Assign a password to a database. Only users who know the password can open the database.
2. Jet Workgroup Security: Users, groups and object permissions can be defined to restrict who can view/edit data and what they can run. Workgroup security is available for MDB databases but not ACCDB databases.
3. File Encryption: Contents of the database can be encrypted at the file level. ACCDB encryption is significantly better than encryption for MDBs.

Unfortunately, these mechanisms are neither robust nor reliable. Database passwords use a very simple encryption mechanism. In fact, removing an Access database password is simple matter given that free and commercial password "removers" are easily found on the web. While Access users may not be concerned about such lapses, IT managers certainly should be.

While Jet Workgroup Security is more robust, it still leaves the contents of the entire MDB database open from the file system. Since all table data and code is stored in plain view, it is a trivial matter to open an MDB file in a string-compatible editor and view code, passwords, and table data.

Finally, because Access requires full read permissions for all users to the actual database file, anyone who can see a shared network drive can walk off with the database on a disk or CDR or email it outside of your organization.

If Security is of importance, SQL Server is much more secure than Microsoft Access, Excel, Word or any other program that stores its files directly on the hard disk accessible to the user.

Reliability and Maintenance

Reliability is one of the key benchmarks to examine when considering upsizing. Indeed, for many mission critical applications, reliability is the most important consideration. Microsoft Access is not as reliable as SQL Server for several reasons.

Database Corruption

When Microsoft Access/Jet databases encounter an unexpected internal error or connection problem, they may become corrupt. A corrupt database generally locks out all users of the database and results in data loss and business disruption.

Microsoft Access/Jet databases are more prone to corruption than SQL Server for a number of reasons. Since Access/Jet uses a file share model, all users are concurrently holding active connections to data. If any one of those users unexpectedly loses the connection, the database can become corrupt. Connection loss can happen if the user’s network connection is intermittent, driver versions are not current or conflicting versions of the Jet DLLs are used to read the same database file.

Microsoft Access includes a Compact/Repair utility, but serious database corruption is not fixed by this utility. Third party repair services are available, but this requires sending the affected database off to another location, paying a fee, and waiting for it to be returned with mixed results.

Maintenance Issues

Windows allows copying a Microsoft Access database even if people have it open, so you can create a backup, but that may be a problem if someone is modifying the data at the time the file is being copied.

Compacting a Microsoft Access database requires all users to be off the database, which is problematic for solutions running 24/7. A lock is placed on the database at the file level (*.ldb or *.laccdb files) as soon as it is opened by the first user.

In a multi-user environment, Microsoft Access does not tell you who is currently in the database, so it can be an administrative headache to get people off the system. Our Total Access Admin program lets you monitor the users going in and out of the database in real-time, which helps and can compact the database after everyone exits, but it's not a built-in feature of Microsoft Access.

It is often difficult to coordinate the process of ensuring all users log off of an Access application before making a backup. Typical scenarios involve users leaving their computers on when they leave the office for the day. This leaves the database open and backup software will not be able to reliably copy the database file. Often this is only detected after the backup fails, leaving the system administrator to track down the problem and hope it is resolved before the next backup runs.

Additionally, Microsoft Access is not self-tuning like SQL Server. It does not automatically reclaim lost database space or optimize indexes and queries. This maintenance is performed by running the repair/compact feature. Our Total Visual Agent program lets you automate this on a schedule you specify and maintain an audit trail, but it's not a built-in feature of Microsoft Access.

If an organization doesn't regularly backup individual desktop databases, people may create Access databases on their PCs that are never backed up or maintained. Organizations can treat them as user data that can be lost as if it were an Excel spreadsheet or Word document, but better policies should be in place so users can save their databases where basic system administration is provided.

Microsoft Access databases need to be backed up for disaster recovery and periodically compacted to remain healthy. An automated system should be implemented to take care of that for the many Access databases that exist today and those created in the future. SQL Server upsizing should be considered for the databases where such automated processes cannot be implemented due to continuous use of the database or the cost of losing any data is high.

Scalability

Scalability is defined as the ability of an application to operate in an acceptable manner as the number of users or processes calling the application increases. In some situations, Microsoft Access/Jet cannot scale to support the demand on the database.

Split Database Architecture

Before doing upsizing, your Access database should have a Split Database architecture. This separates the user application database from the back-end shared data database. The front-end application database links to the shared database and upgrades are easier because the data does not need to be modified for new releases (provided tables structures aren't modified). For more information, see our paper on Microsoft Access Split Database Architecture to Support Multiuser Environments, Improve Performance, and Simplify Maintainability. The split database architecture will also help in an upsizing endeavor to preserve the existing Access front-end.

If a single copy of a Microsoft Access database resides on a network drive and is being opened by multiple users at one time, you may have a scalability issue. But that should be addressed by using a split database architecture rather than upsizing to SQL Server.

Number of Concurrent Users

There have been myths about Microsoft Access databases not being able to support more than a 20 users. That may have been the case when Microsoft Access was introduced in 1992, but that has NOT been the case for quite some time.

Microsoft Access technically allows up to 255 connections per database. That means it can be used for applications for teams many times that if they don't all need to use it at the same time.

The number of connections/users that an Access database can support is dictated by how well the application was designed and implemented. A well designed Access application can support 100+ simultaneous users with decent performance. On the other hand, a poorly designed Access application can run at a crawl with two users. Or some processes are just time consuming and will be slow no matter what database is used. It's really not about the technology but the implementation.

Unfortunately, many Access databases are not well designed or implemented with best practices. This is because most Access databases are created by users who simply do not have the experience or knowledge to create professional database applications. They are built over time and new features and data models are tacked-on as the need arises. The result is an overall solution that cannot reliably support more than a few users.

Do not upsize Microsoft Access applications for user count reasons if the number of simultaneous users is relatively low. Upsizing should be considered if one needs to support 100+ simultaneous users and more in the future.

Maximum Database Size

Each Microsoft Access databases has a maximum size of 2 GB. That's a lot of text and numeric data, but can be easily exceeded if files and graphics are being stored. For the vast majority of Microsoft Access databases, that's much larger than the database will ever get. Sometimes databases get large, but a compact usually reduces it to a more reasonable size. Make sure you also decompile your Microsoft Access databases from time to time when compacting.

With a split database architecture, a Microsoft Access application can link to data from multiple Microsoft Access databases that add up to more than 2 GB. Of course, that's not a good design if data shouldn't be split across multiple tables or databases.

Upsizing should be considered if the database exceeds 1 GB or will exceed 2 GB in the near future.

Different Versions of Microsoft Access and Jet

There are many versions of Microsoft Access. An Access database solution may be dependent on a particular version of an Access database format, rely on features that are only available on certain versions of Access, components such as ADO and DAO, etc. While it's easy to distribute an Access database to users who have Access already installed on their PC, it's not so easy to control what version of Access is opened to run it.

A PC may have multiple versions of Access installed on it. Opening an ACCDB or MDB file directly launches the Access version that's associated with that file extension (usually the last Access version that was opened).

Additionally, when Access 2000 was introduced, the new MDB file format was not compatible with the prior Access 97 version. Opening the database in Access 2000 (or later) could upgrade the database to the new format and prevent users of the old version from opening it which was a problem in multiuser environments. This problem has not recurred since the 2000 version.

Our Total Access Startup program helps organizations centrally manage and deploy Microsoft Access databases to each user's desktop and control which version of Access is launched for it. This makes it easy to distribute your Access applications with a shortcut, and the latest version of your database is always installed on each user's machine. It also makes it simple to upgrade your application from one version of Access to another.

Problems managing different versions of Microsoft Access, or distributing updates of Microsoft Access databases, is not solved by migrating to SQL Server if you keep the Access front-end of your database. It can only be addressed by replacing Microsoft Access completely. That may solve the immediate problem, but it prevents users from getting their work done. We've seen organizations ban Microsoft Access only for employees to purchase worse database products instead since the database problem did not go away. For most organizations, it's impractical to use professional developers to create every database needed when most can be handled by end users.

Performance

Performance is a common reason people want to upgrade their Microsoft Access databases to SQL Server. There is an assumption that SQL Server will run faster than a Microsoft Access database. After all, SQL Server is managed by a separate server that only sends the requested records, and can use multiple CPUs and machines to manage huge datasets.

Microsoft Access, being file based, sends the entire database, table, or index depending on the request for the client (PC) to process.

While this would give the edge to the server approach, we have found that performance is NOT improved in many situations. Here are some reasons:

• Many Access databases are relatively small (under 100 MB). With current hardware and network connectivity, the amounts of data being passed is almost instantaneous whether its a few records or the entire table.
• SQL Server performs extra work that Access databases do not. For instance, SQL Server can maintain a transaction log and performs continuous maintenance that adds overhead. We have seen decreases in performance when small databases are upsized to SQL Server.
• SQL Server is designed to handle very large databases well beyond the 2 GB limit of Access. It is not optimized for what it would consider tiny databases.
• Performing queries from different linked table sources may not benefit from SQL Server optimization since the work is still done by Access
• Data caching by Microsoft Access for Access tables exists because it knows if the data has changed. For SQL Server data, caching in memory is not possible because the data may have changed since the last request.
• A poorly designed set of tables, queries or VBA code is going to run slowly regardless of whether the data is in Access or SQL Server.

Before upsizing to SQL Server for performance reasons, make sure your Access database is optimized so that it runs as well as it can in Access. Going to SQL Server will not fix serious database and query design issues. Fixing these in Access is actually easier than doing so later in SQL Server, so the time and effort spent doing this is preserved should you upsize later.

To ensure your Microsoft Access database does not have serious problems, use our Total Access Analyzer program to document and analyze your database. It detects over 300 types of errors, design suggestions, and performance issues that can help you optimize your Access database.

For relatively small Microsoft Access databases, do not upsize to SQL Server solely for performance reasons. There may be a decrease in performance after upsizing. Look for ways to optimize the problems in Access before making the investment to upsize. Performance issues may be unrelated to the data storage.

If you decide to upsize your database to SQL Server, be aware that certain features are lost when you migrate to SQL Server. Microsoft Access databases are multiuser aware and automatically handle data that changes in a multiuser environment. SQL Server databases only provide information upon request. It does not broadcast changes, which has implications on your solution:

When contemplating an Access upsizing project, it is important to understand that there are a variety of upsizing options. These range from simply moving the data to completely re-architecting and redesigning the application. In order to choose the correct path for your upsizing project, you should be familiar with the types of data architecture that Access supports:

One of the biggest challenges your organization may face is identifying how many Microsoft Access databases you have and which ones should be upsized. The problem is where to start. How do you efficiently inventory your Access databases just to get an initial handle on the problem? Even with conservative estimates, an organization with 500 desktops may potentially have 10,000 Access databases.

Manual Solution

There are several strategies for solving this problem. The simplest route is to communicate with desktop users, usually through an email message, and ask for basic feedback on each user’s database inventory.

• How many Access databases do you currently use?
• How many tables are in these databases?
• Do you share this database with other users?
• Do you link to, or use import/export on, corporate data?
• Are your databases being backed up?

A well defined (and brief) set of questions will help you identify which databases may be at risk.

Automated Solution

For larger organizations, an automated system is preferable. Point to the machines that need to be examined and automatically inventory and report on the Access databases that are found.

By checking local and network hard drives, one can create an inventory of all the Access databases in the organization to decide and schedule upsizing projects.

Our Total Visual Agent program lets you perform Microsoft Access database chores across your network. In addition to performing tasks such as compact and backup, it can also be used to collect database statistics such as the number of objects in each database and number of records in each table. A free trial is available.

Once you choose to upsize an Access application, there are now many options for storing data in SQL Server. Identify an existing SQL Server installation to use or create one. SQL Server comes in a variety of editions:

• Free Microsoft SQL Server Express edition that can be installed on your desktop. This is a limited version of SQL Server that supports databases up to 10 GB in size.
• SQL Server Enterprise versions that you host on your own server. This requires purchasing a SQL Server license and the hardware to host it.
• Cloud based SQL Server like SQL Azure starting at $10 a month per database. Many ISPs also offer SQL Server databases as part of their hosting plans.

In general, all editions of SQL Server, including Express, are capable of handling small workgroup applications. Consider using a dedicated server version if you are upsizing both the application and database and your needs call for the greatest scalability, functionality and reliability.

Microsoft Access 2013 and 2016 Web Solutions

Microsoft Access 2013 introduced Access Web Apps which is a way to create Access databases on SharePoint 2013/2016 or hosted by Microsoft with their Office365 subscription. The database storing the Access web solution is actually SQL Azure. If you already have an Office365 subscription, you can literally get an unlimited number of SQL Azure databases for free (they usually cost $10 a month). Even better, you can link to the SQL Azure database directly from your MS Access desktop database. However there are limitations:

• Databases for Access Web Apps are limited to 1 GB in size, so it doesn't help if you need to upsize for databases exceeding the 2 GB limit of MS Access Jet databases
• No ability to add stored procedures and triggers. Microsoft Access locks down its databases to prevent your ability to add custom code directly. That means it's not ideal if you're upsizing to consolidate code in the backend database for security or manageability.
• Access Web Solutions adds its own code and storage since Access Web Solutions stores its front-end solution directly in the SQL Server database.

Additionally, Access web solutions are still relatively immature and cannot be customized to provide the functionality that existing Microsoft Access databases can with VBA. If you are migrating to SQL Server in order to extend Microsoft Access databases to the web, with the exception of very limited cases, you will likely need a more robust front-end solution such as Visual Studio .NET.

However, this is worth considering if Access web solutions are sufficient for your internal needs (since Office365 will no longer support the creation of public web sites). You can upload your data to this platform and link your existing Access database to it.

Warning: In March 2017, Microsoft announced that Access Web Apps will not be supported in the future. For on premise SharePoint hosts, Microsoft is committing to supporting AWA in the next version of SharePoint. For Office365, Microsoft is ceasing support in April 2018. They have not provided an equivalent alternative with the same features as AWA. Of course the data in SQL Azure can be moved. (Microsoft Announcement)

To avoid unnecessary costs, ensure application availability and minimize risks, it is important to carefully plan your Access upsizing project. The amount of planning is directly related to the type of upsizing project you envision. For example, a simple migration of data to SQL Server requires less planning that a complete rewrite of the application and data migration. This section provides guidelines and best practices for planning your upsizing project.

Database evolution should be expected and it is a normal event in any company’s usual business development. The importance, size, and/or user accessibility (growth) of a particular application often can exceed its original concept or development platform. While most Access applications can spend their entire useful life functioning perfectly well, some should be migrated to more secure and robust platforms. Knowing which Access databases are candidates for upsizing and exactly how to perform the migration can prove challenging to even the most experienced network manager.

If you are taking over an existing Microsoft Access application, read our paper Taking Over (Inheriting) Legacy Microsoft Access Database Applications before assuming that upsizing is the immediate answer. There are many things that can be done to leverage the existing investment in Microsoft Access so you can focus your efforts on those situations where upsizing is most worthwhile.

By keeping the Access application and data within the Microsoft family of products (e.g., Access to SQL Server) and engaging an experienced technology partner like FMS, the process can be quite manageable and cost effective.

When Microsoft decided to engage a certified partner to lend technical expertise and support for its national Microsoft Access to SQL Server Upsizing campaign, FMS was selected. We have focused on the Microsoft technology platform throughout our history, and remain one of the most trusted advisors to several Microsoft development teams today. Our reputation is one of consistent, thorough and significant involvement in all aspects of the software development community and our products have won some 40 industry awards.

Since the first release of Access in 1992, FMS has been providing award winning Access development software tools to the programming community. Now known around the world as the preeminent supplier of third party Access software, FMS has continually been on the forefront of Access innovation since the beginning. In fact, Luke Chung, our president and founder, is a Microsoft Access MVP and we've had several individuals on our team awarded that title.

Equally important, we have been developing reliable high performance database systems using Microsoft SQL Server since 1993. Our team has extensive experience in data normalization techniques, best practices for database design and a deep internal knowledge of how SQL Server works. We have implemented everything from small-scale workgroup solutions to highly scalable ecommerce business sites using SQL Server and SQL Azure. Contact us for an assessment and more information.