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.