Data Management optimize

10 reasons to turn your Access applications into Web-based applications

When an Access database outgrows its original purpose, you face applying band-aid technology or upgrading to a more powerful database system. But before you toss Access out the window and start signing purchase orders for consultants, developers, licensing, and new hardware, consider one more option -- turning your Access application into a Web-based application. Here are a few reasons why this might make sense.

An Access database often outgrows its original purpose. When that happens, you face applying band-aid technology or upgrading to a more powerful database system, such as SQL Server Express or even SQL Server. But before you toss Access out the window and start signing purchase orders for consultants, developers, licensing, and new hardware, consider one more option -- turning your Access application into a Web-based application. Let's look at some reasons why this might make sense.

Shameless disclaimer: If you truly need a more powerful database system and can afford its trappings, spend and grow!

Note: This information is also available as a PDF download.

#1: Client versus server

A server-side database, such as MySQL, SQL Server, and Oracle, evaluates requests on the server side (sent in the form of a SQL statement) and then returns data to the client. Jet, on the other hand, lets the client do all the work. Jet is the database engine behind Access. Even if the database (.mdb) is on a network server, the client still does all the work. The server simply responds to client file requests.

This arrangement retrieves more then just the data across the network. As a result, indexes and unused data clog the network and slow things down. An alternative is to place the Access database on your Web server's local drive and then build the interface on the Web server. Doing so creates an ad hoc server-side database that handles transactions on the server (using your code). Requests from the client are in Hyper Text Transfer Protocol (HTTP) format instead of SQL.

Recommendation: Put the Access database (the .mdb file) in a folder that isn't shared. That way, users won't have direct access to the database. Their only access will be via the Web server. Your code will serve as the layer that allows users to interact with the actual data.

#2: No client installation

A Web-based front end minimizes installation issues. Users need only a browser. The database doesn't care whether the user is sending requests via a Windows PC, a Mac, or a machine running Linux.

#3: Easy cross-platform usage

You're free to use your language of choice to create the Web interface and the code that the server users to interact with the database. Users get clean and standard HTML that almost all browsers can use.

Recommendation: Keep the Web interface simple to ensure that everyone can use it. If you need the advantages of client-side tools, such as client side scripting, Flash media, and so on, go for it. Just keep in mind that not every HTML feature works in every browser. The back end can be as complex as necessary because the Web server is the only one using it.

#4: Simplified security

