When and How to Upsize Microsoft Access Databases to SQL Server

Microsoft Access to SQL Server Migration ServicesBackground

Microsoft Access databases often start as a small solution for one user. As the databases become more important, they may be used by multiple people across a network.

For more advanced solutions, a split database architecture separates the data from the application. The back-end shared data in a Microsoft Access database (MDB or ACCDB format) is stored on a file server and each user has their own copy of the Microsoft Access application database linked to the shared data.

As the solution becomes more popular, issues arise that drive the upsizing of the data from a Microsoft Access database to SQL Server. There are benefits and tradeoffs for making this transition and multiple ways to take advantage of SQL Server. This paper shares our experiences with upsizing Microsoft Access databases to SQL Server over the years, so you can do it for the right reasons and understand when it's not appropriate.

Contents


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:

Microsoft Access Microsoft SQL Server
Description A database development environment that includes tables, queries, forms, reports, and programming logic A scalable, reliable and secure client/server database engine
Maximum Database Size 2 GB "Unlimited"
Maximum Concurrent Users 255 "Unlimited"
Security Basic desktop security, limited by Windows permissions Robust enterprise level security, users cannot see or copy the database directly
Performance Depends on local PC and network performance; may be very fast for "small" databases Based on server hardware performance
Reliability Reliable to the extent of the PC's reliability; requires periodic database compact and repair Enterprise quality reliability; automated database
Transaction Logging None All data changes may be logged
Table Triggers None for MDB databases.
For ACCDB, Access 2010 introduced Data Macros which can be similar to triggers.
Tables may have triggers to automate processes whenever the data changes

Microsoft Access and Jet Single User

In its default configuration, Access uses the Microsoft Jet database engine to store both object definitions and table data. Access and Jet are run on the user’s computer and the database is stored on a local hard disk.

Microsoft Access and Jet Multi-User

Access and the Jet engine allow multi-user access. In this scenario, each user runs a local copy of Access and Jet pointing to a shared database on a network drive.

Microsoft Access, Jet, and SQL Server

Access also allows you to point to SQL Server for your data storage. In this scenario, Access still uses Jet to run queries, store object definitions, manage temporary tables and hold security settings. However, all table data is stored in SQL Server.

Using Microsoft Access and SQL Server without Jet

In this scenario, the Jet engine is bypassed completely. Access 2000 and later has the ability to directly connect to SQL Server without the need for the Jet engine.

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.

Microsoft Access Upsizing Opportunities for SQL Server

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.

Schedule Microsoft Access Database Compact and RepairMonitor Your Microsoft Access Databases in Real-TimeIn 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

Microsoft Access Database LauncherThere 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.

Microsoft Access Database AnalyzerWhile 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:

Issue Microsoft Access Microsoft SQL Server
Data Being Viewed is Modified by Another User The new data is automatically updated on the screen. No keystrokes or code is necessary to reflect this. The old data remains on the screen until an explicit refresh (requery) is invoked.
Edit a Record that Someone Else is Editing A warning is issued that the record is currently being edited by someone else. Saves the user from wasting time editing a record that can't be saved. A warning is issued AFTER an attempt is made to save the record. May result in discarding all the modifications the user made. Developers can write code and tag records to implement a record locking mechanism, but this is not a built-in feature of SQL Server.
Multi-value Filter Feature This nice feature simplifies end-user filtering on datasheets and appears without the need for programming. It was introduced in Microsoft Access 2007 for Access tables:

Microsoft Access Field Multivalue Filtering

