A database application is not complete until you have prepared professional-grade documentation for each element of the application. The "Visual Basic 4.0" qualifier is missing from the preceding truism of professional database developers
because the statement applies to all database applications created with any front-end development tool or any programming language. This chapter prescribes the following three basic documentation elements for all database applications:
The major sections of this chapter deal sequentially with each of the documentation elements in the preceding list.
Data dictionaries are the single most important element of database documentation. At the very least, data dictionaries include the following components:
The sections that follow describe writing your own Visual Basic 4.0 application to create a data dictionary, using a graphical design tool to create a Jet database (including a data dictionary for the database), and obtaining table data from Access
95's Documentor add-in application to create data dictionaries for Jet databases. The emphasis of this chapter is on the .mdb database format because the Jet database structure is the most likely to be used in the majority of new Visual Basic 4.0
applications.
It is relatively easy to write a Visual Basic 4.0 database application to create a fully formatted data dictionary as a text file that you can import into Word for Windows or any another word processing or spreadsheet application. It is an even simpler
process if you have created (or have access to the source code for) a data dictionary generator for Jet databases that relies primarily on Access VBA code.
The sample data dictionary application, Vb4dd.vbp, that is described in the following sections is derived from the Access 2.0 DataDict.mdb application that appears in Chapter 27, "Writing Documentation for Access Applications," of this
author's book Access 2 Developer's Guide (Sams Publishing). The Access 2.0 application has been updated to include the minor revisions needed to port the DataDict.mdb application to Visual Basic 4.0.
Vb4dd.vbp and DataDict.mdb each create the following two text files:
Database is a placeholder for the name of the .mdb file from which the data dictionary is created. Database.txt and Database.qry are created in the directory where Database.mdb is located.
Figure 25.1. The Northwind.txt data dictionary file imported and parsed into an Excel 7.0 worksheet.
Figure 25.2. The Northwind.qry query definition file imported into a Word 7.0 table.
The self-contained Access 95 application, DataDict.mdb, and the project files for Vb4dd.vbp are included on the accompanying disk and are located in your \Ddg_VB4\32_bit\Chaptr25 folder (provided that you installed the files from the accompanying disk in the Setup application's default folder). The Vb4dd.vbp project shares the Table and QueryDef objects in DataDict.mdb with the Access 95 version of this application. If you installed the Chaptr25 files elsewhere, you'll need to change references in the Vb4dd.bas module that point to the location of DataDict.mdb.
The Vb4dd.vbp project was created by saving the Access VBA code to a text file and then loading this file into a new module, Vb4dd.bas, of a new Visual Basic project. The revisions needed to port the Access VBA code to Visual Basic 4.0 are noted by
comments that begin with "Rev 2.0." You can search for the revisions in Vb4dd.bas to view the necessary alterations. Following are the types of modifications that are required to port the Access VBA code to Visual Basic 4.0:
No fancy accoutrements, such as progress metering gauges, are added to Vb4dd.vbp because one of the purposes of this sample application is to demonstrate the ease with which Access applications that rely primarily on Access VBA code can be moved to Visual Basic 4.0.
To run Vb4dd.vbp, follow these steps:
If you receive an Error Opening File message, open the database in Access and make sure that the Admin user (or your user ID) has read permissions on all system tables in the database.
Figure 25.4. A message box that indicates a user ID and password are required.
Figure 25.6. The Northwind.txt data dictionary opened in Notepad.
If you have Access 95, you can run the Access VBA equivalent of Vb4dd.vbp by following these steps:
Figure 25.7. The Debug window function call to run the Access Data Dictionary application.
Figure 25.8. The tblFields table created by either the Access or the Visual Basic Data Dictionary application.
You can change the format of the text files created by Vb4dd.vbp to increase the levels of indentation by adding tab characters, revising headings, or adding more information. Listing 25.1 shows the Visual Basic 4.0 code for the CreateDataDict
subprocedure that controls the execution of the application, and the CreateTextFile and CreateQueryText subprocedures that use the low-level binary file instructions of Visual Basic to create the two text files.
A full description of the purpose and operation of each of the subprocedures in Vb4dd.vbp and in the Data Dictionary module of DataDict.mdb appears in the aforementioned book Access 2.0 Developer's Guide.
Listing 25.1. Code for the CreateDataDict, CreateTextFile, and CreateQueryText subprocedures.
Sub CreateDataDict(strDBName As String)
'Purpose: Create a data dictionary for the strDBName database
'Argument: Path and file name of source database as a literal
'Uses: AddTable and AddQuery procedures
'Note: All database objects are module-level variables
Dim wTableNum As Integer 'table number
Dim wQueryNum As Integer 'query number
'Rev 2.0 1/27/96 MH Added error-handling
On Error GoTo FatalDictError
If Len(strDBName) > 0 Then
'Open the data dictionary database
Set dbDataDict = OpenDatabase( _
"c:\ddg_vb4\32_bit\Chaptr25\datadict.mdb", _
False, False, "")
'Open the source database being analyzed
Set dbCurrent = OpenDatabase(strDBName, False, False, "")
'Rev 2.0 1/27/96 MH Added simple progress monitor
frmVB4DD.lblDBName.Caption = dbCurrent.Name
frmVB4DD.txtProgress.Text = ""
Else
MsgBox prompt:="Error: No source database name.", _
buttons:=vbCritical, _
Title:="Create Data Dictionary Error"
Exit Sub
End If
'Newline character (removed from SQL statement)
strCRLF = Chr(13) & Chr(10)
'Create an array of the names of the four tables created
ReDim rgstrTableList(5)
rgstrTableList(1) = "tblTables"
rgstrTableList(2) = "tblQueries"
rgstrTableList(3) = "tblFields"
rgstrTableList(4) = "tblIndexes"
'Delete the current entries in each of the tables
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'vRetVal = SysCmd(1, "Deleting records of existing tables", 4)
'Rev 2.0 1/27/96 MH Added simple progress monitor
With frmVB4DD.txtProgress
.Text = .Text & "Deleting records." & strCRLF
End With
For wCtr = 1 To 4
Set rstTemp = dbDataDict.OpenRecordset(rgstrTableList(wCtr), _
dbOpenTable)
If rstTemp.RecordCount > 0 Then
rstTemp.MoveFirst
BeginTrans
Do While Not rstTemp.EOF
rstTemp.Delete
rstTemp.MoveNext
DoEvents 'Safety
Loop
CommitTrans
End If
rstTemp.Close
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'vRetVal = SysCmd(2, wCtr)
Next wCtr
'Open the data dictionary tables
Set rstTables = dbDataDict.OpenRecordset(rgstrTableList(1), _
dbOpenTable)
Set rstQueries = dbDataDict.OpenRecordset(rgstrTableList(2), _
dbOpenTable)
Set rstFields = dbDataDict.OpenRecordset(rgstrTableList(3), _
dbOpenTable)
Set rstIndexes = dbDataDict.OpenRecordset(rgstrTableList(4), _
dbOpenTable)
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'vRetVal = SysCmd(1, "Creating Data Dictionary", _
(dbCurrent.TableDefs.Count + dbCurrent.QueryDefs.Count))
wTableNum = 1
For wCtr = 1 To dbCurrent.TableDefs.Count
Set tdfTable = dbCurrent.TableDefs(wCtr - 1)
If InStr(tdfTable.Name, "MSys") = 0 Then
'Add records for tables
AddTables wTableNum
wTableNum = wTableNum + 1
End If
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'vRetVal = SysCmd(2, wCtr)
DoEvents 'Safety
Next wCtr
wTableNum = wTableNum - 1
For wCtr = 1 To dbCurrent.QueryDefs.Count
Set qdfQuery = dbCurrent.QueryDefs(wCtr - 1)
'Add records for queries
AddQueries wCtr
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'vRetVal = SysCmd(2, wCtr + wTableNum)
DoEvents 'Safety
Next wCtr
'Create the tab-delimited text file for Excel or Word for Windows
CreateTextFile strDBName
'Create the query text file
CreateQueryText strDBName
'Close everything
rstTables.Close
rstQueries.Close
rstFields.Close
rstIndexes.Close
dbCurrent.Close
dbDataDict.Close
'Reset the status bar
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'vRetVal = SysCmd(5)
'Rev 2.0 1/27/96 MH Added simple progress monitor
With frmVB4DD.txtProgress
.Text = .Text & "Data Dictionary Complete." & strCRLF
End With
Exit Sub
FatalDictError:
MsgBox prompt:="Error: " & Err.Number & Chr(13) & Err.Description, _
buttons:=vbCritical, Title:="Create Data Dictionary Error"
'reset the Jet database engine
Set DBEngine = Nothing
End Sub
Sub CreateTextFile(strTextFile As String)
'Purpose: Create a text data dictionary file
'Argument: Name of database file (used as text file name)
'Called by: CreateDataDict()
Dim strWrite As String
Dim strIndex As String
'Create the file name (database.TXT) in the same directory
strTextFile = Left(strTextFile, Len(Trim(strTextFile)) - 3) & "txt"
'Delete any prior version of this file
On Error Resume Next
Kill strTextFile
On Error GoTo 0
'Open the file for sequential (line-by-line) output
Open strTextFile For Output As #1
rstFields.Index = "Index1"
rstIndexes.Index = "Index1"
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'Set progress indicator
'rstTables.MoveLast
'vRetVal = SysCmd(1, "Creating text file", rstTables.RecordCount)
'Rev 2.0 1/27/96 MH Added simple progress monitor
With frmVB4DD.txtProgress
.Text = .Text & "Creating text file." & strCRLF
End With
'Rev 2.0 1/27/96 MH Added version reporting
'State the Jet version number
strWrite = Chr(9) & "Jet Database v" & dbCurrent.Version
Print #1, strWrite
wCtr = 1
'Create the records for each table using legal-style outlining
rstTables.MoveFirst
Do While Not rstTables.EOF
'Create header entry for table
strIndex = "1." & LTrim(Str(rstTables!Sequence))
strWrite = strIndex & Chr(9) & "Table: " & rstTables!Name
strWrite = strWrite & Chr(9) & "Type: " & rstTables!TableType
If Not IsNull(rstTables!Fields) Then
'Records from attached databases may not contain this entry
strWrite = strWrite & Chr(9) & "Fields:" & Str(rstTables!Fields)
End If
If (IsNull(rstTables!RecordCount) Or rstTables!RecordCount = -1) Then
strWrite = strWrite & Chr(9)
Else
'Records from attached databases may not contain this entry
strWrite = strWrite & Chr(9) & "Records:" & _
Str(rstTables!RecordCount)
End If
'Add source and connection data, if applicable
If Not IsNull(rstTables!SourceTable) Then
'Only attached tables have a SourceTable entry
If Len(rstTables!SourceTable) > 0 Then
strWrite = strWrite & strCRLF & Chr(9) & Chr(9) & _
"Source: " & rstTables!SourceTable
End If
If Not IsNull(rstTables!Connect) Then
If Len(rstTables!Connect) > 0 Then
strWrite = strWrite & Chr(9) & "Connect: " & rstTables!Connect
End If
End If
End If
'Add the description, if it exists
If Not IsNull(rstTables!Description) Then
If Len(rstTables!Description) > 0 Then
strWrite = strWrite & strCRLF & Chr(9) & Chr(9) & _
"Description:" & rstTables!Description
End If
End If
'Add the table record to the file
Print #1, strWrite
'Add the field data for the record
rstFields.Seek "=", rstTables!Name, 1
If Not rstFields.NoMatch Then
'Add a header for the field values
strWrite = Chr(9) & strIndex & ".1 Fields" & _
Chr(9) & "Field Name" & Chr(9) & "Field Type" & _
Chr(9) & "Description"
Print #1, strWrite
'Add the individual lines for each field
Do While Not rstFields.EOF
'This structure is required to inhibit "No Current Record" errors
If rstFields!TableName <> rstTables!Name Then
Exit Do
End If
strWrite = Chr(9) & strIndex & ".1." & _
LTrim(Str(rstFields!Sequence))
strWrite = strWrite & Chr(9) & rstFields!Name & _
Chr(9) & rstFields!FieldType
If rstFields!Name <> rstFields!SourceField Then
strWrite = strWrite & Chr(9) & "Source: " & _
rstFields!SourceField
End If
If Not IsNull(rstFields!Description) Then
strWrite = strWrite & Chr(9) & rstFields!Description
End If
Print #1, strWrite
rstFields.MoveNext
Loop
End If
'Add the index data for the record
rstIndexes.Seek "=", rstTables!Name, 1
If Not rstIndexes.NoMatch Then
'Add a header for the indexes
strWrite = Chr(9) & strIndex & ".2 Indexes" & _
Chr(9) & "Index Name"
strWrite = strWrite & Chr(9) & "Fields" & Chr(9) & "Properties"
Print #1, strWrite
'Add the entries for the indexes
Do While Not rstIndexes.EOF
If rstIndexes!TableName <> rstTables!Name Then
Exit Do
End If
strWrite = Chr(9) & strIndex & ".2." & _
LTrim(Str(rstIndexes!Sequence))
strWrite = strWrite & Chr(9) & rstIndexes!IndexName
strWrite = strWrite & Chr(9) & _
LTrim(Str(rstIndexes!FieldCount)) & Chr(9)
If rstIndexes!Clustered Then
strWrite = strWrite & "Clustered, "
End If
If rstIndexes!Primary Then
strWrite = strWrite & "Primary, "
End If
If rstIndexes!Foreign Then
strWrite = strWrite & "Foreign, "
End If
If rstIndexes!Unique Then
strWrite = strWrite & "Unique, "
End If
If rstIndexes!Required Then
strWrite = strWrite & "Required, "
End If
If rstIndexes!IgnoreNulls Then
strWrite = strWrite & "Ignore Nulls, "
End If
strWrite = Trim(strWrite)
If Right(strWrite, 1) = "," Then
strWrite = Left(strWrite, Len(strWrite) - 1)
End If
Print #1, strWrite
rstIndexes.MoveNext
Loop
End If
rstTables.MoveNext
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'vRetVal = SysCmd(2, wCtr)
wCtr = wCtr + 1
Loop
'Close the text file
Close #1
End Sub
Sub CreateQueryText(strTextFile As String)
'Purpose: Create a text query list with SQL statements
'Argument: Name of database file (used as query file name)
'Called by: CreateDataDict()
Dim strWrite As String
Dim strIndex As String
'Create the query file name (database.QRY) in the same directory
strTextFile = Left(strTextFile, Len(Trim(strTextFile)) - 3) & "qry"
'Delete any prior version of this file
On Error Resume Next
Kill strTextFile
'Rev 2.0 1/27/96 MH Added error-handler
On Error GoTo QryFileError
'Open the file for sequential (line-by-line) output
Open strTextFile For Output As #1
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'Set progress indicator
'rstQueries.MoveLast
'vRetVal = SysCmd(1, "Creating query file", rstQueries.RecordCount)
wCtr = 1
'Create the records for each table using legal-style outlining
rstQueries.MoveFirst
Do While Not rstQueries.EOF
'Create header entry for table
strIndex = "1." & LTrim(Str(rstQueries!Sequence))
strWrite = strIndex & Chr(9) & "Query: " & rstQueries!QueryName
strWrite = strWrite & " Type: " & rstQueries!QueryType
strWrite = strWrite & " Created: " & rstQueries!DateCreated
'Add the query record to the file
Print #1, strWrite
'Add the SQL statement to the file
strWrite = strIndex & ".1" & Chr(9) & "SQL Statement: " & _
rstQueries!SQLStatement
Print #1, strWrite
'Skip a line
Print #1, ""
rstQueries.MoveNext
'Rev 2.0 1/27/96 MH No SysCmd in VB4
'vRetVal = SysCmd(2, wCtr)
wCtr = wCtr + 1
Loop
'Rev 2.0 1/27/96 MH Added error-handler
QryFileError:
'Close the text file
Close #1
End Sub
The Description property is only available in Table and Field objects in Jet database versions 2.0 and higher. Because the Jet 3.0 database engine permits you to open Jet databases in any format from 1.0 and higher, some code was added in Vb4dd.vbp to prevent errors if you use Vb4dd.vbp to build a data dictionary for Jet 1.0 or Jet 1.1 databases. (Which you might want to do if you need to update or convert older databases for which a data dictionary isn't available.)
Excel 7.0's Text Import Wizard was used to create the Excel 7.0 worksheet from the Northwind.txt file created by Vb4dd.vbp. (See Figure 25.1 near the beginning of this chapter.) The three windows of the Text Import Wizard are shown in Figures 25.10,
25.11, and 25.12. If you choose the Tools | Record Macro menu command in Excel 7.0 when you first launch Excel 7.0, you can create a Visual Basic for Applications (VBA) subprocedure that you can later modify so that you can call the
subprocedure using the OLE Automation statements and functions of Visual Basic 4.0. Alternatively, you can create an Excel 7.0 dialog to enter the name of the text file to import.
To record an Excel 7.0 VBA macro while using the Text Import Wizard to create a worksheet from Northwind.txt or any other Database.txt data dictionary file, follow these steps:
Figure 25.13. The VBA subprocedure created by recording the steps to import a text file.
You can pass the path and the name of the file to the subprocedure as arguments when you call ImportDataDict after your Visual Basic application launches Excel 7.0 through either an early-binding reference to the Excel object library, or with a
CreateObject instruction. Chapter 15, "Using OLE Automation with Productivity Applications," provides additional information on manipulating Excel 7.0 objects with Visual Basic 4.0 applications.
Access 95 includes a Documentor feature that creates a data dictionary report for a database and any or all of the objects in the database. You can use the Documentor to generate a data dictionary report for any table, query, form, report, macro, or
module in a database. You can generate a Documentor data dictionary report for any single object in the database, any combination of selected objects in the database, or for all of the objects in the database. You can tailor the level of detail in the
Documentor report to include all of the available information for the objects, or only selected information. Like other Access 95 reports, you have the option of exporting the completed report to an .rtf (Rich Text Format) file for later importation into
Word for Windows, or you can export the completed Documentor report to an .xls file for use with Microsoft Excel.
The Access 95 Database Documentor creates its report "on the fly"; it does not create permanent documentation tables (or any other object) in the database being documented. When you execute the Database Documentor, the sole result is the database documentation report you see previewed onscreen. You may print the report and/or use the Microsoft Office links to export the report to an .rtf or .xls file. If you want to preserve a copy of the Documentor report in electronic form, be sure to export it to an .rtf or .xls file format.
Access 95's Documentor offers a great deal of flexibility regarding the information you choose to include in your report. You can use the Database Documentor to obtain complete or partial information about the database object itself, as well as any
object in the database: tables, forms, reports, queries, macros, or modules. The following list names all of the objects you can choose to include in a Database Documentor report and summarizes the available reporting options:
As you can see, the Access 95 Database Documentor permits you to obtain a complete report of any database object—including stored code belonging to forms, modules, and macros. To use the Access 95 Database Documentor, follow these steps:
The specific Print Definitions dialog that appears when you click the Options button of the Database Documentor dialog (Figure 25.14) depends on whether you have selected a table, query, form, report, macro, or module in the Objects list of the Database Documentor dialog. The specific Print Definitions dialog that appears will contain only the option selections appropriate to the selected object, and the Print Definition dialog will also have a specific title indicating for which object type you are setting the options.
Figure 25.17. The first page of a Database Documentor report for the Employees table of Northwind.mdb.
Despite the almost universal reluctance of software users to refer to printed manuals, your application is not complete until you've written its user documentation. Relying on users to read or print the ReadMe.txt file that accompanies your application
before installing it is seldom an effective approach. Most users will simply insert your distribution diskette 1 in a drive, choose Run from Windows 95's Start menu, type a:\setup or b:\setup in the Open text box, and then click the OK
button. If this entry doesn't work, users next try a:\install or b:\install. When this fails, the prospective user might read the "Installation" section of your documentation. It is more likely, however, that the person will
call you to find out "what's wrong." Most software manufacturers report that the vast majority of the questions posed to members of their Product Support Staff are answered in either the ReadMe.txt file or the documentation that accompanies their
product.
The structure of user manuals for Visual Basic 4.0 is similar to that of a book, even if the manual contains only a few pages. The following three sections describe the basic structure of a conventional software user manual.
Much of the material in the following sections is based on information contained in Jan V. White's Graphic Design for the Electronic Age, a Xerox Press book published by Watson-Guptill Publications, New York. The subtitle of the book, "The Manual for Traditional and Desktop Publishing," aptly describes its content. Graphic Design for the Electronic Age provides background and pointers on type selection, page design, and publication construction.
Front matter for user manuals usually is more extensive than that for a conventional textbook, and it follows the general structure of the front matter for computer software tutorial and reference works, such as this book. Following are the elements of
the front matter for a typical user manual:
Except for the table of contents, front matter normally is not included in the online help file for your application. You can move the installation instructions to the text section of the book, if you want, but the preferred location is close to the
front of the manual. Users are more likely to refer to the instructions if they appear in the first few pages of the manual.
The text of manuals usually is broken down into chapters and sections. If the manual consists of more than about 10 chapters, you may want to break the text up into collections of related chapters, called parts. If your manual contains a
tutorial and a reference guide for the application, separate parts should be assigned to contain the chapters in each category. The text component of the manual is used to create the online help file. Separate help files often are used for the tutorial and
reference parts.
Material that is supplemental to the text or assists readers in understanding the text is included in the back matter. Back matter may contain the following elements:
Adding the glossary to the online help system is optional but recommended. Appendixes do not ordinarily appear in help files, and the index serves as the backbone of the search topics in the help system.
Blue Sky Software's RoboHELP 95 application consists of a set of templates for Word 7.0 that are specifically designed for writing Windows online help files and software user manuals. The next chapter of this book, "Creating Help Files for
Database Front-Ends," describes how to use RoboHELP 95 to create Windows 95 .hlp files. Figure 25.18 shows a RoboHELP 95 example help file, Nwind.rtf, from Chapter 26's Nwind.hpj help project opened in Word 7.0.
Figure 25.18. Part of a RoboHELP 95 example help file opened in Word 7.0.
Although you can write manuals that you can convert to Windows 95 .hlp files with any word processing application that supports footnotes and can export text and images as Microsoft rich-text format (.rtf) files, using Word 7.0 together with RoboHELP
95 saves a considerable amount of time in creating the printed version of your manual. Figure 25.19 shows RoboHELP 95's Export Help Document for Printing dialog. RoboHELP 95's standard templates provide the foundation for attractively formatted manuals,
but you can alter the styles employed by the standard templates if you need to conform to company-wide publication standards. Figure 25.20 illustrates part of a page using RoboHELP's standard 8 1/2 x 11-inch manual style. The major time-saving feature of
RoboHELP 95—and thus the principal justification for acquiring RoboHELP 95—is the ease with which you can compile your manuals into Windows 95 .hlp files that provide access to almost every feature offered by the Windows 95 version of
Winhelp.exe. RoboHELP 95 provides the ability to import manuals to help projects or output help projects to manuals.
Figure 25.20. Part of a page that illustrates RoboHELP 95's standard manual style.
This chapter described methods of creating data dictionaries for your Visual Basic 4.0 database applications and the basic structure of the printed manual that should accompany every production database application. The chapter concluded with a brief
description of the use of a commercial help authoring tool, RoboHELP 95, which is used to convert manuals to help files and to convert help files to documentation format.
The next chapter, "Creating Help Files for Database Front-Ends," shows you how to use Blue Sky Software's RoboHELP 95 to design and generate Windows 95 WinHelp files.