Storing the database in a non-shared folder (see #1) restricts access. Only the Web server's administrator has access to the database file. That leaves security to the Web server. Now, you might argue either way as to whether this method is more or less secure than a server-side database. However, someone with direct access to a machine with a server-side database could probably also gain direct access to that database.

In addition, a server-side database requires a network connection. An Access database on a Web server isn't directly available. You can access the Web server, but not the database. Only the Web server can access the database on the server's local drive. On the other hand, Access has a security system known as Access User Level security (this isn't available with Access 2007). Most server-side database security systems are more secure than Access User Level security.

Recommendation: Even though you impose an almost absolute-type form of security by placing a database on a Web server, it can't hurt to apply Access User Level security. The database is still an .mdb that you can copy and open on any machine that has Access installed. As a developer, you will probably have local copies of that .mdb (and copies on backup tapes for your Web server). Be on the safe side and keep honest people honest by putting a little extra security in place. Users via the Web interface won't even know the additional security is there.

#5: Easy use of NT authentication

Using Visual Basic for Applications (VBA), you can determine the NT name of users logged into an Access database and thereby restrict which users can do what. However, this method isn't foolproof, and it doesn't truly authenticate users. Your Web interface (on an IIS Web server) can use Integrated Windows Security to authenticate user credentials to individual web pages.

#6: Goodbye to corruption!

Most developers complain that Access is susceptible to corruption. Used incorrectly, it certainly is. With an Uninterruptible Power Source (UPS) and redundant drives, your Web-based database (.mdb file) won't suffer from corruption.

#7: No version problems

With the quick pace of upgrades, many of us have users spread across two and three versions of Access. Unfortunately, not all versions play well together. A Web interface eliminates version incompatibility issues because the Web server uses Jet. That means the Web server doesn't even need Access -- it doesn't load Access. Your Web server doesn't care what version of Access the client uses.

#8: Live, behind-the-scenes interface updates

To update an Access front end, you must copy or modify an .mdb file. Access won't let you make changes while people are using it. (Beginning with Access 2000, you can make some changes, but a few still require exclusive access to the database.) In contrast, you can change the Web interface files (.asp, .aspx, and so on) whenever you like. The changes are almost immediate.

#9: Portability

Every Windows OS since Windows 98 has had personal Web server capabilities. That means you can develop and test a Web site using a laptop running Windows 98 (or later). Using an Access database as the data source has a few benefits:

  • There's no need to install and run a heavy-duty server-side database on your laptop.
  • There's no need to maintain a network connection to a live server.
  • You can copy the live system and its database as just a bunch of files. You don't have to import, export, or attach database files. For example, you can build a Web site on your laptop or desktop and then move it to a Web server. To work on an update, simply copy the Access database file (.mdb) from the Web server to your laptop.

Recommendation: Jet allows many transaction type SQL statements. You can build and modify tables and views using SQL, along with the typical data reading and altering capabilities. Sometimes, if you put a system on a remote server where you no longer have the ability to get to the actual .mdb, it's pretty simple to whip up an .asp page that lets you run SQL on the fly against the database.

#10: More users

By their very nature, Web interfaces are unbound. In other words, once a page is loaded, the interface is no longer connected to the database. But a bound Access front end maintains a connection to the source, and Jet limits you to 255 concurrent connections. Your Web application, unless you have 255 users hitting the database at the exact same moment (which would require approximately 30,000 users a minute at a transactions speed of .5 seconds) can have more concurrent users.


Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.

Drew Wutka is a Microsoft Access/Visual Basic/Web developer for Marlow Industries, Inc. He also does independent contract development and has developed many free projects, such as the Microsoft Access MiniCalendar, the Dynamic FrontPage Navigation ASP Sitemap, and the Password Enabled Enigma Encryption VB program. You can reach Drew at dwutka@marlow.com .

18 comments
Ray_Walsh01
Ray_Walsh01

Am looing at developing MS Access application but would ideally like it web based. Is this possible and if so how? What about front ending MS Access with Visual Basic - how difficult is this?

vic53
vic53

Concordo perfettamente con tutto quello che hai scritto. L'obiettivo finale di Access era proprio questo, venire utilizzato su server web dalla applicazione web. E' piu veloce, pi? semplice e pi? manutenibile ma questo i veri sviluppatori lo sanno... (non tutti per?...) from a microsoft technet systemist by Italia ciao manarolla.vincenzo@euro1.it ?

simon.bates
simon.bates

This is an interesting article, but I am left unsure about how exactly you would do this at a practical level. What tools are there to provide the web server level that would sit on the server? Is it possible to give some specifics for an example system?

Jaqui
Jaqui

see any reason to use Access at all. MySQL and PostgreSQL are both much easier to use, scale to handle millions of simultaneous transactions, and are free. Why should I spend money getting Access when I have far more capable tools already?

anthony.b
anthony.b

I work for a government agency, that has about 10,000 employees, most of which have a PC that is hooked up to a large network, that reaches out about 500 miles in distance. It seems to me, that at my agency, there is no way to ensure that a shared MS Access database will not get corrupted, considering that there are too many factors that are out of my control (I have no control over people's PC's, or the network routers, network connections or servers). What we are doing at my agency, is trying to avoid MS Access for medium to large sized shared databases, and instead we are using a web front-end on a ColdFusion server, and putting the back-end data onto a server that uses DB2. At my agency, a few of the MS Access databases have gotten so corrupt, that there is no way to fix them, and yet still, more and more MS Access databases are being put onto the servers and shared (perhaps this is because there are not enough programmers available who know db2 and ColdFusion). At my agency, the "standard" for medium to large databases is DB2, and this is why we are not having the ColdFusion point to an Access database. We have other font-end platforms at my agency (such as Java, .net, and others), but the ColdFusion is very popular at my agency, since it is already in wide use here, and it is easier to learn and use than Java or .net. I would like to add that, at my agency, MS Access is a great application for small stand-alone databases that are kept on the c-drive of a PC, and are backed up properly, and I personally have several Access databases that I use in this way (both at home and at work). However, I think that many people don't understand the limitations of MS Access, and get into trouble when their shared MS Access database gets corrupted. Thanks for your articles "10 reasons to turn your Access applications into Web-based applications" and "10 ways to prevent Access database corruption", as this has been some very interesting, helpful, and informative discussion on these subjects.

richardrabins
richardrabins

If you would like a phone/web demo of a fast web 2.0 (ajax enabled) app that can be created in a lot less time (and less hassle) than DOT Net - email me at richard@alphasoftware.com and we can set that up. Richard Rabins

NateLANman
NateLANman

We are encountering this exact problem at work and would like to port it to the web. Currently, we are wondering which web language to write the front end in? Does anyone have any formal experience or good recommendations. Currently the database only has a few forms but would need the ability to generate reports (which are currently done through macros) to create a word document. Any suggestions would be great!

richardrabins
richardrabins

I happen to agree with the authors, but they laid out the case extremely well. Full Disclosure: we make a product - Alpha Five v9 Platinum - www.alphasoftware.com - that is designed to let you build custom ajax enabled web apps against any SQL backend (including Access) in much less time than in PHP or RUBY or Java or Dot Net. You can download on our site and the first 5 people who email me at richard@alphasoftware.com will get a no charge full copy thanks richard rabins www.alphasoftware.com

alexdyb
alexdyb

You can easy switch access to work with SQL server, it much more easy then converting to web app. for #2 and #3 terminal server solutions works fine

cactus
cactus

Great thing to point out. One upcoming limitation though, is that JET is for 32 bit only. This means that on a 64 bit OS both JET and the web server (IIS) must run in the same 32 bit subsystem. /gustav

b.krisanski
b.krisanski

Let me start this off by saying that I agree that I don't think access should be used for business critical applications... However, as a lot of businesses already have MS Office Pro, they already have a copy of Access, so installing it is not another financial expense (as they already have the licence)... hence, for the less tech savy people within the business that are already very familiar with the look and feel of other Office applications, installing and using Access to perform a task, is far easier to them than to try and download and setup MySQL etc. Plus I'd be willing to wager that most non-it people within any business would more than likely not even have heard of MySQL and PostgreSQL, let alone know how to get it up and running!! My 2 cents worth...

ssharkins
ssharkins

I agree -- use the tools of your choice and the best tool for the job that's available. That's exactly as it should be.

ssharkins
ssharkins

It sounds like you guys have everything under control.

jhutto
jhutto

Great points. And I would agree with most, but then you have to learn a whole new environment. I've starting trying to learn Dot Net but the learning curve is quite high. Is there something that is easier? Why doesn't Microsoft create a web environment for all the access developers. Give us an environment that we are familiar with a web output.

jw
jw

Upgrading Access db to SQL server is not (or at least wasn't) entirely simple whilst the web server solution is trouble free, popular with users and secure. No corruption fears, no client to install/maintain, GUI changes without ever touching mdb. We had 500 users using Access across the web and no problems (the mdb only sees one).

dwutka
dwutka

SQL Server isn't cheap, for one. A web server both hardware and software is a fraction of the cost of just the SQL Software. Plus, there are many advantages (listed in the article) for the advantages of using a web interface from your Front End.

mabingle
mabingle

What I see so far are a lot of opinions... and nothing more. So, let's look at this logically: MS Access has been around for a very long time. Some companies I have worked for had tens of thousands of MDBs. IT spent $$$$$$$ trying to eliminate them, but always failed. Access is a RAD environment. It allows a novice to create simple methods of getting data that they need quickly. It also provides the experienced developer with a solid environment, that if maintained properly, will surpass everyone's expectations. SQL Sever is in many places already, so splitting an access BE into SQL Server is easy and inexpensive. I also use Access to clean up data and do a lot of data analysis so our legacy replacement projects work well... and it does it very well. So, all in all, Access isn't going away. Actually, it's growing as I write this little note. What we should be seeking is a stronger Access that is web enabled, and breaks the 2 gig barrier with a real DBMS of its own. I use a number of development tools. However, when I need to create something quickly i revert to Access. It's also great for creating POCs. My 2 cents. Mike