The multi-value selection filter is not available for SQL Server tables
Heterogeneous Joins Microsoft Access databases can link to tables from different sources (different Access databases, SQL Server tables, Oracle, tables, external files in other formats, etc.) and perform queries across them to generate results. When Access databases query data from a linked SQL Server table with data from another source, the performance optimization features of SQL Server are not utilized since all the table's data (or index) needs to be passed to Access for processing. Any expected performance gain for these types of queries are unlikely.
Cost Nothing extra to buy or host. The free Microsoft Access Runtime version is available for distributing your Access databases to users who don't have their own license of Microsoft Access. In addition to the hardware costs, SQL Server enterprise licenses are expensive if you want to host your own copy. A free SQL Server Express edition is available for relatively small databases (up to 10 GB).
VBA Support in Queries Microsoft Access queries support the use of VBA functions on data from the query fields. VBA is not supported by SQL Server. Access queries that use VBA functions cannot be run on SQL Server so all the requested data must be passed to Microsoft Access for processing. Translating these queries to Stored Procedures in SQL Server may be necessary for adequate performance.
User Defined Functions in Queries Microsoft Access queries support the use of functions defined in modules to process data passed to them. Queries can be standalone queries or SQL in form/report record source and the data source of combo boxes and list boxes on forms, reports and table fields. SQL Server cannot use functions defined in Access modules. The process needs to be redesigned, or those functions need to be translated to Stored Procedures on SQL Server. Depending on what those functions do and other objects they interact with, that may not be possible to replicate on SQL Server.
Tables without Primary Keys Microsoft Access supports tables with and without primary keys SQL Server requires tables to have primary keys in order to edit them. All tables should have primary keys anyway, so this should not be a limitation for upsizing, but there are often lookups that have unique text values (e.g. state lookups, zip codes, etc.).

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:

Architecture Description % of Databases
Already Right-sized Many Access databases do not need to be upsized; leave it in Access 85%
Upsize Data Only Leave the application and logic in Access, move the data to SQL Server and link the Access database to it 10%
Upsize Application with ADP Move the data to SQL Server and convert the Access application to an Access Data Project (ADP) 0%
Complete Replacement Treat the Access application as a prototype and completely replace it with a new solution on SQL Server 5%

If you were to inventory the use of Access in your organization, you would likely find hundreds to thousands of MDB databases scattered across computers and network drives. These databases run the gamut from simple lists built by staff members to workgroup-level multi-user applications.

With database counts that run into the hundreds, and given the potential cost and disruption to business that upsizing may involve, it is obvious that only a small subset of the total should be candidates for upsizing.

The first rule of upsizing is that the large majority of your databases should not be upsized: the cost is prohibitive. And even if you had the resources to upsize a majority of your Access databases, there would be no real gain. Simple lists or reports used by a single person typically do not fall into the realm of mission critical applications. Indeed, these types of applications are what Access is designed for and are well within its capabilities.

Finally, many of the databases you would find in a typical inventory process may not have been used for 6 months to a year. Other than for archival purposes, these obsolete databases are no longer important to your organization and are not candidates for upsizing.

The key advantage to this architecture is that you don’t have to do anything; no cost and no business disruption. The disadvantage is that Access/Jet based solutions cannot scale and do not enjoy the reliability and security of SQL Server. But that is typically not an issue for the majority of your Access databases.

Advantages Disadvantages
  • Cost: No additional software is needed since Jet is included with Access
  • Ease of use: No SQL Server knowledge required
  • Lowest development costs
  • Jet databases may have compatibility issues if new versions of Office, Access, Jet or data access components are installed
  • Limited scalability
  • Limited security
  • Limited reliability
  • Limited number of users

Because Microsoft Access has the ability to link to SQL Server for table data, migrating only the data is one of the best balances between cost and advantages. With this architecture, all table data is moved to SQL Server while all forms, reports, queries, macros and logic remain in the existing Access database. You may leave local Access tables to support local user selections that are not part of any queries that would occur on SQL Server.

The key benefit of this approach is that it is the quickest and most cost effective because it has the least impact on existing application logic. In most cases, existing objects continue to work. With a relatively small investment, you gain the reliability and maintenance benefits of SQL Server while retaining most of your existing MS Access investment.

Advantages Disadvantages
  • Data is located in SQL Server offering security, scalability, and reliability
  • Extensible for other programs to share the same data for web, mobile and other devices
  • Multiple copies of local Access databases still require synchronization
  • Local Access databases offer limited security, scalability and reliability
  • Since Jet is still used, local databases may encounter compatibility issues if new versions of Access, Jet or data access components are installed

Access Data Projects were introduced with Microsoft Access 2000 and discontinued in Microsoft Access 2013. Therefore, we do not recommend converting existing databases to ADPs since Microsoft no longer supports ADPs in future versions. The current recommendation is to use an ACCDB or MDB Jet database and link to the SQL Server database as described in Scenario 2. We provide this information on ADPs since you may need to maintain an existing ADP.

