3

Week In Review

The third and final week of this book covered several very important topics. This week's work was focused on database issues you'll encounter when you develop database applications for multiple users or multiple sites. You learned advanced SQL language for defining databases (DDL) and manipulating records within existing databases (DML). You also learned the five rules of data normalization and how applying those rules can improve the speed, accuracy, and integrity of your databases.

You learned about Visual Basic database locking schemes for the database, table, and page level. You also learned the advantages and limitations of adding cascading updates and deletes to your database relationship definitions. You learned how to use Visual Basic keywords BeginTrans, CommitTrans, and Rollback to improve database integrity and processing speed during mass updates.

You learned how to write data entry forms that use the ODBC API calls to link directly with the ODBC interface to access data in registered ODBC data sources. You also learned how to install the ODBC Administrator and how to create new ODBC data sources for your ODBC-enabled Visual Basic programs.

You learned how to create application-level security schemes such as user login and logout, program-level access rights, and audit trails to keep track of critical application operations.

Finally, you learned how to design and build online help systems for your Visual Basic applications, including the 10-point checklist for creating quality help systems. You also learned how to link help files directly to fields on a Visual Basic data form.

Day 15, "SQL Data Definition Language"

You started the week by learning how to create, alter, and delete database table structures using DDL (Data Definition Language) SQL keywords. You learned that using DDL statements to build tables, create indexes, and establish relationships is an excellent way to automatically document table layouts.

You learned how to maintain database structures using the following DDL keywords:

• CREATE TABLE enables you to create entirely new tables in your existing database.
• DROP TABLE enables you to completely remove a table, including any data that is already in the table.
• ALTER TABLE enables you to add a new column or drop an existing column from the table without losing existing data in the other columns.
• CREATE INDEX and DROP INDEX enable you to create indexes that can enforce data integrity and speed data access.
• The CONSTRAINT clause can be added to the CREATE TABLE or ALTER TABLE statement to define relationships between tables using the FOREIGN KEY clause.

Day 16, "SQL Data Manipulation Language"

You continued your SQL studies on Day 16 with Data Manipulation Language (DML) keywords. These SQL keywords enable you to add, delete, and edit data within tables. You also learned how to use DML statements to quickly create test data for tables and load default values into startup tables. Plus, you learned that DML statements such as Append queries, Make Table queries, and Delete queries can outperform equivalent Visual Basic code versions of the same operations.

You learned how to manage data within the tables using the following DML keywords:

• The INSERT INTO statement can be used to add new rows to the table using the VALUES clause.
• You can create an Append query by using the INSERT INTO...FROM syntax to copy data from one table to another. You can also copy data from one database to another using the IN clause on an INSERT INTO...FROM statement.
• You can create new tables by copying the structure and some of the data using the SELECT...INTO statement. This statement can incorporate WHERE, ORDER BY, GROUP BY, and HAVING clauses to limit the scope of the data used to populate the new table you create.
• ou can use the DELETE FROM clause to remove one or more records from an existing table. You can even create customized views of the database using the JOIN clause and remove only records that are the result of a JOIN statement.

Day 17, "Data Normalization"

On Day 17, you learned how to improve database integrity and access speed using the five rules of data normalization. You learned the following five rules:

• Rule 1: Eliminate Repeating Groups. If you have a set of fields that have the same name, followed by a number (such as skill1, skill2, skill3), remove these repeating groups and create a new table for them.
• Rule 2: Eliminate Redundant Data. Don't store the same data in two different locations. This can lead to update and delete errors. If the same data element is stored in two places, remove the second data element, create a new table with the element and a key field, and then place the key field in the locations that used to hold the data element.
• Rule 3: Eliminate Columns not Dependent on Keys. If you have data elements that are not directly related to the primary key of the table, these elements should be removed to their own data table. Only store data elements that are directly related to the primary key of the table.
• Rule 4: Isolate Independent Multiple Relationships. Use this rule to improve database design when you are dealing with more than one one-to-many relationships in the database. Before you add a new field to a table, ask yourself whether this field is really dependent upon the other fields in the table. If not, you should create a new table with the independent data.
• Rule 5: Isolate Related Multiple Relationships. Use this rule to improve database design when you are dealing with more than one many-to-many relationship in the database. If you have database rules that require multiple references to the same field or sets of fields, isolate the fields into smaller tables and construct one or more link tables that contain the required constraints to enforce database integrity.

Day 18, "Multiuser Considerations"

