Microsoft

Grab Access data online

This article will walk you through downloading information from and Access table and displaying it on a Web page.


There was a time when connecting to the Internet and retrieving live data was a painful excursion. But Personal Web Server (PWS) on Windows 98 makes connecting to Access 98 and Access 2000 data easy; connecting to Access 2002 with Internet Information Services (IIS) on Windows 2000 is even easier. This article will show you how to use PWS or IIS to download data from an Access table and present it in HTML.

You will learn how to install PWS or IIS (depending on whether you run Windows 98 or 2000), create a DSN connection, write an ASP script that retrieves data, and display the downloaded data on your browser in a simple HTML table. The links to technical articles on the right will help you expand on what you learn in this introduction to ASP tables.

Install the local server
The first step is to install Personal Web Server or Internet Information Services, so skip to the next page if a server is already installed. PWS is a desktop Web server that allows you to connect to a network and share data. The example ASP files will use your system's server (PWS or IIS) to simulate downloading data from the Internet, but that data will really be on your local system.

To install PWS on Windows 98:
  1. 1.      Insert your Windows 98 install disc in your CD-ROM drive.
  2. 2.      Click the Start button on the taskbar and choose Run.
  3. 3.      Enter the statement
    x:\add-ons\pws\setup.exe
    where x represents your CD-ROM drive letter. Click OK.
  4. 4.      Click Next after reading the welcoming message.
  5. 5.      Click the Add/Remove button.
  6. 6.      Make sure the following items are checked: Common Program Files, FrontPage 98 Server Extensions, Microsoft Data Access Components 1.5, Personal Web Server (PWS), and Transaction Server. Then click Next. Windows will begin the installation, which will take a few minutes.
  7. 7.      When prompted, click Finish to complete the installation. Then restart your computer.

To install IIS on Windows 2000:
  1. 1.      Insert your Windows 2000 install disc in your CD-ROM drive.
  2. 2.      Click Install Add-on Components.
  3. 3.      Check the Internet Information Services (IIS) item and click Next. You can expect to wait a few minutes while Windows completes the process.
  4. 4.      When Windows displays the Windows Components Wizard dialog box, click Finish.
  5. 5.      Select Exit in the Microsoft Windows 2000 CD dialog box.

After installing PWS or IIS, open Windows Explorer and locate a new folder named inetpub on your local drive.C:\inetpub is the most likely location. Open this folder to find the wwwroot subfolder, otherwise known as the Web root. All subfolders and the files within wwwroot will be accessible via the Web. Initially there will be only two files in this folder, default.asp and global.asp, but this article doesn't deal with them.

A known bug installing PWS
You may encounter an error when installing the Transaction Server on Windows 95 and 98 systems. For complete information, read the Knowledge Base article at Microsoft's site or follow these instructions:
  1. 1.      Create a temporary folder named \Temp on your local hard drive.
  2. 2.      Insert your Windows 98 install CD and copy the complete contents of x:\add-ons\pws\ to \Temp.
  3. 3.      Download mtssetup.exe from the Knowledge Base page.
  4. 4.      Execute mtssetup.exe by double-clicking it, extract Mtssetup.dll to \Temp, and confirm the overwrite process.
  5. 5.      In \Temp, double-click setup.exe to begin the installation process. Follow the PWS instructions above.

Set up the server folder and ASP files
After installing PWS or IIS, you should create a new folder to store the database and ASP files that you'll execute in order to retrieve data from that database. Create the new folder in\inetpub\wwwroot and name it sample. Then copy the Northwinds sample database that comes with Access into this new folder. You'll find the Northwind.mdb file in \Program Files\Microsoft Office\Office\Samples. If you can't locate Northwind.mdb, use the Windows Find feature or insert your Office CD and install it.

By default, a folder won't share files. So after you've created thesample folder, right-click its icon in Windows Explorer, select Properties, then click the Web Sharing tab. Select the Share This Folder option and click OK. Now Windows will allow you to share the files within this folder on a network.

Create the example ASP files
The three links below open three short ASP example scripts. To create these files, simply copy and paste each script into Notepad as an individual file. Save each as an ASP file by manually adding the.asp extension to the file's name. For instance, to save the first script, choose Save As from the File menu, select All Files (*.*) from the Save As Type control, then enter the filename example1.asp. Put all three ASP files in the \inetpub\wwwroot\sample folder you created.

