Making MS Access Available Online
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.
Understanding MS Access Database Limitations
MS Access was built to support small group, 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:
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.
MS Access Online Workarounds
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
- Placing the MS Access application on a shared network drive
- 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 is using the MS Access replication & synchronization feature. In our experience, this approach to supporting multi-user, multi-site needs always ends the same: MS Access becomes corrupted, 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!
Practical Differences: MS Access, MSDE, MS SQL Server
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 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)
MS Access Application With SQL Server Database
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. Oftentimes, converting Access to SQL Server will make many of your problems simply go away.
The Real Cost Of Staying With MS Access
Many people think that developing applications in MS Access is less expensive than alternatives like MSDE or MS SQL Server. Our experience at PCA speaks to the contrary: developing custom applications with MS Access 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.