Appendix B

Answers to Quizzes and Exercises

Answers to Day 1 Quiz

  1. The two properties you must set when you are linking a form to a database are the DatabaseName property and the RecordSource property.
  2. Set the Caption property of the data control to display a meaningful name between the record pointer arrows.
  3. You must set the DataSource property of the input control to the data table and the DataField property of the input control to the field name in the data table.
  4. You only need one line of Visual Basic code (not including the Sub...End Sub statements) to add delete functionality to a data entry form when using the Visual Basic data control.
  5. Set the Save Project Before Run option from the Options | Environment Menu to Yes in order to save the project before you run the program.

Answers to Day 1 Exercises

  1. While in design mode, select the form by clicking anywhere on the form that doesn't have a control. Press F4 and select the Caption property. Type "The Titles Program" (without the quote marks) and press Enter. Note that the title appears on the title bar of the form as you type.
  2. Complete the following steps to build an Exit button:

    Double-click the Command Button control on the Visual Basic toolbox to add a new button to the form.

    Set the following properties for the new button:

    Name cmdExit

    Drag the new button to align it with the Add and Delete buttons.

    Caption E&xit

    Enter the following code in the cmdExit_Click procedure:

    
    
    
    
    Sub cmdExit_Click()
    
    
    
       End
    
    
    
    End Sub

    Save your changes and execute your program.

    Click on the Exit button to stop the program.

  3. Enter the following code in the cmdAdd_Click procedure:

    
    
    
    
    Sub Command1_Click()
    
    
    
       datTitles.Recordset.AddNew  ' Add a new record to the table
    
    
    
       Text1.SetFocus  ' Set the focus to the Text1 control (added chp3 exercise #3
    
    
    
    End Sub

Answers to Day 2 Quiz

  1. The three main building blocks of relational databases are data fields, data records, and data tables.
  2. The smallest building block in a relational database is the data field.
  3. A data record is a collection of related data fields.
  4. The main role of a primary key in a data table is to maintain the internal integrity of a data table.
  5. A data table can have any number of foreign keys defined. It can have only one primary key defined.
  6. There are only two values that can be stored in a BOOLEAN data field: -1 (True) and 0 (False).
  7. The highest value that can be stored in a BYTE field is 255. Visual Basic will allow users to enter up to 32767 without reporting an error, but any value higher than 255 will be truncated to a single-byte value.
  8. Any attempt to edit and update a counter field will result in a Visual Basic error.
  9. The CURRENCY data type can store up to four places to the right of the decimal. Any data beyond the fourth place will be truncated by Visual Basic without reporting an error.
  10. You can use the International applet from the Windows Control Panel to determine the display format of DATE data fields.

Answers to Day 2 Exercises

  1. There are three records in the table above.
  2. The SSN (Social Security Number) would make an excellent primary key for this table because it would be unique for all records entered.
  3. The answer to part C is shown in the following table:

    Field Data Type VISUAL BASIC Type
    SSNo Text String
    Last Text String
    First Text String
    Age Byte Integer
    City Text String
    St Text String
    Comments Memo String

  4. Perform the following steps to add the checkbox: First, double-click on the checkbox control. Second, position the checkbox in an aesthetically pleasing position. Third, set these Properties:

    Property Setting
    DataSource datFieldTypes
    DataField BOOLEAN
    Name chkBoolean

    Run your program and check the BOOLEAN box. Notice that nothing happens to the BOOLEAN text field. Now move to the subsequent record, and then return. You should see -1 displayed in the BOOLEAN text field.

    This example shows how to use a checkbox to enter values into fields. Your program can now reference this field and get the value as -1 (yes) or 0 (no), which are the only two values that can be in a BOOLEAN type data field.

Answers to Day 3 Quiz

  1. The advantages of using the Data Manager to create databases include the following:
  2. The major disadvantage of the Data Manager is that it is not a complete database administration tool. You cannot print table structures or index definitions.
  3. Only Microsoft Access databases (*.MDB) can be compacted with the Data Manager.
  4. You can only have one Primary Key in an Access table. Remember that Access databases have multiple tables, each having at most only one primary key.
  5. You can use the Data Manager to remove indexes from a table. You cannot, however, use the Data Manager to remove the field on which your index was created until the index itself is removed.
  6. The statement means to display all names and phone numbers found in the CompanyMaster database.

Answers to Day 3 Exercises

Complete the following steps to build your data table and enter data:

  1. Start Visual Basic 4.
  2. Select Data Manager from the Add Ins menu.
  3. From the Data Manager menu bar, select File | New Database.
  4. Enter an appropriate name and location in the New Database dialog box.
  5. Select the New button from the Tables/QueryDefs form. Provide an appropriate name, such as Address.
  6. Add fields such as SSN (a unique identifier of the individual), LastName, FirstName, Address1, Address2, StateProv, Country, and Zip.
  7. Enter a Primary Key index with a descriptive name, such as PKCompanyMaster. Add the Social Security number field, and press Add (ASC). Finally, check the Primary Index box to designate this as the primary key.

    It is recommended that you not depend on the combination of the LastName and the FirstName fields as the unique identifier for the record. It is quite possible to have two people with the same first and last name. Also, complex multifield primary keys make query design much more difficult.

    If you have followed the exercises in this chapter closely, you may remember that we did build a key for a table using the last name, first name field combination. This was for illustrative purposes only and was meant only to show how to combine fields in a key. This practice is not recommended for combinations of fields that could have multiple occurrences (How many John Smiths are there in the world?), and is definitely taboo for primary keys. In fact, a good rule of thumb to remember is that the need for a multifield primary key is indicative of a database that needs to be redesigned.

  8. Close the Table Design dialog.
  9. Select the Open button from the Tables dialog.
  10. Select Add to enter your first record. Create information to be entered. Select Update to commit your entries. Move through entry fields by pressing the Tab key. Move backward by pressing the Shift and Tab keys simultaneously.
  11. Enter several records in this fashion. Click the arrow buttons on the data control at the bottom of the screen to move through the data records after you have entered them.

Answers to Day 4 Quiz

  1. Visual Basic database objects are dataset orientated. You will work with a set of records at one time, not one record at a time as you would with a record-orientated database.
  2. The Dynaset is the most common Visual Basic data object. It is the object that is created when you open a form with a data control.
  3. Dynasets use minimal RAM resources. Visual Basic stores only the pointers to the records in the underlying table, not the actual data.
  4. Weaknesses of using Dynasets include the following:
  5. Table data objects allow you to utilize indexes and the Seek method.
  6. You do not use the Refresh method with the Table data object because this object is the underlying data.
  7. You must use code to open a Table object in Visual Basic.
  8. A Snapshot stores all the data in the workstation's memory, whereas the Dynaset stores only pointers to the data. The Snapshot is also read-only and can't be updated. A Dynaset can be updated.
  9. You use the Database data object to extract field and table names from a database.

Answers to Day 4 Exercises

  1. You would use the Dynaset data object because it is the only data object that can update an ODBC data source. Your code could look similar to the following:

    
    
    
    
    Sub Form_Load()
    
    
    
    'Create a database and dynaset object
    
    
    
    Dim Dat as Database
    
    
    
    Dim dyn1 as Dynaset
    
    
    
    'Declare standard variables
    
    
    
    Dim cDBN ame as String
    
    
    
    Dim cTable as String
    
    
    
    'Initialize variables
    
    
    
    cDBName = "c:\DATAS\ACCTPAY.MDB"
    
    
    
    cTable = "Vendors"
    
    
    
    'Set values
    
    
    
    set Dat = OpenDatabase(cDBName)
    
    
    
    Set dyn1 = Dat.CreateDynaset(cTable)
    
    
    
    End Sub
  2. The Snapshot data object should be used for this purpose because it will not change after it is created. This will prevent the data used in your report from being updated while your report is generating.

    Your code could look as follows:

    
    
    
    
    Sub Form_Load()
    
    
    
    'Create a database and snapshot object
    
    
    
    Dim Dat as Database
    
    
    
    Dim snpObject as Snapshot
    
    
    
    'Declare standard variables
    
    
    
    Dim cDBName as String
    
    
    
    Dim cTable as String
    
    
    
    'Initialize variables
    
    
    
    cDBName = "c:\DATAS\ACCTPAY.MDB"
    
    
    
    cTable = "Vendors"
    
    
    
    'Set values
    
    
    
    set Dat = OpenDatabase(cDBName)
    
    
    
    Set snpObject = Dat.CreateSnapshot(cTable)
    
    
    
    End Sub
  3. You would use the Table data object because it will give you instant information when records are changed. Your code could look like the following:

    
    
    
    
    Sub Form_Load()
    
    
    
    'Create a database and table object
    
    
    
    Dim Dat as Database
    
    
    
    Dim tblObject as Table
    
    
    
    'Declare standard variables
    
    
    
    Dim cDBName as String
    
    
    
    Dim cTable as String
    
    
    
    'Initialize variables
    
    
    
    cDBName = "c:\DATAS\ACCTPAY.MDB"
    
    
    
    cTable = "Vendors"
    
    
    
    'Set values
    
    
    
    set Dat = OpenDatabase(cDBName)
    
    
    
    Set tblObject = Dat.OpenTable(cTable)
    
    
    
    End Sub

