|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Appendix C
  - Answers to Quizzes and Exercises
  - - Day 1
    - Day 2
    - Day 3
    - Day 4
    - Day 5
    - Day 6
    - Day 7
    - Day 8
    - Day 9
    - Day 10
    - Day 11
    - Day 12
    - Day 13
    - Day 14
    - Day 15
    - Day 16
    - Day 17
    - Day 18
    - Day 19
    - Day 20
    - Day 21

## Appendix C

## Answers to Quizzes and Exercises

##

#### Day 1

Answers to Day 1 Quiz

  1. The two data control properties you must set when you link 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. An example of this code is

>     datTitles.Recordset.Delete  ` delete the current record

  5. Select either the Save Changes or Prompt to Save Changes option from the Program Starts environmental variable option group. This can be found by selecting the menu option Tools | Options and then selecting the Environment tab. Once one of these choices is selected, you save, or will be prompted to save, your project each time you run it.

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" 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 Name property to cmdExit for the new button.

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

  - Set the Caption property to E&xit.

  - Enter the following code in the `cmdExit_Click` procedure:

> >     Private Sub cmdExit_Click()
> >        End
> >     End Sub

- Save your changes and execute your program.

- Click the Exit button to stop the program.

  3. Modify the `cmdAdd_Click` procedure as shown below to set the focus on the ISBN field when the Add button is pressed.

>     Private Sub cmdAdd_Click()
>        datTitles.Recordset.AddNew  ` Add a new record to the table
>        txtISBN.SetFocus  ` Set the focus to the txtISBN control
>     End Sub

#### Day 2

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, however, 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 allows users to enter up to 32767 without reporting an error, but any value higher than 255 is truncated to a single-byte value.
  8. Any attempt to edit and update a counter field results 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 is 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.
  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 appears 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 the checkbox control. Second, position the checkbox in an aesthetically pleasing position on the form. 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.

#### Day 3

Answers to Day 3 Quiz

  1. Visual Basic database objects are dataset-oriented. You work with a set of records at one time, not one record at a time as you would with a record-oriented database.
  2. The Dynaset is the most common Visual Basic data object. It is the object 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:
  - You can't specify an index with a Dynaset. Dynasets are only a portion of the underlying table, whereas indexes are for the entire table.

  - You can't use the `Seek` method with Dynasets.

  - Errors can occur if records in the underlying table have been altered or deleted between the time that the Dynaset is created and the time that a record is updated.
  5. Table data objects allow you to use 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 3 Exercises

  1. You use the Dynaset data object because it is the only data object that can update an ODBC data source. Your code might look like this:

>     Sub Form_Load()

>     `Create a database and dynaset object
>     Dim Dat as Database
>     Dim rsDyn as Recordset

>     `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 rsDyn = Dat.OpenRecordset(cTable,dbOpenDynaset)

>     End Sub

  2. The Snapshot data object should be used for this purpose because it does not change after it is created. This prevents the data used in your report from being updated while your report is generating.
  Your code might look like this:

>     Sub Form_Load()

>     `Create a database and snapshot object
>     Dim Dat as Database
>     Dim rsSnap as Recordset

>     `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 rsSnap = Dat.OpenRecordset(cTable,dbOpenSnapshot)

>     End Sub

  3. You use the Table data object because it gives you instant information when records are changed. Your code might look like this:

>     Sub Form_Load()

>     `Create a database and table object
>     Dim Dat as Database
>     Dim tblObject as Recordset

>     `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.OpenRecordset(cTable,dbOpenTable)

>     End Sub

#### Day 4

Answers to Day 4 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 4 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 look 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

#### Day 5

Answers to Day 5 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 returns 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 txtUpper field is being trimmed of spaces and then tested to see whether the length is anything other than zero. This code is used to test whether 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 5 Exercises

  1. Enter the following code inside your field's `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.

#### Day 6

Answers to Day 6 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 Pro can attach to any database type recognized by Visual Basic 5. This includes Microsoft Access, dBASE, FoxPro, Btrieve, Paradox, and any ODBC data source.
  3. You can type text directly on a form in Crystal Reports Pro, 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 Pro by selecting File | New and then selecting the Mail Label item.
  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 selection criteria to your Crystal Reports Pro report by choosing the Select Records Expert from the Report menu and then entering your criteria.
  7. You can join tables in Crystal Reports Pro by selecting Visual Linking Expert from the Database menu option.

