Creating Dynamic Web Pages with Microsoft Access

01
of 10

Open the database

Open the Database
Open the Database.

In our last tutorial, we walked through the process of creating a static web page from data stored in an Access database. That simple method of publishing web pages was adequate for environments where we want a "snapshot" of a database such as a monthly report or where the data rarely changes. However, in many database environments, the data changes frequently and we need to offer web users up-to-date information at the click of a mouse.

We can meet these requirements by utilizing Microsoft's Active Server Pages (ASP) technology to create a dynamic server-generated HTML page that links to our database. When a user requests information from an ASP page, the web server reads the instructions contained within the ASP, accesses the underlying database accordingly, and then creates an HTML page that contains the requested information and returns that to the user.

One of the limitations of dynamic web pages is that they can not be used to distribute reports like we did in our static web page tutorial. They can only be used to display tables, queries, and forms. In this example, let's create an up-to-the-minute product catalog for our web users. For the purposes of our example, we'll once again be using the Northwind sample database and Microsoft Access 2000. If you haven't used this sample database in the past, there are simple installation instructions located on this site. Select it from the menu shown below and click OK to continue.

02
of 10

Open the item you wish to publish

Open the item you wish to publish
Open the item you wish to publish.

When you see the database main menu, select the Tables submenu. Double-click the Products entry in the table (as shown in the figure below).

03
of 10

Begin the export process

Pull down the File menu and choose the Export option.

04
of 10

Create a Filename

At this point, you need to provide a name for your file. We'll call ours Products. Also, you should use the file browser to locate the path to publishing your file. This will depend upon your web server. The default path for IIS is \Inetpub\wwwroot. Once you've completed this step click the Save All button.

The Microsoft ASP Output Options dialog box allows you to specify the details of your ASPs. First, you can choose a template to provide formatting. Some sample templates are stored in the directory \Program Files\Microsoft Office\Templates\1033\. We'll use the "Simple Layout.htm" in this example.

The next entry is the Data Source Name. It's important to remember the value you enter here -- it defines the connection used by the server to access the database. You can use any name here; we'll set up the connection in a few minutes. Let's call our Data Source "Northwind."

The final section of our dialog box allows us to specify the URL and timeout values for the ASP. The URL is the method by which our ASP will be accessed over the Internet. You should enter a value here that corresponds to the file name and path you selected in step 5. If you placed the file in the wwwroot directory, the URL value is "http://yourhost.com/Products.asp", where yourhost is the name of your machine (i.e. databases.about.com or www.foo.com). The timeout value allows you to specify how long a connection will be left open for an idle user. Five minutes is a good starting point.

05
of 10

Save the file

Click the OK button and your ASP file will be saved to the path you specified. If you try to access the page now, you'll receive an ODBC error message. This is because we have yet to define the data source and the web server can not find the database. Read on and we'll get the page up and running!

06
of 10

Open the ODBC Data Source Control Panel

The process to do this differs slightly based upon your operating system. For all operating systems, click on Start, Settings and then Control Panel. If you're using Windows 95 or 98, double-click the ODBC (32-bit) icon. In Windows NT, choose the ODBC icon. If you're using Windows 2000, double-click Administrative Tools and then double-click the Data Sources (ODBC) icon.

07
of 10

Add a new Data Source

First, click on the System DSN tab at the top of the control panel dialog box. Next, click on the "Add" button to begin the process of configuring a new Data Source.

08
of 10

Choose the Driver

Select the Microsoft Access driver appropriate for your language and then click the Finish button to continue.

09
of 10

Configure the Data Source

In the resulting dialog box, enter the Data Source Name. It is imperative that you enter it exactly as you did in Step 6 or the link may not function properly. You may also enter a description of the Data Source here for future reference.

10
of 10

Select the Database

Finished Product
Finished Product.

Click on the "Select" button and then use the file navigation window to browse to the database file you wish to access. If you set it up with the default installation, the path should be Program Files\Microsoft Office\Samples\Northwind.mdb. Click the OK button in the navigation window and then click the OK button in the ODBC setup window. Finally, click the OK button in the Data Source Administration window.

Use your browser to verify that your Active Server Page works properly. You should see something like the output below.