Query.ASP - a general-purpose database query manager for Microsoft Access tables

Download QUERY.ZIP (contains the QUERY.ASP program, this documentation, and a sample CSS file).

QUERY.ASP is a general-purpose query manager for Microsoft Access databases stored on Web servers. QUERY.ASP reads a table or query in a Microsoft Access database, displays it in the Web browser window as a table, and provides feature to scroll through the result set, display it in various ways, and refine the result set using SQL statements. QUERY.ASP supports the following database query functions:

I have written QUERY.ASP primarily to quickly look at database tables and queries on my Intranet. QUERY.ASP is similar to Editor.ASP, a general-purpose database editor I have published in March 2000. QUERY.ASP is freeware. It is designed for people who know what they are doing; this is not an end-user tool. You may use or distribute the program, as long as you keep the copyright notice in the code. If you modify or enhance the program, you must keep a reference to the original copyright notice (and I would appreciate getting a copy of the enhanced code - just to learn from you). You use QUERY.ASP at your own risk. Although I use the program on my home Intranet on a regular basis, I do not accept any responsibility for the correctness of the results you get from QUERY.ASP.

This document describes QUERY.ASP version 1.02. The document assumes that you have a general understanding of the ASP concept, Microsoft Access, VBScript, HTML and the SQL Query language.

20. October 2002 / Roman Koch
Mail: roman@romankoch.ch
Homepage: http://www.romankoch.ch

System Requirements

As I'm using QUERY.ASP on my home Intranet only, I can only document what I'm using to make it work and take some assumptions about other platforms.

Component My environment Assumptions: Should also run with...
Server OS Microsoft Windows 2000 Windows 95, Windows 98, Windows NT 4, Windows XP
Web server Windows 2000 Internet Information Server Personal Web Server
Database Microsoft Access 2000 English All languages
Jet version 4.0 -
Web browser Microsoft Internet Explorer 6.0 IE 5, Netscape Communicator, Opera

Installation

Installation is easy - just unzip QUERY.ZIP and copy the files to a directory on your Web server that supports ASP execution. To keep multiple instances of the program in the same directory, just copy QUERY.ASP (and, if you want, the associated QUERY.CSS style sheet) and give it another name.

Starting and Using QUERY.ASP

Normal (Interactive) Mode

To start QUERY.ASP, open your Web browser and type the URL for QUERY.ASP, e.g.

http://maxi/home/cgi-bin/query.asp

QUERY.ASP then displays the entry menu. The first two fields, database and table/query, are required; the remaining options fields are optional:

Mandatory Fields
DatabasePath and name of your Microsoft Access database, using relative addressing, e.g. "test.mdb" or "../databases/test.mdb". To find out what databases are available in the current directory (the directory where QUERY.ASP is stored), click the Browse command.
Table/QueryA valid table or query name. To find out what tables and queries are available in your current database, click the Browse command.
Mandatory If Customised
Application PasswordEnter the QUERY.ASP application password. This field only appears if the cPW has been customised in the program code.
Optional Fields (reset to default values on each call)
Page SizeNumber of rows to display per screen. The default is 20. Use a smaller number for low-resolution screens.
Text Field SizeQUERY.ASP can truncate long text and memo fields to the number of characters you specify here. Enter 0 if you don't want text fields to be truncated.
Truncation MarkerEnter a character sequence that QUERY.ASP uses to indicate truncated text fields.
Date FormatSelect a format that QUERY.ASP applies to all date/time fields in your table or query.
The "European" and "European Date" formats are similar to the "General" and "Short Date" formats, but the output if formatted programmatically instead of using the server's regional settings.
Query MenuSelect the position for the query input fields.
Default ViewSelect the view QUERY.ASP uses initially to format your data.
HTML EncodeSelect this checkbox if your table or query contains HTML-formatted data; if checked, QUERY.ASP will "un-HTML" the data.
Ignore Style SheetSelect this checkbox to view the query results without applying cascading style sheet formatting.

Press the Open button to open the database and read the first page of your table or query.

Direct Mode

You can skip the entry menu by specifying the database and the table/query directly in the URL.

http://maxi/home/cgi-bin/query.asp?action=direct&db=my.mdb&table=mytable

QUERY.ASP then opens the table or query "mytable" in database "my.mdb" and displays the data in the page view. Note that this mode is not available when the application password is defined.

Page, Record and List View

QUERY.ASP displays the data from your table or query in page, record or list view.

Refining Your Query

Upon startup, QUERY.ASP displays your table or query using the simplest of all SQL commands: SELECT * FROM mytable (assuming your database contains a table named "mytable"). You can refine your query by entering SQL syntax into the SELECT, the WHERE and the ORDER BY fields. If you are not used to SQL syntax, use the Microsoft Jet SQL Reference that is included in your Microsoft Access Help.

ClauseWhat to enter
SELECTEnter the fields you want to display, separating the fields with commas, e.g.
pID, pName, pOrderNum, pOrderDate
Note that you also can use VBScript functions to build new fields, e.g.
pID, Left(Name,5) AS ShortName, pOrderNum
WHEREEnter the selection criteria, e.g.
pOrderNum > 50 and pName LIKE '%iller%'
ORDER BYEnter the sort criteria, e.g.
pOrderDate DESC, ShortName ASC

Click the Execute button to submit your search criteria to QUERY.ASP. QUERY.ASP combines your entries into a new SQL SELECT command and executes it against your table or query. You can further refine your query by modifying the clause fields. To turn back to the original query, either delete all entries in the clause fields and click the Execute button, or click the Restart command and then the Open button.

Note that the clause fields are positioned below the data table for Page and Record views, but above the data table in List view.

Other Commands

The Restart command brings you back to the entry menu. Use this command to select a new database or table, or to change the display options.

The Properties command displays the connection, recordset, field, session and server properties. This is a rather technical (and rather long) page that I often need to analyse data- and presentation-related problems.

The Help command displays this document.

Customisation

Formatting the Output

QUERY.ASP uses cascading style sheets to format the output. The CSS structures are defined in an external file. Depending on the value of the cCSS constant in the QUERY.ASP code, QUERY.ASP determines the style sheet as follows:

Internally, QUERY.ASP uses three class attributes to format the tables:

Note that QUERY.ASP applies the class to the <table> tag only; you therefore need to use the .class * td syntax in your style sheet to apply the formatting for table and header cells.

Security

As QUERY.ASP is highly dynamic and is able to browse files and tables, it might be considered security-critical. You can stop unauthorised users from accessing QUERY.ASP by entering a (case-sensitive) password in the cPW constant.

Note: If you set a password, the direct start mode is not available.

Known limitations

QUERY.ASP heavily relies on ASP session variables. These variables are initialised correctly during startup, either in normal or in direct mode. If you call one of the subsequent pages, e.g. the page view, directly, QUERY.ASP will fail.

QUERY.ASP does little error checking and input validation, be it in the entry screen or in the SQL clauses.

Support

The program is provided "as is". I'm not in a position to give support for it, neither by eMail nor by phone. I will try to answer your eMails, but this may take a couple of days, and most often all I can say you is "it works on my home Intranet".