Answers to Day 6 Exercises

  1. Here is the formula:

>     Count({NameLast})

1. 2. `IsNull({EmployerID})`
3. 3. Perform the following steps to build the report:
   - Start Crystal Reports Pro and select New from the File menu. Select the `BOOKS5.MDB` database.

   - Double-click the PubID field in the PublisherComments table and drop the field in the detail band of the form. Do the same for the Publisher and Comments fields.

   - Choose Printer Setup from the File menu and then select the Landscape option.

   - Drop the Name field in the detail band. Next, select Database | Visual Linking Expert. Create the link between the Publisher Comments and the Publishers tables on the PubID field, if it is not already done.

   - Select Sort Records from the Report menu. Double-click the Name field and set the sort direction to descending.

   - Select Text Field from the Insert menu. Enter Comments on Publishers as the title text and then select Accept. Drop the field in the middle of the page header band. Select Format | Font and then select Arial, 14 point bold, and press OK.

   - To insert the count of the records, select the `PubID` field and then choose Grand Total from the Insert menu. Select Count from the combo box that appears and then press OK.

   - Select Insert | Special Field | Page Number Field and drop the field in the page footer band of the report.

   - Select Special Field | Print Date Field from the Insert menu. Drop the field in the page footer band of the report.

   - Print the report by selecting Print | Printer from the File menu.

   - Print the report definition by selecting Print | Report Definition from the File menu.

#### Day 7

Answers to Day 7 Quiz

  1. The Visdata project can be found in the `Samples\Visdata` subdirectory of Visual Basic 5.
  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 Properties object in the Database window shows the complete name and path of the database, the version of the database engine in use, the connect property, the login time-out, the Microsoft Access version, the replica ID, and several other update settings.
  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 File | Compact MDB command. This action is not advisable, however, as problems can occur during the compacting process.
  8. You cannot 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:
  - Microsoft Access (Jet)

  - dBASE IV, III

  - FoxPro 2.6, 2.5, 2.0

  - Paradox 4.x, 3.x

  - Btrieve

  - Excel 5, 4, 3

  - Text

  - ODBC
  11. You can use the Files | Compact Database option to convert existing Microsoft Access 2.0 databases to newer versions by selecting the new data format at the Compact Database submenu. Do not do this, however, as this is not the same converter used by Microsoft Access, and some objects in the database will be unusable by Access after the conversion.

Answers to Day 7 Exercises

  1. To create the new database, select File | New | Microsoft Access | Version 7.0 MDB. Next, enter the path and the name of the database, and save.
  2. Click the alternate mouse button in the Database window and select New from the menu that appears 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 you complete each one. 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 `DatabaseQueries` window, and select Design from the menu that appears when you click the alternate mouse button in the Database window. Select the Add Index button from the Table Structure window. Enter the name of the primary key (`PKtblCustomers`), and 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 Database window and click the alternate mouse button. Select Design from the menu that appears. Next, select Print Structure in the bottom right corner of the Table Structure window.
  5. To enter records, first double-click `tblCustomers`. You can enter data in any Form type you want; however, you can only 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 File | 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 (,,).

#### Day 8

Answers to Day 8 Quiz

  1. SQL stands for Structured Query Language. You pronounce SQL by saying the three individual letters (ess-que-ell). 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 updatable result sets whose records have an exact match in both tables. The `LEFT JOIN` is used to return an updatable 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 8 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"`

#### Day 9

Answers to Day 9 Quiz

  1. 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.
  2. The top level DAO is the DBEngine.
  3. You use the `RepairDatabase` method to repair a database. This command uses the following syntax:

>     DBEngine.RepairDatabase DatabaseName

  4. The syntax for the `CompactDatabase` method is

>     DBEngine.CompactDatabase oldDatabase, newDatabase, locale, options

  Please note that `oldDatabase` and `newDatabase` require the database name and path.
  5. Visual Basic creates a default Workspace if you fail to identify one when you open a database.
  6. The `OpenRecordset` method can open data from a data source as a Table, a Dynaset, or a Snapshot.
  7. The `CreateTableDef` method builds a table in a database. The syntax of this statement is

>     Database.CreateTableDef(table name)

  8. Use the Type property of the Field object to display the data type of a table column.
  9. The Index data object can be used to contain information on Microsoft Jet databases only.
  10. 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.

