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 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.
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: 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!
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 (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)
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
(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 Server vs. SQL Express
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.
The Real Cost of Staying with MS Access
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.