Answers to Day 5 Quiz

  1. You can establish a database for a data control by setting the DatabaseName property of the data control to the name of the database (including the path), or to a defined variable that points to the database. For example, to attach the data control Data1 to a Microsoft Access database C:\DATAPATH\XYZ.MDB, you can enter the following:

    
    
    
    
    Data1.DatabaseName = "C:\DATAPATH\XYZ.MDB"
  2. You use the RecordSource property to establish the name of a table for a data control in Visual Basic. For example, to set the data control Data1 to a table of vendors in an accounts payable application, you can type the following:

    
    
    
    
    Data1.RecordSource = "Vendors"

    It is better form, however, to assign the RecordSource to a variable that has been defined and points to the data table. Here's an example:

    
    
    
    
    Dim cTable as String' Declare the variable
    
    
    
    cTable = "Vendors" ' Establish the name of the table
    
    
    
    Data1.RecordSource = cTable ' Set the data control 
    
    
    
    Data1.Refresh ' Update the data control
  3. The UpdateControls method takes information from the underlying database table and places it in the form controls; whereas the UpdateRecord method takes information entered into the form controls and updates the attached table.
  4. Checkboxes should only be bound to boolean fields and can only produce values of 0 (No or False) and -1 (Yes or True).
  5. You use the DataField property to bind a control to a table field.
  6. The standard color for a Windows 95 form is light gray. Input areas are white. Display-only controls are light gray. Labels are left aligned.

Answers to Day 5 Exercises

  1. You should enter the following code as a new procedure in the general declarations section:

    
    
    
    
    Sub OpenDB()
    
    
    
    'Declare the variable for the name of the database
    
    
    
    Dim cDBName as String
    
    
    
    'Assign the variable to a database, including the path
    
    
    
    cdbName = App.Path + " \Students.MDB"
    
    
    
    'Set the name of the database used by the data control
    
    
    
    Data1.DatabaseName = cDBName
    
    
    
    'Refresh and update the data control
    
    
    
    Data1.Refresh
    
    
    
    End Sub
  2. Your code should like this:

    
    
    
    
    Sub OpenDB()
    
    
    
     'Declare the variable for the name of the database
    
    
    
    Dim cDBName as String
    
    
    
    'Declare the variable for the table
    
    
    
    Dim cTable as String
    
    
    
    'Assign the variable to a database, including the path
    
    
    
    cdbName = App.Path + "\Students.MDB"
    
    
    
    'Assign the variable to the appropriate table
    
    
    
    cTable = "Addresses"
    
    
    
    'Set the name of the database used by the data control
    
    
    
    Data1.DatabaseName = cDBName
    
    
    
    'Set the name of the table used by the data control
    
    
    
    Data1.RecordSource = cTable
    
    
    
    'Refresh and update the data control
    
    
    
    Data1.Refresh
    
    
    
    End Sub
  3. Your code should look like this:

    
    
    
    
    Sub OpenDB()
    
    
    
    Dim cDBName as String
    
    
    
    Dim cTable as String
    
    
    
    Dim cField1 as String
    
    
    
    Dim cField2 as String
    
    
    
    Dim cField3 as String
    
    
    
    Dim cField4 as String
    
    
    
    Dim cField5 as String
    
    
    
    'Assign variables
    
    
    
    cdbName = App.Path + "\Students.MDB"
    
    
    
    cTable = "Addresses"
    
    
    
    cField1 = "StudentID
    
    
    
    cField2 = "Address"
    
    
    
    cField3 = "City"
    
    
    
    cField4 = "State"
    
    
    
    cField5 = "Zip"
    
    
    
    'Set the data control properties
    
    
    
    Data1.DatabaseName = cDBName
    
    
    
    Data1.RecordSource = cTable
    
    
    
    'Bind the text fields
    
    
    
    txtStudentID.DataField = cField1
    
    
    
    txtAddress.DataField = cField2
    
    
    
    txtCity.DataField = cField3
    
    
    
    txtState.DataField = cField4
    
    
    
    txtZip.DataField = cField5
    
    
    
    'Refresh and update the data control
    
    
    
    Data1.Refresh
    
    
    
    End Sub

Answers to Day 6 Quiz

  1. Input validation occurs as the data is entered, whereas error trapping occurs after the data is entered. Input validation is used to guarantee uniformity in the data that is saved.
  2. Subtracting 32 from the lowercase value will return the uppercase value.
  3. The KeyPress event occurs whenever a key is pressed.
  4. No, a validation list can be entered in any order.
  5. The field txtUpper is being trimmed of spaces and then is being tested to see if the length is anything other than zero. This code is used to test if any values are entered into a field. The Trim command is used to remove any spaces entered into the field either intentionally or inadvertently.
  6. Conditional field validation should be performed at the form level. Users may skip around on the form using the mouse, thus making field level validation impractical.
  7. Validation lists should be loaded by the Form_Load procedure.
  8. The first section is the format of a positive number. The second section is the format of a negative number. The third section is the format of zero. Each section is separated by a semicolon(;).

Answers to Day 6 Exercises

  1. Enter the following code inside your fields KeyPress event:

    
    
    
    
    Sub FieldName_KeyPress(KeyAscii as Integer)
    
    
    
         If KeyAscii >26 then 'If anything other than a control code
    
    
    
              If Chr(KeyAscii) >= "a" and Chr(KeyAscii) <= "z" Then
    
    
    
                   KeyAscii = KeyAscii - 32 ' Capitalize small letters
    
    
    
              Else
    
    
    
                   KeyAscii = 0 ' No input from keyboard
    
    
    
              End if
    
    
    
         End if
    
    
    
    End Sub
  2. #,##0.00;-#,##0.00
  3. Enter the following code into the cmdOK Click event:

    
    
    
    
    Sub cmdOK_Click ()
    
    
    
         Dim nOK as Integer ' Declare a test variable
    
    
    
         nOK = True
    
    
    
         If Len(Trim(txtDate)) = 0 then ' Check for entry (exclusive of spaces)
    
    
    
              MsgBox "Input is required in the txtDate field before this record can be saved" ' Issue a message if no data is entered
    
    
    
              nOK = False 'Set test variable to False
    
    
    
              txtDate.SetFocus ' Place cursor in txtDate
    
    
    
         End if
    
    
    
         If nOK = True then
    
    
    
              Unload Me ' Exit form if data is entered
    
    
    
         End if
    
    
    
    End sub
  4. Enter the following code in the Form_Load event:

    
    
    
    
    Sub Form_Load()
    
    
    
         'Load the combo box
    
    
    
         cboEmployees.AddItem "Smith"
    
    
    
         cboEmployees.AddItem "Andersen"
    
    
    
         cboEmployees.AddItem "Jones"
    
    
    
         cboEmployees.AddItem "Jackson"
    
    
    
    End sub

    You set the Sorted property of the combo box to True to alphabetically sort the information displayed in the combo box. This property can only be set at design time.

Answers to Day 7 Quiz

  1. The three bands are the header, footer, and detail bands. The header is used to insert information that displays on the top of each page of the report. The footer band inserts information on the bottom of each page of the report. The detail band displays the actual information.
  2. Crystal Reports can attach to any database type recognized by Visual Basic 4. This includes Microsoft Access, dBASE, FoxPro, Betreive, Paradox, and any ODBC data source.
  3. You can type text directly on a form in Crystal Reports, but remember that it cannot be moved or resized (you can, however, change the font size and appearance). The more versatile way to enter text is with the Text Field option from the Insert menu.
  4. You can produce mailing labels in Crystal Reports by selecting New Mailing Labels Report from the File menu.
  5. You can browse data in a database by choosing a field on the report during design time and selecting Browse Field Data from the Edit Menu.
  6. Yes, you can add select criteria to your Crystal Reports report by choosing Select Records from the Report menu and then entering your criteria.
  7. You can join tables in Crystal Reports by selecting Links from the Database menu option.

