Convert Access Tables Into ASP Web Pages

I hope the following article and associated code
download
will prove to be as useful and exciting for readers of DBJ as
they have been for me.  Creating web pages to interface with
Microsoft Access databases is a natural evolution for Access developers and
there is a plethora of great tutorial information out there.

Nevertheless, I continue to get requests from my Access developer friends and
associates for a simple way to convert their databases.  I finally decided
it was time to clean up that old "table-to-ASP converter" and
make it user-friendly enough to distribute.  The result is a single
Microsoft Access form that, when imported into any database will do the
following:

1. Expose
a list of tables from which you can select one to convert to ASP
2. Creates
an ASP page based on that table’s columns that will …

  • Display a text box for every field with data size validation

  • Demonstrate how to load a record  (links to top 3 ID values)

  • Jump to the last (most recent) record

  • Writes the code for INSERTS and UPDATES

3. Opens
the ASP page in Notepad to allow you to view and edit the code
4. Supplies
a hyperlink to the newly created web page
(works only if database is located in properly configured web folder)

The output looks something like the screen shot shown below.  You see
there is a Save button to commit record edits using an UPDATE
SQL statement, a New button to create an new record and
execute an INSERT SQL statement and a Last button for
retrieving the last (most recent) record.  The code generator also inserts
several hyperlinks that demonstrate how one could allow users to
"jump" to a specific record.  There is also a link to a fully
functioning ASP page where you can test how each function works.

Check out live page
at …  http://amazecreations.com/dbj/Employee.asp 

Getting Started

There are a couple of things to keep in mind
when converting Access tables to ASP:  Location and Security.  By location,
I mean that you cannot just create an ASP page anywhere on your computer and
expect it to work.  It must be in a folder that is recognized by IIS as a
location for web applications.  The default is the  C:\InetPub\wwwRoot\ 
directory.  Any folder created under this directory will automatically be
published through the local host web site.  Therefore, if you create, for
example, a folder named SLM under your wwwRoot folder, the hyperlink to launch
a page in that folder would look something like this:  http://localhost/slm/default.asp.

This presupposes, of course, that you haven’t modified any of the default
properties for IIS, particularly the Local Path property under
the Home Directory tab of the Default Web Site
properties.  If you are not familiar with IIS, just leave this property
alone and place your folder under the wwwRoot directory.  It is beyond the
scope of this article to describe all of the different settings for IIS but
suffice it to say that you have some flexibility as to where you place your web
folders.  For the sake of this article, keep things simple and focus your
location on the wwwRoot folder.

Security is the second critical issue.  When users log onto an IIS
published web site that allows anonymous access, the system assumes the
identity of a default user named IUSR_MachineName, where MachineName is the
name of the computer running the web site.  By default, this
user does not have permission to do dangerous things like create and modify
files.  Accordingly, IUSR_MachineName cannot even open an Access database,
let alone edit or add records.  Remember that in order to open Access, a
locking file must be created and a user who does not have rights to create
files cannot create the YourDatabase.ldb file necessary to open and read a
table.

To get around this, you need to right click the web folder, select Security and
add the IUSR_MachineName user to the list of allow users, giving him read,
write and modify permissions.  Once this step is accomplished, you are
ready to begin playing with the sample code.  (see above for download)

Using the Code Generator

Now, if you are all set up correctly, you have
some web folder created under the C:\IntePub\wwwRoot directory and your folder
contains an Access mdb file with some tables ready to be published.  If that
is the case, then import the form named   fwrkGenerateASP
  into your database from the downloaded code for this article.  This
single form contains the entire code and user interface necessary to generate
an ASP page for any of your tables.  Open the form and you will see
something like this:

The process is simple and straightforward.  Select a table.  Next, if
you want to open the new ASP page in Notepad to view and or edit it then check
the associated box.  Finally, click the Write ASP button.  When
finished, the bottom-most label acts as a hyperlink to the new page, assuming it
is available on the local machine and assuming your file was created in a valid
web folder.