On Day 18, you learned about the following three important challenges that face every database programmer writing multiuser applications.

• Database locking schemes.
• Using cascading updates and deletes to maintain database integrity.
• Using database transactions to provide commit/rollback options for major updates to your database.

You also learned the following three levels of locking that are available to Visual Basic programs.

• Database Level: You can use the Exclusive property of the data control or the second parameter of the OpenDatabase method to lock the entire database. Use this option when you need to perform work that will affect multiple database objects (such as tables, queries, indexes, and relations).
• Table Level: You can set the Options property of the data control (set to 3) or the third parameter of the OpenRecordset method (use dbDenyRead+dbDenyWrite) to lock the entire table for your use only. Use this option when you need to perform work that affects multiple records in a single table (for example, increasing the sales price on all items in the inventory table).
• Page Level: Microsoft JET automatically performs page-level locking whenever you use the data control to edit and save a record or whenever you use Visual Basic code to perform the Edit/AddNew and Update/CancelUpdate methods. You can use the LockEdits property of the Recordset to set the page locking to pessimistic (perform locking at edit time) or optimistic (perform locking only at update time).

You learned how to use the Visdata application to create relation objects that enforce referential integrity and automatically perform cascading updates or deletes to related records. You learned that there are times when it is not advisable to establish cascading deletes (for example, do not use cascading deletes when the base table is a validation list and the foreign table is a master).

You also learned how to use database transactions to protect your database during extended, multitable operations. You learned how to use the BeginTrans, CommitTrans, and Rollback methods of the Workspace object. You also learned some of the advantages and limitations of transaction processing.

Day 19, "ODBC Data Access Via the ODBC API Interface"

On Day 19, you learned how to use the Open Database Connectivity (ODBC) API set to directly link your Visual Basic program to target data sources via the ODBC interface. The ODBC interface is generally faster than Microsoft JET when it comes to linking to ODBC defined data sources.

You also learned how to install the ODBC interface on your workstation and how to use the ODBC Administrator program to install ODBC driver sets and define data sources for ODBC connections.

You learned how to build a program library that uses a minimum set of ODBC API calls along with several Visual Basic wrapper routines. This library set provides the basic functions needed to read and write data to and from a defined ODBC data source. You can use these routines to create fully functional data entry forms for ODBC data sources.

Finally, you used the library routines to build a data entry form that opens a link to a defined ODBC data source and allows the user the read and write data records for the ODBC data source.

Day 20, "Securing Your Database Applications"

Today's lesson covered several methods you can use to increase the level of security for your Visual Basic database applications. You learned the merits of using the Microsoft Access SYSTEM security file and the advantages and disadvantages of encrypting your database.

You also learned how to add application level security to your Visual Basic programs by adding user login/logout routines and creating a user access rights scheme for your applications. In this lesson, you designed and implemented a login screen you can use for all your Visual Basic applications, and you created several screens for maintaining user lists and managing access rights for each user.

You also learned how to add an audit trail option to your programs. You added routines to existing libraries that will log all critical user activity to an audit trail file, including user logins, database modifications, and all critical program operations, such as running reports or processing mass database updates.

Day 21, "Preparing Online Help for Your Database Application"

On Day 21, you learned how to design and build online help files for your Visual Basic applications. You learned the 10 key points to developing good online systems:

1. 1. You need to have a Topic file and a Project file to build a help file.
2. 2. The Topic file you create includes all the text and footnotes for the help system. It is written in Rich Text Format (RTF).
3. 3. You insert footnotes to define each topic. Insert a pound sign (#) footnote to declare a context string, a dollar sign ($) footnote to denote a Title, a capital K to denote a keyword, and a plus sign (+) to denote the Browse sequence.
4. 4. The Project file has the codes to inform the compiler how to build the help file from the topics file. It is saved as ASCII text and must have the extension .HPJ.
5. 5. You declare your projects help file by setting its path and name in Project Options. You declare your topic for a control by setting its HelpContextID property.
6. 6. Jumps can be added to your help file by double underlining your jump text and immediately inserting the context string of the topic to which the user will jump. This context string is formatted as hidden text.
7. 7. You can create a pop-up window in the same way you created a jump, by single underlining the text rather than double underlining the text.
8. 8. You can add help menu items to your application by making an API call to WINHELP.
9. 9. There are several commercially available programs that can assist you in creating help files.

10. Plan your help file in advance of writing it. Remember to write your online help first. Also, prepare your documentation as you develop your application rather than after the project is completely coded.