When you use Visual Basic to develop database applications, you have a number of data access options. The option designed to be most easily integrated into Visual Basic applications, and the one that is the subject of chapters 3 through 5, is the Microsoft Jet database engine. However, if a situation warrants, you should be aware that data can be stored in sequential or random-access files and accessed using Visual Basic's file read and write commands (alternatively, a third-party data access engine can be used). This chapter looks only at using the Jet engine and, in particular, using Microsoft Access databases (the native database for Visual Basic). (Accessing other databases through the Jet engine is covered in Chapters 8 and 9.)
There are three main methods of creating an Access database for use with Visual Basic:
This chapter demonstrates how to do the following:
If you have used Visual Basic Version 3 to create a database, you will note several changes in the methods used to create databases in Version 4. Any of your existing programs should still run (Version 4 supports the older methods of creating databases for backward compatibility). The major changes you should be aware of are as follows:
This chapter focuses on using data access objects to create portions of the sample database defined in Chapter 1. There are several ways to implement the data access objects. Although each method is not described in detail, the sample case on the companion CD contains examples of the various methods. Each group of commands is tied to a command button on the form. By examining the code, you can see the different methods. The project file for this sample case is CHAPTER3.MAK. The sample case also uses the SQL DDL statements that are covered in Chapter 6, "Understanding Structured Query Language (SQL)." In addition to discussions about using data access objects, this chapter presents a section on using Data Manager to create a database.
You can use Visual Basic's database commands to write a program that creates a database for use in your design work, or to write a program that creates a new database while the program is running. The database creation commands are the only way to make a new database at program run time. Using the program to create the database is particularly useful in creating and distributing commercial applications because you don't have to worry about including and installing the database files with the application. The database is created the first time the user runs your application.
Creating files at run time is also useful if the user is expected to create different database files with the same structure but different user-defined names. Each time the user wants to create a new file, the program asks for the filename and then creates the database accordingly.
Creating a new database involves the following eight steps:
To explain the process of creating a new database, this chapter steps you through the process of creating the database and Retail Items table from the sample application described in Chapter 1. The designed structure of the Retail Items table is repeated in table 3.1 for your convenience.
Table 3.1 Data Structure of the Retail Items Data Table
| Data Element | Type | Length | Description |
| Item Code | Numeric | Integer | Unique item ID |
| Item Description | Character | 50 | Item description |
| Product Category | Character | 10 | Product category |
| Wholesale | Numeric | Single | Wholesale price |
| Retail | Numeric | Single | Retail price |
| Min Quantity | Numeric | Integer | Desired minimum inventory |
| On Hand | Numeric | Integer | Current inventory level |
| SupplierID | Numeric | Long Integer | Supplier ID for item |
| Photo | Picture | Long Binary | Picture of product |
Previous versions of Visual Basic used the CreateDatabase statement to create a new database. Visual Basic 4 contains the DBEngine and Workspace objects as data access objects. In Visual Basic 4, the CreateDatabase statement was changed to a method of the Workspace object. A default workspace is always created for you whenever you access the Jet database engine. You can create other workspaces if necessary. If you do not include the ID of a workspace when using the functions, the default workspace is assumed.
Listing 3.1 shows the statements used to define a database object and create the database (steps 1 and 2 defined in the preceding section). The first part of the listing shows the full syntax of using the CreateDatabase method of the workspace. This is the method used for Visual Basic 4. The second part of the listing shows the use of the CreateDatabase statement used in Visual Basic 3. Either listing creates a new database. The CreateDatabase statement is supported in Visual Basic 4 for backward-compatibility. However, it is recommended that you use the workspace's CreateDatabase method for any new work, since the old methods may not be supported in future versions.
Listing 3.1 Defining a Database Object and Creating a Database
Any valid variable name can be defined as a database object using the Dim statement. Although a database ("A:\TRITON.MDB") was specified in the argument of the CreateDatabase function, a string variable could have been used to hold the name of the database to be created. This arrangement gives the user the flexibility of specifying a database name meaningful to him or her or allows you to create multiple databases with the same structure.
The constant dbLangGeneral is a required argument of the CreateDatabase method. It specifies the language and code page information for the database.
Another optional argument is available for the CreateDatabase method: the options argument allows you to create an Access 1.0, 1.1, 2.0, or 3.0 database (the default is Access 2.0 for Windows 3.1 and 3.0 for Windows 95) and to encrypt your database. To invoke these options, the constants for each option are summed to a long integer and specified as the last argument of the function. The code below shows how you can change the code in listing 3.1 to create an Access 1.1 database and encrypt it.
When using the CreateDatabase method, a trappable error occurs if the file to be created already exists. You should include a trap for this error in your error-handling routine or, better yet, check for the existence of the file name before invoking the function.
Creating a database creates a file on a disk. You can't do anything with that file until the tables have been created and added to the database (refer to steps 3 through 8 in "Writing a Program To Create a Database," earlier in this chapter).
Although this book typically refers to "tables" in a database, keep in mind that a database may contain only a single table.
Defining the TableDef Object
The first step in creating a new table is to create a new TableDef object. This object allows you to set the properties for the new table. The following lines of code show how to create a TableDef object and give your table a name. In earlier versions of Visual Basic, the use of the New keyword in the Dim statement was required. Visual Basic 4 no longer uses this keyword.
The Name property of the table is only one of several properties for the TableDef object, but it is typically the only one required for the creation of an Access database. Some of the other properties (Attributes, Connect, and SourceTableName) may be used when attaching an external table to the database. You can set the Attributes, SourceTableName, and Connect properties in successive arguments of the CreateTableDef method. You can also specify other properties by setting them equal to a value, as you do if you want to set the validation rule and validation error message for a table (as shown below). These statements follow the CreateTableDef method.
Defining the Fields
After defining the TableDef object for the new table, the field objects must be defined. A table can contain one field or a number of fields. For each field, you must define its name and type. Depending on the type of field, you may be required to define other properties, or you may want to set some optional properties.
For text fields, you must set the size property to specify how long a string the field can contain. The valid entries for the size property of the text field are 1 to 255. If you want to allow longer strings, set the field type to memo.
You should be aware of two optional settings for the Attribute property. The first is the auto-increment setting, which tells the database to increment the value of the field each time a new record is added. This setting can provide a record counter and can be used to ensure a unique value in that field for each record. The auto-increment field can then be used as a primary key field. The auto-increment setting is only valid for fields with the long data type. The other optional setting is the updatable setting, which allows you to specify whether a field can be changed. This setting is not typically used when initially creating a table but can be very useful in limiting access to information, particularly in a multiuser environment.
Listing 3.2 shows how the field objects are created and the field properties set for the Retail Items table of the sample application. The field name, type, and size can be specified as optional arguments of the CreateField method. You can also use the CreateField method without any arguments and then set all the field properties with assignment statements. Both of these methods are shown in listing 3.2. Any other properties must be set using an assignment statement. As an example of an assignment statement, the listing sets a validation rule for the wholesale price field.
Listing 3.2 Creating Field Objects and Setting Properties
Once you define each of the fields to be included in the table, the Append method is used to add the fields to the table definition, as shown in listing 3.3.
Listing 3.3 Adding Fields to the Table Definition
If you have a large number of fields, or if you want to create a generic routine for adding fields to a table, consider using an array to define your fields. Arrays allow you to write a simple FOR loop to add all the fields to the table (as shown in the code statements below). And depending on the structure of the table, you may be able to use a loop to set the type properties of several fields, although you must still define each field you intend to add to the table.
Defining the Indexes
Defining indexes for a table is another key aspect of developing your database. If you have created an index with Visual Basic 3, forget everything you know about the process. Remember, however, why you want to create an index and how indexes are used. Visual Basic 4 introduces a new method for creating indexes. This method is more closely related to the creation of the table itself than to the creation of a field (as was the case in Visual Basic 3). For each index, you must still assign a name, define the fields to be included in the index, and determine whether the index is a primary index and whether duplicate values are allowed in the fields that comprise the index key.
To create an index, follow these six steps:
Two of the most commonly used optional properties of the Index object are the Primary property and the Unique property. A primary index is one that is typically used for finding a specific record in a table. To make an index primary, set the Primary property to True. Making an index primary ensures that the value of the index key for each record is unique and that there are no null values.
Use the Unique property on a nonprimary index to make sure that the values of fields other than the primary field are unique (for example, to make sure that you enter a unique social security number for each employee in a table).
For the field objects, the only property of concern for creating indexes is the Attributes property. This property is used to determine whether the sort order of the field is ascending (from A to Z) or descending (from Z to A). The default value is ascending. If you want to sort the field in descending order, set the Attributes property to the value of the constant dbDescending.
You can create a multiple-field index (for example, an index on the first and last names of a customer). To create such an index, simply create multiple fields using the CreateField method. Remember that the order of the fields can have a dramatic impact on the order of your records. The order of the fields in an index is determined by the order in which the fields are appended to the index.
As described in the preceding section, after you create the fields and set the properties of the fields and index, use the Append method to add the fields to the index and the index to the table definition.
You can create a maximum of 32 indexes per table.
For the sample case, create a primary index on the Item Code field and an index on the Wholesale price in descending order. Listing 3.4 shows how this is accomplished.
Listing 3.4 Creating Index Objects, Assigning Properties, and Adding Indexes to the Table
Although spaces are acceptable in field names, they are not allowed in index names. If you want a multiple word index name, separate the words with an underscore.
The final step in creating a database is adding the table or tables to the database. Use the Append method of the Database object to accomplish this as shown in the following code. The code also shows the Close method, which closes the database file and releases the system resources associated with the database object.
Using a Query To Add a Table and Index
As an alternative to using the data access objects, you can also create tables and indexes using a DDL query. The details of DDL queries are presented in Chapter 6, "Understanding Structured Query Language (SQL)," but the following code shows an example of how DDL queries can be used to create tables and indexes.
To further demonstrate the various commands used to create tables, the CHAPTER3.MAK file on the companion disc contains the source code to create all the tables used in the sample case. Each table is created using a different set of commands as follows:
The creation of each table is assigned to a command button on the form. Before you run the table commands, make sure that you run the Create Database section of the form.
Chapter 1, "Designing Your Database Applications," discussed normalizing data and the need to relate normalized tables. The way tables are related with the Jet engine is through the use of a Relation object stored in the database. The relation tells the database which two tables are related, which table is the parent and which is the child, and the key fields used to specify the relationship.
To create a relationship between two tables, follow these seven steps:
Listing 3.5 demonstrates the creation of a relationship, showing how to create a relation between the Customer (primary) table and the Orders (foreign) table of the sample database.
Listing 3.5 Specifying a Relationship between Two Tables Using the Relation Object
NewDb.Relations.Append NewRel
A War Story
If you're like me, you sometimes get ahead of yourself in a program. I did the first time I tried to create a relationship. I had set up the commands just like the manual said, but kept getting the error message, Customer_Orders is not an index in this table.
The problem was that I had forgotten to create a primary index on the Custno field in the Customers table. Although the documentation does not point this out, you must have a primary key field in your primary table. This is the field that identifies the records to the relationship.
The Data Manager application that comes with Visual Basic provides the user with an interactive way of creating and modifying Access databases. The application is invoked by selecting the Data Manager bar under the Add-Ins pad on the Visual Basic main menu.
Once the Data Manager application is active, open the File menu and select the New Database option to create a new Access database. The New Database dialog box appears (see fig. 3.1) so that you can name the database and select the location for it. Enter a valid file name and click OK; you enter the design mode shown in figure 3.2.
If your are working in Windows 3.1, the Data Manager will create an Access 2.0 database. If you are working in Windows 95, the Data Manager will create an Access database for the Jet 3.0 engine. This database format is not compatible with earlier versions.
The New Database dialog box allows you to specify the name of your new database.
The Data Manager design window provides access to the design functions for tables, fields, indexes, and relations.
The database design window gives you the choice of creating a new table, opening an existing table to "browse" the data, changing the design of a table, deleting a table, attaching an external table, or creating table relations. To create a new table, click the New button; the Add Table design area opens (see fig. 3.3). This design window allows you to specify the table name and enter the names, types, and sizes of each field in the table. To add a field to the table, follow these steps:
The Add Table dialog box allows you to specify a name for and add fields to a new table.
If you want to remove a field from the list, highlight the field in the list at the right of the dialog box and click the < button. Clicking the << button removes all fields from the list. When you are satisfied with the fields in the table, click the OK button to create the table.
After you create a new table, you are returned to the main Data Manager design window. If you want to set any optional properties for the fields in the table, highlight the table name and click the Design button to enter the Table Editor window (fig. 3.4 shows the table design window). This window allows you to add new fields, edit existing fields, delete fields (edit and delete functions are new for Visual Basic 4), add and delete indexes, and process key fields.
You can add, edit, and delete fields, or add indexes from the Table Editor dialog box.
To add a new field, click the Add button. The Add Field data screen appears (see fig. 3.5). This screen allows you to enter the field name, type, and size (if necessary), and set optional properties such as validation rules, default values, and required entry flag. When you finish entering the data for the field, click OK to add the field to the table.
You can specify the name and properties of a field with the Add Field dialog box.
To edit a field, open the Table Editor dialog box, highlight the field you want to change, and click the Edit button. The same dialog box you used to add a field (see fig. 3.5) appears. Make your changes and click OK to save the changes.
If you change the properties of a field that contains data, the data in that field is lost. The Data Manager cannot preserve the data.
To delete a field from a table, highlight the field in the Table Editor dialog box and click the Remove button. This action deletes the field and all its associated data.
Visual Basic does not allow you to edit or delete any field that is part of an index expression or a relation. If it is necessary to delete such a field, you must delete the index or relation containing the field and then make the changes to the field.
To work with the indexes for a table, click the Indexes button in the Table Editor dialog box; the Indexes dialog box is presented (see fig. 3.6).
You can add, edit, or delete indexes for a table from the Indexes dialog box.
To add a new index, click the Add button; the Add Index window is presented (see fig. 3.7). In this dialog box, first enter an index name. Then select the fields to be included in the index by highlighting your choice in the Fields in Table list and clicking either the Add(ASC) or Add(DEC) button (depending on whether you want an ascending or descending sequence). The fields you select are added to the Fields in Index list. To remove a field from the Fields in Index list, highlight it and click Remove.
The Add Index dialog box provides a visual means of creating the indexes for a table.
Once you define the fields for the index, you may choose to require the index to be unique or to be a primary index by choosing the appropriate check box in the window. When the index is completed to your liking, save it by clicking OK. The index you have just created is added to the index list on the Indexes dialog box. To delete an index, simply highlight it in the list box and click Remove.
When you finish all your indexes, click the Close button in the Indexes dialog box to return to the table design window.
You can also establish the relationship between two tables with Data Manager. To set a relationship, click the Relations button from the main design window. The Relationships dialog box opens (see fig. 3.8). To start setting up the relation, you choose your primary table from the Primary Table drop-down list and the related table (also known as child table) from the Related Table drop-down list. After you have chosen the primary table, you see a list of available primary key fields in the area of the dialog box under the label Primary Key Fields. For most primary tables, there will be only one primary key field listed. If there is more than one, you will need to choose one of the fields in this block as the primary relation key for this relation.
Once the primary key field is defined, you need to choose the matching field for the related table from the Select Matching Fields drop-down list. You may also define the type of relation (one-to-one or one-to-many) by selecting the appropriate option button under the "Type" label at the top of the dialog box. The default type is one-to-many. If you want to have the relation enforce referential integrity, you will need to mark the check box at the bottom of the dialog box. When you have entered all the information for the relation, add it to the database by clicking the Add button. Clicking the Close button returns you to the database design window.
You can create or delete relations between tables with the Relationships dialog box.
Closing the Data Manager window or opening the File menu and choosing Exit takes you back to the Visual Basic main design screen. To manipulate databases without having to start Visual Basic every time, you may find it convenient to make the Data Manager application a program item in your Visual Basic group.
The other option for creating an Access database for use with a Visual Basic application is to use Microsoft Access. Access has a good visual design interface for setting up tables, indexes, queries, and table relationships. Obviously, this option is only available if you have a copy of Access.
If you have Access and want more information on using it to create and modify databases, the book Using Microsoft Access 2, Special Edition (published by Que Corporation) is recommended.
For many development projects, you load your initial data into the database after you create the database. This is typically the case in custom development work when your client already has some data for the system (either in written or electronic form). It is also usually necessary to load data into a test database in order to check out the functions of your system.
You have several choices for loading this data into the database you have created:
To use Data Manager, start the Data Manager application and open the database as just explained (assuming that the Data Manager is not already open). From the Data Manager design window, select a table from the table list and click the Open button. The Table Editing dialog box appears (see fig. 3.9). This window is similar to the data entry panel you will create in Chapter 5, "Using Visual Basic's Data and Bound Controls." From this panel, you can add new records, update the data in existing records, and delete records. When finished, close the Data Entry window and exit Data Manager.
You can edit your database from the Data Manager.
Even if you create the perfect database for an application, sooner or later someone will come along and say, "Well, I really need this program to handle other data, too." At this point, you must modify the structure of your database and tables. Modifications can take the form of new tables, new fields or indexes in tables, or changes in the properties of tables, fields, or indexes. On occasion, you may also have to delete a table, field, or index.
The following sections cover the modification of a database through the use of Visual Basic 4 program commands. As with the creation of a database, you can also use the Data Manager application or Microsoft Access to perform the modifications.
To add a table, follow the same steps that you took to create tables in a new database:
To delete a table from a database, you can use the Delete method of a database object as shown in this statement:
OldDb.TableDefs.Delete "Customers"
Use the Delete method with extreme caution. When you delete a table, all fields, indexes, and—most importantly—data is deleted with it. And when it's gone, it's gone. The only way to get it back is to create the table again from scratch and reload all your data.
Adding a new index involves the same steps as creating an index for a new table. You must define an index object, set the properties of the index, and append the new index to the table. An example of these steps was shown earlier in listing 3.4.
To delete an index, simply use the Delete method shown in this statement. This code deletes the Price_Paid index from the Retail Items table.
OldDb.TableDefs("Retail Items").Indexes.Delete "Price_Paid"
It is not possible to edit the properties of an index in a table. Therefore, if a change to an index is required, the old index must be deleted from the table and a new index with the new desired properties created. This is accomplished using the methods shown in the section "Defining the Indexes" earlier in this chapter.
You cannot delete an index that is required by a relation. To delete such an index, you must first delete the relation.
As you learned when creating a new database, you add a field to a table by defining the field object, setting its properties, and appending it to the table. These commands were presented in listings 3.2 and 3.3.
Unlike Visual Basic 3, Version 4 provides you with a method to delete a field from a table. To delete a field, use the Delete method shown here. This example deletes the Address field from the Customers table:
Unfortunately, there is no direct way of changing a field's properties. There are, however, two indirect ways to accomplish this task. If you have a new table that contains no data, or if you don't care about losing the data in the field, you can delete the field from the table and then recreate it with the new properties. If you have a table that contains data and want to preserve the data, you must create a whole new table (making the appropriate changes), move the data to the new table, and then delete the old table. The difficulty of this process of making changes to fields dramatically underscores the importance of a good initial design.
To move data from one table to another existing table, you must follow these steps:
If you have Microsoft Access, you can change the properties of a table’s fields while preserving the fields’ contents.
Remember that you cannot delete a field that is part of an index or relation.
If it is necessary to delete a relation, you can do this with the Delete method of the database object. The following statement shows how to delete the relation created in listing 3.5:
In this chapter, you have learned that the Data Manager application and Microsoft Access can create, modify, and load data into a database. So the question you may be asking is: "Why do I ever need to bother with the Visual Basic 4 program commands for these functions?" The answer is that, in many cases, you don't. If you have direct control over the database (that is, you are the only user or you can access the database at any time), you may never need to use program commands to create or change a database.
However, if you have an application with many customers—either throughout your company or across the country—there are several benefits to using a program. One benefit is in initial installation. If the database creation routines are in the program itself, you don't have to include empty database files on your installation disks. This may reduce the number of disks required and certainly reduces the possibility that a key file is left out. Another benefit occurs when you distribute updates to the program. With changes embedded in a program, your user merely runs the update program to change the file structure. There is no need to reload data into a new, blank file. Also, by modifying the file in place, you can preserve most structure changes in the database made by the end user.
Another reason for putting database creation and maintenance commands in a program is for performance considerations. There are times when it is desirable from a performance standpoint to create a temporary table to speed up a program or to store intermediate results and then delete the table at the completion of the program. It may also be desirable to create a temporary index that creates a specific order or speeds up a search and then delete the index later.
Queries are a powerful method of gathering information from more than one table or of selecting information from a table that matches a specific criteria (for example, customer records for people who live in Alabama). As you will learn in Chapter 4, "Manipulating Data with a Program," an object called a dynaset can store this type of information for use in your programs. In fact, using a query is one method of creating a dynaset. The advantage of creating a query is that the information about it is saved in the database itself, making it convenient to test and store information needed to create recordsets that are used often.
To create a query, you define a QueryDef object and then use the CreateQueryDef method of the database. When calling the function, you must specify the name of the query. You can specify the Structured Query Language (SQL) syntax of the query, or you can define the SQL statement in a separate program line. The following code shows the two different methods of creating a query.
The heart of defining queries is the SQL statement. This statement defines the fields to be included, the source of the fields, record filters, and the sort order of the resulting recordset. SQL statements are covered in Chapter 6, "Understanding Structured Query Language (SQL)."
Queries can be created and stored in the database only for Access databases.
As with most other objects in the database, if you create it, you may, at some time, need to delete it. Queries are no exception. If you have a query that you no longer need in your database, you can remove it using the following command:
When you use a query in your program, you open the query by creating a data access object for it. Therefore, when you need to delete the query, it is a good idea to specifically close a query variable before you try to delete a query. Closing the variable ensures that the query is not in use, and that no error occurs during the deletion. The syntax for closing a query is: NewQry.Close.
To learn more about some of the subjects mentioned in this chapter, please refer to the following chapters:
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.
