2

Week In Review

Week 2 concentrated on topics that are of value to developers in the stand-alone and workgroup environments. A wide variety of topics were covered in Week 2, including the following:

• How to use Visdata to build and manage databases.
• How to use the Structured Query Language (SQL) to extract data from existing databases.
• What the Microsoft JET engine is, and how you can use Visual Basic code to create and maintain Data Access Objects.
• How to create data entry forms with Visual Basic code.
• How to use the Microsoft graph control to create graphs and charts of your data.
• How to use data-bound list boxes, data-bound combo boxes, and data-bound grids to create advanced data entry forms.
• How to make applications more solid with Error Trapping.

The following is a more detailed look at the topics covered in each lesson.

Day 8, "Using the Visdata Program"

On Day 8, you learned how to use the Visdata application to build and manage databases. You learned how to compile the Visdata project and how to add it to the Visual Basic Add-Ins menu.

Highlights of this chapter include learning how to use VisData to perform the following tasks:

• Create databases, tables, fields, and indexes.
• Perform data entry using automatically generated Grid or Form Layout screens.
• Copy existing tables with or without data.
• Export data to other data sources, including Excel spreadsheets.
• Create, execute, and store SQL queries.
• View existing data as a Table, Dynaset, or Snapshot object.
• Test data-bound list and combo boxes.
• Copy tables from one database to the next, repair corrupted databases, convert databases from older versions of Microsoft JET, compact databases, and perform global replace operations on tables.

Day 9, "Selecting Data with SQL"

On Day 9, you learned what Structured Query Language is and what it can do. You learned how to use the SELECT clause to extract data. Several additional clauses were presented that work with the SELECT clause.

• The WHERE clause is used to limit the rows in a result set and can also be used to link two tables into a single, nonupdatable view.
• The ORDER BY clause is used to control the order in which the result set is displayed (either ascending or descending).
• The GROUP BY clause can be used to create a subtotal result set.
• The HAVING clause can be used with the GROUP BY clause to act as a WHERE clause for the GROUP BY subtotal clause.
• The INNER JOIN clause can be used to merge two tables together into a single, updatable result set.
• The LEFT JOIN and RIGHT JOIN clauses create single, updatable record sets from two tables that include everything from the side selected (LEFT or RIGHT) and only the records with a corresponding match in the second table.
• The UNION clause can be used to combine two or more complete SQL queries into a single result set.
• The TRANSFORM[el]PIVOT clause can be used to create a cross tab query as a result set.

Day 10, "Visual Basic and the Microsoft JET Engine"

You spent Day 10 learning about the hierarchically structured database engine that ships with Visual Basic[md]the Microsoft Joint Engine Technology (JET). You learned about the different objects contained within the engine, and their properties, events, and methods.

• The DBEngine data object is the default data object that contains all other data objects. The methods of this object include RepairDatabase, CompactDatabase, RegisterDatabase, Idle, and CreateWorkSpace.
• The WorkSpace data object identifies the user's database session. This object contains three collections: Databases, Groups, and Users.
• The Database data object contains all the tables, queries, and relations defined for the database.
• The TableDef data object contains all the information needed to define a base table in a database. The collections within this object are Fields, Indexes, and Properties.
• The Field data object contains all the information regarding a data table field.
• The Index data object contains all information on defined indexes for the associated table.
• The Relation data object contains all the information about a stored SQL query. These objects run faster than SQL queries because they actually perform preprocessing that Visual Basic would normally need to perform on an SQL statement.

Day 11, "Creating Database Programs with Visual Basic Code"

On Day 11, you learned how to abandon the data control and use Visual Basic code to manage databases. Using code to create your applications produces a product that is easier to maintain. You also gain complete control over the database administration process.

You learned the following in this chapter:

• The three approaches to handling key-oriented searches are Move, Find, and Seek.
• Seek is the fastest method to locate a record. This method can only be used on RecordSets opened as tables.
• Bookmarks can be utilized to remember a specific record's position a data set.

You also created a series of library functions that you can use to create data entry forms without using the data control.

Day 12, "Displaying Your Data with Graphs"

On Day 12, you learned how to convert your data into pictures and display it for users' interpretation.

• You can use the NumSets and NumPoints properties of the Visual Basic graph tool to declare the number of data sets and the number of data points that will be included in the graph.
• You can use the QuickData property to quickly add graph data at runtime.
• The Graph tool has many options to set the appearance of the graph.

Also during this lesson, you created library routines that allow you to make simple calls from within your Visual Basic code to quickly create graphical displays. These graphs can be displayed in numerous formats with output directed to the screen, printer, or a file.

Day 13, "Data-Bound List Boxes, Grids, and Subforms"

In the lesson on Day 13, you learned how to use the data-bound list box, the data-bound combo box, and the data-bound grid to select and display information. You learned how to set the properties of these controls to read from and write to specified data sources. You also learned how to create subforms to display related information.

In this chapter, you learned the following:

• How to set the RowSource property of the data-bound list control to define the RecordSet object that will be used to fill the list box. You concentrated on using data controls as the record source in the exercises.
• The ListField property determines the field that will be displayed in the list box. The BoundColumn displays the name of the field that will be saved from this control. These two properties do not have to be equivalent.
• The DataSource and DataField properties are set to identify the destination RecordSet and field that will be updated by the list box.
• A data-bound grid can be dropped on a form and linked to a data control by setting the DataSource property. Code can be added to the Reposition event of the data control that will filter data for the grid, which serves as a subform.
• The data-bound grid control has numerous events that can be used to monitor users' actions to the underlying data. These events include BeforeInsert, AfterInsert, BeforeUpdate, AfterUpdate, BeforeDelete, and AfterDelete.

Day 14, "Error Trapping"

No program is complete until error trapping routines are included. Problems can arise in your code, as well as in numerous other unforeseen events, which can cause erratic behavior in your programs. On Day 14, you learned about the different problems that can occur and how to guard against them.

An error handler has three basic parts:

• The On Error Goto statement
• The body of the error handler code
• The error handler exit

There are four possible exits to an error handler:

• Resume reexecutes the code that created the error.
• Resume Next continues processing at the line immediately after the line of code that caused the error.
• Resume label continues processing at the location designated by the label.
• Exit ends processing of the current routine, and END exits the program.

You also learned several types of errors and how to manage them:

• General File errors can occur when files cannot be found or opened. Use Resume as the exit for this type of error.
• Database errors occur when data has been entered incorrectly, or if records are locked. The Data Control manages these errors for you.
• Physical media errors occur when printers are turned off, communication ports are not responding, hard drives cannot be read, and so forth. You should give your users the option to correct the error or safely exit from the program if these problems are encountered.
• Program code errors are bugs in your Visual Basic code. It is best to notify the user to contact the programmer, and then shut down the process safely.
• Global error handlers can be used rather than local error handlers. This reduces the time it takes to write code but has a significant drawback because processing will not be able to return to the point at which the problem occurred.