Access Data Projects from Microsoft Access 2000 through 2010, let you connect an ADP file directly to a SQL Server database and eliminated the use of the Jet database. Queries, forms, reports, macros, and modules were similar to an MDB/ACCDB database. There are no local tables since all the data is stored in SQL Server. Microsoft Access could be used to modify table structures on SQL Server and any changes to the SQL Server table structures were automatically reflected in the ADP. For ACCDB/MDB databases linked to SQL Server, the linked table needs to be relinked in order to see structural changes like new fields.

Problems with ADPs

Microsoft stopped supporting ADPs, because over time, it became clear it wasn't possible to support this architecture.

  • The SQL Server team released versions on their own schedule, while Microsoft Access was bound to the Microsoft Office release schedule
  • Because of the tight integration between ADPs and SQL Server databases, differences in versions and service packs caused problems
  • Module VBA code that manipulated tables needed to be converted from DAO to ADO.
  • The lack of local tables made it difficult for many tasks that are user specific
  • The inability to link to other data sources (ADPs are limited to just one SQL Server database) meant that many Microsoft Access solutions which combined data from multiple sources was not possible with ADPs

Sometimes, Microsoft Access applications are so successful that they outgrow the capabilities of Access. Microsoft Access may no longer be able to keep up with your organization’s needs for data capacity and performance. Or you may need to migrate all or part of an application to the web. Some Microsoft Access upsizing projects require a complete migration from Access.

In this scenario, the Microsoft Access application is used as the prototype for a completely new design. Alternative technologies such as Visual Studio .NET with SQL Server are used to completely rewrite the application. The data moves to SQL Server or other sources such as Oracle, MySQL and DB2 for a centrally managed server solution.

The key advantage of this approach is flexibility. You can create an application that can target Windows desktops, the Web, tablets, mobile devices, etc. while sharing the same data. A professional development environment such as Visual Studio .NET offers advantages such as managed code, team based management, source code control, and professional tools and components available from a rich array of third party vendors. With this scenario, you can create a reliable, scalable and manageable application that can move from the business unit to the enterprise level.

The key disadvantage of this approach is cost. Since you are ultimately discarding the Microsoft Access application and its database, you are creating a new application with a new design, development and implementation project. Fortunately, only a small majority of Access applications require this level of effort.

Advantages Disadvantages
  • Flexibility: application can target Windows, Web, and more
  • Scalability and reliability: using .NET development technologies with SQL Server offer the best mid-business and enterprise level return on investment
  • Ease of Management: Versions of Access no longer play any role in the application’s ability (or inability) to be used across the enterprise
  • Opportunity to redesign and create a more modern system that meets today's needs rather than adapting to past design decisions
  • Highest cost
  • Retraining of staff
  • New application testing
  • Additional developer expertise

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.

Microsoft Access Inventory CollectorAutomated 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.

Microsoft Access and SQL AzureOnce 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 Web SolutionsMicrosoft 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.

Choose Your Upsizing Scenario

Your level of planning and overall effort is directly related to which upsizing scenario you choose. For example, upsizing data to SQL server while leaving the Access front-end in place requires less effort, but yields fewer benefits. Once you have chosen your plan, be sure to clearly state goals, timeline and budget.

Administration

Before your upsizing project is deployed, you should have an administrative plan in place for your new SQL Server data. Planning for this before the rollout is key. Installing SQL Server and creating objects is only part of the equation. You should define backups schedules, fault tolerance parameters (as needed), and administrative staff who are responsible for the database component and disaster recovery.

Development Plan

Create a development plan that covers each aspect of the Access application that must be changed. If you are only planning to upsize the data to SQL Server, there are still parts of the Access front-end that may need to change. For example, the Jet database engine uses different data types and a different SQL grammar than does SQL Server. Plan to identify any areas of incompatibility and change Access objects as needed. If your scenario calls for a complete rewrite of the Access application in a different environment, such as .NET, you need to approach the project as full lifecycle software development effort and plan accordingly. Finally, be sure to identify risk areas such as data destabilization or loss that could potentially occur and have a proactive plan in place to address them.

Evaluate the Microsoft Upsizing Wizard