Answer to Day 9 Exercise Drop a command button onto a form, name it cmdCreate, and 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 table 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

#### Day 10

Answers to Day 10 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 used only on Recordsets opened as tables. `Seek` cannot be used 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 dataset.
  8. The `Seek` method is the fastest way to locate a record in a dataset.
  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 dataset with the `Update` method.

Answers to Day 10 Exercise 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 code and a field for City. Set the Tag property of these two textboxes 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 alignment 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.

#### Day 11

Answers to Day 11 Quiz

1. 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 datasets 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 following code moves the data pointer to the end of a dataset 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 11 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

#### Day 12

Answers to Day 12 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 the name of the dataset 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 dataset 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 property 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 12 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 fields.
  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 dataset 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 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. Resize 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.

#### Day 13

Answers to Day 13 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.

  - 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 Microsoft 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`

Answer to Day 13 Exercise Enter the following code to build the CustomerType and Customers tables. Please note that the CustomerType table must be built before the Customers table, because of the foreign key constraint on CustomerType in the Customers table.

    // Create the database
    dbmake C:\CUSTOMER\CH13EX.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;

#### Day 14

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
  - `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 users have done something that they can easily correct. For example, a 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 enters 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` command or the `END` command to terminate the program when there is no good way to return to the program once the error has occurred. This might happen 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 deletion.

  - 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 at which 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.
  11. The `Err.Raise` method allows you to create your own error message and confirm to the Visual Basic standard, including the ability to "post" a message to another application through the OLE object model interface.

Answers to Day 14 Exercises

  1. Insert a command button on a new form, and then double-click 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.Number = 53 Then
>             cMsg = "Unable to open ABC.TXT" + Chr(13)
>             MsgBox cMsg, vbCritical, "Command1Click"
>             Unload Me
>             End
>         Else
>             MsgBox CStr(Err.Number) & " - " & Err.Description, 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 CStr(Err.Number) & " - " & Err.Description
>             Resume Next
>         End If
>     `Routine exit
>     Command2ClickExit:
>     End Sub

#### Day 15

Answers to Day 15 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 15 Exercises

1. 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';

1. 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;

#### Day 16

Answers to Day 16 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 relationships with 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 invoke the fourth rule of data normalization if you have multiple independent one-to-many relationships within the same table. You need to use 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 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 16 Exercises

  1. To achieve 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 tracks the customers (Customers), and the second tracks 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 does change, whereas a license plate can change on an annual basis.
  Next, you need to reach 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 | SerialNumber | VehicleType
  (Primary Key) | (Primary Key) | (Primary Key)
  CustomerName | CustomerID | Make (Foreign Key)
  Address | License | Model
  City |  | VehicleType (Foreign Key)
  State | Color |
  Zip | Odometer |
  Phone |  |

  To reach 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 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 Fourth Normal Form.
  Similarly, no changes need to be made to reach Fifth Normal Form because you have no dependent many-to-many relationships in your tables. Most data structures do 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 key constraint to the VehicleTypes table. In such situations, the foreign key must be defined prior to its use in another table, or an error occurs.

      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));

  ####

#### Day 17

Answers to Day 17 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 2KB in size.
  2. You want to use database locking when compacting a database because compacting affects all the objects in a database.
  3. You 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 have pessimistic locking. Setting this property to `False` means you 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 are deleted.
  8. You use transaction management in your applications to provide an opportunity to reverse a series of database updates if your program 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.

  - Datasets that are the result of some SQL `JOIN` or `WHERE` clause, and datasets that contain data from attached tables do 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 17 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.

#### Day 18

Answers to Day 18 Quiz

  1. The Standard Data control uses the Microsoft Jet database engine to connect to databases. The Remote Data control uses a different data engine designed to connect to remote database management systems (RDBMSs).
  2. Cursor drivers are the tools that manage the location of the recordset pointer in a dataset. RDO/RDC connections can use client-side or server-side cursors.
  3. The four dataset types available when using RDO/RDC connections are:
  - rdOpenForwardOnly--Creates a read-only, scroll-forward-only dataset. This is the default option.

  - rdOpenStatic--Creates an updatable dataset that has non-changing membership.

  - rdOpenDynamic--Creates an updatable dataset that has changing membership. Record keys are not used.

  - rdOpenKeyset--Creates an updatable dataset that has changing membership. Record keys (the key set) are created to point to all members of the set. This enables you to use bookmarks with the dataset.
  The five lock types you can use with RDO/RDC are:
  - rdConcurReadOnly--Provides no row-level locking. This is the default option.

  - rdConcurLock--Provides pessimistic locking for the entire row set. The lock occurs as soon as the data is accessed--not when an edit operation begins.

  - rdConcurRowver--Provides optimistic locking based on internal row ID values (usually, the TimeStamp column).

  - rdConcurValues--Provides optimistic locking based on a column-by-column check of the data in each row.

  - rdConcurBatch--Provides optimistic locking based on the value in the UpdateCriteria property when using Batch Update mode. Not supported by all RDBMSs.

The Microsoft equivalent of the rdoResultset object is the Recordset object. The rdoResultset and the Microsoft Jet Recordset object both contain the actual collection of data records for display or update.

The RDO/RDC equivalent of the Microsoft Jet Workspace object is the rdoEnvironment object. You can use the rdoEnvironment object and the Workspace object to manage connections to multiple databases and to control transaction management. Answer to Day 18 Exercise To complete this assignment, you need to create a data form that has two buttons, and a list box. You use RDO for this project, so be sure to add the RDO library from the Project | Components menu.

  1. Start a new Visual Basic 5 Standard EXE project.
  2. Add two command buttons (cmdTables and cmdClear) and a list box (List1) to the form.
  3. Add the following code to the form (this is all the code you need).

>     Private Sub cmdTables_Click()
>         `
>         ` get rdo table collection
>         `
>         On Error GoTo LocalErr
>         `
>         Dim rdoEnv As rdoEnvironment
>         Dim rdoCon As rdoConnection
>         Dim rdoTbl As rdoTable
>         `
>         ` set env/con
>         Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "")
>         Set rdoCon = rdoEnv.OpenConnection("")
>         `
>         ` update the tables collection
>         rdoCon.rdoTables.Refresh
>         `
>         ` show table properties
>         List1.Clear
>         For Each rdoTbl In rdoCon.rdoTables
>             List1.List(List1.ListIndex) = rdoTbl.Name & " [" & rdoTbl.Type Â& "]"
>         Next
>         `
>         rdoCon.Close
>         rdoEnv.Close
>         Set rdoTbl = Nothing
>         Set rdoCon = Nothing
>         Set rdoEnv = Nothing
>         `
>         Exit Sub
>         `
>     LocalErr:
>         MsgBox Err.Description, vbExclamation, "RDOTables"
>         `
>     End Sub

>     Private Sub cmdClear_Click()
>         List1.Clear
>     End Sub

>     Private Sub Form_Load()
>         `
>         Me.Caption = "RDBMS Tables Lister"
>         `
>     End Sub

  4. Save and run the project.

#### Day 19

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, 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 dataset. 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 event 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 the driver exists for that format).
  6. 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.

Answer to Day 19 Exercise To complete this assignment, you must first register a new ODBC data source on your workstation. To do this:

  1. Call up the ODBC Administrator from the Windows Control Panel.
  2. Press the Add button, and select Microsoft Access as the database driver.
  3. Set the DSN name to Exer19 and then press the Database button to select the target database (`C:\TYSDBVB5\SOURCE\CHAP19\EXERCISE\EXER19.MDB`).
  4. Select OK to save the new ODBC definition.

Next you need to create a Visual Basic 5.0 Standard EXE project that is almost identical to the `prjSQLTest` project you built in this chapter. If you want, you can copy that project to a new directory and make the following changes:

  1. Copy the `prjSQLTest` project to a new directory.
  2. Load the `prjSQLTest` project.
  3. Modify the `StartDB` method to match the following code:

>     Public Sub StartDB()
>         `
>         ` handle chores of connecting and getting data
>         `

>         ` create reference to ODBC object
>         Set objSQL = New objODBC
>         `
>         ` populate properties
>         objSQL.DataSource = "Exer19" ` <<< modified line
>         objSQL.UserID = "admin"
>         objSQL.Password = ""
>         objSQL.SQL = "SELECT * FROM SampleTable" ` <<< modified line
>         objSQL.Table = "SampleTable" ` <<< modified line
>         objSQL.Key = "Name"
>         objSQL.ResultSetType = sqlStatic
>         objSQL.CursorDriver = sqlUseODBC
>         objSQL.LockType = sqlValues
>         `
>         ` do real work
>         objSQL.Connect ` establish connection
>         objSQL.Refresh ` build dataset
>         objSQL.MoveFirst Me ` display first row
>         `
>     End Sub

  4. Modify the screen to contain three input boxes and three label controls. Set the label control captions to: Name, Address, and Phone.
  5. Save and run the project.

#### Day 20

Answers to Day 20 Quiz

  1. Database replication refers to the act of creating a master database (the Design Master) and copies of the master (replicas), and synchronizing data contained in all members of the replica set (the Design Master and all replicas).
  2. You may want to use database replication in systems deployed on a wide area network or to remote users. Replication can also be useful for making backups of databases that cannot be shut down to make a copy. Replication is also good for building datasets that must be "frozen," which is a reason for building static databases and reporting systems.
  3. You do not want to use replication in an application that can be better deployed as an intranet application. Also, replication should not be considered in systems that are heavily transaction-orientated (such as reservation systems), or where data accuracy and timeliness are of the utmost importance (such as emergency response systems).
  4. Three fields are added to each table when a database is turned into a Design Master. The first field, s_Generation, identifies records that changed during data entry. The second, s_GUID, serves as a unique identifier for each record. The third field, s_Lineage, stores the name of the last replica set to update the record.
  5. The Replicable and the ReplicableBool properties are added to the database during creation of the Design Master to indicate that replicas can be made of the Design Master.
  6. A replicable database consumes an additional 28 bytes per record, plus the space required to accommodate the additional system tables added when the database is made replicable.
  7. An AutoNumber field stops incrementing by 1 and starts inserting random numbers for each new record added to a table. This is but one reason why AutoNumber data types should not be used in your application.
  8. You use the `MakeReplica` method to create a copy of the Design Master. You can also use the `MakeReplica` method to create a copy of any member of the replica set (that is, you can use a replica other than the Design Master). In fact, you are better off using a copy of a replica set member as a backup of the Design Master than you are using a tape backup.
  9. In a synchronization conflict, the Microsoft Jet engine takes the record that has been changed the greatest number of times. In the case of a tie, the Microsoft Jet engine takes the record from the replica set member with the lowest ReplicaID.
  10. You can use the star, linear, ring, and fully connected topology synchronization schemes. The star is the most commonly used.
  11. Use the `KeepLocal` method to keep database objects from replicating to other members of the replica set. This method must be used before the Design Master is created.

Answers to Day 20 Exercise You should keep the Design Master at your Cincinnati office. Distribute all tables, except for the SalaryInfo table, to the Los Angeles, Chicago, and New York offices.

For backup, each office should synchronize often with the Cincinnati Office. The Chicago office, given its size, should synchronize more often than the other two remote offices. In case of failure of the Cincinnati database, the Chicago database is probably the best choice to use as a backup. Do not keep a tape backup of the Cincinnati database. The use of a backup copy of the Design Master could be disastrous for the entire replica set.

You should use the star topology in your synchronization strategy. This is the easiest to understand and use, as well as being the most logical because the order in which databases synchronize is unimportant.

The following code can be attached to a command button (in this case cmdKeepLocal) to keep the SalaryInfo table from replicating. It then goes on to turn the `EMPLOYEE.MDB` database into a Design Master, and creates a replica named `COPYCHI.MDB`.

    Private Sub cmdKeepLocal_Click()

        Dim dbMaster As Database
        Dim LocalProperty As Property
        Dim KeepTab As Object
        Dim repProperty As Property

        `Open the database in exclusive mode
        Set dbMaster = OpenDatabase("c:\tysdbvb5\source\data\employee.mdb", True)

        Set KeepTab = dbMaster.TableDefs("SalaryInfo")
        Set LocalProperty = dbMaster.CreateProperty("KeepLocal", dbText, "T")
        KeepTab.Properties.Append LocalProperty
        KeepTab.Properties("Keeplocal") = "T"

        MsgBox "The SalaryInfo table is set to not replicate"

        `Create and set the replicable property
        Set repProperty = dbMaster.CreateProperty("Replicable", dbText, "T")
        dbMaster.Properties.Append repProperty
        dbMaster.Properties("Replicable") = "T"

        `Display a message box
        MsgBox "You have created a Design Master out of EMPLOYEE.MDB!"

        dbMaster.MakeReplica "c:\tysdbvb5\source\data\copychi.mdb", "Replica of " & "dbMaster"

        dbMaster.Close

        MsgBox "You have created a copy of EMPLOYEE.MDB"

    End Sub

Use the following code from the `Click` event of a command button (cmdSynch) to synchronize the Chicago and the Cincinnati databases:

    Private Sub cmdSynch_Click()
        Dim dbMaster As Database

        `Open the database
        Set dbMaster = OpenDatabase("c:\tysdbvb5\source\data\employee.mdb")

        dbMaster.Synchronize "c:\tysdbvb5\source\data\copychi.mdb"

        MsgBox "The synchronization is complete."

    End Sub

#### Day 21

Answers to Day 21 Quiz

  1. The disadvantages and limitations of using the Microsoft Access `SYSTEM` file to secure a database include:
  - You must own Microsoft Access to create a `SYSTEM` security file. You can't use a Visual Basic 5 utility to create a `SYSTEM` file.

  - It is possible to have multiple `SYSTEM` files, which could lead to problems if the wrong file is used.

  - `SYSTEM` file security can be removed by simply deleting the `SYSTEM` file itself.

  - Some applications do not recognize the `SYSTEM` file. It is possible for these applications to skirt the security implemented within the `SYSTEM` file.
  2. The disadvantages of using data encryption to secure a database include:
  - Encryption affects an entire database and cannot be applied only to critical tables.

  - Encrypted databases can't be read by other programs. This makes distribution of data more difficult.

  - Encrypted databases cannot be replicated using the Microsoft Replication Manager tools.
  3. Application security focuses on processes, not just the underlying data. Application security allows you to grant permissions for forms, reports, and procedures. Database security, on the other hand, focuses strictly on the data and the database.
  4. There are the two main features of any good application security scheme:
  - It must have a process that allows users to log into 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 the Visual Data Manager. Application security only works within an application. Therefore, you should not rely on it as the only means of securing your application or your data.
  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 applications.

  - To provide detail as to the status of the application when a system error occurs.

  - To keep a record of major user activities, such as data table updates and the running of key reports or processes.

Answers to Day 21 Exercise You can complete this assignment by using the `SecTest` project as a guide.

  1. Start `SQL-VB5` and build the Assets table in a database called `EXER21.MDB`. Be sure to add the secUsers and secAccess tables in the database definition. Use the SQL-Visual Basic code in the `secTest.SQV` script as a guide.
  2. Add some records to the Assets, secUsers, and secAccess tables in the new database. You can do this with `SQL-VB5` or with the Visual Data Manager.
  3. Now start a new Visual Basic 5.0 Standard EXE project. Create a form with six input textboxes and size label prompts (use control arrays). Add a five-item command button array and a data control to the form.
  4. Add a reference to the new security objects library. Select Project | Components from the main menu and locate and select the library you built in Chapter 21.
  5. Add the following code to the form. This is all the code you need for the form. (You can copy most of this from the SecTest project).

>     Option Explicit
>     `
>     Dim objUser As Object
>     Dim objLog As Object

>     Private Sub cmdBtn_Click(Index As Integer)
>         `
>         ` handle button clicks
>         `
>         Dim X As Integer
>         `
>         On Error GoTo LocalErr
>         `
>         Select Case Index
>             Case 0 ` close
>                 objLog.WriteLog "LogOut", objUser.UserID
>                 objUser.LogUser urLogOut
>                 Unload Me
>             Case 1 ` refresh
>                 Data1.UpdateControls
>             Case 2 ` update
>                 Data1_Validate vbDataActionUpdate, 1
>                 Data1.UpdateRecord
>             Case 3 ` add
>                 Data1.Recordset.AddNew
>                 txtField(0).SetFocus
>             Case 4 ` delete
>                 For X = 0 To 5
>                     objLog.WriteLog "Delete", Data1.RecordSource, txtField(X).DataField, txtField(X), ""
>                 Next X
>                 Data1.Recordset.Delete
>                 Data1.Recordset.MovePrevious
>         End Select
>         `
>         Exit Sub
>         `
>     LocalErr:
>         MsgBox Err.Description, vbExclamation, Err.Number
>         `
>     End Sub

>     Private Sub Data1_Validate(Action As Integer, Save As Integer)
>         `
>         ` log any changes
>         `
>         Dim ctlTemp As Control
>         `
>         MousePointer = vbHourglass
>         `
>         ` check text boxes for changed data
>         For Each ctlTemp In Controls
>             If TypeOf ctlTemp Is TextBox Then
>                 If ctlTemp.DataChanged Then
>                     objLog.WriteLog GetAction(Action), Data1.RecordSource, ctlTemp.DataField, Data1.Recordset.Fields(ctlTemp.DataField), ctlTemp
>                 End If
>             End If
>         Next
>         `
>         MousePointer = vbNormal
>         `
>     End Sub

>     Private Sub Form_Load()
>         `
>         SetForm ` set up form
>         SetLog ` set up log stuff
>         SetUser ` set up user stuff
>         `
>     End Sub

>     Public Sub SetLog()
>         `
>         ` setup logging
>         `
>         Set objLog = New logObject
>         objLog.filename = App.Path & "\" & App.EXEName & ".log"
>         objLog.LogHeader = App.EXEName & " Audit Log"
>         `
>     End Sub

>     Public Sub SetUser()
>         `
>         ` set up user details
>         `
>         Dim ctlTemp As Control
>         `
>         ` create object & set properties
>         Set objUser = New usrObject
>         objUser.DBName = Data1.DatabaseName
>         objUser.LoginTitle = "Assets Login"
>          `
>         ` login and set up rights
>         If objUser.UserLogin = True Then
>             objLog.WriteLog "LogIn", objUser.UserID
>             For Each ctlTemp In Controls
>                 If TypeOf ctlTemp Is CommandButton Then
>                     ctlTemp.Enabled = objUser.CheckRights(ctlTemp.Caption, ctlTemp.Index)
>                 End If
>             Next
>         Else
>             End ` reject user
>         End If
>         `
>     End Sub

>     Public Sub SetForm()
>         `
>         ` set up form controls
>         `
>         `
>         Me.Caption = "Assets Login"
>         `
>         lblPrompt(0).Caption = "AssetID"
>         lblPrompt(1).Caption = "Description"
>         lblPrompt(2).Caption = "Cost"
>         lblPrompt(3).Caption = "DateAcq"
>         lblPrompt(4).Caption = "SerialNbr"
>         lblPrompt(5).Caption = "Department"
>         `
>         txtField(0).DataField = "assetid"
>         txtField(1).DataField = "description"
>         txtField(2).DataField = "cost"
>         txtField(3).DataField = "dateacq"
>         txtField(4).DataField = "serialnbr"
>         txtField(5).DataField = "department"
>         `
>         Data1.BOFAction = vbMoveFirst
>         Data1.EOFAction = vbMoveLast
>         Data1.DatabaseName = App.Path & "\exer21.mdb"
>         Data1.RecordSource = "Assets"
>         Data1.Refresh
>         `
>     End Sub

>     Public Function GetAction(intAction As Integer) As String
>         `
>         ` convert action constant into friendly name
>         `
>         Select Case intAction
>             Case vbDataActionMoveFirst `1
>                 GetAction = "MoveFirst"
>             Case vbDataActionMovePrevious `2
>                 GetAction = "MovePrevious"
>             Case vbDataActionMoveNext `3
>                 GetAction = "MoveNext"
>             Case vbDataActionMoveLast `4
>                 GetAction = "MoveLast"
>             Case vbDataActionAddNew `5
>                 GetAction = "AddNew"
>             Case vbDataActionUpdate `6
>                 GetAction = "Update"
>             Case vbDataActionDelete `7
>                 GetAction = "Delete"
>             Case vbDataActionFind `8
>                 GetAction = "Find"
>             Case vbDataActionBookmark `9
>                 GetAction = "Bookmark"
>             Case vbDataActionClose '10
>                 GetAction = "Close"
>             Case vbDataActionUnload '11
>                 GetAction = "Unload"
>         End Select
>         `
>     End Function

  6. Save and run the project.

Appendix B

Answers to Appendix B 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 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 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 becomes the contents page by default if none is declared.
  6. You set the HelpContextID property to identify the help file that appears 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 Appendix B 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 perform the following steps in order to complete this exercise:
  - Open your topic file.

  - Enter text for a new topic. Give the topic a descriptive heading such as The Company Master Form.

  - Insert footnotes for the context string, title, and keywords.

  - Save the file in RTF format.

  - Add the context string to the [Map] section of the project file.

  - Compile your project.

  - Set the HelpContextID of the form to the number you have assigned the context string in the project file.

  - Execute your program and press F1.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.