Often Requested Solution
I often see posts at comp.databases.ms-access where developers are asking for advice
on how to publish their Microsoft Access databases to the web. Replies almost always
include advice to learn ASP, and a reference to the book,
ASP 3.0 Programmer’s Reference from Wrox is usually provided.
Fact is, publishing Access tables to the web is not trivial, in spite of the Publish To
Web Wizard (which I have never been fond of). Also, Access developers like myself are often
disappointed at losing the rich functionality that comes with Access at no extra charge,
such as the automatic saving of edits as well as inherent INSERT and DELETE functionality.
Take, for example, the simple datasheet. A table of rows and columns. How can this be
reproduced on an ASP page? That was my goal a few months ago when I set about to publish
a simple phone list database at my web site. Using the Wrox book mentioned above, I
put together some simple ASP code that would allow me to display records from an MS Access
table in editable Input Boxes and save changes made by users.
Build the ASP Datasheet
Since the code is available in the download, I will not
reproduce it here, but merely describe the process.
For this demonstration I’ll be using an MS Access version of the Authors table from
the Pubs database in SQL Server. I want to create ADO Database and Recordset objects,
connect to the database and return a sorted list of all records from the Authors table.
The SQL looks like this:
SELECT * FROM Authors ORDER BY au_lName, au_fName
Now comes one of the 2 tricky parts. As you loop through the records, you use the key
field (Authors.au_id in this case) to associate each Input box with a specific record.
The ASP code below is an example of naming the Author First Name text box as “fname_”
plus the value of au_id.
<TD><input type="text"
value=”<%=rsAuthors(“au_fname”)%>”
name=”fname_<%=rsAuthors(au_id)%>”></TD>
The source that is output to the client browser looks like this:
<TD><input type="text"
value="Abraham"
name="fname_409-56-7008" ></TD>
This process is repeated for each field: ID, FName, LName, Phone, etc. until the entire row of the
recordset has been converted to a row in our ASP Datasheet. In my example, I set the ReadOnly property
to True for the Primary Key field (au_id) and it could just as easily be hidden.
Write Code to Update Edited Fields
Great! We have an ASP page of text boxes, filled with values from our MS Access database. Now we
need to add code to our page that will save all changes made by the user.
Since we only want this code to execute the second time the page is loaded (after the user has made
some edits) we will add a QueryString argument named Processed and set it’s value to 1 to indicate
that an update needs to be done.
If Request.QueryString(“Processed”)=1 Then fPerformUpdate = True
If this argument returns a value, then we know that the page has been called it’s own Submit button
and an UPDATE needs to be executed. Here is where the next trick comes in. We will use a For Each
loop to cycle through ALL text boxes looking for ID fields. Once we have found an ID field and
extracted the ID value, we will know how to address all associated fields. As illustrated above,
for the record corresponding to au_id=409-56-7008 we have a First Name box identified by the property:
name=fname_409-56-7008.
If fPerformUpdate = True ThenDim AuthorID
Dim FName, LName, Phone
Dim Address, City, State
‘ Loop through each control on the page
For Each item in Request.Form
‘ Our record ID controls have prefix ID_
If Left(item,3)=”ID_” then‘ Load variables with text box values
AuthorID = Mid(Item,4)
FName=Request.Form(“fName_” & AuthorID)
LName=Request.Form(“lName_” & AuthorID)
Phone=Request.Form(“Phone_” & AuthorID)
Address=Request.Form(“Address_” & AuthorID)
City=Request.Form(“City_” & AuthorID)
State=Request.Form(“State_” & AuthorID)‘ SQL will break on names like O’Leary
‘ if single quotes are not doubled.
LName=Replace(LName,”‘”,”””)‘ Create the SQL UPDATE statement
sql = ” UPDATE Authors SET ”
sql = sql & ” au_fName='” & FName & “‘, ”
sql = sql & ” au_lName='” & LName & “‘, ”
sql = sql & ” Phone='” & Phone & “‘, ”
sql = sql & ” Address='” & Address & “‘, ”
sql = sql & ” City='” & City & “‘, ”
sql = sql & ” State='” & State & “‘ ”
sql = sql & ” WHERE au_ID='” & AuthorID & “‘”‘ Execute the UPDATE
cnnPubs.Execute sql
End If
Next
There are 2 drawbacks to this method:
- You update ALL records, whether edited or not
- Submit ALWAYS causes the update to occur
It would, of course, be better if the posted form had a way to know which records,
if any, had been updated. That would prevent the execution of an update statement
on a record that has no changes (not to mention a server round trip) Sounds like
a project for a rainy day.
We haven’t yet shown how to ADD New records or DELETE records. That will be the
subject of Part II in this series.
