Make an Active Server Page from a MS Access table

Extracting MS Access table records and properties to Active Server Pages.



Keywords: MS Access table, display table with HTML and Active Server Page,
get DataType (DATA_TYPE), TablesSchema, OpenSchema, ISAUTOINCREMENT

I do a fair amount of creating Active Server Pages that will allow me to interact with the data in a MS Access table. I have tried a couple of the MS wizards for accomplishing this task, but I have found the code generated quite innefficient and bloated making for tough troubleshooting.

I wasn't looking for anything fancy, just simple display, add, edit, and delete of data (and records) in the Access table. Also, I don't care for data appearing in the hyperlink parameters, so I pass the data as hidden temporary cookies. The programming language I use for system scripting and Server-side ASP scripts is MS Visual Basic.

Automating the task of creating this type of web page seemed like a time saver, but extracting the information required from an Access table to make my script work turned out to be a real challenge. Specifically, I need to know the DataType of a field, so that I can get the proper syntax in my SQL INSERT and UPDATE commands. I also need to know if a field is an AutoNumber, and if the DataType can't be handled in a web page (OLE OBject, Number-GUID).

Hunting down all this information was quite the challenge, so I figured I would throw together this web page to help others looking for this information. Plus this is a decent example of connecting to and extracting various information from a database and writing to a function ASP page (text file). My scripting is not very tidy, (and neither are my web pages) but if you are here then you are probably more interested in mechanics (functionality) any way.

Caveats: the parameter test will verify that the passed in database resides locally, so if you are using IIS on another machine, then the path to the database will need to be the same relative path for both the server and the workstation you are running the script from. Hint: using UNC naming eliminates this problem.

All the files are available in OAC-Sample.zip compressed file. Contents:
- ADODB-MakeASP-OACDataTypeSample.mdb, database with a table containing all Access DataTypes and DataType values.
- ADODB-MakeASP-ListAccessTable-AllProps.asp, list all the interesting table properties.
- ADODB-MakeASP-ListAccessTable-AllProps-Output.html, output from above ASP page.
- ADODB-MakeASP-FromAccessTable.vbs, make an ASP page containing an HTML table from an Access table.

This guide assumes you have extracted the OAC-Sample directory to your wwwroot directory. Of course, running this on a production server is strongly discouraged

The examples use the Northwinds sample database, click here to quick view the code for ADODB-MakeASP-ListAccessTable-AllProps.asp.

Click here to quick view the results from this code.

To make an ASP page that will dynamically update with data from an Access table, you can view the Visual Basic Script here.
A sample command to execute this script:
c:\>cscript C:\InetPub\wwwroot\OAC-Sample\MakeASP-DbCode.vbs _
  "C:\Program Files\Microsoft Office\OFFICE\SAMPLES\Northwind.mdb" Products _
  C:\InetPub\wwwroot\OAC-Sample\RefProg-ADODB-MakeASP-ProductPage.asp


Click here to view the Active Server Page file this script creates.

Click here to quick view a static (html) version that this Active Server Page outputs.

This VBScript just creates an ASP that will display the output of a table. There are easier ways to accomplish this task, but the point of this was to demonstrate some how-to do it.

If you are interested in a VBScript that will create a fully functional Active Server Page for editing, adding and deleting records from an Access table, then send me an email: jayro at ohmancorp.com



Copyright 1996-2017 Ohman Automation Corp. All rights reserved.