Programming Microsoft Access 2000 (Microsoft Programming Series)
Using FrontPage, you can easily create a guestbook that collects visitor information (such as name and contact information) in an HTML-formatted web page. This approach requires no knowledge of databases or open database connectivity (ODBC) connections. Site visitors see a professional-looking form in which they can enter contact information, and FrontPage users have a simple way to create and view the guestbook. This approach works well as long as the site does not attract a high volume of visitors.
If visitor volume swells, you can transfer the complete set of contact information to a database file. Even if a site switches to depositing the data directly into a database, you still have the problem of recovering the initial HTML-formatted information. This case study illustrates one approach to recovering legacy text data and demonstrates how to generate mailing labels and form letters with the converted information.
Importing Data
You can use the Text Import wizard to copy the raw guestbook file into an Access table. Figure 6-3 shows excerpts displaying two records from the raw HTML-formatted file (for Karl Doe1 and Boban Doe2). Notice the huge number of HTML tags. The internal FrontPage browser uses these tags to format the guestbook display, but the tags make it impossible to directly import the data into an Access table for the preparation of mailing labels. All the contact information appears in one long column. Some rows contain contact data, but other rows hold descriptive labels or serve general formatting purposes. Relational database processing conventions dictate a separate row for each guestbook registrant. This requires spreading the information that appears for one registrant in a single column across several columns.
Figure 6-3. Excerpts from a FrontPage guestbook file imported into an Access table.
Whether or not you encounter this precise data conversion requirement, Access development typically requires the processing of a text stream. Large businesses often must convert legacy departmental data to a relational database format using a similar process. The rich array of string processing functions and Microsoft Visual Basic for Applications (VBA) in Access help you automate this kind of task.
Converting the Data Structure
The goal of this string processing effort is to transform the data in a table similar to the Temp1 table in Figure 6-3 into a more traditional contact table layout, such as that of the table shown in Figure 6-4. The strategy defines two recordsets—one for the source table and one for the destination table. The procedure moves down the rows of the source table to extract the contact information and store the parsed data in variables. After parsing all the essential data for a contact, it adds the parsed data as a new row to the destination table. Because different contact fields have unique requirements, the parsing rules sometimes vary between fields. Rules can also vary because the raw data for a field represents unique problems not present for other fields.
The following excerpt from the procedure transforms the data from the format of the data in the table in Figure 6-3 to the format of the data in the table in Figure 64 (on the next page). The complete version is on the book's companion CD. This abbreviated version shows the conversion code for the first three fields represented by the variables strFname, strLname, and strCname. While the listing is lengthy and the complete version is even longer, converting data is a critical first step for Access database projects. If you don't successfully address this critical issue, your Access project might not start.
Figure 6-4. Converted contact data from a FrontPage guestbook file in HTML format. The conversion removes HTML tags and places each contact record on a separate row.
| Sub getfp() Dim cnn1 As New ADODB.Connection Dim rst1 As ADODB.Recordset Dim strFname As String, strLname As String Dim strCname As String, strSt1 As String Dim strSt2 As String, strCity As String Dim strSt As String, strPostal As String Dim strCountry As String, blSkip As Boolean Dim rst2 As New Recordset 'Open two recordsets and set references to them. cnn1 = CurrentProject.Connection Set rst1 = New Recordset rst1.ActiveConnection = CurrentProject.Connection rst1.CursorType = adOpenKeyset rst1.LockType = adLockOptimistic 'Raw contact information is in table temp1. rst1.Open "temp1" rst2.ActiveConnection = CurrentProject.Connection rst2.CursorType = adOpenKeyset rst2.LockType = adLockOptimistic 'The application stores parsed contact info in the WebBasedList table. rst2.Open "WebBasedList" 'Start a loop through the recordset of raw contact information. Do Until rst1.EOF blSkip = False 'Start a new contact record when you find 'a label named "SiteEvaluation_FirstName:". If InStr(1, rst1.Fields(1), _ "SiteEvaluation_FirstName:") <> 0 Then rst1.MoveNext If rst1.Fields(1) <> " <dd> </dd>" Then 'The length of the first name field is the number of 'characters between ">" and "<" delimiters. intFirst = InStr(1, rst1.Fields(1), ">") + 1 intLen = InStr(6, rst1.Fields(1), "<") - intFirst strFname = Mid(rst1.Fields(1), intFirst, intLen) 'Move two records to process last name field. rst1.Move 2 Else 'If the first name is blank, set a Boolean flag 'to skip the whole record. blSkip = True End If 'Process last name field. intFirst = InStr(1, rst1.Fields(1), ">") + 1 intLen = InStr(6, rst1.Fields(1), "<") - intFirst strLname = Mid(rst1.Fields(1), intFirst, intLen) 'Process company name field. rst1.Move 2 If rst1.Fields(1) <> " <dd> </dd>" Then intFirst = InStr(1, rst1.Fields(1), ">") + 1 intLen = InStr(6, rst1.Fields(1), "<") - intFirst 'If there is a leading blank in the company name field, 'see if you can find the name after the blank. If InStr(2, rst1.Fields(1), " ") <> 0 Then intLen = InStr(6, rst1.Fields(1), " ") _ - intFirst End If 'The parsing rule for the company name field converts with the new 'VBA Replace function html's " into a single apostrophy. strCname = Replace(Mid(rst1.Fields(1), _ intFirst, intLen), """, "'") Else 'Set company name to zero-length string if there is no 'entry for the field. strCname = "" End If |
Each of the three converted fields relies on slightly different parsing logic. There is more consistency in the remaining fields. Nevertheless, several significant distinctions in parsing rules between alternative fields remain. You can read the comments and study the code to gain insights about VBA functions useful for string processing. After conversion, the code checks to make sure that any field conversion did not set the skip flag to True. A False value for the skip flag enables the procedure to add a new record to the relational contact table in the format shown in Figure 6-4. The procedure then moves on to a new record in the source table (the copy of the FrontPage guestbook). When the original source table returns its end of file (EOF) as True, the outer Do loop ends and the procedure halts.
Creating Mailing Labels
Figure 6-5 shows the Design view of a mailing label report and an excerpt from the Preview display of the mailing labels. These draw on the contact information shown in Figure 6-4.
Access ships with a graphical mailing label wizard that can accommodate multiple label sizes for laser and tractor-feed printers from many manufacturers, including Avery, EXPE, Herma, and Zweckform. The wizard also lets you define new form sizes. It removes the need to write string-processing expressions for the construction of labels. The top window in Figure 6-5 shows the label fields expanded longer than they would otherwise appear so you can see the complete expressions within them. The wizard automatically sizes the text boxes containing its string expressions so that the labels fit on the form.
Figure 6-5. The top window shows a mailing label report in Design view. The bottom window shows an excerpt of the labels in Preview mode.
Creating a Form Letter
Another typical use for a table of contacts is for use in a form letter. Using a mix of controls, string constants and variables, and custom VBA functions, you can create a form letter such as the one depicted in Figure 6-6 on the following page, which features several noteworthy Access report features:
- A logo heads the letter.
- The return address information appears in a different font than the body of the letter.
- The letter's date spells the month's name.
- The outgoing address and salutation change for each record.
- The letter's final paragraph also changes for each record.
- The letter's closing appears with a signature.
Figure 6-6. Excerpts from a form letter.
While it was never a programmatic challenge to convert a month number to a month name (such as converting 1 to January), Microsoft has simplified this task further with Access 2000 and VBA 6. The text box showing the date reveals the innovation and one way to use it. It contains the following string expression:
| =ThisMonthName() & " " & Day(Date()) & ", " & Year(Date()) |
The expression contains a pair of nested built-in functions for the day and the year, but a custom function (ThisMonthName) returns the month's name. Below is a custom VBA 6 function, MonthName, that convert's a month's number to its matching name. This function relieves you from having to code a Select Case statement in a function procedure or invoke a Choose function to make a month name appear in a report.
| Public Function ThisMonthName() ThisMonthName = MonthName(Month(Date)) End Function |
You might wonder why you couldn't apply the MonthName function directly to the text box within the report. Doing so would generate an error because MonthName is not an Access function. You have to call it in a procedure and then return the result to an Access object such as a text box on a form.
Figure 6-7 presents the Design view of the form letter report showing a mix of label and text box controls. If the body of the letter includes no customization for each record, a simple label can display all the text. However, since the last paragraph starts with the contact's first name, the report needs a way to recover the FirstName field value. A string expression inside a text box is an easy way to accomplish this. The bottom portion mixes the field value with the string constant. You can tell that you have to experiment with the placement of the text box relative to the preceding label control. To have the text flow nicely in Preview mode, you must overlap the controls in Design view. Proper alignment can require some trial and error.
Figure 6-7. The bottom portion of the letter in Figure 6-6 in Design view.
Note the use of a script font with a label control to simulate a signature. This is a viable option when you don't need an actual signature—for example, for large mailings such as form letters.