Answers to Day 7 Exercises

  1. Here is the formula:

    
    
    
    
          Count({NameLast})
  2. IsNull({EmployerID})
  3. Perform the following steps to build the report:

    Answers to Day 8 Quiz

    1. The Visdata project can be found in the Samples\Visdata subdirectory of Visual Basic 4.
    2. To copy a table, simply select the table from the Table | Queries window, press the alternate mouse button, and select Copy Structure.
    3. You need to Refresh the Tables | Queries window each time you enter an SQL statement to create a new table.
    4. You can open and edit Excel spreadsheets in Visdata.
    5. The Files | Properties | DbEngine menu option shows the version of the database engine in use, the login time-out, the .INI path, the default user, and the default password for the current database.
    6. You compact databases to remove empty spaces where deleted records used to reside and to reorganize any defined indexes that are stored in the database.
    7. You can compact a database onto itself with the Compact MDB command. This action is not advisable, however, as problems can occur during the compacting process.
    8. You can not modify a table's structure once data has been entered. You must delete all records before you can modify the structure.
    9. You can save queries in Visdata for future use. You do this by building a query with the Query Builder and saving the results, or by entering an SQL statement and saving its result set.
    10. Visdata can export data in the following formats:
      • JET (Microsoft Access)
      • dBASE IV, III
      • FoxPro 2.6, 2.5, 2.0
      • Paradox 4, 3
      • Excel 5, 4, 3
      • Text
      • ODBC
    11. You can use the Files | Compact Database to convert existing JET 1.1 or JET 2.0 databases to newer versions by selecting the new data format at the Compact Database submenu.

    Answers to Day 8 Exercises

    1. To create the new database select these menu items:

      File | New | JET Engine MDB | Version 2.0 MDB

      Next, enter the path and the name of the database and save.

    2. Select New from the Table | Queries window to build the new table. Insert the name tblCustomers in the Table Name field. Next, select Add to insert the fields. Enter the name, type, and size for each field, clicking OK after each is completed. When all fields are entered, select Close. When you return to the Table Structure form, select Build Table.
    3. To build the primary key, first make sure that tblCustomers is highlighted in the Tables\Queries window, and select Design. Select Add Index button from the Table Structure window. Enter the name of the primary key (PKtblCustomers), and double-click the ID field in the Available Fields list box. Make sure that the Primary and Unique checkboxes have been checked. Finally, click OK to build the primary key index.
    4. Select the tblCustomers table from the Tables | Queries window and click Design. Next, select Print Structure in the bottom right corner of the Table Structure window.
    5. To enter records, first make sure that tblCustomers is highlighted. Then, select Open from the Tables | Queries window. You will be able to enter data in any Form type you would like to use. You will, however, only be able to enter Notes data in the Grid form.
    6. To copy a table structure, highlight the table, click the alternate mouse button, and select Copy Structure. Leave the Target Connect String empty and make sure that neither the Copy Indexes, nor the Copy Data checkboxes are checked. Enter the table name tblVendors when prompted for the name of the new table. Select the OK button to create the table.

      Once the table is copied, you should then go into the table design and add a primary key. Build this index the same way you built the primary key for the tblCustomers table.

    7. To export, select Utility | Import/Export. Select the tblCustomers table and then press Export Table(s). Next choose the text format as the data source, and click OK. You are then prompted to enter a path and a name. Select Save, and the file is created.

      Review the file. Notice that empty fields in a record are denoted by the use of two commas (,,).

    Answers to Day 9 Quiz

    1. SQL stands for Structured Query Language. You pronounce SQL by saying the three individual letters. It is not pronounced sequel.
    2. Use the SELECT...FROM statement to select information from table fields.
    3. Use the asterisk (*) in a SELECT...FROM statement to select all the fields in a data table. For example, to select all fields in a table of customers, you can enter the following SQL statement:

      
      
      
      
      SELECT * FROM Customers
    4. Use the ORDER BY clause to sort the data you display. For example, to sort the data from quiz answer 3 by a field contained within the table, CustomerID, you would enter the following:

      
      
      
      
      SELECT * FROM Customers ORDER BY CustomerID
    5. A WHERE clause can be used to limit the records that are selected by the SQL statement, as well as to link two or more tables in a result set.
    6. Use the AS clause to rename a field heading. For example, issue the following SQL statement to rename the field CustomerID in the Customers table to Customer.

      
      
      
      
      SELECT CustomerID AS Customer FROM Customers
    7. SQL aggregate functions are a core set of functions available in all SQL-compliant systems used to return computed results on numeric data fields. The functions available through JET include AVG, COUNT, SUM, MAX, and MIN.
    8. Chief among the drawbacks of using Visual Basic functions in your SQL statement is the loss of portability to other database engines. There is also a slight performance reduction when Visual Basic functions are used in your SQL statement.
    9. Both the DISTINCT and DISTINCTROW clauses extract unique records. The DISTINCTROW command looks at the entire record, whereas DISTINCT looks at the fields you associate with it.
    10. You should always use the ORDER BY clause when you use the TOP n or TOP n PERCENT clauses. The ORDER BY clause ensures that your data is sorted appropriately to allow the TOP n clauses to select the appropriate data.
    11. The three types of joins found in Microsoft Access JET SQL are INNER, LEFT, and RIGHT. An INNER join is used to create updateable result sets whose records have an exact match in both tables. The LEFT join is used to return an updateable result set that returns all records in the first table in your SQL statement, and any records in the second table that have matching column values. The RIGHT join is just the opposite of the LEFT join; it returns all records in the second table of your SQL statement and any records in the first table that have matching column values.
    12. UNION queries are used to join tables that contain similar information but are not linked through a foreign key. An example of a UNION query would be listing all of your company's customers and suppliers located in the state of Iowa. There won't be any foreign key relationships between a data table of supplier's information and a table of customer's information. Both tables will, however, contain fields for names, addresses, and phone numbers. This information can be joined through a UNION query and displayed as one result.

    Answers to Day 9 Exercises

    1. SELECT * FROM CustomerMaster
    2. SELECT InvoiceNo, CustomerID AS Account, Description, Amount FROM OpenInvoice
    3. SELECT InvoiceNo, CustomerID AS Account, Description, Amount FROM OpenInvoice ORDER BY CustomerID, InvoiceNo
    4. SELECT * FROM Suppliers WHERE City LIKE ("New York *") and State = "NY"
    5. SELECT CustomerMaster.CustomerType, CustomerMaster.Name, CustomerMaster.Address, CustomerMaster.City, CustomerMaster.State, CustomerMaster.Zip FROM CustomerMaster WHERE CustomerMaster.CustomerType = "ABC"
    6. SELECT CustomerID, Name FROM CustomerMaster WHERE Left(Name,3) = "AME"
    7. SELECT DISTINCT OpenInvoice.CustomerID, CustomerMaster.Name FROM OpenInvoice INNER JOIN CustomerMaster ON OpenInvoice.CustomerID = CustomerMaster.CustomerID ORDER BY OpenInvoice.CustomerID
    8. SELECT TOP 5 * FROM OpenInvoice ORDER BY Amount Desc
    9. SELECT Name, Phone FROM CustomerMaster WHERE State = "OHIO" UNION SELECT Name, Phone FROM Suppliers WHERE State = "Ohio"

    Answers to Day 10 Quiz

    1. JET is short for Joint Engine Technology.
    2. A property is data within an object that describes its characteristics, whereas a method is a procedure that can be performed upon an object. You set a property, and invoke a method.
    3. The top level DAO is the DBEngine.
    4. You use the RepairDatabase method to repair a database. This command uses the following syntax:

      
      
      
      
      DBEngine.RepairDatabase DatabaseName
    5. The syntax for the CompactDatabase method is

      
      
      
      
      DBEngine.CompactDatabase oldDatabase, newDatabase, locale, options

      Please note that the oldDatabase and newDatabase require the database name and path.

    6. Visual Basic creates a default Workspace if you fail to identify one when you open a database.
    7. The OpenRecordset method can open data from a data source as a Table, Dynaset, or Snapshot.
    8. The only difference between the Execute and the ExecuteSQL methods is that the ExecuteSQL method returns the number of rows affected by the SQL statement.
    9. The CreateTableDef method builds a table in a database. The syntax of this statement is

      
      
      
      
      Database.CreateTableDef(table name)
    10. Use the Type property of the Field object to display the data type of a table column.
    11. The Index data object can be used to contain information on Microsoft JET databases only.
    12. The QueryDef object stores Structure Query Language (SQL) statements. A QueryDef is faster than an actual SQL statement because Visual Basic has to perform an additional preprocessing step for an SQL statement, which it does not need to perform for the QueryDef.

    Answers to Day 10 Exercises

    Drop a command button onto a form, name it cmdCreate, and then enter the following code:

    
    
    
    
    Private Sub cmdCreate_Click()
    
    
    
        On Error Resume Next
    
    
    
        'Define Variables
    
    
    
        Dim dbFile As DATABASE
    
    
    
        Dim cDBName As String
    
    
    
        Dim tdTemp As TableDef
    
    
    
        Dim fldTemp As Field
    
    
    
        Dim idxTemp As Index
    
    
    
        Dim relTemp As Relation
    
    
    
        Dim ctblCustomers As String
    
    
    
        Dim ctblCustomerTypes As String
    
    
    
        Dim cidxCustomers As String
    
    
    
        Dim cidxCustomerTypes As String
    
    
    
        Dim crelName As String
    
    
    
        'Set variables
    
    
    
        cDBName = App.Path + "\10ABCEX.MDB"
    
    
    
        ctblCustomers = "Customers"
    
    
    
        ctblCustomerTypes = "CustomerTypes"
    
    
    
        cidxCustomers = "PKCustomers"
    
    
    
        cidxCustomerTypes = "PKCustomerTypes"
    
    
    
        crelName = "relCustomerType"
    
    
    
        'Delete the database if it already exists
    
    
    
        Kill cDBName
    
    
    
        'Create the database
    
    
    
        Set dbFile = CreateDatabase(cDBName, dbLangGeneral, dbVersion20)
    
    
    
        'Create the Customers table
    
    
    
        Set tdTemp = dbFile.CreateTableDef(ctblCustomers)
    
    
    
        'Insert fields into the Customers table
    
    
    
        Set fldTemp = tdTemp.CreateField("CustomerID", dbText, 10)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("Name", dbText, 50)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("Address1", dbText, 50)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("Address2", dbText, 50)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("City", dbText, 25)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("StateProv", dbText, 25)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("Zip", dbText, 10)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("Phone", dbText, 14)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("CustomerType", dbText, 10)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        'Build the Primary Key index to the Customers table
    
    
    
        Set idxTemp = tdTemp.CREATEINDEX(cidxCustomers)
    
    
    
        idxTemp.PRIMARY = True
    
    
    
        idxTemp.Required = True
    
    
    
        Set fldTemp = tdTemp.CreateField("CustomerID")
    
    
    
        idxTemp.Fields.Append fldTemp
    
    
    
        tdTemp.Indexes.Append idxTemp
    
    
    
        'Add the Customers table to the databases
    
    
    
        dbFile.TableDefs.Append tdTemp
    
    
    
        'Create the Customer Types table
    
    
    
        Set tdTemp = dbFile.CreateTableDef(ctblCustomerTypes)
    
    
    
        'Insert fields into the Customer Types table
    
    
    
        Set fldTemp = tdTemp.CreateField("CustomerType", dbText, 10)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        Set fldTemp = tdTemp.CreateField("Description", dbText, 10)
    
    
    
        tdTemp.Fields.Append fldTemp
    
    
    
        'Build the Primary Key index for the Customer Types table
    
    
    
        Set idxTemp = tdTemp.CREATEINDEX(cidxCustomerTypes)
    
    
    
        idxTemp.PRIMARY = True
    
    
    
        idxTemp.Required = True
    
    
    
        Set fldTemp = tdTemp.CreateField("CustomerType")
    
    
    
        idxTemp.Fields.Append fldTemp
    
    
    
        tdTemp.Indexes.Append idxTemp
    
    
    
        'Add the CustomerTypes table to the database
    
    
    
        dbFile.TableDefs.Append tdTemp
    
    
    
        'Create the relationship
    
    
    
        Set relTemp = dbFile.CreateRelation(crelName)
    
    
    
        relTemp.TABLE = ctblCustomerTypes 
    
    
    
    ' The tabl that contains the validation information
    
    
    
        relTemp.ForeignTable = ctblCustomers 
    
    
    
    ' The table that utilizes the validation table
    
    
    
        Set fldTemp = relTemp.CreateField("CustomerType")
    
    
    
        fldTemp.ForeignName = "CustomerType"
    
    
    
        relTemp.Fields.Append fldTemp
    
    
    
        dbFile.Relations.Append relTemp
    
    
    
        'Issue a message when the procedure is completed
    
    
    
        MsgBox "Database build is complete"
    
    
    
    End Sub

    Answers to Day 11 Quiz

    1. The chief advantage of using the Data Control is that you can quickly build a Visual Basic database application without the use of much code. The disadvantages of using the Data Control include the following:
      • The project will be more difficult to maintain.
      • Data entry forms will not be as easily utilized in other database applications.
    2. The chief advantage of using code to build Visual Basic data entry forms is that you have complete control of the process. Code can also be used in other Visual Basic projects to quickly build forms.
    3. The Find method most resembles the SQL WHERE clause.
    4. The Seek method can be utilized only on recordsets opened as tables. Seek cannot be utilized on Dynasets or Snapshots.
    5. The four Move methods that can be applied to the recordset object are MoveFirst, MovePrevious, MoveNext, and MoveLast.
    6. The FindFirst method starts its search from the beginning of the recordset. The FindLast method starts its search from the end of the recordset.
    7. You use the Bookmark to remember a specific location in a data set.
    8. The Seek method is the fastest way to locate a record in a data set.
    9. You create a control array in Visual Basic by copying and pasting a control on a form and answering Yes when prompted to create a control array by Visual Basic.
    10. You must invoke the Edit or AddNew method prior to writing to a data set with the Update method.

    Answers to Day 11 Exercises

    Perform the following steps to complete the addition of the ZipCity form:

    1. Open VisData and create the new table ZipCity. Add a field for Zip code (Zip) and for city (City).
    2. Modify the menu on the Company master form by adding mnuListZip with a caption of &Zip/City.
    3. Add the following code to the mnuListZip_Click event:

      
      
      
      
      Private Sub mnuListZip_Click()
      
      
      
          'Open the ZipCity form
      
      
      
          frmZipCity.Show 1
      
      
      
      End Sub
    4. Create a new form (frmZipCity) and add a field for Zip and a field for City. Set the Tag property of these two text boxes to Zip and City respectively.
    5. Add a control array to this form. Do this by adding a command button, naming it cmdBtn, and then copying and pasting it seven times.
    6. Make the following variable declarations in the General Declaration of your form:

      
      
      
      
      Option Explicit
      
      
      
      Dim dbFile As DATABASE
      
      
      
      Dim cDBName As String
      
      
      
      Dim rsFile As Recordset
      
      
      
      Dim cRSName As String
      
      
      
      Dim nBtnAlign As Integer
      
      
      
      Dim nResult As Integer
    7. Add the following procedure to your form:

      
      
      
      
      Sub StartProc()
      
      
      
          '
      
      
      
          ' open db and rs
      
      
      
          '
      
      
      
          ' on error goto StartProcErr
      
      
      
          '
      
      
      
          cDBName = App.Path + "\master.mdb"
      
      
      
          cRSName = "ZipCity"
      
      
      
          '
      
      
      
          nResult = RSOpen(cDBName, cRSName, dbOpenDynaset, dbFile, rsFile)
      
      
      
          If nResult = recOK Then
      
      
      
              nResult = RecInit(Me)
      
      
      
          End If
      
      
      
          '
      
      
      
          If nResult = recOK Then
      
      
      
              nResult = RecRead(Me, rsFile)
      
      
      
          End If
      
      
      
          '
      
      
      
          GoTo StartProcExit
      
      
      
          '
      
      
      
      StartProcErr:
      
      
      
          RecError Err, Error$, "StartProc"
      
      
      
          GoTo StartProcExit
      
      
      
          '
      
      
      
      StartProcExit:
      
      
      
          '
      
      
      
      End Sub
    8. Add the following to the frmZipCity Form Load event:

      
      
      
      
      Private Sub Form_Load()
      
      
      
          ' initialize and start up
      
      
      
          '
      
      
      
          StartProc ' open files
      
      
      
          nResult = RecEnable(Me, False)  ' turn off controls
      
      
      
          nBtnAlign = btnAlignBottom      ' set aligment var
      
      
      
          BtnBarInit Me, nBtnAlign        ' create button set
      
      
      
          BtnBarEnable Me, "11111111"     ' enable all buttons
      
      
      
      End Sub
    9. Add the following to the Form Resize event:

      
      
      
      
      Private Sub Form_Resize()
      
      
      
          BtnBarInit Me, nBtnAlign    ' repaint buttons
      
      
      
      End Sub
    10. Add the following to the Form Unload event:

      
      
      
      
      Private Sub Form_Unload(Cancel As Integer)
      
      
      
          dbFile.Close    ' safe close
      
      
      
      End Sub

      From this point, save your work and run the project. Please note that most of this code can be copied directly from the StateProv example.

    Answers to Day 12 Quiz

    1. The use of graphics in your Visual Basic database applications offers the following advantages:
      • Visual representation of data is easier to understand than tables or lists.
      • Graphics offer a different view of the data.
      • Graphics give your application a polished appearance.
    2. The NumSets property determines how many groups of data will be plotted. The NumPoints property shows how many points will be plotted in the group.
    3. No, graphBar3D should be gphBar3D.
    4. The Tab character, Chr(9), separates data points in a series. The carriage return/line feed combination—Chr(13) + Chr(10)—separates data sets for QuickData.
    5. Yes, GraphTitle is a valid property.
    6. gphBlit sets the graph control to bitmap mode.

      gphCopy copies the graph to the Windows Clipboard.

      gphDraw draws the graph on-screen.

    7. The Variant data type must be used for all optional arguments.
    8. The IsMissing() function can be used to determine whether an optional argument has been passed. This function returns TRUE if the optional argument is not passed.
    9. You should use the snapshot data type for graphics applications if possible. Snapshots might not be useable if large quantities of data are being graphed and workstation resources are limited.
    10. The following code moves the data pointer to the end of a data set and counts the total number of records in the set:

      
      
      
      
      Dim nPoints as Integer
      
      
      
      Dim rsData as Recordset
      
      
      
      rsData.Movelast
      
      
      
      nPoints = rsData.RecordCount

    Answers to Day 12 Exercises

    You can complete this project by performing the following steps:

    1. Create the database in Visdata. Build the table, add the fields, and enter the data.
    2. Start Visual Basic and begin a new project. Insert LIBGRAPH.BAS and FRMGRAPH.FRM into your project.
    3. Build the new form by adding the command buttons. Insert the following code behind each button:

      
      
      
      
      Private Sub cmdPie_Click()
      
      
      
          Dim rsFile As Recordset
      
      
      
          Dim dbFile As DATABASE
      
      
      
          Dim cSQL As String
      
      
      
          Dim cField As String
      
      
      
          Dim cTitle As String
      
      
      
          Dim cLegend As String
      
      
      
          Dim cLabel As String
      
      
      
          Dim dbName As String
      
      
      
          cSQL = "Select * from Activity WHERE month =1"
      
      
      
          cField = "Passengers"
      
      
      
          cLegend = ""
      
      
      
          cLabel = "Airline"
      
      
      
          cTitle = "Market Share for January"
      
      
      
          dbName = "c:\abc\ch12\12abcex.mdb"
      
      
      
          Set dbFile = DBEngine.OpenDatabase(dbName)
      
      
      
          Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
      
      
      
          ShowGraph gphPie3D, rsFile, cField, cTitle,
      
      
      
      cFldLegend:=cLegend, cFldLabel:=cLabel
      
      
      
      End Sub
      
      
      
      Private Sub cmdLine_Click()
      
      
      
          Dim rsFile As Recordset
      
      
      
          Dim dbFile As DATABASE
      
      
      
          Dim cSQL As String
      
      
      
          Dim cField As String
      
      
      
          Dim cTitle As String
      
      
      
          Dim cLegend As String
      
      
      
          Dim cLabel As String
      
      
      
          Dim dbName As String
      
      
      
          cSQL = "Select Month, Sum(Passengers) as TotPassengers from
      
      
      
      Activity Group by Month;"
      
      
      
          cField = "TotPassengers"
      
      
      
          cLegend = ""
      
      
      
          cLabel = "Month"
      
      
      
          cTitle = "Total Activity"
      
      
      
          dbName = "c:\abc\ch12\12abcex.mdb"
      
      
      
          Set dbFile = DBEngine.OpenDatabase(dbName)
      
      
      
          Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
      
      
      
          ShowGraph gphLine, rsFile, cField, cTitle,
      
      
      
      cFldLegend:=cLegend, cFldLabel:=cLabel,
      
      
      
      cLeftTitle:="Passengers", cBottomTitle:="Month"
      
      
      
      End Sub
      
      
      
      Private Sub cmdBar_Click()
      
      
      
          Dim rsFile As Recordset
      
      
      
          Dim dbFile As DATABASE
      
      
      
          Dim cSQL As String
      
      
      
          Dim cField As String
      
      
      
          Dim cTitle As String
      
      
      
          Dim cLegend As String
      
      
      
          Dim cLabel As String
      
      
      
          Dim dbName As String
      
      
      
          cSQL = "Select * from Activity WHERE Airline='ABC';"
      
      
      
          cField = "Passengers"
      
      
      
          cLegend = ""
      
      
      
          cLabel = "Month"
      
      
      
          cTitle = "ABC Airlines Annual Activity"
      
      
      
          dbName = "c:\abc\ch12\12abcex.mdb"
      
      
      
          Set dbFile = DBEngine.OpenDatabase(dbName)
      
      
      
          Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
      
      
      
          ShowGraph gphBar3D, rsFile, cField, cTitle,
      
      
      
      cFldLegend:=cLegend, cFldLabel:=cLabel,
      
      
      
      cLeftTitle:="Passengers", cBottomTitle:="Month"
      
      
      
      End Sub

    Answers to Day 13 Quiz

    1. Using a data-bound list or combo box increases the speed of data entry, gives you added control over data validation, and provides suggested values to use for entry.
    2. You set the RowSource property to identify the data source for the list box.
    3. The BoundColumn property sets the column that is saved in the new data record. Put another way, it's the field that is extracted from the source and placed in the destination. Remember that the bound column does not have to equal the ListField property of the control.
    4. You set the DataSource property to set the name of the data set that should be updated by the contents of the data bound list/combo box. You set the DataField property to identify the field in the data set determined by the DataSource property that will be updated.
    5. You must set the AllowAddNew property to True to permit users to add records. You must set the AllowDelete method to True to permit removal of records.
    6. Use the BeforeDelete event to confirm deletion of records.
    7. The Column-Level events of the Data-Bound Grid control provide field level validation functionality.
    8. You would use the data-bound combo box, rather than the data-bound list box, when you want to allow the user to type the entry or when space on the data entry form is limited.
    9. You use the ReBind method to refresh a data-bound grid.
    10. Subforms are typically used to display data from two different data tables that are linked through a common key. For example, Subforms can display invoice detail of a customer linked by customer ID, or work orders that have been performed on a fixed asset linked by asset ID.

    Answers to Day 13 Exercises

    Complete the following steps to build this form:

    1. Add a data control (Data1) and a data-bound list box to a new form.
    2. Set the following properties of Data1:

      DatabaseName C:\VB4\BIBLIO.MDB (include appropriate path)
      RecordSource Publishers

    3. Set the DataSource property to Data1 and the ListField property to Name for the data-bound list.
    4. Add a second data control (Data2) and set its Database property to BIBLIO.MDB and its RecordSource property to Publishers.
    5. Add text fields in an array to the form. Set their DataSource properties to Data2 and their DataField properties to their respective field.
    6. Add a third data control to the form. Set its DatabaseName to BIBLIO.MDB (include path) and its RecordSource property to Titles.
    7. Set the Visible property of all three data controls to False.
    8. Add a data-bound grid to the form. Set its DataSource property to Data3.
    9. Load the data set column names into the grid by selecting Retrieve Fields from the context menu of the DBGrid. Then select Properties from the context menu of the DBGrid and click on the Columns tab. Make sure that the Visible checkbox is selected only for the Title, Year Published, and ISBN columns.
    10. Use the context menu again on the DBGrid and select Edit. Re-size the columns as needed.
    11. Set the BoundColumn property of the data-bound list control to PubID. Blank out the DataField and DataSource properties.
    12. Enter the following code in the DBList1 click event:

      
      
      
      
      Private Sub DBList1_Click()
      
      
      
          Dim cFind As String
      
      
      
          cFind = "PubID=" + Trim(DBList1.BoundText)
      
      
      
          Data2.Recordset.FindFirst cFind
      
      
      
      End Sub
    13. Enter the following code in the Data2 Reposition event:

      
      
      
      
      Private Sub Data2_Reposition()
      
      
      
          Dim cSQL As String
      
      
      
          cSQL = "Select * from Titles WHERE PubID=" + Trim(Text1(0))
      
      
      
          Data3.RecordSource = cSQL ' filter the data set
      
      
      
          Data3.Refresh ' refresh the data control
      
      
      
          DBGrid1.ReBind ' refresh the data grid
      
      
      
      End Sub
    14. Save and execute your program.

    Answers to Day 14 Quiz

    1. These are the three main parts of error handlers in Visual Basic:
      • The On Error Goto statement
      • The error handler code
      • The Exit statement
    2. The four ways to exit an error handler routine are as follows:
      • Resume: Returns to execute the code that caused the error.
      • Resume Next: Resumes execution of the Visual Basic code at the line immediately following the line that created the error.
      • Resume label: Resumes execution at a specified location in the program that caused the error.
      • EXIT SUB or EXIT function: Exits the routine in which the error occurred. You could also use END to exit the program completely.
    3. You use Resume to exit an error handler when the user has done something that he or she can easily correct. For example, the user may have forgotten to insert a disk in drive A or close the drive door.
    4. You would use Resume Next to exit an error handler when the program runs properly even though an error has been reported, or if code within the program corrects the problem.
    5. You use Resume label to exit an error handler when you want the program to return to a portion of code that allows for correction of the invalid entry. For example, if the user inputs numeric data that yields improper results (division by zero, for example) you may want the code to redisplay the input screen so that entry can be corrected.
    6. You would use the EXIT or End command to terminate the program when there is no good way to return to the program once the error has occurred. This may occur if the user forgot to log onto a network or if there is insufficient memory to run the program.
    7. The following are the four types of Visual Basic errors:
      • GENERAL FILE ERRORS: Errors that occur when you try to open, read, or write file information.
      • DATABASE ERRORS: Errors that occur during database operations such as reads, writes, or data object creation or deletions.
      • PHYSICAL MEDIA ERRORS: Errors that are caused by physical devices, such as printers and disk drives.
      • PROGRAM CODE ERRORS: Errors that result from improper coding.
    8. You should not use error trapping for the Visual Basic data control because it provides its own error trapping.
    9. It is a good idea to open a data table with the FORM LOAD event. This allows you to capture most database related errors prior to any data entry.
    10. The advantage of a global error handler is that it enables you to create a single module that handles all expected errors. The major disadvantage of a global error handler is that you are not able to resume processing at the point the error occurs. To be able to resume processing at the point of an error, you need to use Resume, Resume Next, or Resume label in a local error handler.

    Answers to Day 14 Exercises

    1. Insert a command button on a new form, and then double click on that button and enter the following code:

      
      
      
      
      Private Sub Command1_Click()
      
      
      
          On Error GoTo Command1Clickerr
      
      
      
          Dim cMsg As String ' Declare string
      
      
      
          Open "C:\ABC.TXT" For Input As 1 'Open file
      
      
      
          GoTo Command1ClickExit
      
      
      
      'Error handler
      
      
      
      Command1Clickerr:
      
      
      
          If Err = 53 Then
      
      
      
              cMsg = "Unable to open ABC.TXT" + Chr(13)
      
      
      
              MsgBox cMsg, vbCritical, "Command1Click"
      
      
      
              Unload Me
      
      
      
              End
      
      
      
          Else
      
      
      
              MsgBox Str(Err) + " - " + Error$, vbCritical, "Command1Click"
      
      
      
              Resume Next
      
      
      
          End If
      
      
      
      'Routine exit
      
      
      
      Command1ClickExit:
      
      
      
      End Sub
    2. You first need to place a common dialog on your form. Then place a command button and add the following code to it:

      
      
      
      
      Private Sub Command2_Click()
      
      
      
          On Error GoTo Command2ClickErr
      
      
      
          'Declare variables
      
      
      
          Dim cFile As String
      
      
      
          Dim cMsg As String
      
      
      
          Dim nReturn As Integer
      
      
      
          'Define the file to open
      
      
      
          cFile = "C:\ABC.TXT"
      
      
      
          'Open the file
      
      
      
          Open cFile For Input As 1
      
      
      
          MsgBox "ABC.TXT has been opened."
      
      
      
          GoTo Command2ClickExit
      
      
      
      'Error handler
      
      
      
      Command2ClickErr:
      
      
      
          If Err = 53 Then
      
      
      
              cMsg = "Unable to open ABC.TXT!" + Chr(13)
      
      
      
              cMsg = cMsg + "Select OK to locate this file. "
      
      
      
              cMsg = cMsg + "Select CANCEL to exit this program." + Chr(13)
      
      
      
              nReturn = MsgBox(cMsg, vbCritical + vbOKCancel, "Command2Click")
      
      
      
              If nReturn = vbOK Then
      
      
      
                  CommonDialog1.filename = cFile
      
      
      
                  CommonDialog1.DefaultExt = ".txt"
      
      
      
                  CommonDialog1.ShowOpen
      
      
      
                  Resume
      
      
      
              Else
      
      
      
                  Unload Me
      
      
      
              End If
      
      
      
          Else
      
      
      
              MsgBox Str(Err) + " - " + Error$
      
      
      
              Resume Next
      
      
      
          End If
      
      
      
      'Routine exit
      
      
      
      Command2ClickExit:
      
      
      
      End Sub

    Answers to Day 15 Quiz

    1. These are the benefits of using SQL to create and manage data tables:
      • SQL statements can serve as documentation for your table layouts.
      • It's easy to produce test or sample data tables with SQL statements.
      • You can easily load test data into new tables with SQL statements.
      • [lb You can utilize SQL for multiple data platforms.
    2. The syntax is

      
      
      
      
      CREATE TABLE TableName (Field1 TYPE(SIZE), Field2 TYPE(SIZE), ...);

      You first enter CREATE TABLE, followed by the name of the table, and then the fields in parentheses. The field types and sizes (sizes apply to TEXT columns only) are entered after each field.

    3. The default size of a MS JET TEXT field is 255 bytes.
    4. You use the ALTER TABLE...ADD COLUMN statement to add a column to a table. The ALTER TABLE...ADD COLUMN statement uses the following format:

      
      
      
      
      ALTER TABLE <Name of Table> ADD COLUMN <Name of column> <Type> <Size>;
    5. You use the DROP TABLE statement to remove a table from a database. The DROP TABLE statement uses the following format:

      
      
      
      
      DROP TABLE <Table Name>;
    6. You create indexes to data tables with the CREATE INDEX SQL statement.
    7. The following are the three forms of the CONSTRAINT clause:
      • PRIMARY KEY
      • UNIQUE
      • FOREIGN KEY

    Answers to Day 15 Exercises

    1. Enter the following code to build the CustomerType and Customers tables. Please note that the CustomerType table must be built before the Customers table, due to the foreign key constraint on CustomerType in the Customers table.

      
      
      
      
      // Create the database
      
      
      
      dbmake C:\CUSTOMER\CH15EX.MDB;
      
      
      
      // Build the Customer Types Table
      
      
      
      CREATE TABLE CustomerType(
      
      
      
           CustomerType TEXT(6) CONSTRAINT PKCustomerType PRIMARY KEY,
      
      
      
           Description TEXT(30));
      
      
      
      // Build the Customers table
      
      
      
      CREATE TABLE Customers(
      
      
      
           CustomerID TEXT(10) Constraint PKCustomerID PRIMARY KEY,
      
      
      
           Name TEXT(30),
      
      
      
           CustomerType TEXT(6) CONSTRAINT FKCustomerType
      
      
      
      REFERENCES CustomerType(CustomerType),
      
      
      
           Address TEXT(30),
      
      
      
           City TEXT(30),
      
      
      
           State TEXT(30),
      
      
      
           Zip TEXT(10),
      
      
      
           Phone TEXT(14),
      
      
      
           Fax TEXT(14));
      
      
      
      // Build the index on Zip
      
      
      
      CREATE INDEX SKZip on Customers(Zip);
      
      
      
      //Display the results
      
      
      
      SELECT * FROM CustomerType;
      
      
      
      SELECT * FROM Customers;

    Answers to Day 16 Quiz

    1. You use the INSERT statement to insert data into tables. The basic form of this statement is

      
      
      
      
      INSERT INTO TableName(field1, field2,...) VALUES(value1, value2,...);
    2. You use the INSERT INTO...FROM statement to insert multiple records into a data table. The format of this statement is

      
      
      
      
      INSERT INTO TargetTable SELECT field1, field2 FROM SourceTable;
    3. You use the UPDATE...SET statement to modify existing data. This statement uses the following form:

      
      
      
      
      UPDATE <table name> SET <field to update> = <New Value>;
    4. You use the SELECT...INTO...FROM SQL statement to create new tables and insert existing data from other tables. The format of this statement is

      
      
      
      
      SELECT field1, field2 INTO DestinationTable FROM SourceTable;

      In this statement, field1 and field2 represent the field names in the source table.

    5. You use the DELETE...FROM statement to remove records from a data table. The form of this statement is

      
      
      
      
      DELETE FROM TableName WHERE field = value;

    Answers to Day 16 Exercises

    1. Enter the following INSERT...INTO statements after your CREATE INDEX statement to insert the data.

      
      
      
      
      INSERT INTO CustomerType VALUES('INDV', 'Individual');
      
      
      
      INSERT INTO CustomerType VALUES('BUS', 'Business - Non-corporate');
      
      
      
      INSERT INTO CustomerType VALUES('CORP', 'Corporate Entity');
      
      
      
      INSERT INTO Customers VALUES('SMITHJ', 'John Smith', 'INDV',
      
      
      
           '160 Main Street', 'Dublin', 'Ohio', '45621',
      
      
      
           '614-569-8975', '614-569-5580');
      
      
      
      INSERT INTO Customers VALUES('JONEST', 'Jones Taxi', 'BUS',
      
      
      
           '421 Shoe St.', 'Milford', 'Rhode Island', '03215',
      
      
      
           '401-737-4528', '401-667-8900');
      
      
      
      INSERT INTO Customers VALUES('JACKSONT', 'Thomas Jackson', 'INDV',
      
      
      
           '123 Walnut Street', 'Oxford', 'Maine', '05896',
      
      
      
           '546-897-8596', '546-897-8500');
    2. Your script should now look like this:

      
      
      
      
      // Create the database
      
      
      
      dbmake C:\CUSTOMER\CH15EX.MDB;
      
      
      
      // Build the Customer Types Table
      
      
      
      CREATE TABLE CustomerType(
      
      
      
           CustomerType TEXT(6) CONSTRAINT PKCustomerType PRIMARY KEY,
      
      
      
           Description TEXT(30));
      
      
      
      // Build the Customers table
      
      
      
      CREATE TABLE Customers(
      
      
      
           CustomerID TEXT(10) Constraint PKCustomerID PRIMARY KEY,
      
      
      
           Name TEXT(30),
      
      
      
           CustomerType TEXT(6) CONSTRAINT FKCustomerType REFERENCES
      
      
      
      CustomerType(CustomerType),
      
      
      
           Address TEXT(30),
      
      
      
           City TEXT(30),
      
      
      
           State TEXT(30),
      
      
      
           Zip TEXT(10),
      
      
      
           Phone TEXT(14),
      
      
      
           Fax TEXT(14));
      
      
      
      // Build the index on Zip
      
      
      
      CREATE INDEX SKZip on Customers(Zip);
      
      
      
      // Insert Data
      
      
      
      INSERT INTO CustomerType VALUES('INDV', 'Individual');
      
      
      
      INSERT INTO CustomerType VALUES('BUS', 'Business - Non-corporate');
      
      
      
      INSERT INTO CustomerType VALUES('CORP', 'Corporate Entity');
      
      
      
      INSERT INTO Customers Values('SMITHJ', 'John Smith', 'INDV',
      
      
      
           '160 Main Street', 'Dublin', 'Ohio', '45621',
      
      
      
           '614-569-8975', '614-569-5580');
      
      
      
      INSERT INTO Customers Values('JONEST', 'Jones Taxi', 'BUS',
      
      
      
           '421 Shoe St.', 'Milford', 'Rhode Island', '03215',
      
      
      
           '401-737-4528', '401-667-8900');
      
      
      
      INSERT INTO Customers Values('JACKSONT', 'Thomas Jackson', 'INDV',
      
      
      
           '123 Walnut Street', 'Oxford', 'Maine', '05896',
      
      
      
           '546-897-8596', '546-897-8500');
      
      
      
      // Copy data into the localities table
      
      
      
      SELECT CustomerID, City, State INTO Localities FROM Customers;
      
      
      
      // Display the results
      
      
      
      SELECT * FROM CustomerType;
      
      
      
      SELECT * FROM Customers;
      
      
      
      SELECT * FROM Localities;
    3. You would issue the following SQL statement to delete the SMITHJ record from the Customers table:

      
      
      
      
      DELETE FROM Customers WHERE CustomerID = 'SMITHJ';

      You would use the DROP TABLE command to delete an entire table. To delete the Customers table, you would issue the following statement:

      
      
      
      
      DROP TABLE Customers;

    Answers to Day 17 Quiz

    1. It is not necessarily a good idea to look at database optimization strictly from the point of view of processing performance. Other factors such as data integrity are also important. The role of data normalization is to strike a balance between speed and integrity.
    2. If the term First Normal Form is applied to a database, it means that the first rule of data normalization—eliminate repeating groups—has been achieved.
    3. The first rule of data normalization is to delete repeating groups, whereas the second rule of normalization requires the deletion of redundant data. Rule one requires the separation of fields that contain multiple occurrences of similar data into separate tables. Rule two requires that fields that must maintain constant relationship to other fields (for example, the name of a customer as associated with the customer ID) should be placed in a separate table.
    4. Do not include calculated fields in a data table. Not only does the calculated data take up disk space, but problems can arise if one of the fields used in the calculation is deleted or changed. Calculations are best saved for forms and reports. Placing a calculated field in your data table violates the third rule of data normalization—eliminate columns not dependent on keys.
    5. You would invoke the fourth rule of data normalization if you have multiple independent one-to-many relationships within the same table. You need to utilize this rule when you unwittingly create relationships that do not necessarily exist. For example, if you included educational degree in the Employee skills table in the examples used in this lesson, you mistakenly aligned skills with degrees that do not necessarily match.
    6. You would invoke the fifth rule of data normalization if you have multiple dependent many-to-many relationships. To resolve any potential conflict under this rule, you might need to break the different components of the relationships into separate tables and link them through another table.

    Answers to Day 17 Exercises

    1. To achieve the first normal form, you must delete repeating groups. In this exercise, this includes the fields for the multiple automobiles (VehicleType1, Make1, Model1, Color1, Odometer1, VehicleType2, Make2, Model2, Color2, Odometer2). This requires that you create two tables. The first would track the customers (Customers), and the second would track their vehicles (Vehicles).

      Customers Table Vehicles Table
      CustomerID (Primary Key) SerialNumber (Primary Key)
      CustomerName CustomerID (Foreign Key)
      License VehicleType
      Address Make
      City Model
      State Color
      Zip Odometer
      Phone

      Please note that by separating the VehicleTypes into a separate table, you can have any number of vehicles for a customer. Also note that SerialNumber makes a better primary key than license because the serial number of an automobile will not change, whereas a license plate can change on an annual basis.

      Next, you need to reach the second normal form. This requires you to take the Customer and Vehicle tables and remove any redundant data. There is no redundant data in the Customers table. The Vehicles table, on the other hand, has redundant data describing the VehicleType. You should move the type information into a separate table to yield the following structure:

      Customers Vehicles VehicleTypes
      CustomerID
      (Primary Key)
      SerialNumber
      (Primary Key)
      VehicleType
      (Primary Key)
      CustomerName CustomerID
      (Foreign Key)
      Make
      Address License Model
      City VehicleType
      (Foreign Key)

      State Color
      Zip Odometer
      Phone

      To reach the third normal form, you must delete any fields that do not describe the primary key. A review of all fields shows that you have already eliminated any fields that do not describe the entire primary key.

      To achieve the fourth normal form, you need to separate any independent one-to-many relationships that can potentially produce unusual answers when you query the data. The Vehicles table does have several one-to-many relationships with the CustomerID and the VehicleType fields. The combination of these two fields in the same table would not, however, lead to misleading results further down the line. Therefore, you do not need to make any changes to reach the fourth normal form.

      Similarly, no changes need to be made to reach the fifth normal form because you have no dependent many-to-many relationships in your tables. Most data structures will not require you to use the fourth and fifth rules of normalization to optimize your structure.

      As a final point, you might want to add a Comments field to each table. This allows users to store any miscellaneous data they choose to track. Adding a memo field to track comments is a good idea in almost every table, because memo fields do not take up room when empty, and they provide great flexibility to your system.

    2. The following SQL code builds these tables.

      NOTE:

      Please note that you need to create the VehicleTypes table before the Vehicles table. This is required because the Vehicles table has a foreign constraint to the VehicleTypes table. In such situations, the foreign key must be defined prior to its use in another table, or an error will occur.


      
      
      
      
      Create Table Customers
      
      
      
         (CustomerID TEXT (10),
      
      
      
           CustomerName TEXT (40),
      
      
      
           Address TEXT (40),
      
      
      
           City TEXT (40),
      
      
      
           State TEXT (20),
      
      
      
           Zip TEXT (10),
      
      
      
           Phone TEXT (14),
      
      
      
           Comments MEMO,
      
      
      
           CONSTRAINT PKCustomers Primary Key (CustomerID));
      
      
      
      Create Table VehicleTypes
      
      
      
         (VehicleType TEXT (10),
      
      
      
           Make TEXT (25),
      
      
      
           Model TEXT (25),
      
      
      
           Comments MEMO,
      
      
      
           CONSTRAINT PKVehicleTypes Primary Key (VehicleType));
      
      
      
      Create Table Vehicles
      
      
      
         (SerialNumber INTEGER,
      
      
      
           CustomerID TEXT (10),
      
      
      
           License TEXT (10),
      
      
      
           VehicleType TEXT (10),
      
      
      
           Color TEXT (15),
      
      
      
           Odometer INTEGER,
      
      
      
           Comments MEMO,
      
      
      
           CONSTRAINT PKVehicles Primary Key (SerialNumber),
      
      
      
           CONSTRAINT FKCustomer Foreign Key (CustomerID)
      
      
      
      REFERENCES  Customers(CustomerID),
      
      
      
           CONSTRAINT FKType Foreign Key (VehicleType)
      
      
      
      REFERENCES VehicleTypes(VehicleType));

    Answers to Day 18 Quiz

    1. The Microsoft JET database engine provides three levels of locking: database locking, which locks the entire database for exclusive use; table locking, which locks a table for exclusive use; and page locking, which locks data pages 2K in size
    2. You would want to use database locking when compacting a database because compacting affects all the objects in a database.
    3. You would want to use table locking when doing a mass update of a single table. You want exclusive use of the data to be changed, but you do not necessarily have to have exclusive use of the entire database when performing field update functions.
    4. You use the LockEdits property of a recordset to control how page locking is handled by your application. Setting this property to True means you will have pessimistic locking. Setting this property to False means you will have optimistic locking.
    5. Pessimistic locking prohibits two users from opening a data page at the same time (that is, when the Edit or AddNew method is invoked). Optimistic locking permits two users to open the same page but only allows updates to be saved by the first user to make the changes.
    6. You cannot use pessimistic locks on an ODBC data source. ODBC data sources use optimistic locking only.
    7. When cascading deletes are used in a relationship, each time a base table element is deleted, all foreign table records that contain that element will be deleted.
    8. You use transaction management in your applications to provide an opportunity to reverse a series of database updates if your programs fails to complete all requested data changes. This is particularly useful if you have processes that affect multiple tables within the database. Failure to fully complete such a transaction could lead to a database that has lost or inaccurate data. This can also result in a database that is difficult or impossible to repair.
    9. The limitations of transactions include the following:
      • Some database formats do not support transactions.
      • Data sets that are the result of some SQL JOIN or WHERE clauses, and data sets that contain data from attached tables will not support transactions.
      • Transaction operations are kept on the local workstations, which could lead to errors if the process runs out of space in the TEMP directory.
    10. Declaring a unique workspace object is not required; however, it is highly recommended that you do so because transactions apply to an entire workspace.

    Answers to Day 18 Exercises

    1. Enter the following code to load a database exclusively when you bring up a form:

      
      
      
      
      Private Sub Form_Load()
      
      
      
          Dim DB As Database
      
      
      
          Dim dbName As String
      
      
      
          On Error GoTo FormLoadErr
      
      
      
          dbName = App.Path + \abc.mdb"
      
      
      
          Set DB = DBEngine.OpenDatabase(dbName, True) ' Open database exclusive
      
      
      
          MsgBox "Database opened successfully"
      
      
      
          GoTo FormLoadExit
      
      
      
      FormLoadErr:
      
      
      
          MsgBox "Unable to load database ABC.MDB"
      
      
      
          GoTo FormLoadExit
      
      
      
      FormLoadExit:
      
      
      
          Unload Me
      
      
      
      End Sub
    2. Enter the following code in the Form Load event to load a table exclusively:

      
      
      
      
      Private Sub Form_Load()
      
      
      
          Dim db As Database
      
      
      
          Dim rs As Recordset
      
      
      
          Dim dbName As String
      
      
      
          Dim tabName As String
      
      
      
          dbName = App.Path + "\abc.mdb"
      
      
      
          tabName = "Customers"
      
      
      
          On Error GoTo FormLoadErr
      
      
      
          Set db = DBEngine.OpenDatabase(dbName)
      
      
      
          Set rs = db.OpenRecordset(tabName, dbOpenTable,
      
      
      
          dbDenyRead + dbDenyWrite) ' table opened exclusively
      
      
      
          MsgBox "Table opened exclusively"
      
      
      
          GoTo FormLoadExit
      
      
      
      FormLoadErr:
      
      
      
          MsgBox "Unable to load table exclusively"
      
      
      
          GoTo FormLoadExit
      
      
      
      FormLoadExit:
      
      
      
          Unload Me
      
      
      
      End Sub
    3. To start the project, insert the following code into the general declarations section:

      
      
      
      
      Option Explicit
      
      
      
      'Declaration of global variables
      
      
      
      Dim DB As Database
      
      
      
      Dim wsUpdate As Workspace
      
      
      
      Dim nErrFlag As Integer

      Next, start a new procedure and insert the following code. This code creates a workspace and opens the database.

      
      
      
      
      Public Sub OpenDB()
      
      
      
          On Error GoTo OpenDBErr
      
      
      
          Dim dbName As String
      
      
      
          nErrFlag = 0 'Reset the error flag
      
      
      
          dbName = App.Path + "\abc.mdb"
      
      
      
          'Open the workspace and database
      
      
      
          Set wsUpdate = DBEngine.CreateWorkspace("WSUpdate", "admin", "")
      
      
      
          Set DB = wsUpdate.OpenDatabase(dbName, True)
      
      
      
          GoTo OpenDBExit
      
      
      
      OpenDBErr:
      
      
      
          MsgBox Trim(Str(Err)) + " " + Error$(Err), vbCritical, "OpenDB"
      
      
      
          nErrFlag = Err
      
      
      
      OpenDBExit:
      
      
      
      End Sub

      Now build the following procedure to perform the posting:

      
      
      
      
      Public Sub Post()
      
      
      
          On Error GoTo PostErr
      
      
      
          Dim cSQL As String
      
      
      
          wsUpdate.BeginTrans
      
      
      
          'Create the SQL statement to insert the records.
      
      
      
      'Note that we do not use the TransNo field 
      
      
      
      'as it is a counter field necessary only 
      
      
      
      'for the Transactions table
      
      
      
          cSQL = "INSERT INTO History Select CustID, InvoiceNo,
      
      
      
      Amount FROM Transactions"
      
      
      
          DB.Execute cSQL
      
      
      
          'Delete the temporary transactions data
      
      
      
          cSQL = "DELETE FROM Transactions"
      
      
      
          DB.Execute cSQL
      
      
      
          'Commit the transactions
      
      
      
          wsUpdate.CommitTrans
      
      
      
          MsgBox "Transactions have been committed"
      
      
      
          'Set the error flag and exit the program
      
      
      
          nErrFlag = 0
      
      
      
          GoTo PostExit
      
      
      
      PostErr:
      
      
      
          'Display the error and rollback the transactions
      
      
      
          MsgBox Trim(Str(Err)) + " " + Error$(Err), vbCritical, "Post"
      
      
      
          wsUpdate.Rollback
      
      
      
          MsgBox "Post routine has been aborted"
      
      
      
      PostExit:
      
      
      
      End Sub

      Finally, insert the following code into the cmdPost_Click event:

      
      
      
      
      Private Sub cmdPost_Click()
      
      
      
          OpenDB
      
      
      
          If nErrFlag = 0 Then
      
      
      
              Post
      
      
      
          End If
      
      
      
          If nErrFlag <> 0 Then
      
      
      
              MsgBox "Error Reported", vbCritical, "cmdPost"
      
      
      
          End If
      
      
      
          Unload Me
      
      
      
      End Sub

      You can test this program by building the database in VisData or Data Manager and then inserting some sample records into the Transactions table.

    Answers to Day 19 Quiz

    1. The letters ODBC stand for Open Database Connectivity.
    2. When you use the Microsoft JET interface to connect to an ODBC data source, your Visual Basic program must first communicate with Microsoft JET, which communicates to the ODBC front end. When you use the ODBC API calls, your Visual Basic program communicates directly with the ODBC front end, skipping the Microsoft JET layer entirely.
    3. When you use the ODBC API to link to your data, you are actually creating a static, Snapshot-type, data set. You must collect a set of data and bring it back to your workstation. You might also be limited by the amount of memory available on the workstation.
    4. You use the ODBC Administrator to define or modify ODBC data sources. This program is part of the Windows Control Panel.
    5. You can use the ODBC interface to connect to Excel spreadsheets and even text files, as long as an ODBC driver is installed on your system to handle the data format. There is no restriction to the type of data that you can access from an ODBC data source (as long as a driver exists for the format).
    6. You cannot use the same set of API declarations for the 32-bit Visual Basic 4.0 as you do for the 16-bit Visual Basic 4.0. Today's exercises work with the 16-bit version only. If you want to access the ODBC interface through 32-bit API calls, you need a new set of declarations for the 32-bit ODBC API. These can be found in the file ODBC32.TXT. This file was added when you installed Visual Basic 4.0.
    7. Before you can pass an SQL SELECT statement to a new ODBC data source, you must complete the following four preliminary steps:
      • Allocate an Environment Handle (SQLAllocEnv) to create a unique identifier for this ODBC session.
      • Allocate a Connection Handle (SQLAllocConnect) to create a unique identifier for this ODBC connection.
      • Connect to the ODBC data source (SQLConnect) using the data source name, user login, and password.
      • Allocate a Statement Handle (SQLAllocStmt) to create a unique identifier for passing data and SQL statements back and forth.

    Answers to Day 19 Exercises

    To complete this assignment, you must first register a new ODBC data source on your workstation. To do this, complete the following steps:

    1. Call up the 16—bit ODBC Administrator and, at the Data Sources dialog box, click the Add button to add a new data source.
    2. At the Add Data Source dialog box, select Access 2.0 for Microsoft Office (*.MDB) driver.
    3. At the ODBC Microsoft Access 2.0 Setup dialog box, enter Ch1902 as the data source name and Chapter 19 Exercise as the description. Click the Select button to locate and select the C:\TYSDBVB\CHAP19\EXER19.MDB data file. Click OK to save this data source.
    4. At the Data Sources dialog box, click OK to exit.

    Now you need to call up the 16-bit version of Visual Basic and create the data entry form project by completing the following steps:

    1. Load 16-bit Visual Basic 4.0.
    2. Load the existing project called TYSODBC.VBP.
    3. Select File | Save File As and save the form as EXER19.FRM.
    4. Select File | Save Project As and save the project as EXER19.VBP.
    5. Modify the following lines in the Form_Load event of the EXER19.FRM form:

      
      
      
      
      cDSN = "EXER19"           ' ODBC data source
      
      
      
          cUser = "Admin"           ' login ID
      
      
      
          cPW = " "                 ' empty password
      
      
      
          cTable = "Transactions"   ' default base table
      
      
      
          cKeyFld = "OrderNbr"      ' default key field
      
      
      
          cRefresh = "SELECT * FROM Transactions ORDER BY OrderNbr"
      
      
      
          ' default SQL
      
      
      
          cFormTitle = "Chapter 19 Exercise"   ' form title
    6. Save and run the project.

    Answers to Day 20 Quiz

    1. The disadvantages and limitations of using the Microsoft Access SYSTEM.MDA file to secure a database include the following points:
      • You must own Microsoft Access to create a SYSTEM.MDA file. You can't use a Visual Basic utility to create a SYSTEM.MDA.
      • It is possible to have multiple SYSTEM.MDA files, which could lead to problems if the wrong file is used.
      • System security can be removed simply by deleting the SYSTEM.MDA file.
      • Some applications do not recognize the SYSTEM.MDA file. It is possible for these applications to skirt security implemented with this file.
    2. The disadvantages of using data encryption to secure a database include the following points:
      • Encryption affects an entire database and cannot be applied only to critical tables.
      • Encrypted databases can't be read by other programs. This makes dissemination of information more difficult.
      • Encrypted databases cannot be replicated.
    3. Application Security focuses on processes, not just the underlying data. Application Security focuses on granting access to forms, reports, and procedures. Database Security, on the other hand, focuses strictly on the data and the database.
    4. These are the two main features of any good application security scheme:
      • It must have a process that allows users to log in to the application using stored passwords.
      • It must have an access rights scheme that limits the functions that users can perform within the system.
    5. Application security schemes can't prevent unauthorized use of your data by tools such as Visdata and Data Manager. Application Security only works within an application. Therefore, you should not rely on it as the only means of securing your application.
    6. Access rights security schemes build an added level of security into your application. This type of security allows you to define a set of secured operations within your application and then define access rights for each of the operations on a user-by-user basis.
    7. You add audit trails for these reasons:
      • To track when users log into and out of the applications.
      • To provide detail as to the status of the application when a system error occurs. This will help with on-going system maintenance.
      • To keep a record of major user activities, such as data table updates and the running of key reports or processes.

    Answers to Day 20 Exercises

    Perform the following steps to complete this process:

    1. Start Visual Basic and load 20ABC01.VBP.
    2. Load Data Manager from the Add/Ins menu.
    3. Create the new database CH20EX.MDB.
    4. Build the Assets table. Add some sample records.
    5. Return to Visual Basic and build form frmFixedAssets by dropping a data control on a new form and adding text fields and labels for each field of your data table.
    6. Modify the Main procedure of the LIBUSER.BAS module to call the form you created in the previous step. You simply substitute the new form for the name of the usrMaint form used in the example. Use the following code as a guide to make the change.

      
      
      
      
      If accGetSet(usrUserID, dbUsers, rsAccess) = accOK Then
      
      
      
                  frmFixedAssets.Show vbModal
      
      
      
                  'usrMaint
      
      
      
              End If
    7. Enter the following code in the Validate event of the data control on your frmFixedAssets.

      
      
      
      
      Private Sub Data1_Validate(Action As Integer, Save As Integer)
      
      
      
          On Error GoTo Data1Err
      
      
      
          errProcStack errPush, "LibUser.logChanged"
      
      
      
          'Declare variables
      
      
      
          Dim cField As String
      
      
      
          Dim lFlag As Boolean
      
      
      
          'Reset the flag for changing of data
      
      
      
          lFlag = False
      
      
      
          'Set flag if data changes
      
      
      
          If Text1.DataChanged Then lFlag = True
      
      
      
          If Text2.DataChanged Then lFlag = True
      
      
      
          If Text3.DataChanged Then lFlag = True
      
      
      
          If Text4.DataChanged Then lFlag = True
      
      
      
          If Text5.DataChanged Then lFlag = True
      
      
      
          If Text6.DataChanged Then lFlag = True
      
      
      
          'Write log if record changes
      
      
      
          If lFlag = True Then
      
      
      
              logWriteFile "RecUpdate", cRecordSet:="CH20EX.MDB.Assets",
      
      
      
               cKey:="AssetID=" + Trim(Text1.Text)
      
      
      
          End If
      
      
      
          GoTo Data1Exit
      
      
      
      Data1Err:
      
      
      
          nResult = errHandler(Err, Error$, errResume)
      
      
      
          GoTo Data1Exit
      
      
      
      Data1Exit:
      
      
      
          errProcStack errPop, "" 
      
      
      
      End Sub

      This code checks to see whether any data has been changed in the current record. If so, an entry record is made in the audit log.

    8. Run the project and log in as USERA with a password of USERA. Enter some records and then edit them. Read the log file, which can be found in the Visual Basic default directory.

    Answers to Day 21 Quiz

    1. You use the pound sign (#) for a context string. You cannot put spaces in the context string footnote.
    2. You insert the dollar sign ($) as the custom mark for a title footnote. These footnotes can include spaces.
    3. Keywords will be used in the Search box of your help application. You use the semicolon (;) to separate multiple keywords in a topic.
    4. You should save your topic file in a rich text format and in your word processor's normal file format (in case you need to make subsequent revisions). Your project file should be saved in ASCII text and be given an .HPJ extension.
    5. The contents page of your help file is declared in the [Options] section of the project file by making the following entry:

      
      
      
      
                Contents = ContextString

      In this entry, the context string is the topic you want displayed as the contents page. The first topic of your help file will default as the contents page if none is declared.

    6. You set the HelpContextID property to identify the help file that displays when the control has focus and F1 is pressed. The numeric value for this field is determined in the [Map] section of the project file.
    7. Allow approximately one hour for each typed page of documentation. Adjust this figure for your personal writing style.

    Answers to Day 21 Exercises

    1. + Processing:3
    2. Creating a New ProjectNewProject

      Note that the jump text is double underlined. Also note that the context string, NewProject, should be formatted as hidden text.

    3. Just change the double-underlined text to single-underlined text and the jump becomes a pop-up.
    4. You will perform the following steps in order to complete this exercise:
      1. Open your topic file.
      2. Enter text for a new topic. Give the topic a descriptive heading such as The Company Master Form.
      3. Insert footnotes for the context string, title, and keywords.
      4. Save the file in RTF format.
      5. Add the context string to the [Map] section of the project file.
      6. Compile your project.
      7. Set the HelpContextID of the form to the number you have assigned the context string in the project file.
      8. Execute your program and press F1.