When and How to Upsize Microsoft Access Databases to SQL Server
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
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.
Value of Microsoft Access in Your
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
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
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
within an Organization's Overall Database Strategy.
Microsoft Access and SQL Database Architectures
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
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
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
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:
||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
|Maximum Concurrent Users
||Basic desktop security, limited by Windows
||Robust enterprise level security, users cannot
see or copy the database directly
||Depends on local PC and network performance; may
be very fast for "small" databases
||Based on server hardware performance
||Reliable to the extent of the PC's reliability;
requires periodic database compact and repair
||Enterprise quality reliability; automated
||All data changes may be logged
||None for MDB databases.
For ACCDB, Access 2010 introduced Data Macros which can be similar to
|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.
Making the Decision to Upsize
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.
Microsoft Access offers three different security mechanisms.
- Database Passwords: Assign a password to a database. Only users who know the password can open the
- 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.
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.
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
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
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 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
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
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
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
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
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.
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 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
- 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.
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
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
Downside of Upsizing to SQL Server
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:
|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
||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.
||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
|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
||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:
||Many Access databases do not need to be upsized; leave
it in Access
|Upsize Data Only
||Leave the application and logic in Access, move the
data to SQL Server and link the Access database to it
|Upsize Application with ADP
||Move the data to SQL Server and convert the Access
application to an Access Data Project (ADP)
||Treat the Access application as a prototype and
completely replace it with a new solution on SQL Server
Architecture 1: Already Right-sized
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
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.
- Cost: No additional
software is needed since Jet is included with Access
- Ease of use: No SQL Server
- 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
Architecture 2: Upsize Data Only
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
- 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
Architecture 3: Upsize Application with Access Data Projects (ADP)
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
- 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
Architecture 4: Complete Replacement
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.
- 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
Creating an Inventory of Microsoft Access Databases in Your Organization
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
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.
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
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.
Choosing a SQL
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
starting at $10 a month per database. Many ISPs also
offer SQL Server databases as part of their hosting
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.
Access 2013 Web Solutions
Microsoft Access 2013 introduced Access Web
which is a way to create Access databases on SharePoint
2013 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
- 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
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.
Planning an Upsizing Project
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.
Phase 1: Design and Planning
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.
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
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
|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
|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
|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
||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
|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.
Phase 2: Implementation
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.
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.
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
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.
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.
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.
Phase 3: Stabilization and Optimization
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
(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