There has been a tremendous surge in interest in putting databases on the web in recent years. This has been spurred in part because of the dramatic decrease in deployment costs when contrasted to conventional “fat client” front-end applications such as Visual Basic. And of course, browsing web pages can be both simpler and more esthetically pleasing.
In keeping with this trend many organizations have begun to place Microsoft Access databases on the web. While of great benefit to the users this does not come without some complexity for the Access developer. The purpose of this article is to present a simple outline of one common approach using Active Server Page technology. It focuses on web pages that will be directly used to retrieve and manipulate the data. It assumes the existence of the typical static HTML pages.
You will be able to download the Access starter database, library2, at
box.net once we have it uploaded.
Three Tier Architecture
Web databases are commonly deployed into a three-tier-architecture as shown below. In a Microsoft environment the web server and database servers would typically be separate as shown, although this is not mandatory. An Access database, especially, might reside directly on the web server. In either case, a System ODBC connection to the database is set up on the web server. This connection will be used by the Active Server Pages to access the database base.
The web pages used to access the database will reside on the web server, typically in their own folder within the \inetpub\wwwroot directory. IIS would most often be used as the web server although Apache with ChiliSoft extensions could be used. If the database is placed on the web server, it should be placed outside the server root for security reasons.
The client machine does not need any special software or configuration other than a modern web browser such as Internet Explorer. This is the beauty of this arrangement.
To implement a database driven website within the above architecture several components will be discussed.
The database itself
The web server
Technologies used by the web site data driven web pages
Active Server Pages
Other than ensuring the clients have modern browsers, there are no special client-side considerations.
The article concludes with a section on putting the web page pieces together into a working web site.
The Access database can be simply uploaded to the appropriate server using any standard Ftp program. As mentioned above, if placed on the web server it should reside in a folder outside the server root. Maybe \inetpub\databases. The server’s web master should have more details about this and setting up security.
Of course, Access itself should be installed on the machine that will be running Access.
The Web Server
Once the Access database is uploaded, an ODBC connection to it needs to be created. This connection must be on the web server machine. Typically, these are System connections, not File or User.
On a Windows server, the ODBC connections can be set up and administered through the ODBC Administrator reached through the Control Panel. No special software or drivers should be needed.
The web server needs to have a pre-processor to interpret a special type of web page called an Active Server Page. (More about these later). In a Microsoft environment the web server would run IIS that includes this capability automatically. If other web servers are used, then special steps may have to be taken. Apache, for example, requires extensions from ChiliSoft.
In a Microsoft environment the server can be accessed as an object. I.e. the programmer can request the server to execute methods, some of which may create other objects. More about this later.