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: 
Private Sub cmdExit_Click()
   End
End Sub
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:

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:

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:

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:

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:

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:
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:

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:

2. The four ways to exit an error handler routine are

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:

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:

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:

The five lock types you can use with RDO/RDC are:

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:

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:

2. The disadvantages of using data encryption to secure a database include:
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:
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:

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: