Exporting an Access object to static Web pages is quick and easy, but if you want to manipulate the data as you create the static pages, the export process won’t be of much use. A better way is to create a module containing VBA code that manipulates the data.
As an example, suppose The Garden Company wants to use data in the Customers table to create a set of 26 Web pages, one for each letter of the alphabet. Each page will list the customers whose last names start with the correlating letter and will contain links to all the other pages in the set. All the legwork for setting up the pages can be done with VBA code.
The sample GardenCo database for this exercise includes HTML_final, a finished and fully commented version of the HTML module you will be creating. If you don’t want to type the code as instructed, you can copy it from each Step# module provided in the sample database, paste it into your new HTML module, and then delete the comment from the copied code.
Tip
The more complex a program is, the more ways there are to write it. This exercise doesn’t pretend to illustrate the best programming methods and doesn’t take the time to explain every code detail. You can learn more about each VBA command by clicking it and then pressing 
to read about it in Access online Help.
In this exercise, you will follow the typical programming process of writing a module in stages, testing each stage before moving on to the next. In the first stage, you will write code to open the database and look at each record in the Customers table. In the second stage, you will figure out how to spot the change in the first letter of each person’s last name. In the third stage, you will open a new text file for each letter and add some HTML code to it. In the fourth and final stage, you’ll do some housekeeping and close all the files.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSPgsModsVBA folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the Objects bar, click Modules.
On the database window’s toolbar, click the New button.
The Visual Basic Editor opens, with a new module highlighted in the Project Explorer.
On the Visual Basic Editor toolbar, click the Save GardenCo button, name the module
HTML, and click OK.Click in the Code window to place the insertion point there.
On the Insert menu, click Procedure to display the Add Procedure dialog box.
In the Name box, type
createHTML, and then click OK to accept the default settings.A new sub procedure is inserted in the Code window.
If Option Explicit isn’t in the Declarations section at the top of the Code window, position the insertion point at the end of Option Compare Database, press
, type Option Explicit, and press again.Any variables now must be declared before running the program. If they aren’t, the program won’t run, and you will have to stop and declare the variables.
Tip
To have the editor add Option Explicit to every new module, on the Tools menu, click Options. On the Editor tab of the Options dialog box, click Require Variable Declaration. It is a good idea to select all the options on this tab. Press the
key with the tab visible to read information about the options.Click the empty line below Public Sub, press the
key, and then either copy and paste the following lines from Step08, or type them, pressing at the end of each line:Dim con As Object Dim rs As Object Dim stSql As String Dim firstRec As Boolean Dim activeDir As String Dim curWord As String Dim curLtr As String Dim oldLtr As String Dim skipLtr As String Dim qt As String Dim i As Integer
Important
If you want to copy and paste a code block from the Step# modules, double-click the module name in the Project Explorer, select the code in the Code window, press
, click the Code window to activate it, position the insertion point where you want the copied code to appear, and press 
. Close the Step# Code window. You will then need to remove the comments so that the code will be run with the rest of the procedure. To do this, right-click a blank area on the Visual Basic Editor’s toolbar, click Edit to display the Edit toolbar, select the commented code block, and click the Uncomment Block button. Then make any necessary adjustments to the indents to make the lines match what you see in the instructions.The Dim (dimension) statement declares all the variables you will use in this procedure. You would normally do this as you found a need for each variable.
Press
twice to leave a couple of blank lines. (You will add code here later.) Then either copy and paste the following lines from Step09, or type them, pressing at the end of each line:qt = Chr(34) Set con = Application.CurrentProject.Connection stSql = "SELECT * FROM [Customers] ORDER BY LastName" Set rs = CreateObject("ADODB.Recordset") rs.Open stSql, con, 1 ' 1 = adOpenKeysetThe first line sets the value of the variable qt to represent a quotation mark. You use this trick to print a quotation mark, because simply typing the quotation mark in the code would be interpreted as part of the code. The rest of the lines connect to the current database and run a query that selects all records from the Customers table, sorted by LastName.
Tip
When you press
after typing a line of code, the line is analyzed, its syntax is checked, and all variable names and keywords are set to the appropriate capitalization style. If you get in the habit of declaring variables with mixed case and typing all code in lowercase, you will be able to spot typos more easily.Add a few more blank lines, and then copy and paste the following lines from Step10, or type them. (Press
to indent the second line, and press the 
key to remove the indent before typing the last line.)Do Until rs.EOF curWord = rs!LastName Debug.Print curWord rs.MoveNext LoopThis segment (called a Do...Loop statement) opens the first record in the Customers table, sets the variable curWord to the value of the LastName field, prints the value of curWord, and then moves to the next record. This set of steps is repeated until the last record is printed.
Add a few more blank lines, and copy and paste the following lines from Step11, or type them:
rs.Close Set rs = Nothing Set con = Nothing
These lines close the database and free up the object variables by disassociating them from the actual objects, which in turn frees up memory and system resources.
On the View menu, click Immediate Window.
The Immediate window is displayed below the Code window. You can use the Immediate window to test a line of code or to change the value of a variable. In this case, you will use it as an output window to view the results of the Debug.Print command.
Click anywhere within your sub procedure, and then click the Run Sub/UserForm button on the toolbar.
The procedure runs, and the last name of each customer from the Customers table is printed in the Immediate window.
Click in the Immediate window, press
to select all its content, and press the 
key to delete the selection.Click your code to shift the focus to the procedure, and press
to begin stepping through the code.The first line of the sub procedure is highlighted in yellow, indicating that it will be the next line of code processed.
The highlight skips the Dim statements and moves to the qt = Chr(34) line.
Continue pressing
and watching the highlight until it has passed Loop and returned to Do Until rs.EOF.This loop is the core of the program. It will execute one time for each record in the table. The first time through, it sets curWord to the value of the last name in the first record of the table, prints that value in the Immediate window, and then moves to the next record.
Hold the pointer over curWord for a few seconds, and then do the same over rs!LastName.
The current value of the variable is displayed in a ScreenTip.
Press
again, and check the values displayed on both sides of the expression on the line above.The values are the same.
Press
to finish running the procedure.Your procedure now opens the database and gets the last name from each record in the Customers table.
Replace the Debug.Print line in your code with the following code, by either copying and pasting it from Step22 or typing it:
curLtr = UCase(Left(curWord, 1)) If curLtr <> oldLtr Then 'we have a new letter Debug.Print curLtr oldLtr = curLtr End IfThe first line changes the leftmost character of curWord to uppercase if it isn’t already, and sets it as the value of curLtr. The If statement compares the value of curLtr to oldLtr. (At this point oldLtr will be blank, because its value hasn’t been set.) If the value is different, it is printed, and then oldLtr is set to the current value of curLtr; otherwise, the flow of code passes to the next line after the If statement, which moves to the next record.
The letters of the alphabet are printed in the Immediate window, below the list of customer names. If you scroll through the window’s contents, you will see that several letters are missing, not because something is wrong with your code, but because no customers have last names beginning with those letters.
In the blank lines you left below the last Dim statement, copy and paste these lines of code from Step24, or type them:
activeDir = Application.CurrentProject.path If Dir(activeDir & "html_pages", vbDirectory) = "" Then MkDir activeDir & "html_pages" End IfThe first line sets the value of activeDir to the path of the folder (or directory) containing the database. The If statement checks to see if that directory has a subdirectory named html_pages in which to store the HTML pages you create. If it doesn’t, the MkDir command creates the subdirectory.
Insert a line above the Do...Loop statement, and then copy and paste this line from Step25, or type it:
firstRec = True
You need to differentiate between the first record of a letter and all remaining records. Variables such as firstRec are often referred to as flags that can be set to true or false.
Delete the Debug.Print line above the line that compares oldLtr to curLtr.
Click at the end of the line that compares oldLtr to curLtr, press
to insert a new line, press to indent, and copy and paste the following code from Step27, or type it:If Not firstRec Then 'end the previous page Print #1, "</body>" Print #1, "</html>" Close #1 Else firstRec = False End If Open activeDir & "html_pages" & "cust" & curLtr & ".htm" _ For Output As #1 Print #1, "<html>" Print #1, "<head><title>Alphabetical List of Customers "_ & "– " & curLtr & "</title></head>" Print #1, "<body bgcolor=yellow link=red>" Print #1, "<h1>Alphabetical List of Customers –" _ & " " & curLtr & "</h1>" Print #1, "<br>" Print #1, "<br>"Important
In the previous step, and in some of the following steps, lines of code have been broken to fit the width of this book. When you break a line of code in the Visual Basic Editor, use a space followed by an underscore.
This code checks to see if the record being processed is the first one: if not, it writes the tags to close out the previous HTML file. It then opens a new HTML file and writes tags to it.
Insert a line above rs.MoveNext, and copy and paste from Step28, or type:
Print #1, "<p>" & rs!FirstName & " " & rs!LastName
This creates a paragraph in the HTML file containing the first and last names of the customer, separated by a space.
Insert a line after Loop, and then insert this code from Step29, or type it:
Print #1, "<body>" Print #1, "</html>" Close #1
This adds the closing HTML tags to the last file, and closes it.
Click the Save button, and then click the Run Sub/UserForm button to run the program, which will create a series of HTML files in a new folder called html_pages in the working folder for this exercise. It should take only a few seconds.
In Windows Explorer, browse to the html_pages folder in the working folder.
The folder contains a series of HTML files, one for almost every letter of the alphabet.
Double-click custA to open it in your browser.
View the HTML source code for the page. (If you are using Internet Explorer, right-click the body of the page, and click View Source.)
Your VBA code wrote the HTML tags and database information to the file.
Close the source window and your browser, and then return to the Visual Basic Editor.
Insert a line between Print #1, "<br>" and End If, if necessary, and copy and paste the following code from Step35, or type it:
For i = 65 To 77 Print #1, "<font color=" & qt & "purple" & qt & "size=+1><a href=" _ & qt & "cust" & Chr(i) & ".htm" & qt & ">" & Chr(i) & _ "</a> | </font>" Next i Print #1, "<br>" For i = 78 To 90 Print #1, "<font color=" & qt & "purple" & qt & "size=+1><a href=" _ & qt & "cust" & Chr(i) & ".htm" & qt & ">" & Chr(i) & _ "</a> | </font>" Next i Print #1, "<p>"This code prints the letters A through M on one row at the top of each page, and N through Z on the next row. Each letter is a link to the HTML page for that letter.
Save your changes, and run the program again. Then return to the html_files folder, and open custB to display the Web page.
Click F to jump to the page containing last names starting with F.
The appropriate page is displayed.
Click X.
An error tells you that the page you requested cannot be displayed. No customers have a last name starting with X, so your VBA code didn’t create a page for it. If you were going to release these pages to the public, you would want to modify the code so it either created blank pages for the missing letters, or didn’t include a letter in the header if there were no names starting with that letter.
Close your browser, and close the Visual Basic Editor.
CLOSE the GardenCo database.