HOWTO: Display Records from an Access Database with ASP

ID: Q180814

The information in this article applies to:

Microsoft Visual InterDev, version 1.0
Microsoft Internet Information Server version 3.0

SUMMARY

This article shows you how to create a Web interface that displays information from an Access 97 relational database using Visual InterDev.

There are four main steps in this process:

Create the Web project.

Add a Data Connection to Your project.

Create an Active Server Pages (ASP) page and Add it to a Data Command Control.

Write Code to Act on the Recordset Object Returned by the Data Command Control.

MORE INFORMATION

Follow the steps below to create an interface that reads data stored in an Access 97 database.

Step 1: Create the Web Project

Launch Visual InterDev.

On the File menu, click New.

Select the Web Project Wizard from the list on the Projects tab.

Enter MyDatabaseApp for the name of your Web project in the Project Name box (this is the name of your project stored on your remote workstation).

In the Location box, enter a path or click the ellipsis (...) to browse your local workstation for a place to store your project and files.

Click OK to continue to the next step.

Type or select the server for your Web, and click Next.

In Step 2 of the wizard, select "Create a new Web."

Type DB for the name of your new Web. This helps to demonstrate the difference between a project and a Web.

Clear "Enable full text searching for pages in this Web."

Click Finish to create your new Web.

Step 2: Add a Data Connection to Your Project

From the Project menu, point to Add to Project, and then click Data Connection.

Click New on the File Data Source tab.

Select "Microsoft Access Driver (*.mdb)" and click Next.

Click Browse.

By default, this places you in the Data Source folder located in the WinNT\ODBC directory on Windows NT or the Program Files\ Common Files\ODBC directory on Windows 95.

In the File Name box, type MyDataSource and click Save. The full path to your new Data Source Name (DSN) file appears in the box.

Click Next, then click Finish.

In the "ODBC Microsoft Access 97 Setup" dialog box, click Select (located in the Database section of the dialog box).

Browse or type in the path to the Adventure Works (AdvWorkds.mdb) database.

NOTES:

The Adventure Works example site is installed by default when you add Active Server Pages to Microsoft Internet Information Server. The site is installed by default into the InetPub\ASPSamp\AdvWorks directory.

If your database resides on a machine other than the Microsoft Internet Information Server server, you may want to use a Universal Naming Convention (UNC) path so that the server can locate it on your network. Following is a sample UNC path:
```
      \\SERVERNAME\ROOTSHARENAME\InetPub\ASPSamp\AdvWorks\AdvWorks.mdb 
```
SERVERNAME represents the name of your server and ROOTSHARENAME represents the share name of your root directory.

Note also that when you use an Access 97 database, you must set the NTLM CHANGE permissions for the directory where the database is located. This means that the IUSR_<machinename> account, for anonymous user logon, must have CHANGE permissions for the entire directory.

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

Q175804 PRB: Server Object Error 'ASP 0177:80040154' Server.CreateObject

Q180815 BUG: IIS 4 Fails When Using a UNC to Access

Click OK to select the database for your DSN.

Click OK to complete the DSN setup.

Select MyDataSource.dsn from the list in the Select Data Source dialog box.

Click OK to complete the process.

Step 3: Create an ASP Page and Add It to a Data Command Control

On the Project menu, point to Add to Project, and click New.

Select Active Server Page on the Files tab.

Type "Database" in the File Name box, and leave all other default options.

Click OK to create the page.

Leave the "" line selected in the code window. On the Insert menu, point to Into HTML, and click ActiveX Control.

Click the Design-time tab in the Insert ActiveX Control dialog box.

Select the Data Command control from the list provided, and click OK. A Properties dialog box for your new control appears.

Leave "DataCommand1" in the ID box, and select DataConn from the DataConnection list. This is the DataConnection that you created in step 2.

Click SQL Builder to build a SQL statement that returns a Recordset.

Click the plus sign (+) to expand the Tables tree in the Data View window.

Drag the Products table into the Query Builder window.

Select * (All Columns) in the Products table (located in the Query Builder window) to create the following SQL statement:
```
      SELECT Products.*
      FROM Products 
```

On the File menu, click Close to close the Query Builder, and click Yes when prompted to update DataCommand1.

You should see the new SQL statement in the Command Text box of the Properties dialog box.

Close the DataCommand1 control window.

Note that Visual InterDev creates the code necessary to execute the query against the Access database and to return the results in the Recordset object.

Step 4: Write Code to Act on the Recordset Object Returned by the Data

Command Control

Place the following code after the  tag in your ASP page:


      <%
        Dim fldTemp
        For Each fldTemp In DataCommand1.Fields
          Response.Write fldTemp.Value & "<BR>"
        Next
      %>

On the File menu, click Save to save the ASP page.

Right-click the ASP page file name in the FileView window, and select "Preview in browser."

REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, link to the following Microsoft Technical Support site:

http://support.microsoft.com/support/vinterdev/


Keywords          : kbcode kbADO kbASP kbVisID kbVisID100 kbGrpASP 
Version           : Win95:1.0,3.0; WINNT:1.0,3.0
Platform          : Win95 winnt 
Issue type        : kbhowto

Last Reviewed: May 4, 1999