FMS Site Search

When and How to Upsize Microsoft Access Databases to SQL Server

Background

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.

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.

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

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.

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.

Database Passwords: Assign a password to a database. Only users who know the password can open the database.

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.

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:

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:

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

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

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

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

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.

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.