That is all there is to using the code generator.  It is simple and it is
fast.  It takes care of a lot of painful typing that you would have had to
do by hand and when you see the output, you will be grateful you have a utility
for writing this tedious code.  There are some down sides to this code
generator and they include:

  • The naming and formatting standards are mine.  They may not
    match your style.

  • There is no code written for deleting records.

  • Text box controls are laid out vertically, not
    horizontally.  You will have to move things around.

  • There are ways to break this page.  It is not perfect!

Yes, this code generator is not perfect … but it is free.  For years,
I have been using it to do the bulk of my typing and to make my pages
consistent.  For the sake of this article, I cleaned up the code a little
bit and did some extended testing, but there are ways to make it break, such as
using a string value for the table’s primary key.  Now, I could have
written code to handle this condition, but like I said, the tool is free and does
not claim to be the end all of ASP page generators.  Play around with it
and maybe you will come to the same conclusion as I have:  It sure is nice
to have all that text typed automatically for you.  I can live with a few
manual tweaks!

What you Get

While the actual output code is too verbose to display, the sample snippets
below give you an idea what to expect.  The page includes all the pieces
necessary to make it function, including the Option Explicit statement to
assure you that all variables are accounted for, and the On Error Resume Next
command to ensure that the code runs to completion.  Errors are returned
to the page with bold red text.

The output is, as might be expected, very regular and predictable.  It is
relatively easy to follow and easy to adapt to your needs.  Most of all, it
is tested, it works and it will save you hours of typing time.

    ‘ standard code page directive and error handler
Option Explicit
On Error Resume Next

‘ variable declarations
Dim sMsg, sErr, sMDB, sConn, vID, vLoginID, vWebPWD …

‘ code to extract user supplied values from the querystring
vLoginID = Request.Form(“txtLoginID”)
vWebPWD = Request.Form(“txtWebPWD”)

‘ code to determine which action to take
If Request(“btnSave”) <> “” Then
If vID = “-1” Then
vID = InsertNewRecord()
Else
Call UpdateRecord(vID)
End If
Else
Call RetrieveRecord(vID)
End If

‘ functions to perform each record action
Function RetrieveRecord(vID)

End Function

Function UpdateRecord(vID)

End Function

Function InsertNewRecord()

End Function

‘ the HTML to create the ASP FORM and TABLE
<FORM method=post action=Employee.asp?ID=<%=vID%>
id=frmEmployee & name=frmEmployee>
<table border=”1″ width=”440″ id=”Employee”>


It was my original intention for this article to explain the workings of all
the VBA code behind the form, fwrkGenerateASP.  However,
that code, too, is very verbose, especially with comments included, so I will
leave that to the hard-core coders.  It is actually a simple matter
of creating a DAO TableDef object and setting it to the selected
table.  Then the code loops through the fields’ collection, examining
properties and building various strings that will be written out to a
file.  Leaving out the string concatenation, it looks something like this:

    ‘ Create DAO Database and Table objects to be
used to generate the table attribute-centric code.

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(sTable)

On Error Resume Next
For Each fld In tdf.Fields
… process fields here
sText = sText & …
‘ capture information about the current field and apply
that to ASP control attributes

Next

‘ output the text string to a file
intFileNum = FreeFile
sFile = “C:\InetPub\wwwRoot\Employee.asp”
Open sFile For Append Shared As intFileNum

Print #intFileNum, sText
Close #intFileNum

Granted, this is vastly oversimplified, but if you know how the
ASP code needs to be constructed and you can read the attributes of the table,
then it is just a matter of putting all of the building blocks in the correct
place at the correct time and saving the results out to a file.  That
is what this little Access to ASP code generator does. Download
the code and give it a look. You may be glad you did.

»


See All Articles by Columnist
Danny J. Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles