Data Management

Access and reusable ASP code make for quick intranet applications

Access may not be your first choice for enterprise-grade development, but combined with ASP, it can be just the ticket for sites with a handful of users. Use these handy functions in your everyday ASP development projects.

Access might not be your first choice for a high-power database solution. But for applications that will be used by a dozen or fewer simultaneous users, the combination of Access and Active Server Pages (ASP) can be a great fit. Let’s take a look at six reusable ASP code functions that will help you quickly build database-enabled Web sites using Microsoft Office technologies.

To use these functions, you’ll need the built-in Windows Web server and the appropriate ODBC or OLE-DB drivers with any necessary connection data. For the following examples, we’ll use the local Microsoft Access database driver, but with a little modification, you can tweak these functions to support any ODBC or OLE-DB database.

Convenience and utility
Before we dig into the code, usage, and descriptions, let’s take a look at how these functions help your intranet application.

For the first three functions we’ll discuss—fnDatabaseExists, fnTableExists, and fnCreateTable—the convenience is being able to quickly debug, handle, and prevent conflict errors between the database server and Web server.

The main purpose of the fnRunSQL function is running CREATE TABLE, UPDATE, and INSERT INTO procedures. These procedures return only a success/fail status.

The fnSQLtoHTML  and fnSQLtoXML functions are critical. These, after all, are the most fundamental technologies for changing the static, bulletin-board format of many Web sites to the dynamic sites that present relevant and timely content based on user preferences and patterns. You can use these functions to structure, store, communicate, and present your data.

Combine these three functions and you have a toolbox that will minimize the skills required to build any database application—contact managers, spatial data, help desk/trouble ticket, billing, and so forth. You only need to know how to use HTML and SQL. You don’t need to know ADO, ADOX, RDO, ODBC, JDBC, DAO, VBScript, JavaScript, or any other Web language (but it does help).

This function verifies that an .MDB file exists at the location passed as a parameter. As you can see in Listing A, fnDatabaseExists uses the scripting system’s FileSystem object and returns a simple Boolean value of TRUE or FALSE.

This function accepts the file path on the server for a Microsoft Access .MDB file. If it exists, you get a TRUE; If not, you get a FALSE. The syntax is as follows:
bRetVal = fnDatabaseExists(“/dbFunctions/dbFunctionsSample.mdb”)

This function uses ADOX to create a new, empty, Microsoft Access .MDB file at the location passed as a parameter to the function. If the file already exists, the function returns a FALSE and leaves the existing file alone. If the function fails to create the new file for any other reason, a value of FALSE will be returned. If it’s successful, you’ll get a TRUE. You can see the code in Listing B.

This function accepts one parameter: the location where Access database file is to be placed. It returns a Boolean indicating that the function completed successfully or not. The syntax is:
bRetVal = fnDatabaseExists(“/dbFunctions/dbFunctionsSample.mdb”)

If the function fails, you won’t necessarily know right away why it failed. To keep the code simple, I’ve written it to return no error code—just success or fail. You can extend this functionality by including the Err.Raise function in VBScript, or by creating your own error class and having the functions return the error values to that class.

This function tells you if a particular table exists within the Access database file. You pass the file and table name as parameters. The function returns a Boolean value of TRUE or FALSE. Listing C provides the code.

This function accepts two parameters, the filename and the name of a table. It also returns a Boolean indicating whether the table does or does not exist. The syntax is:
bRetVal = fnTableExists(“Users”)

This useful little function accepts an SQL statement and runs it without returning a result set. This is handy for working with Data Definition Language (DDL) SQL commands that create, drop, and modify tables.

Although the function doesn’t return a result set, it does return values. If the connection to the database or server fails, it returns a Boolean FALSE. If the connection succeeds, it returns a Boolean TRUE. If it connects, but the SQL fails, the function returns a string describing the error. Listing D includes the code.

Be careful, because the SQL failure string error value will equate to TRUE in Boolean equation. If your return value is TRUE, you’ll want to check that it also equates to a blank string to make sure no error was generated by the SQL statement itself.

This function accepts two parameters: the location and name of the Access database file and an SQL statement. If the function can’t find the database file, it returns a Boolean FALSE. If it connects, but the SQL statement fails, it returns an error description. If the function succeeds, it returns a simple Boolean TRUE. To deal with the mixed type (Boolean/string), a little extra work is required to gracefully handle errors. The syntax is as follows:
bRetVal = fnRunSQL(“CREATE TABLE users (userid INT NOT NULL, username TEXT)”);
If bRetVal = FALSE Then

Response.Write “Could not connect to database.”
End If

This is a very useful function. It accepts a filename and SQL statement as parameters and returns a string value in the form of an HTML table loaded with the data returned from the SQL statement. If the SQL statement returns an error, that error is returned instead of an HTML table. See Listing E for the code.

The syntax is:
Response.Write fnSQLtoHTML(“/dbFunctions/dbFunctionsDemo.mdb”, “SELECT userid, username FROM users”)

This is perhaps most useful function on our list. It accepts an SQL statement and returns an XML data island. This data island can be saved to a file or inserted into your HTML document for use as a client-side recordset.

This is handy for working with data that users want to analyze but not modify. The user can sort, group, and manipulate the data repeatedly without having to repeatedly requery the database. You can see the code in Listing F.

As with the SQL to HTML function, this one also accepts two parameters: a file path and an SQL statement. It returns a basic XML data island. If there is an error anywhere in the process of executing the query, it returns a string description of the error.

The syntax is:
Response.Write fnSQLtoXML(“myfile.mdb”, “SELECT userid, username FROM users”)

Impact on performance and scalability
A drawback of using encapsulated database functions is their impact on performance and scalability. In the above examples, repeated use of the fnRunSQL function would result in establishing and breaking connections and other system resources for every instance of the command.

You can get around this limitation by adding other functions, specific to your applications’ needs, based on the ones provided above. For example, the fnRunSQL command could accept an array of SQL commands instead of just one. The function could then use the same connection for all of them.

A little cleanup
You’ll notice that all error handling is performed within the functions in a series of nested if/else constructs and through liberal use of the On Error Resume Next statement. Good coding rules dictate that you modify this to use the On Error Goto [label] statement instead. This will give you the ability to respond intelligently to individual errors, instead of dismissing them with a FALSE value as we’ve done in our basic examples.

Put them to good use
With these functions, you should be able to build ASP applications that work with a back-end Microsoft Access database without worrying about establishing and closing connections. You can use these functions to generate XML and HTML tables and extend to XML XHTML document files. You can perform SQL-based database management functions with a minimum of redundant code.

Editor's Picks