Today you will learn how to use the Data Manager that is shipped with Visual Basic 4. This utility program gives you the power to create and maintain basic databases without leaving Visual Basic 4 design mode. You will learn how to use the Data Manager program to do the following:
Plus, today is the day you start building your first extended Visual Basic 4 database project—The Company Database Project. You will use the Data Manager to construct the first data table in the database—the CompanyMaster table.
The Visual Basic 4 Data Manager is a complete program (written in Visual Basic!) that ships with Visual Basic 4.0. This program can be used to create new Microsoft Access databases and edit, convert, compact, repair, encrypt, and decrypt existing databases. You can use Data Manager to create or delete data tables and indexes. You can also use the Visual Basic 4 Data Manager to perform simple data entry on data tables.
The Visual Basic 4 Data Manager can create databases in the Microsoft Access database format. It can also be used to attach to and perform field maintenance and data entry on Paradox, dBASE, FoxPro, Btrieve, and ODBC data sources. It can even attach to Excel spreadsheets and DOS Text files.
The Visual Basic 4 Data Manager provides nearly complete support for Microsoft Access databases. It allows you to create databases and create and delete tables, indexes, and data fields. You will not, however, be able to delete data fields that are used in indexes.
The Data Manager program has several key features that make it an excellent tool for constructing and maintaining databases for your Visual Basic 4 applications. First, you can launch this program directly from the Visual Basic 4 Add-Ins menu. As long as Visual Basic 4 is up and running in design mode, you can call up the Data Manager and create new databases, open existing databases, or modify data tables and indexes without having to leave Visual Basic 4 or close down your Visual Basic 4 project.
Another advantage of having the Data Manager is that you can use it to do quick data entry into existing data tables. This allows you to quickly create test data for your Visual Basic 4 applications. Do you need to see whether a database lookup routine you wrote really works? You can pop up the Data Manager add a few records to the appropriate data table, and then return to Visual Basic 4 and run your application.
You can also use the Data Manager to compact out deleted records (in other words, physically remove spaces left by deleted records), and for those occasions when you get the dreaded "corrupted database" error, you can use the Data Manager to repair existing Microsoft Access type databases.
You can even use Data Manager to build and test SQL statements. This is an extremely handy tool to have in order to test the logic of SQL statements as you need to incorporate them into your Visual Basic 4 code. These statements, once tested and working properly, can then be saved by Data Manager.
A major disadvantage of using the Data Manager to create databases for your Visual Basic 4 applications is that it is not a complete database administration tool. Although you can use the Data Manager to construct and maintain data tables and indexes, you cannot print out data structures or index parameters.
Even with this limitation, the Data Manager is a very useful tool. Let's go through a short course on how to use the Data Manager to construct and maintain Microsoft Access-type databases.
First, if you haven't already started the Data Manager, do it now. To start the program from within Visual Basic 4, select Add-Ins | Data Manager.
The first screen shows two menu choices: File and Help. The Help menu lets you bring up an online reference about how to use the Data Manager. All other program options appear underneath the File menu. Let's look at one of the first commands you will use on this menu.
You use the Open command under the File menu to open an existing database. Data Manager allows you to open Microsoft Access databases. When the database is open, you can perform table maintenance functions such as adding new tables, modifying existing tables, and deleting tables.
Now open a Microsoft Access database. Select File | Open Database. Use the Open Database dialog box to locate and select the BIBLIO.MDB database from your Visual Basic 4 directory (see Figure 3.1).
NOTE: To guard against any accidental changes to the structure of the BIBLIO.MDB database, it is recommended that you copy the pristine BIBLIO.MDB database to a different directory and change its name. A logical choice would be C:\TYSDBVB\CHAP03\DATAMGR.MDB.
Opening a database in Data Manager.
For Users of Visual Basic 3.0
The Visual Basic/Microsoft Access compatibility layer is an upgrade that provides improved database speed and additional SQL language options to Visual Basic 3.0. If you are using Visual Basic 3.0 and have not installed the compatibility layer, you should do so now. Most of the examples in this book will require the added functionality of the Visual Basic/Microsoft Access compatibility layer. The Visual Basic 4.0 Data Manager can handle all current versions of Microsoft Access database files. If you are using Visual Basic Version 3.0 and have installed the Visual Basic/Microsoft Access compatibility layer, you can open Version 1.1 and Version 2.0 Microsoft Access databases. If you are using Visual Basic Version 3.0 and have not installed the compatibility layer, you can open Microsoft Access Version 1.0 and Version 1.1 databases only.
After you select the database, Data Manager will load all the tables and display the Tables/QueryDefs and SQL Statement forms. Your screen should look something like the one in Figure 3.2. You can perform all the necessary table maintenance operations from the Tables/QueryDefs form. You can use the SQL Statement form to enter and generate SQL statements. You will perform exercises using both of these forms throughout this chapter.
Viewing the forms of the Data Manager.
You can close the Microsoft Access database by selecting Close Database from the File menu.
The Data Manager has two additional database functions that can only be used on Microsoft Access databases—Compact and Repair. These two functions reduce the amount of disk space used by a database (Compact) and attempt to recover data from damaged databases (Repair).
Occasionally, a database you have been working on becomes corrupted. This usually occurs when there has been a hardware failure during a database update operation. Hardware failures include loss of power to the PC, loss of power to the file server where the database is stored, disk read/write failures due to power fluctuations, component failure—the list is (sadly) endless. If you are unable to open a data table, index, or entire database, and you know you have correct connection information, you might have a corrupted database.
To repair a database using Data Manager, select Repair Database from the File menu. You should now see the Repair Database dialog box (see Figure 3.3). Locate and select a Microsoft Access database to repair (remember that you can only repair Microsoft Access databases with Data Manager). When you click on the OK button, Data Manager will begin the process of repairing any corrupted items in the database. This might result in lost records in a data table or even a lost table or index. It is therefore a good idea to review your data after doing a repair.
In severe cases, Data Manager might report that it cannot repair the database at all. In such cases, your only recourse is to replace the corrupted database with your most recent backup copy.
Selecting a Microsoft Access database to repair.
NOTE: Your database might increase in size after running the Repair Database routine. For this reason, we advise you to run the Compact Database routine after running the Repair Database routine.
As a database is used, records are added, updated, and some are eventually deleted. All this activity in the database tends to increase the overall disk space used to store the database. It is a good practice to run the Compact Database routine to clear out any empty space that can accumulate. This routine also optimizes the storage of the data, based on existing indexes. This optimization not only saves disk space, but it can also improve overall performance of the database.
To compact a database using Data Manager select File | Compact Database. You are asked to locate and select a Microsoft Access database to compact (see Figure 3.4). Remember that you can only compact Microsoft Access databases with Data Manager. After you click the OK button, you will be shown The Compact To Database dialog. You must supply a recipient database "to compact to." This is the name of a new database (see Figure 3.5). After you enter a new database name, click OK. Data Manager begins copying all valid items (data table and index definitions, actual data and indexes, and system control information) to the new database. When the routine completes successfully, you can erase the old database and rename the new database using the previous name.
Selecting a Microsoft Access database to compact.
Selecting a recipient database in the Compact To dialog.
For example, if you want to compact a database named CH03.MDB, you would select CH03.MDB as the database in the Compact From dialog box and COMPACT.MDB as the database filename in the Compact To dialog box. After the compacting is completed, delete CH03.MDB and rename COMPACT.MDB to CH03.MDB.
The Encrypt menu options on the File menu enable you to scramble the contents of an existing database. This is a security feature that keeps individuals who do not have a utility that can read Access databases (such as Data Manager) from reading the information stored in them.
To perform an encryption, simply select the File | Encrypt option. You will then be prompted by the Encrypt Database dialog box (see Figure 3.6) to select a database to encrypt.
Selecting a Microsoft Access database to encrypt.
When you select the on-screen Open button, Data Manager finds the database and prompts you for the name of the new, encrypted database (see Figure 3.7). The New Database filename must be different from the original database name. Also, you cannot have the database open for use when you attempt an encryption.
Entering a Microsoft Access database filename to encrypt to.
Data Manager also provides a Decrypt utility to unscramble data that you scrambled with the Encrypt option. This simply reverses the effects of the encryption.
Select Decrypt from the File menu to perform this option. You will be prompted for the name of the database to decrypt (remember, you can't have the database you want to decrypt open at the time of decryption). You will be greeted with a similar dialog box (see Figure 3.8).
Selecting a Microsoft Access database to decrypt.
Click Open to select the highlighted database. Next, a screen will appear to enable you to enter a new database filename (see Figure 3.9). This name cannot be the same as one existing within the same path.
Entering a filename for a decrypted database.
Encryption can provide a useful security feature for your data, but it does have some drawbacks. First, it doesn't provide complete security. A user with appropriate network rights can erase an encrypted file in the same way as an ordinary data file. Secondly, encrypting a database means it can't be read by other applications. One of the main benefits of a database written in Access is that it can be read by numerous programs (Excel, for example). Encrypting can keep users from performing the tasks that they most need in order to perform their work efficiently. Finally, encryption is not selective at the table level and can only be applied at the database level. You will have to encrypt an entire database even if you only want one table of that database encrypted.
To create an entirely new Microsoft Access database using Data Manager, select File | New Database. You should now see a New Database dialog box where you can enter a name for the database in the File Name field. For this example, enter MASTER.MDB as the database name (see Figure 3.10).
Entering the name of a new Microsoft Access database.
When creating Microsoft Access databases, you should use the .MDB extension. If you leave it off and do not include the period (.) in your filename, Data Manager will add the .MDB to your database name automatically. When you click the Save button, Data Manager creates the Microsoft Access database and presents you with the Tables/Query Defs form (see Figure 3.11). Now you are ready to create and maintain data tables and indexes in your new database. You'll add these in the next section of this chapter.
A new Microsoft Access database ready for table definitions.
After you have opened an existing database or created a new database, you can perform data table maintenance. Data Manager allows you to create new tables, modify existing tables, and delete existing tables. You can add or delete fields and indexes for an existing table. You can even use Data Manager to perform data entry on existing tables.
You must first create a new table before you can add fields and indexes to a database. To do this, you must first open an existing database or create one. In the following example, you will use the MASTER.MDB Microsoft Access database you created in the previous part of this chapter. If you haven't done this already, see the previous section, "Creating New Databases with Data Manager."
To add a new table to this database, click New and enter a table name in the first field of the dialog box that appears. For this example, enter CompanyMaster. (See Figure 3.12.)
Adding a new table to the MASTER.MDB database.
Next, press the Tab key to move to the Field Name field. Enter the first field of this table, EntryNbr. Select the Data Type of LONG INTEGER, and then press the right arrow (>) to move the field into the fields container. Now select OK to create the table with the first field included.
You could have built an entire table in this screen, but for the sake of practice, move on to the Table Edit function to add more fields.
The Data Manager Add Table form only allows you to add fields to a table. You need to use the Design function on the Tables/QueryDefs form to add indexes, keys, default values, and validation rules to your data tables.
To go into the Design of the table, first select the table by clicking on its name once with the left mouse button in the Tables/QueryDefs form. Next, click the design button. You should see a screen similar to the one in Figure 3.13.
The Table Editor form.
Take a look at the buttons that appear on this form. From here, you can edit an existing field, add fields, remove fields, add indexes, and add keys. The first section covered is Edit.
To edit a field, simply select it by clicking on its field name in the grid that appears, and click Edit. Do this with the EntryNbr field. You should see a form similar to Figure 3.14.
The Field Edit form.
Notice how this form enables you to enter the field type, size, default value, ordinal position, collating order, validation text, and validation rule. Notice also how you can set attributes and confirm whether the data is updatable, required, or can be of zero length.
For this exercise, make the EntryNbr field a counter field. Simply select the Counter checkbox and click OK. You will be notified that this change will delete data that exists. Select Yes to continue because there are currently no data records in the table.
TIP: Adding a counter field to your Microsoft Access data tables is an excellent way to automatically generate unique record IDs. Each time a new record is added to the table, Visual Basic 4 generates a new counter value and inserts it into the designated field. This works in a multiuser environment, too. Because the underlying data type for a counter field is the Visual Basic 4 type LONG, you can add more than 2 billion records to a single data table before you have to worry about running out of unique values.
Now you need to complete the rest of the CompanyMaster Table. This table should contain the basic name and address information for all the customers of the company. Throughout the next three weeks, you will build this Company Database and create programs that will access the data you put into this table.
To add a field to a data table, click the Add button on the Design form. Data Manager presents you with the Add Field dialog box. Note that this is the same form you used earlier for editing. Each time you click the OK button on this form, another field is added to the table. You can add as many fields as you like to a data table.
Table 3.1 contains a list of the field names, data types, and lengths for the CompanyMaster data table. Please refer to this listing as you continue with this lesson and construct this table.
Table 3.1. The field list for the CompanyMaster data table.
| Number | Field Name | Field Type | Field Length |
|---|---|---|---|
| 1. | EntryNbr | Long Integer | |
| 2. | CompanyName | Text | 50 |
| 3. | LastName | Text | 30 |
| 4. | FirstName | Text | 30 |
| 5. | Title | Text | 30 |
| 6. | Addr1 | Text | 40 |
| 7. | Addr2 | Text | 40 |
| 8. | City | Text | 30 |
| 9. | StateProv | Text | 10 |
| 10. | PostalCode | Text | 12 |
| 11. | Country | Text | 20 |
| 12. | VoicePhone | Text | 20 |
| 13. | FAXPhone | Text | 20 |
| 14. | Extension | Text | 10 |
| 15. | CustFlag | Boolean | |
| 16. | CompanyLogo | Long Binary | |
| 17. | Notes | Memo | |
| 18. | LastUpdated | Date/Time |
The second field you'll add to the table is the CompanyName field. To do this, click on the Add button of the Design table form. This brings up the Add Field dialog box. First, type CompanyName in the Field Name input box (remember, do not put spaces in field names). Then, click on the down arrow of the Field Type drop-down listbox.
TIP: You can also open a listbox by pressing the F4 key. Field types can also be selected by typing the first letter of the desired type. Repeatedly striking the first letter of a desired type cycles through types that begin with the same first letter.
Select Text as your field type. Then press Tab to move to the size box. Enter the value (50) from Table 3.1. Before you click the OK button for the Add Field dialog, your screen should look like the one in Figure 3.15.
Adding the CompanyName field to the CompanyMaster data table.
NOTE: Data can only be entered into the Field Size input box if you have selected the Text data type. The field sizes of all other data types are controlled by the Microsoft JET engine.
After clicking the OK button on the Add Field dialog box, Data Manager adds the field to the data table and returns to the Design form. You can now see that the CompanyName field has been added to your table (see Figure 3.16).
A view of the Table Design form after adding the CompanyName field.
Using the information in Table 3.1, create the remaining data fields for the CompanyMaster table. Be sure to enter the Field Size values for the Text data type fields.
NOTE: The Visual Basic 4 Data Manager gives you only limited ability to modify existing Microsoft Access data tables. You can add new fields to an existing data table, but you cannot modify or delete existing fields in a table without deleting data in the edited fields.
After you have added all 17 fields to the data table, you are ready to create the indexes.
You can speed data processing and establish database integrity by creating indexes for data tables. It is usually a good idea to create at least one index for each data table. This one index should be based on the primary key field of the data table. The primary key field is the one field that will always contain a unique value. Additional indexes can be created to speed data searches or to enforce data integrity across multiple tables. Integrity indexes are covered in Week Two.
To add a primary index to a database, you must first open a database and select a data table in design mode.
Now let's add a primary key index to the CompanyMaster data table you created earlier in this chapter. If you have not already done so, create the CompanyMaster data table defined in the previous section. If you already created the data table, start Data Manager and select the MASTER.MDB database. At the Tables/QueryDefs form, select the CompanyMaster data table, and click on the Design button to bring up the Design form. Your screen should look like the one in Figure 3.17.
Adding an index to the CompanyMaster data table.
Click on the Indexes button at the bottom of the form. This brings up the Index dialog box. The Index dialog box shows you a listing of all the current indexes for the table on which you are working. From this point, you can either edit, add, or remove an index. Select Add so that you can build the primary key for this table.
The Add Index dialog now appears. The first value you should enter on this form is the Index name. This name will be used in your Visual Basic 4 program when you are selecting the index. Embedded spaces are allowed in the Index name, but don't use them. It is good database practice to have no embedded spaces in data object names. For this example, enter PrimaryKey as the Index name.
NOTE: Microsoft Access databases allow embedded spaces in field and index names (for example, First Name instead of FirstName). This makes working with Microsoft Access databases very comfortable for novice users. However, most other database systems do not allow embedded spaces in field names or index names. If you build your databases assuming that embedded spaces are allowed, and then convert your database to another system (for example, use an upgrade Wizard to move to Microsoft SQL Server), you will run into problems. It is better to use no embedded spaces in your field and index names.
When you built this table, you added the Counter data type field EntryNbr for use as a unique key field for the data table. This is the field you want to use for the PrimaryKey index. Locate the EntryNbr field in the field list and click on it once to highlight it. Notice that two Add buttons are now enabled. Clicking on either of these buttons will add the highlighted field to the index definition. The ASC stands for Ascending and the DEC stands for Descending. You can use these two buttons to force the index to sort the data from the smallest value to the largest (ascending) or from the largest value to the smallest (descending). You want the PrimaryKey index to run from the smallest EntryNbr to the largest, so click on the Add (ASC) button. Notice that the highlighted field now appears in the list of the Fields in the Index box, followed by (ASC) to denote ascending order.
Before you click the OK button to save the index definition, click the Primary Index checkbox at the top of the form. Clicking this checkbox tells Visual Basic 4 that the values in this field must always be unique. If users attempt to save a record with an index value that duplicates that of another record in the data table, Visual Basic 4 issues an error message. Also, Visual Basic 4 only allows one primary index for each data table. Your index dialog box should look like the one in Figure 3.18.
Defining an index using the Data Manager.
When you have toggled the primary checkbox, click on OK to save the index definition. Data Manager stores the index definition and returns to the Indexes dialog box. You will now see that the PrimaryKey index has been added (see Figure 3.19).
The Indexes form showing the PrimaryKey index.
Select Close from the Indexes dialog to return to the Design grid.
Add two more indexes to this data table. Create an ascending index called Names that contains two fields—LastName and FirstName. Multiple fields can be included in an index by selecting the first field and pressing the Add(ASC) key. Then repeat this process for the second field in the index. Click the Unique checkbox to make this index unique. This prevents two records in the data table from containing the exact same name. Your Add Index dialog should look like the one in Figure 3.20. Select OK when you are finished.
Creating the Names multifield index.
The last index to create will be one built on a descending key. Create a nonunique, descending index called Updates that contains one field—LastUpdated. Be sure to use the Add (DEC) button to add the LastUpdated field to the Fields In Index list. Your Index dialog should look like the one in Figure 3.21. Notice that the field name LastUpdated in the Fields in Index list is followed by (DEC) to denote descending order.
Creating a nonunique, descending key index called Updates.
When all three indexes are defined, your Indexes form should look like the form in Figure 3.22.
The completed CompanyMaster data table index definitions.
You can delete an index by clicking on the index name in the Indexes dialog box and clicking the Remove button at the bottom of the form. Before the index is removed, you will see a dialog box asking you to confirm the deletion.
For now, delete the Names index from the CompanyMaster data table. Select the Names index by clicking on the Names line of the Index grid. Click on the Remove button to delete the selected index. Your screen should now look like the one in Figure 3.23.
Deleting an index from the CompanyMaster data table.
Click Yes to confirm that you want to delete the index.
There may be times when an index will need to be edited. For example, you may have forgotten to select the Unique box, or you may want to add another field to the index. Editing an index is very similar to adding an index. Simply select the index you want to edit and press the Edit button. You will then be presented with the same form you used to build the index. Make changes as needed in this form and press OK to save your work.
To delete an existing table, select a table from the Tables/QueryDefs window by clicking on it. Then click the Delete button to remove the table from the database. By deleting the table, you also delete all the data stored in that table.
One of the beauties of working with Visual Basic 4 and Microsoft Access data tables is the openness that you have with data. Data can be read from MDB databases by off-the-shelf products such as Microsoft Excel and Lotus 123. In addition, MDB databases can incorporate and modify data that exists in other sources. Using data that exists in other data sources is made possible by attaching that data source.
Attaching a data table from another database simply places a pointer in your original database to the external source. You will be able to add and edit data in the attached table just like you would with an ordinary table. This helps you be more productive by eliminating the need to copy or replicate the same data among several applications.
Let's take a look at attaching a table from your MASTER.MDB database. First, start Data Manager and open MASTER.MDB, if you do not currently have it open. Next, select Attached Tables from the Tables/QueryDefs window. You should now see the Attached Tables dialog box (see Figure 3.24).
The Attached Tables dialog box.
Now, click the New button to display the New Attached Table dialog box (see Figure 3.25).
The Attached Tables definition dialog.
You will now complete this dialog to define the parameters of the attachment. First, enter an Attachment Name. This will serve as the name of the table in your database. Remember that it is good practice to avoid entering spaces in the names of database objects. For this exercise, enter Test.
Now enter the name of the database, including its path, into the Database Name field. You'll want to open the Authors table in the BIBLIO.MDB database that ships with Visual Basic 4, so you can enter C:\PROGRAM FILES\MICROSOFT VISUAL BASIC\BIBLIO.MDB. To play it safe, open the copy of this database, C:\TYSDBVB\CHAP03\DATAMGR.MDB), that you created earlier in this chapter. Please note that you must know the full path and filename of the database you want to attach. Data Manager does not provide an open dialog from which to choose a database.
Select JET Engine MDB as the Connect String. The connect string is the format of the database to which you are attaching. Take a minute to look at the different formats that can be attached.
Finally, open the Table to Attach box. When you click on the down arrow of this box, Data Manager finds the database you entered in the Database Name field and lists its tables. You will receive an error message if you entered an invalid name in the Database Name field. Select Authors as the table to attach.
Compare your form to Figure 3.26. Click Attach to create the attachment and click Close. You should now return to the Attached Tables dialog box, which displays the new table reference.
The completed Attached Tables definition dialog.
Close the Attached Tables dialog to see the Test attachment in the Tables/QueryDefs window. Double-click on it to open it. You have now created an attachment where you can read and write data.
Finish this exercise by closing the Test table and returning to the Tables/QueryDefs window. You no longer have a need for this attachment, so you can delete it. Do this by clicking once on Test and then selecting the Delete button. A confirmation will appear to confirm the deletion. Select OK and the attachment is gone.
NOTE: Deletion of the attachment does not delete the underlying table, or the data it contains. The deletion simply removes the pointer to the data table.
Relationships are the cornerstone of modern database theory. By setting a relationship between tables, you are actually telling a table to use the information contained within another table and restrict entry to the values that currently exist. This provides benefits by keeping entry consistent. Relationships also make tables more efficient because similar data does not have to be entered into multiple tables. Let's construct relationships to demonstrate what they can do for you.
Open the BIBLIO.MDB database found in your Visual Basic 4 root directory (or your copy in C:\TYSDBVB\CHAP03). Examine the design of the Publishers table by clicking on its name in the Table/QueryDefs window and then pressing Design. Take note of the field names—especially the PubID field.
Now do the same with the Titles table. Note that this table also has a PubID field. You will next build a relationship between these two tables. By doing so, you will be able to restrict entry to the Titles table of only PubIDs that exist in the Publishers table.
Begin this function by selecting the Publishers table and pressing the Relations button. You are then presented with the Relationships form, as shown in Figure 3.27.
The Relationships form.
The primary table in this example will be Publishers. Select this table from the Primary combo box. The Related table will be the Titles table. Select this table from the Related Table combo box.
You want to join these two tables with a field that is included in both tables. This is the PubID field. Select this field from the Select Matching Fields combo box if it is not already displayed.
You want to create this as a one-to-many relationship. This means that there can be multiple occurrences of the PubID field of the Publishers table (the one side) to the PubID in the Titles table (the many side). Select the Many option button in the Type group.
Finally, check the Enforce Referential Integrity checkbox at the bottom of the form. This will keep users from deleting records in the Publishers table that are used in the Titles table. Your completed form should match Figure 3.28.
NOTE: Enforcing referential integrity is the process of telling the database that a record can not be deleted from a data table if it is used in another data table (in other words, if it is referenced from another table). For example, if you set up a table of customers (the parent), you may want to assign a customer type (child) to each record. This customer type field may be stored in a table which contains numerous fields of information (terms, finance charges, tax status, and so on) that you shouldn't repeat in every customer master. You will want to enforce the integrity of this relationship so that a customer type is not deleted if it is used in a customer master. Failure to do so could lead to having undefined customer types (orphans).
The completed Relationships form.
Click on the Add button to create the relationship. Then click Close to return to the main screen. The relationship has been created and is ready for you to test.
To do this, open the Titles table by double-clicking it in the Tables/QueryDefs window and find the PubID that is used in the first record. Close this table and open the Publishers table. Look up the PubID you saw in the first record of the Titles record by pressing the arrow buttons at the bottom of the form. Now press Delete. You should get a message similar to the one in Figure 3.29 that notifies you that this record cannot be deleted because referential integrity rules will be violated.
The error message caused by attempted deletion of a Primary Table record utilized in a related table.
Let's perform one more test on this data. Open the Titles table and select any record. Modify the PubID of the record you selected by entering 4444 in this field. When you select Update, you receive an error message similar to the one displayed in Figure 3.30 stating that referential integrity requires a related record in the Publishers table. Referential integrity is now working to keep your tables consistent.
Enforcement of referential integrity rules.
In addition to building databases, fields, and indexes, Data Manager provides a function to build and save queries. A query is a program statement that reads a data table and creates a selection of data arranged in a manner most suitable to the task at hand. For example, you might build a query that selects all the customers from a customer table that have invoices greater than 120 days past due. Or, you might build a query to select all students that walk to school rather than ride the bus.
You use Structured Query Language, commonly referred to as SQL (pronounced S-Q-L—not sequel) to create a query. This book covers SQL in depth in Day 9, "Selecting with SQL." For now, let's run a quick exercise to demonstrate the functionality of the Data Manager to manage SQL statements.
If you don't still have it loaded, load BIBLIO.MDB. Now you can test a SQL statement that will produce a listing of all publishers with a publisher ID of less than 12. To do this, enter the following statements in the SQL Statement window, after which, your screen should look like Figure 3.31.
SELECT * FROM Publishers WHERE PubID<12
Entry of an SQL statement.
This statement will display every field (the * represents all fields) from the Publishers table (FROM Publishers) with an ID of less than 12(WHERE PubID<12).
Execute this statement by pressing the Execute button. Move through the records that appear. Notice that no records appear with an ID greater than 12.
Data Manager allows you to save a query for future use. To do this, simply select Save from the SQL Statement window. You will then be prompted to enter the name of the Query. For this example, enter ABC. Your screen should look like Figure 3.32. After you select OK, you will be questioned as to whether this is a SQL Passthrough. Answer NO.
NOTE: In the preceding exercise, the Microsoft JET database engine that ships with Visual Basic 4 questioned if the SQL statement was a SQL Passthrough. When you answered no, the JET engine performed error checking based upon its internal rules on how SQL statements should be composed. If you would have answered yes, JET would not have performed any error checking and would have passed the responsibility of error checking on to the next database engine. A passthrough is commonly used in situations where Visual Basic 4 is used as a front-end tool to access databases such as Microsoft SQL Server.
Entering a name for a saved SQL query.
A query based upon the statement you entered is now saved in the same way as a table. Double-click on ABC in the Tables/QueryDefs window and explore the data presented. Next, open the Publishers table to compare your results set. Notice how many more records are in the table than in the query.
This completes the lessons on the construction features of Data Manager. The remainder of today's lesson will focus on using Data Manager to enter data.
The Data Manager can be used to enter data into existing data tables. You can do this by opening a database, selecting a data table from the Tables/QueryDefs window, and clicking on the Open button. When you do this, Data Manager will present a data entry form that lists all the fields in the data table along with a set of command buttons for adding, deleting, and locating records. You can also use the data control at the bottom of the form to move forward and backward through the data table. As an example, open the CompanyMaster data table you built earlier in this chapter. You should see a screen similar to the one in Figure 3.33.
Initial data entry form for CompanyMaster data table.
To add a record, click on the Add button at the top left of the form. Using Figure 3.34 as a guide, enter information in the first record of the table. Data Manager will save the new record when you click on the Update button.
Ready to save a new record to the CompanyMaster table.
NOTE: While entering records, you might decide that you don't want to make some fields required entry. If so, you can return to the Design form, select a field, and check its Allow Zero Length checkbox. This will make entry into that field optional.
CAUTION: Do not enter any data in the EntryNbr field. This is a COUNTER data type field. It will be updated by Visual Basic 4 after you save the record. If you enter any data into the COUNTER data type field, Visual Basic 4 will issue an error message and you will not be able to save the record to the table.
Keep this in mind when designing data entry forms. Set counter fields off by themselves, and don't give users the ability to enter or edit data in a counter field.
When you update one or more fields in an existing record of a data table, you can save the changed data by clicking on the Update button at the top of the form.
If you have changed one or more fields in a record but then decide you do not want to save the changes, you can use the Refresh button to restore the record to its original state. This is the Data Manager equivalent of an undo feature. Pressing the Refresh button tells Data Manager to forget all the changes you made to the current record and to reset the fields to the values still stored in the data table. The Refresh button restores the entire record; you cannot refresh a single field. After you refresh a record, you cannot change your mind and recall the changes you made. You can't undo your refresh! Also, you will not get a confirmation when you click Refresh.
When you open a data table that already has records, Data Manager shows you the first record in the data table. You can walk through a data table to locate a record using the arrow buttons on the data control to move forward and backward through the table. This is fine if you have only a few records to review before you find the record you need. However, if the table has several records—possibly hundreds—you need a more effective method for locating a specific record.
That's why the Find button is handy. When you click the Find button, Data Manager pops up an input box that enables you to enter a search string (see Figure 3.35).
Using Data Manager to find a record.
Let's give the Find button a try. First, make up and add several additional records to your CompanyMaster table. When you are finished, press the Find button. Select CompanyName from the Fields list and the equal sign (=) from the Operator list, and enter the name of a company record that you entered into your CompanyMaster table into the Value field. Click OK. You should now be at the requested record.
If you enter a valid search string but no record is found that meets your criteria, Data Manager will issue an error message and return you to the same record you were pointing to before the search began.
You can use the Delete button on the data entry form to permanently remove a record from the data table. To delete a record, bring up the record you want to delete on the form. You can use the data control to walk through the data table to locate the record, or use the Find button (as described in the preceding section). When you have located the record to delete, simply click on the Delete button and your record will be removed.
You cannot undo a delete operation. If you want to restore a deleted record to the table, you must re-enter the data using the Add button.
NOTE: When a record is deleted from a Microsoft Access database, it is possible that the physical size of the database will not decrease. This is because a delete operation can leave empty space in the data table where the data record used to reside. You can use the Compact Database option (discussed earlier) to remove the empty space from a database.
Today you learned how to use the Data Manager that comes with Visual Basic 4 to perform the following database maintenance operations on Microsoft Access databases:
SELECT Names, VoicePhone FROM CompanyMaster
Create a Microsoft Access database to track mailing addresses. Include fields for social security number, last name, first name, address, city, state, and zip code. Identify and create a primary index. After creating the database, enter records for five individuals.
