Lecture

Lecture - DBM/405 Flexnet - Week 4

Putting an Access Database on the Web

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 lecture is to present a simple outline 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.

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.

Components

To implement a database driven website within the above architecture several components will be discussed.

1. The database itself

2. The web server

3. Technologies used by the web site data driven web pages

a. HTML forms

b. Active Server Pages

c. VBScript highlights

d. ADO

Other than ensuring the clients have modern browsers, there are no special client-side considerations.

The lecture concludes with a section on putting the web page pieces together into a working web site.

The Database

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.

HTML Forms

In a web environment, users input data via HTML forms. The important point here is that HTML forms have “controls” similar to Visual Basic or Delphi. Server-side scripts or CGI programs can access the values in these controls.

Active Server Pages

Active Server Pages are special web pages with an .asp extension. Pages with these extensions will be handed over by IIS to the asp.dll (the Active Server processor, search in \WINNT and you will see it) for preprocessing. These pages have scripts embedded within the normal HTML tags. These scripts are enclosed within special <%…%> tags. When the pre-processor sees these tags it will interpret the code. The code is most often written in VBScript but it could be written in a variety of other scripting languages such as JavaScript or PerlScript.

The result of the pre-processing is an expanded HTML page that is returned to the web server itself.

VBScript

VBScript is a simplified version of Visual Basic designed for scripting environments. It is based on the original BASIC language that was designed for beginning programmers. It is thus relatively user-friendly and easy to learn.

In looking at code samples there are a couple points worth highlighting.

1. a = b is a simple assignment of the value of b to the value of a.

2. set a = o causes a to refer to the object o.

3. Objects have methods, which are pieces of code that execute an action.

4. o.doit causes the object o to execute the method doit.

5. The dim statement is used to declare a variable (reserve memory space for it)

ADO

ADO stands for the ActiveX Data Object. This object is the workhorse in manipulating the Access data. It is used to retrieve, update, and store the data based on user inputs from HTML forms. It has various methods and collection objects. In this paper we will be concerned with only a few. However, this should be sufficient to convey some idea of the power of ASP technology.

Putting the web page pieces together

In addition to the normal HTML pages, ASP pages as described above are used to retrieve the data from the Access database. These web pages are placed on the web server in the directory reserved for the web site. VBScript is added to these pages to open a connection to the database and manipulate it. The basic steps are:

1. Execute the server createobject method to create an ADO object

2. Execute the ADO open method to open the ODBC connection

3. Execute the ADO execute method to create a recordset collection object

4. Execute various ADO methods to manipulate or access data in the recordset

5. Close the connection and release the variables (not shown)

The following VBScript code was embedded in an ASP page called authors.asp at http://www.dhdursonthosting.com/css417-demo/index.htm . It retrieves all the authors from an author’s table in a library database. Click on the Plain HTML link (http://www.dhdursonthosting.com/css417-demo/authors.html) and use view source to see the HTML and included script, which is normally hidden.

dim oconn

set oconn = server.createobject("adodb.connection")

oconn.open("library2")

dim oset

set oset = oconn.execute("select * from authors order by auname")

oset.movefirst

<!—use recordset methods to retrieve data and display the results in an HTML table -->

<th><font color = "yellow">Phone</th>

</tr>

do until oset.eof

<tr>

</tr>

oset.movenext

loop

</table>

Conclusion

Database driven websites are becoming an increasingly important way of deploying database applications. The costs of deploying applications in this manner can be dramatically lower than with traditional fat-client applications based on Visual Basic.

However, this does not come without some additional complexity versus client-server. This lecture has reviewed some of the elements involved in implementing a database driven web site within the three-tier-architecture. It outlined the overall architecture, technologies, and components. Hopefully, it has also provided some idea of how to implement them all in a working website.