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:
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
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
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.