Build a database front end with ASP

There are various ways to interface with a database, including Access and HTML, but one approach takes advantage of legacy Microsoft ASP (with ADO/ADOX) to build a front end.

This method, which I will explain below, uses sample code that provides a quick and dirty application for connecting to a database and for adding, dropping, and viewing table definitions within that database. You can also extend the functionality of the sample code to provide a deeper and broader control of database management functions. Let’s dive in and create a sample application.

The sample application
The source code for the sample application is contained within a single file (dbfe.asp). If you want to test the application yourself, you'll need to replace the UNC filename with one that points to an Access database file to which you have full privileges. Alternatively, you could change the whole connection string to whatever you need to connect to the database of your choice.

I used a DSN-less ODBC connection (as evidenced by DRIVER= instead of PROVIDER= in the connection string). When you first load the page from a Web server that has ADO and ADOX enabled, you will be presented with a list of tables within that file--even system tables will be displayed. Underneath the tables list are three buttons, used to view and drop existing tables, and one to create a new one. (Figure A shows the interface.)

Figure A: Sample interface

The code
If you look at the code for the sample page, you will be presented with a well-structured ASP application including the following:

1. The object variables are defined through the use of Server.CreateObject().
   
2. The QueryString value for the submitted table name (if any) is loaded into a local variable.
   
3. The code branches are based on the QueryString value for the requested action (if any). Messages to the user are loaded into the sMsg variable, and the result of their action request (add, drop, or view) is loaded into the sView variable.
   
4. The functions that return the GUI forms are defined as fnShowTables, fnShowTable, and fnShowCreateTable.
   
5. The HTML code for the user interface is defined with the sView and sMsg variables echoed inline with the HTML tags.

Beyond the code
The code for the sample application is a good learning tool but only goes so far. By exploring the tools used in it, however, you can find a wealth of additional properties and methods. You can--and in a production-quality application, should--build interfaces that expose those properties and methods to your users in whatever way you like. The rest of this article goes beyond the code to discuss how you can take the skills learned in the sample code to build a production-quality database management utility using legacy ASP.

Connection, security, and error handling
The connection string is static in this example; it's hard-coded in the ASP file. This allows anyone with access to your ASP file to access your database, so you may want to use a Machine DSN or compile an ASP DLL to store the connection string for security purposes.

In addition, the connection is opened at the top of the page and closed at the bottom. The page uses one database connection. The overhead associated with creating, opening, closing, and destroying the connection object only happens once.

At the top of the page, a directive is issued to Resume Next on errors and within the code; no error handling is done. You could choose to add error-handling code, so that the description of the error is sent to the sMsg page-level variable. That variable is used to display messages to the user whenever an activity is attempted.

The last security concern is the unchecked SQL. When the user creates a new table, he or she can embed parenthesized SQL statements to things that have nothing to do with creating a new table. In a production application, you should never let the user have this kind of SQL-level access to your database.

Using ADOX to list tables
The catalog object allows you to view tables, views, procedures, users, and groups. In the sample application, you deal only with the table collection, but you could just as easily--actually more easily--add functions to manage the other collections as well. Be careful when dealing with the users and group collections, because you might accidentally lock yourself and everyone else out of the database file.

Table and View
If you select one of the tables on the list and press the View button, you'll be presented with a brief table definition, describing the table and its columns. Through the ADOX Table object, you can access several additional objects, including Indexes, Keys, and information about the connection used to access that table.

Using ADOX.Column to show column definitions
The ADOX.Column object lets you easily check the settings for various column properties, such as type, name, and constraints. It also has properties that define its relationship to other columns within the table, or within columns in other tables in the same database.

Create table
If you select one of the tables on the list and press the Create button, you'll be presented with an HTML form that lets you specify the name of the table and the column definitions phrase of a SQL statement. You could modify the code to use arrays of form elements, thus making it easier for the user to create columns of specific data types and such, and to filter inappropriate code segments that the user may have entered.

Using ADO.Connection.Execute to create new, and drop existing, tables
The sample application creates a table from user-specified values in an HTML Columns Definition form. For security reasons, some environments will not allow ADOX to create and drop tables, or they make it very complex to do so. For that reason, in the sample code you'll see that the SQL statements perform those functions through the ADO Connection object's Execute method.