You have invested years in a highly-customized MS Access database to support your unique business needs — and now you want to put the system on the Internet, so employees can use it from home or on the road, or from other office locations?

For starters, MS Access was designed to support small groups of users on a LAN, but not for distributed use online or over the web.    There are several work-arounds, but when you exceed the built-in limitations of MS Access (including making Access data available via online), it is important to understand that you will very likely experience problems using an Access database on the web, which can include: slow application or online access database performance, corrupt data, application crashing, strange error messages, and even database security issues.

MS Access was built to support small groups e.g. 2-10 concurrent users over a LAN, not for using MS Access over the web.  MS Access is limited in its database capacity, and the more complex the database application gets, the fewer the number of users and the smaller the amount of data your database can store before running into real performance and reliability issues.  Expect to run into typical MS Access problems if you exceed any single criteria below:

		OK	CAUTION	PROBLEMS
	Concurrent LAN Users	<5	5-10	>10
	Online Access Users	0	3	>3
	Size of Database	<100 MB	100-300 MB	>300 MB
	Application Complexity	Simple	Moderate	Complex

When you push beyond the built-in limitations of MS Access, it becomes simply a matter of "when" not "if" you start running into real problems.  This is especially true if the MS Access application is not designed or coded to professional database engineering standards and MS Access best practices.

Several methods are available to allow remote, online users to utilize your MS Access application online.  If the application is reasonably well engineered, and you have just one or two users who need to access the application intermittently, then you are probably OK with any of these online access workarounds  Probably the lowest risk approach includes: 1) placing the MS Access application on a shared network drive; and 2) using any of the following remote access solutions: Terminal Services, Citrix, or GoToMeeting (there are others, but these are the most popular).

A path that you want to AVOID GOING DOWN is using the MS Access replication & synchronization feature.  In our experience (and we are not alone), this approach to supporting multi-user, multi-site needs always ends the same: MS Access becomes corrupted (it no longer functions), and oftentimes the data also becomes corrupt and/or lost.

The technical limitations of the MS Access platform simply cannot be overcome — in any sustainable way that is — with workarounds or special programming techniques.   While you may be able to temporarily 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 best suited to your business needs (lowest hassle, biggest benefit for your investment) include:

• Mission Criticality of the Business Application
• Location and End Users (LAN vs. Online Access)
• 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, HIPAA)

If you have already outgrown your MS Access Application, or you need to build a more scalable MS Access application, using MS Access in combination with a SQL Server database can make good sense. If Internet access is a requirement, a SQL Server database 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). Oftentimes, converting Access to SQL Server will make many of your problems simply go away.

SQL Express is the lighter-weight, free version of the SQL Server datbase. If your database is exposed online 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 a Microsoft Licensing requirement. In addition, SQL Express has a built-in "performance governor" on Process Queues that limit the number of simultaneous End Users to 8 concurrent (active) sessions SQL Express 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 online is a must have, then MS SQL Server is probably the better option.

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.

Learn more about the best way to convert Access to SQL so you can distribute your application over the Web.