HomeServicesTechnologyAnalyticsOur ClientsAbout PCAContact Us

Migrating MS Access to SQL Server

Most companies eventually out-grow the built-in limitations of MS Access — signs can include: slow performance, error messages, unstable application, security issues, and data corruption and loss.   Adding more forms, more reports, additional users and more data into MS Access will place additional burdens on MS Access, and accelerate shorten its lifespan.  If you need to continually enhance MS Access to keep pace with business growth needs, it's only a matter of time when it becomes imperative to migrate the MS Access database to SQL Server.  And the longer you delay this decision, the more expensive and riskier this becomes.

MS Access Limitations

MS Access has built-in technical limitations that cannot be overcome with workarounds or special programming techniques — limitations that can only be overcome by converting your MS Access to SQL Server.  The following chart will help you determine when you can expect to run into issues with MS Access i.e. slow performance, corrupt database, application crashing, etc, and when you know it is time to convert MS Access to SQL Server:
  OK CAUTION DANGER
Concurrent LAN Users < 5 5-10 > 10
Internet Users 0 3 > 3
Size of Datastore < 100 MB < 300 MB > 300 MB
Application Complexity Simple Moderate Complex
     

Convert MS Access to SQL Server

Microsoft's flagship relational database SQL Server 2014 is designed to handle the data administration and rigorous workload balancing requirements of more demanding business applications.  Converting MS Access to SQL Server can be challenging or fairly straightforward, depending upon a number of factors.   Over the years we have developed a very efficient "recipe" for migrating MS Access to SQL Server — a proven approach that is cost-effective, and ensures the highest protection of your existing business data.
MS Access to SQL Server migration

Avoid the MS SQL Server Migration Trap

A common mistake is attempting to re-write the entire system (back-end Database and front-end MS Access application).    This is unnecessary, and the most costly and risky approach that inexperienced developers often pursue.    The correct approach is to follow a phased-approach, addressing very specific areas of the system, and in the right order.   Attempting to take on too much at one time, or doing things out of order will only add unnecessary cost and risk to the effort.    In most cases, the MS Access user interface can simply be reconnected to a SQL Server database.

MS Access Upsize Wizard — Garbage In, Garbage Out

The MS Access Upsize Wizard is often the MOST EXPENSIVE path to SQL Server.    In most cases, the resulting SQL Server database design needs a much more work, than if you simply created a new SQL Server database from scratch.    SQL Server is a completely different and far more sophisticated database engine than MS Access, and the MS Access Upsizing Wizard does a very poor job of creating a proper SQL Server database design.   Ask anyone who has attempted to use the MS Access Upsize Wizard — and they will tell you it just created much more work for them in the long-run.

The Hidden Cost of MS Access

Many professionals believe that developing applications in MS Access is less expensive than alternatives like .NET and MS SQL Server.    While MS Access is certainly easier to understand and use, in the wrong hands MS Access only adds many unnecessary development, maintenance and support costs to your business; costs that are difficult to track, and add up over time.
In our experience — from developing hundreds of custom database solutions with MS Access, MS SQL Server and .NET — developing custom solutions with MS Access is MOST EXPENSIVE approach.   With MS Access projects, we tend to spend far more time on 'defensive engineering' to overcome MS Access limitations, compatibility issues, User Interfaces constraints, and difficulties associated with deploying and controlling MS Access in a distributed business environment.  And after you have invested all this the extra time and effort, the system does not scale, and you will eventually end up writing off your entire development expense.
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 more scalable.   Time is money, so applications that are built on the right components end up costing you MUCH LESS in the long run.   MS Access is not the lowest cost/cheapest solution, just because it ships free with every copy of Office Professional.