Microsoft provides an upsizing wizard that allows semi-automatic upsizing of Access to SQL Server. Unfortunately, this wizard is quite limited in its ability to create usable SQL Server-based applications. When you are contemplating an upsizing project, you can certainly plan to use the Microsoft Upsizing Wizard as a starting point. However, for all but the most simple (i.e. Scenario 2) upsizing projects, the upsizing wizard will only accomplish about 40% of the work. This section describes the limitations you can encounter with the Microsoft Upsizing Wizard.

Issue Description
Non-standard table/field names Jet and SQL use different naming standards. The upsizing wizard can find some, but not all. And those that it does find and rename will not work in any existing code.
Differences in SQL Access/Jet uses its own dialect of SQL that is different from the ANSI SQL supported by SQL Server. Many Jet-based queries cannot run on SQL Server without rewriting.
Data type conversion issues Access/Jet has its own standards for data types that are different in some cases from SQL Server. The upsizing wizard can make some choices for you in terms of converting data types, but changes require developer review.
Architectural Issues The Microsoft Upsizing Wizard cannot rewrite your application to work correctly with the SQL Server client/server model. Almost all Access/Jet applications are designed to work with the file share model of Jet. These designs do not lend themselves well to the client/server model and can result in poor performance.
Code Not Converted The upsizing wizard does not convert any of the VBA code in your application. This can result in serious errors as parts of your application point to SQL Server while your code still points to an Access/Jet database.
Items not Upsized The Microsoft Upsizing Wizard does not convert any of the following objects: hidden objects, security settings, Format and InputMask properties, Table/Field caption properties, table lookup fields, cross-tab queries, action queries that take parameters, many query properties, macros, and module code.

In general, consider using the Microsoft Upsizing Wizard as a starting point or for proof of concept phases. However, it cannot be relied on to actually upsize an application in the correct way.

Configure SQL Server

Use the data diagram that is part of your development plan to implement the first version of SQL Server objects such as tables, views and stored procedures. Implement users, groups and roles as needed. It is important to have these objects in place before development starts—developers can’t work against a SQL Server backend that isn’t there. Don’t worry about performance optimization yet, that happens later.

Development

Based on your development plan, staff your development team and provide the resources necessary. Make the existing Access application available to the team for use a benchmark or prototype resource. Keep an eye on the milestones and risk areas defined in your planning process.

Testing

Before the first test deployment of the new application, basic developer-based testing should occur. Use the existing Access application as a model to reduce the amount of time needed for the initial testing effort. Compare each functional area in the original Access application against the new code base. If you are completely rewriting the Access front end application as well as moving the data, you should plan to involve dedicated quality assurance/testing staff to find critical errors.

Documentation

Most Access applications are created by end users, and as such, lack documentation. Since you are investing in the process of upsizing, now is a good time to spend some time documenting the new application. At a minimum, create a configuration and troubleshooting document that outlines where the application’s component parts reside, desktop and network settings and basic troubleshooting techniques based on the results of your testing plan. If you have the resources, you may want to consider more complete documentation in the form of data diagrams, flowcharts, code listings, etc.

Training

When you take an existing in-production application and change or rewrite it, you must plan to ensure that the application’s users are on board. Depending on the scope of the changes involved in the upsizing project, training for the application’s users may involve a few hours of walkthroughs to a full formal training regimen with the associated training guides and documentation. Good training is crucial if you want to get the buy-in of the application’s users.

Rollout

Your first rollout of the application is typically deployed to a subset of the entire user population. Select a small group of users and employ them as the beta testers. The obvious goal is to verify the planning and development work—does the new application work correctly? Beyond that, user feedback may help identify any last minute issues not addressed in the planning and implementation process. Users can also provide invaluable information regarding usability.

Once you have been through initial testing and made any necessary changes or fixes, roll the application out to the entire user base. Depending on the number of users in the application and the importance and currency of the data, you may want to consider running the old Access-based system in tandem with the new system for a period of time. This provides an extra degree of security should the new application experience problems.

Once the new application is in production use for all users, the project enters the stabilization period. Defects are identified by users and fixes are planned. Users will also see opportunities for new functionality (as is the case with any application) and these should be duly noted by management. Ongoing support to users is important since an upsizing project often results in application attributes that are no longer under the control of the end user (i.e. SQL Server).

During this period, you should also monitor performance, not only in terms of what users may be reporting as slow, but active monitoring of SQL Server using tools such as the query analyzer and performance counters.

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.

Free Product Catalog from FMS