Cover Story
Deleting, Inserting, and Updating over the Web
By Rick Dobson, Ph.D.
The Publish to the Web Wizard makes it easy to publish a dynamic datasheet based on an Access database. The wizard generates Active Server Pages (ASP) code. This technology makes Web pages dynamic by linking them directly to an Access database. The pages are dynamic in the sense that any change to the database appears in the next link to the Web page. Because ASP script runs on a Web server and writes pure HTML, any browser can read its output.
With a little custom coding, you can create Web pages that achieve similar results. Your reward for the customization is code that is more maintainable and potentially more optimized. In addition, you'll gain skills that help to write more advanced applications. An understanding of the basics of ASP design equip you to easily create applications that maintain Web-based Access databases. A solid grasp of ASP design can even enable you to extend the functionality of published datasheets so that their hyperlinks work the same way in Access as they do in a Web page. The Publish to the Web Wizard fails to deliver this benefit.
This article presents samples that illustrate how to manage an Access database through the Web. It starts with a discussion of the data source names (DSNs). It briefly reviews the three types of DSNs as a prologue to an examination of the File DSN type. All subsequent samples illustrate how to use File DSNs. The samples start with a simple datasheet prepared with the Publish to the Web Wizard. This datasheet includes hyperlinks that work in Access, but fail on the Web. You'll learn how to modify the wizard-generated page to correct this problem. More ASP samples demonstrate how to delete and append selected records in an Access database. You'll also learn how to update field values. A little Structured Query Language (SQL) can take you a long way when performing these tasks.
How Does ASP Talk to Access ?
For a Web page to display results dynamically from a database, it needs an active connection to the database. This is what a DSN does. It creates a live link to an ODBC data source, such as an Access, SQL Server, or Oracle database. ASP-based Web datasheets exploit this live link to make their pages dynamic.
You can create and modify DSNs from the 32bit ODBC applet in Control Panel. There are three types of DSNs: User, System, and File:
The User DSN is local to a computer, and only the current user can invoke it. This type of DSN does not support applications that enable multiple users to access a database over the Web.
A System DSN is also local to a computer, but anyone using the computer can invoke the DSN to make a connection with a data source. Creating a System DSN installs its connection information in the Windows registry.
A File DSN is not for a particular computer or user. The File DSN has the same information as the System DSN, but it stores the information in a text file with a DSN extension. Anyone can use it on any machine. For that reason, File DSNs offer more flexibility than System DSNs for Web work.
To create a File DSN, open the 32-bit ODBC applet in Control Panel and select the File DSN page. Click Add, select the Microsoft Access Driver, and click Next. Type in a name for the DSN, and click Finish. After that, click either Select or Create. Select lets you choose an existing database. Create fabricates a blank .mdb file that you can populate with tables later. By default, this process stores the File DSN and any newly-created .mdb files in the \Program Files\Common Files\Odbc\Data Sources folder.
The listings in FIGURE 1 show a pair of File DSNs used for the samples in this story. These two DSNs are identical, except for the last two lines. You will frequently want to edit this final pair of lines. The DefaultDir parameter specifies the path to the database on the Web server. The DBQ parameter designates the name of the Access database at the end of the path. You will typically want to set these for the Web server on which an ASP script runs. Depending on where you create the File DSN, these last two lines may not be appropriate. Once you view and, if necessary, edit the File DSN, you can copy it to the Web server. It's frequently convenient to position the File DSN in the same folder with the Access database.
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=MS Access
DriverId=25
DefaultDir=C:\InetPub\scripts\ModSolutions
DBQ=C:\InetPub\scripts\ModSolutions\fileCABCustom1.mdb
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=MS Access
DriverId=25
DefaultDir=C:\InetPub\scripts\ModSolutions
DBQ=C:\InetPub\scripts\ModSolutions\fileCABCustom2.mdbFIGURE 1: Sample DSN files.
ASP Datasheets with Hyperlinks
Access 97 makes it particularly easy to perform selected Web functions. First, working with hyperlinks in a datasheet is a snap — just declare a hyperlink data type for a field. Doing this allows you to enter a hyperlink address in a field with that data type. Clicking the hyperlink transports a user from the Access file to the reference for the hyperlink. Second, you can publish dynamic datasheets for use on the Web. The Access 97 Publish to the Web Wizard makes this exceptionally easy. Unfortunately, when you use the Publish to the Web Wizard to create a dynamic datasheet, the hyperlinks on the Web page don't work correctly.
To create a dynamic datasheet with the Publish to the Web Wizard, select File | Save as HTML command from the Database window. Select one or more tables, queries, or forms. Choose Dynamic ASP as the format type. If you are using a System DSN, type the DSN name in its text box. If you are using a File DSN, leave the data source name text box blank. Specify a folder for storing the ASP file(s) that the wizard will generate. If you are working on an intranet, store the ASP files in the folder from which your Web server will publish the datasheets. Otherwise, store the files in any folder and transfer them later to your remote Web site.
If you used a System DSN, and saved your files on your intranet server, then you're ready to link to the ASP files. If you used a File DSN, then you'll need to edit the connection code in the ASP script. FIGURE 2 shows the initial and edited versions of the script. The modification shows how to reference the File DSN that appears at the top of FIGURE 1.
' Initial version.
<%
If IsObject(Session("_conn")) Then
Set conn = Session("_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "","",""
Set Session("_conn") = conn
End If
%>
' Edited version.
<%
If IsObject(Session("_conn")) Then
Set conn = Session("_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "FILEDSN=c:\InetPub\scripts\modsolutions\" + _
"fileCABCustom1.dsn","Admin",""
Set Session("_conn") = conn
End If
%>FIGURE 2: Initial and edited versions of the ASP script code to invoke a File DSN.
FIGURE 3 shows the resulting dynamic datasheet in the IE browser. The datasheet is dynamic because it changes its contents depending on the contents of the GuestBook table in fileCABCustom1.mdb. In addition, notice that the hyperlinks in the browser appear with leading and trailing pound signs (#). These extra characters cause the hyperlinks to fail on the Web page. In contrast, the hyperlinks work well from within Access. Clearly, the wizard doesn't perform the hyperlink transformation correctly for use on the Web. However, you can edit the initial code and correct the problem.
FIGURE 3: Faulty hyperlink output in a dynamic ASP page.
FIGURE 4 shows the initial wizard ASP script for the table. Notice that there is no anchor tag to make the link work in the ASP script. This, in combination with the trailing and leading # signs, contributes to the failure of the links to cause a jump to a new page.
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0>
<FONT FACE="Arial" COLOR=#000000><CAPTION><B>GuestBook
</B></CAPTION>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>ID</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>First Name
</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>Last Name
</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>Email Address
</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>WebPageAddress
</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>Comments
</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT>
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("ID").Value)%><BR>
</FONT></TD>
<TD BORDERCOLOR=#c0c0c0 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("FirstName").Value)%><BR>
</FONT></TD>
<TD BORDERCOLOR=#c0c0c0 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("LastName").Value)%><BR>
</FONT></TD>
<TD BORDERCOLOR=#c0c0c0 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("EmailAddress").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><U>
<FONT SIZE=2 FACE="Arial" COLOR=#0000ff>
<%=Server.HTMLEncode(rs.Fields("WebPageAddress").Value)%>
<BR></FONT></U></TD>
<TD BORDERCOLOR=#c0c0c0 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("Comments").Value)%><BR>
</FONT></TD>
</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>FIGURE 4: An initial version of the ASP script code to publish a dynamic ASP file with hyperlinks.
FIGURE 5 shows the fixed code. There are two parts to the fix. First, you need to strip the # signs from the beginning and end of the hyperlink address. The nested-function expression for the WebURL memory variable achieves this. Notice the expression relies on familiar VBA functions. (This is VBScript, but it supports many VBA functions.) Second, an anchor tag must surround the hyperlink address. The replacement code embeds the WebURL variable in the anchor tag appropriately. After you make these changes, the links look and operate appropriately.
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
webURL=mid(mid(rs.Fields("WebPageAddress").Value,2,256), _
1,(len(rs.Fields("WebPageAddress").Value)-2))
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT>
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("ID").Value)%><BR>
</FONT></TD>
<TD BORDERCOLOR=#c0c0c0 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("FirstName").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("LastName").Value)%><BR>
</FONT></TD>
<TD BORDERCOLOR=#c0c0c0 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("EmailAddress").Value)%><BR>
</FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><U>
<FONT SIZE=2 FACE="Arial" COLOR=#0000ff>
<a href=<%=Server.HTMLEncode(webURL)%>>
<%=Server.HTMLEncode(webURL)%></a><BR></FONT></U></TD>
<TD BORDERCOLOR=#c0c0c0 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("Comments").Value)%><BR>
</FONT></TD>
</TR>
<%
rs.MoveNext
loop%>
</TBODY>FIGURE 5: Edited ASP script to publish a dynamic ASP file with hyperlinks that function correctly.
Deleting Records with ASP Script
With any database, there comes a time when it's necessary to remove some records. We'll look at two samples that demonstrate how to delete records using one, and two, ASP pages. The advantage of using one page to delete records is that the code is simpler. Using two pages, however, allows users to view candidate records for deletion before removing them from the database.
FIGURE 6 shows the ASP code to delete all records from the GuestBook table with an ID value greater than 3. The GuestBook table is in fileCABCustom1.mdb. The ASP code is set off in angled brackets and percent signs. The HTML is the simple message "The records are deleted." The ASP code consists of three statements. The first creates an ActiveX Database Objects reference to a recordset. The second statement contains the SQL DELETE statement. The third statement invokes the SQL statement for the recordset object. This last ASP statement references the File DSN in the top panel of FIGURE 1.
<%
set rs = Server.CreateObject("ADODB.Recordset")
sql = "DELETE * FROM GuestBook WHERE GuestBook.ID>3;"
rs.open sql, "FILEDSN=c:\InetPub\scripts\modsolutions\" + _
"fileCABCustom1.dsn",1,3
%>
<HTML>
<BODY>
The records are deleted.
</BODY>
</HTML>FIGURE 6: An ASP script to delete records from a database.
The SQL statement is an edited version of the initial SQL created from within Access. The unedited version works fine, but the edited version is shorter and easier to read. Therefore, it will be easier to maintain over time. As you can see, this approach does not require much knowledge about SQL. Any knowledge of HTML is similarly minimal.
Simplicity is often a virtue, but the code sample in FIGURE 6 deletes records without giving users a chance to preview them first. A more preferable approach lists the records that are candidates for deletion and offers the option of proceeding or not. You can accomplish this result with two ASP pages. The first page lists candidate records for deletion and offers a link to a second page to perform the deletion. The user has the option of selecting the link or performing other chores, such as revising the SQL statement.
Listing One (beginning on page XX) contains the code from an ASP file, AreYouSure.asp, which presents a table with the candidate records for deletion. It features two SQL statements. The DELETE statement is there strictly for reference purposes. The SELECT statement extracts the candidate records. The HTML code below the ASP script performs two functions. First, it displays the candidate records in a table. Second, it offers a hyperlink to a second page, IAmSure.asp, which deletes the candidate records:
' IAmSure.asp
<%
set rs = Server.CreateObject("ADODB.Recordset")
sqlDelete = "DELETE * FROM GuestBook WHERE GuestBook.ID>3;"
rs.open sqlDelete, "FILEDSN=c:\InetPub\scripts\" + _
"modsolutions\fileCABCustom1.dsn",1,3
%>
<HTML>
<BODY>
The records are deleted.
</BODY>
</HTML>It isn't necessary to write the SQL from scratch. You can use Access' graphical query-by-example functionality to create the SQL code. In addition, the HTML in AreYouSure.asp is mostly an excerpt from the output of the Publish to the Web Wizard for listing a table. I edited this code to permit the hyperlinks to work properly.
FIGURE 7 shows the GuestBook table at three points in the deletion process. It reveals the behavior of the code in Listing One. The top panel displays the datasheet before the deletion of any records. The middle panel reveals the display from the AreYouSure.asp file in Listing One. Notice it lists just those records with an ID greater than 3. The bottom panel shows the feedback from the IamSure.asp file. This feedback appears to confirm the deletion.
FIGURE 7: The operation of the two ASP files shown in Listing One.
Appending Records with ASP Script
Another typical database maintenance chore is adding new records. Two scenarios are common. First, users may want to add records to one table based on the contents of another table in the same database. By storing candidates for additional records in a holding table, you can scan them for admission to your relational database design. Use either of the delete procedures described above to remove records that don't merit addition to the database model. Then, invoke your append logic to add the cleaned records.
Second, you may find it desirable to append records to a database model from a table in another database. This is especially convenient for Web work because you can upload a small table to your remote site and update your permanent database on the site. When you need to update a remote database with transactions occurring off the Web, this is an efficient approach. It offers obvious advantages over downloading the main database from the Web, appending the new records, and reloading the full database to the remote site.
FIGURE 8 shows a listing for an ASP file that appends records from the NewGuests table to the GuestBook table. Both tables reside in fileCABCustom1.mdb. Notice that the File DSN in the script points at this database. The SQL string appears on multiple lines. The sample reveals the syntax to segment a longer SQL statement into multiple lines for easy reading and editing. The basic design of the ASP file to append records is identical to the sample in FIGURE 6 that deletes records. Of course, a different SQL statement is used — an INSERT INTO statement.
<%
set rs = Server.CreateObject("ADODB.Recordset")
sql = "INSERT INTO GuestBook ( FirstName, LastName, " + _
"EmailAddress, WebPageAddress, Comments ) " + _
"SELECT FirstName, LastName, EmailAddress, " + _
"WebPageAddress, Comments FROM NewGuests;"
rs.open sql, "FILEDSN=c:\InetPub\scripts\modsolutions\" + _
"fileCABCustom1.dsn",1,3
%>
<HTML>
<BODY>FIGURE 8: A simple ASP script to append records from one table to another in the same database.
FIGURE 9 shows a pair of screen shots that demonstrate the operation of the append code in FIGURE 8. The top screen displays the contents of the NewGuests table from within Access. While maintaining an Access database on an intranet, you can readily view it this way. The bottom screen shows the two records in NewGuests appended to the end of GuestBook. This view is from the Web. It depicts the state of the GuestBook table after running the code in FIGURE 8.
FIGURE 9: The operation of the append code in FIGURE 8. The top screen shows the new records in NewGuests. The bottom screen reveals these records at the end of the GuestBook table.
Figure 10 shows the more sophisticated appending sample that adds records to one table, based on the records in a table from another database. This approach applies when you update a database on the Web with new records from an off-line activity. This sample requires a different DSN than the one used in the previous samples. This is because the new DSN must point at the second database that contains the new records for addition to the remote database. Notice that the File DSN references fileCABCustom2.dsn. This DSN file appears in the lower portion of FIGURE 1. It, in turn, points at fileCABCustom2.mdb.
<%
set rs = Server.CreateObject("ADODB.Recordset")
sql = "INSERT INTO GuestBook (FirstName, LastName,
WebPageAddress, Comments)
IN '\InetPub\scripts\modsolutions\fileCABCustom1.mdb'
SELECT FromOtherDB.FirstName, FromOtherDB.LastName,
FromOtherDB.WebPageAddress, FromOtherDB.Comments FROM
FromOtherDB;"
rs.open sql, "FILEDSN=c:\InetPub\scripts\modsolutions\" + _
"fileCABCustom2.dsn",1,3
%>
<HTML>
<BODY>
The new records are appended.
</BODY>
</HTML>Figure 10: Appending records from a table in a different database.
The SQL statement in Figure 10 can explicitly reference the main database without the need for another DSN. This SQL statement has a fundamentally different design than the one in FIGURE 8. You can copy much of the code from the SQL View of an append query in Access. However, it requires some editing. This is primarily because the argument for the IN operator appears in double quotes within Access, but it should appear within single quotes in an ASP script. The IN operator refers to the table that will accept the new records. Its argument must specify the path and file name for the table gaining the new records. The DSN in the ASP script designates the table containing the records that you want to append.
The SQL statement in Figure 10 extends across multiple lines without any explicit code to handle line continuation. Even simple text editors, such as Notepad, permit word wrap so that you don't explicitly require continuation characters. However, if you use this approach, you must be careful to remove any non-showing carriage return and linefeed characters at the end of lines.
The operation of the code in Figure 10 behaves identically to that in FIGURE 8, except that the records come from a different source. This alternative strategy speeds the management of a remote database by replacing the transfer of a large database back and forth from a remote server with the transfer of a small database to a remote server.
Updating Fields with ASP Script
The third typical element of database maintenance involves updating the values of fields in a table. This activity calls for a programmatic solution when you need to update a large collection of records accurately and consistently from one value to another. For example, you may want to increase all prices by 5 percent, or assign an abbreviation to replace a full name. Update queries are a standard tool available to database administrators for maintaining databases.
FIGURE 11 shows the code for updating the FirstName field in GuestBook so that every entry of "Microsoft" is changed to "MS". The syntax for this statement relies on three SQL keywords. UPDATE designates the table requiring modification. SET specifies the new value for a specific field in the table. An optional WHERE clause defines a criterion for selectively applying the update to a subset of records.
<%
set rs = Server.CreateObject("ADODB.Recordset")
sql = "UPDATE GuestBook SET GuestBook.FirstName = " + _
"'MS' WHERE GuestBook.FirstName='Microsoft' ;"
rs.open sql, "FILEDSN=c:\InetPub\scripts\modsolutions\" + _
"fileCABCustom1.dsn",1,3
%>
<HTML>
<BODY>
The records are updated.
</BODY>
</HTML>FIGURE 11: A simple UPDATE script for modifying all FirstName fields containing "Microsoft" to fields containing "MS."
Update tasks typically require a single database, so this sample ASP script reverts to using the file CABCustom1.dsn. You can enhance this code to include a display showing the records it will modify before committing to the update (Listing One is an example).
Conclusion
The ASP/Access literature largely ignores the task of database management, but this function is critical to using databases over the Web. Furthermore, there are no wizards to help with these tasks. The samples in this article illustrate how to implement three core database management tasks over the Web: deleting, inserting, and updating. Relying on ASP for these chores insures your applications will work in many different kinds of browsers because ASP script runs on the server and returns pure HTML to browsers.
Download source code for this article here.
Rick Dobson, Ph.D., is president of CAB, Inc., a database, Office, and Web consultant company. His byline appears in many publications, including Byte, NetGuide, and Visual Basic Developer. He is also a contributing editor to Microsoft Interactive Developer. You can reach him at RickD@cabinc.win.net.