Have You Outgrown MS Access?
It is quite common for companies to out grow MS Access due to the inherent limitations of the MS Access
"jet engine" datastore: slow performance, error messages,
unstable application, and data corruption are common
signals that you are near (or have already exceeded) the built-in limitations of
MS Access.
Problems associated with the limitations of MS Access become
all too apparent when you need to scale the application to support more features,
more data, and more users. This is when integrating your MS
Access with a SQL Server back-end database is the logical solution.
MS Access Limitations
MS Access has several important technical limitations that cannot be overcome with
workarounds or special programming techniques. Use the following chart to determine
when you can expect to run into problems i.e. slow performance, corrupt database, application crashing, etc:
|
|
OK |
CAUTION |
STOP |
|
Concurrent LAN Users |
<5 |
5-10 |
>10 |
|
Internet Users |
0 |
3 |
>3 |
|
Size of Datastore |
<100 MB |
100-300MB |
>300MBs |
|
Application Complexity |
Simple |
Moderate |
Complex |
|
|
|
|
|
Integrating MS Access with SQL Server
Microsoft's flagship relational database product SQL Server 2012 is designed to
handle the data administration and rigorous workload balancing requirements
of more demanding business applications. Integrating MS Access with SQL Server can be challenging
or fairly straightforward, depending upon the quality and completeness of your existing MS Access
table structures.
Upsizing Wizard: Garbage In, Garbage Out
The Upsizing Wizard will move MS Access
to SQL Server, but in most cases, the resulting SQL Server database design
needs a lot of work, and often we find that designing a SQL Server database from
scratch is the more efficient and cost-effective approach. The
quality and completeness of your MS Access application determines whether using
the Upsizing Wizard makes sense, or whether the Upsizing Wizard will create more
work for you to do in the long-run.
The Real Cost of Staying with MS Access
Many businesses believe that developing applications in MS Access is less expensive than
alternatives e.g. MSDE or MS SQL Server. In our experience at PCA — from developing
literally hundreds of custom business applications on MS Access, MSDE and MS SQL
Server — developing custom applications with MS Access CAN
BE SIGNIFICANTLY MORE EXPENSIVE than developing business applications with MS SQL Server
and .NET. With MS Access projects, we tend to spend more time and effort on 'defensive
engineering' due to limitations, compatibility issues, User Interfaces constraints,
and difficulties associated with deploying and controling MS Access in a
distributed business environment. And after you have invested the extra time and effort to
overcome these limitations, it is all a sunk cost: the MS Access platform does not scale.
PCA engineers spend on average one-third less time developing custom applications on
on MS SQL Server and .NET than with MS Access. The results are far better, and the application
is more scalable (has a longer shelf-life). Since time is money, applications that
are built on the right components end up costing our Clients LESS, and they get
MORE! Our advise to customers: stop looking at MS Access as the lowest cost/cheapest
solution, just because it ships with every copy of Office Professional. It simply
is not true.