How to Create Dynamic Webpages with Microsoft Access
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.
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).
Pull down the File menu and choose the Export option.
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 publish 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.D106 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.
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!
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.
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.
Select the Microsoft Access driver appropriate for your language and then click the Finish button to continue.
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.
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.
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.
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).
Pull down the File menu and choose the Export option.
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 publish 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.D106 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.
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!
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.
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.
Select the Microsoft Access driver appropriate for your language and then click the Finish button to continue.
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.
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.
Source...