It is quite common for companies to out grow MS Access: slow performance, error messages,
unstable application, security issues, and data corruption are common
symptoms 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 converting your MS
Access to SQL Server makes sense.
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:
Microsoft's flagship relational database product SQL Server 2008 is designed to
handle the data administration and rigorous workload balancing requirements
of more demanding business applications. Migrating MS Access to SQL Server can challenging
or fairly straightforward, depending upon the quality and completeness of your existing MS Access
application design.
Click Here for More...How to Avoid the MS SQL
Server Migration Trap | Using the Upsizing Wizard: Garbage In, Garbage Out | The Real Cost of MS Access,...
Avoid the MS SQL Server Migration Trap
Usually, it is not necessary to completely re-write the entire system (back-end
Database and front-end MS Access application). This is an unnecessary and more costly
change that inexperienced developers often pursue. PCA can help you avoid complete
rewrites by carefully analyzing and planning throughout the application development
process itself, a process that PCA has lots of experience with, and does not take
lightly. We feel that the process of understanding your business needs is an integral
component of going with the right solution, and this is a goal we set for all of
our projects. For some systems, the MS Access user interface is simply pointed to
a new SQL Server database, which is dedicated to safe-guarding and distributing
the business/database information as needed.
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.