It's quite common to have developed a highly-customized MS Access application to support your business data management needs — and now employees want to use the application from home, or on the road from a laptop, or maybe you have other office locations or want to make certain information available to customers over the Internet.  It is important to understand that MS Access was designed to support small workgroups on a LAN, and when you exceed the built-in technical limitations of MS Access (including making it available via the Internet), you will very likely experience problems, which can include: slow performance, corrupt data, application crashing, strange error messages, and/or data security issues.

MS Access was built to support small groups e.g. 2-10 users over a LAN, or a few users over the Internet.  MS Access is also limited in its datastore capacity, and the more complex the application gets, the fewer the number of users and amount data you can store before running into real problems.  You can expect to run into typical MS Access problems if you exceed any single criteria below:

		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

When you push beyond the built-in limitations of MS Access, it is simply a matter of "when" not "if" you start running into problems.  This is especially true if you are using a remote access application (e.g. Terminal Services, Citrix, etc.) or MS Access replication & synchronization to support multi-user, multi-site needs.

The technical limitations of MS Access cannot be overcome with workarounds or special programming techniques.   While you may be able to incrementally extend the shelf-life of your MS Access application, you will eventually end up investing more and more time fixing, repairing and supporting the application, to the point that the cost to maintain the application may exceed the business value of the application itself!

There are significant differences in scale and performance among the various database Servers and Clients available on the Microsoft Business Platform.  Principal considerations for deciding which combination is the best for your business (lowest hassle, biggest benefit for your investment) include:

• Mission Criticality of the Business Application
• Location and End Users (LAN vs. Internet)
• Frequency of Use (Moderate vs. Heavy)
• Number of End Users (5 users vs. Enterprise-wide deployment)
• Business Product/Process Complexity (and associated Database Complexity)
• Static vs. Dynamic Content (Read vs. Read-Write)
• Auditability, Industry Compliance (e.g. SarbOx, HIPPA)

If you have already "hit the wall" with your MS Access Application, or you need a Custom MS Access application designed & developed from scratch, SQL Server is the next upgrade on the Microsoft server path.   If Internet access is a requirement, SQL Server is your only option.  Provided your MS Access application is well designed (you are not simply migrating a poor design from one database to another), migrating to SQL Server will make all of your problems simply go away.

MSDE (also called "SQL Express") also has its limitations. For example, if your database is exposed to the Internet thru anything other than the MS Access interface or Active Server Pages (ASP), you must purchase (or already own) a SQL Server License from Microsoft. This is purely a Microsoft Licensing issue. In addition, MSDE has a built-in "performance governor" on Process Queues that limit the number of simultaneous End Users to 8 concurrent (active) sessions. MSDE is also slightly more difficult to develop on, and more difficult/expensive to install and configure vs. MS SQL Server. If supporting two dozen or more end users over the Internet is a hard requirement, then MS SQL Server is the right choice.

Many people think that developing applications in MS Access is less expensive than alternatives e.g. MSDE or MS SQL Server. Our experience at PCA — from developing literally hundreds of custom business applications on MS Access, MSDE and MS SQL Server — speaks to the contrary: developing custom applications with MS Access CAN BE AND USUALLY IS SIGNIFICANTLY MORE EXPENSIVE than developing business applications with MSDE or MS SQL Server. We tend to spend more time, more effort, and run into more headaches with MS Access. MS Access is significantly more difficult to deploy and control as a distributed business application without driving up IT support costs. And even if you have invested the extra time and effort to overcome the limitations, the MS Access platform does not scale.

PCA developers probably spend around one-third less time developing custom applications on the MSDE and MS SQL Server platforms. The results are far superior, 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 money over time. Our advise: stop looking at MS Access as the lowest cost/cheapest solution, just because it ships with every copy of Windows Professional. This simply is not true.