Create a DSN connection
At this point you need to create a data link. When you execute an ASP file, Windows must know which database you're connecting to. An ODBC Data Source Name (DSN) simply stores the information (the name, the path, the user, and the password) required to connect to a specific database. OLE DB connections are superior to DSN because they're quicker to connect, but you must include the specific connection string in the script, and that's beyond the scope of this article. A DSN connection requires little know-how to set up. There are two types of DSN:
  • ·        System: A System DSN is available to all users on the system and is the one to use when you want others to access a DSN connection via the Web. For this article, it is vital that you use a System DSN created on your Web server or local development system.
  • ·        File: All users who have the appropriate drivers installed can share a File DSN. A driver is a bit of software that provides specific functionality, such as a connection to a data store, a sound card, and so on.

The main difference between the two types is that a File DSN is not available to the public, while the System DSN is available to all users of the machine, including the Web server.

To create a DSN on a Windows 98 system:
  1. 1.      Open the Control Panel and double-click the ODBC Data Sources (32-bit) item.
  2. 2.      Click the System DSN tab.
  3. 3.      Click the Add button. Select the Microsoft Access Driver (*.mdb) item and click Finish.
  4. 4.      When prompted, enter a name for the DSN. For the sample, enter NorthwindDSN.
  5. 5.      Enter a description for the connection.
  6. 6.      Click Select to locate your Access database. Find and select the sample database \inetpub\wwwroot\sample\Northwind.mdb.
  7. 7.      Click OK to create your DSN. Notice that the new DSN is added to the System Data Sources list in the ODBC Administrator.
  8. 8.      Click OK one last time and close the Control Panel window.

To create a DSN on a Windows 2000 system:
  1. 1.      Open the Control Panel and double-click Administrative Tools.
  2. 2.      In the resulting folder, double-click Data Sources (ODBC) to open the ODBC Data Source Administrator.
  3. 3.      Select System DSN, then click Add.
  4. 4.      Scroll through the list of drivers and select the Microsoft Access Driver. Click Finish.
  5. 5.      Follow steps four through eight for the Windows 98 instructions.

Connect to the database
All the pieces are in place. Now you'll use the System DSN you created to connect to the Northwind sample database in\inetpub\wwwroot\sample, the same directory where you stored the ASP example scripts. Open a browser and execute each ASP file by entering a URL in the form
http://server/subfoldername/filename.asp

whereserver is the name of your server, subfoldername identifies the folder within wwwroot containing the ASP file, and filename.asp is the name of the ASP file that you're executing. In this case, the server should be localhost, so to run the first example file enter
http://localhost/sample/example1.asp

The result should be a list of names, last then first, of each employee in the Northwind Employees table. Let's look at the actual scripts; all of the ASP code is enclosed in <% and %> tags. In example1.asp, the first statement
<%Set Conn = Server.CreateObject("ADODB.Connection")

creates a new instance of theConnection object named Conn. You can give objects any name that you want, but descriptive names are recommended. By default the connection is read-only, which is what we want in this case. You can change this by adding a following line
Conn.Mode = constant

Whereconstant is adModeRead for read-only, adModeWrite for write-only, or adModeReadWrite to allow read and write.

Now you must open the connection using the System DSN that you created earlier. So the next statement
Conn.Open "NorthwindDSN"

points the connection to the NorthwindDSN, which gives you access to the Northwind sample database in\inetpub\wwwroot\sample. The next statements
Sql = "SELECT LastName, FirstName FROM Employees" Set rst = Conn.Execute(Sql)

populaterst with the data from the LastName and FirstName fields in the Employees table. The While loop then moves through each record in rst, displaying the contents of the LastName and FirstName fields.

This simple example returns two data fields from every record in the table. You'll rarely retrieve so much data and will more likely limit the retrieved information to some criteria. To illustrate, example2.asp restricts the SQL statement to retrieve only those records where the LastName field equals the string 'Reid':
Sql = "SELECT LastName, FirstName FROM Employees WHERE LastName = 'Reid'

TheIf statement checks rst for records, and the EOF property will be true if rst is empty. In which case, the code displays an error message, and
Response.End

kills the script. Any ASP or HTML after this statement will not be executed.

Formatting tables
The first two ASP files deal with simple data retrieval. The third grabs the data from the LastName and FirstName fields in the Employees table and formats it in a simple HTML table. The formatting begins with
<table border=1>
<caption align="top">Employee List from Northwind. Demo</caption>

This starts the table and adds a title above it, while the statements
<tr>
<th>Last Name</th>
<th>First Name</th>
</tr>

add headings to the table columns. The <tr> and <td> tags create table rows and cells while the While loop populates it. The bgcolor="#00FFFF attribute adds a bit of color to each row.

Editor's Picks