Visual Basic 6 Database How-To

Previous chapterNext chapterContents


- 4 -
Designing and Implementing a Database



No amount of Visual Basic coding skill can overcome the problems of a poorly designed database. This chapter introduces some fundamental principles of relational database design. You'll see how you can use Visual Basic code to create databases and database objects, including tables, fields, indexes, and relationships. You'll also see how you can use the properties of these database objects to enforce business rules for your application.

4.1 Create a New Database

Creating a database with Data Access Object (DAO) code can be done with a single method. This How-To shows you how.

4.2 Define Tables and Fields

Every database design starts with tables and fields. This How-To examines time-tested principles of table design and demonstrates how you can use DAO code to create your database objects.

4.3 Define the Primary Key and Other Indexes

Indexes are the key to establishing relationships between tables and improving database application performance. This How-To introduces several types of indexes and shows you how to create indexes with Visual Basic code.

4.Define Relations Between Tables

This How-To shows you how to use Visual Basic to create the relations for your database.

4.5 Use the Jet Database Engine to Enforce Business Rules

There are two ways to enforce business rules in your database applications. You can write Visual Basic code to enforce your rules, or you can build the rules right into the database schema. This How-To shows you how to use the properties of the objects in your database to enforce business rules.

4.1 How do I...

Create a new database?

Problem

My application needs to create a database at a location chosen by the user. How can I do this with Visual Basic?

Technique

The CreateDatabase method of the Workspace object creates a database and returns a database object you can use in your application. The CreateDatabase method takes three arguments:

Table 4.1. Locale constants for the CreateDatabase method.

CONSTANT Collating Order
dbLangGeneral Western European: English, German, French, Portuguese, Italian, and modern Spanish
dbLangArabic Arabic
dbLangChineseSimplified Simplified Chinese
dbLangChineseTraditional Traditional Chinese
dbLangCyrillic Russian
dbLangCzech Czech
dbLangDutch Dutch
dbLangGreek Greek
dbLangHebrew Hebrew
dbLangHungarian Hungarian
dbLangIcelandic Icelandic
dbLangJapanese Japanese
dbLangKorean Korean
dbLangNordic Nordic languages (Microsoft Jet database engine version 1.0 only)
dbLangNorwDan Norwegian and Danish
dbLangPolish Polish
dbLangSlovenian Slovenian
dbLangSpanish Traditional Spanish
dbLangSwedFin Swedish and Finnish
dbLangThai Thai
dbLangTurkish Turkish

Table 4.2. Options constants for the CreateDatabase method.

CONSTANT DESCRIPTION
dbEncrypt Creates an encrypted database
dbVersion10 Creates a database that uses the Microsoft Jet database engine version 1.0 file format
dbVersion11 Creates a database that uses the Microsoft Jet database engine version 1.1 file format
dbVersion20 Creates a database that uses the Microsoft Jet database engine version 2.0 file format
dbVersion30 (Default) Creates a database that uses the Microsoft Jet database engine version 3.0 file format (compatible with version 3.51)

Steps

Open and run HT401.VBP. Click the Create Database button. Choose a directory and filename using the common dialog, and click Save to create the database, as shown in Figure 4.1.

Figure 4.1. Creating a database.

1. Create a new Standard EXE project, and save it as HT401.VBP. Create the objects and properties listed in Table 4.3, and save the form as FCreateDB.frm.

Table 4.3. Objects and properties for the Database Creator form.

OBJECT PROPERTY SETTING
Form Name FCreateDB
Caption Create Database
CommonDialog Name dlgCreateDB
CommandButton Name cmdCreate
Caption Create Database

2. Add Option Explicit to the declarations section.

3. Create the GetDBName() function. This function sets up the Common Dialog control with the appropriate filters and flags and returns the file selected by the user as the return value of the function.

Private Function GetDBName() As String
` Get the desired name using the common dialog
On Error GoTo ProcError
    Dim strFileName As String
    ` set up the file save dialog file types
    dlgCreateDB.DefaultExt = "mdb"
    dlgCreateDB.DialogTitle = "Create Database"
    dlgCreateDB.Filter = "VB Databases (*.mdb)|*.mdb"
    dlgCreateDB.FilterIndex = 1
    ` set up flags
    dlgCreateDB.Flags = _
    cdlOFNHideReadOnly Or _
    cdlOFNOverwritePrompt Or _
    cdlOFNPathMustExist
    ` setting CancelError means the control will
    ` raise an error if the user clicks Cancel
    dlgCreateDB.CancelError = True
    ` show the SaveAs dialog
    dlgCreateDB.ShowSave
    ` get the selected name
    strFileName = dlgCreateDB.filename
    ` dialog prompted for overwrite,
    ` so kill file if it exists
    On Error Resume Next
    Kill strFileName
ProcExit:
    GetDBName = strFileName
    Exit Function
ProcError:
    strFileName = ""
    Resume ProcExit
End Function
4. Create the CreateDB() procedure. This procedure takes a filename as a parameter and creates a database using the CreateDatabase method of the Workspace object.

Private Sub CreateDB(strDBName As String)
` create the database
    Dim db As Database
    ` if desired, you can specify a version or encrypt
    ` the database as the optional third parameter to
    ` the CreateDatabase method
    Set db = DBEngine(0).CreateDatabase(strDBName, dbLangGeneral)
End Sub
5. Add the following code as the cmdCreateDB_Click event procedure. This procedure calls the GetDBName function to obtain a filename and passes it to CreateDB to create the database.

Private Sub cmdCreateDB_Click()
On Error GoTo ProcError
    Screen.MousePointer = vbHourglass
    Dim strDBName As String
    strDBName = GetDBName()
    If Len(strDBName) > 0 Then
        CreateDB strDBName
    End If
ProcExit:
    Screen.MousePointer = vbDefault
    Exit Sub
ProcError:
    MsgBox Err.Description
    Resume ProcExit
End Sub

How It Works

Two simple procedures--GetDBName and CreateDB--do all the work. The first obtains a filename from the user via the Common Dialog control, and the second creates the database using the filename provided.


THE SQL CREATE DATABASE STATEMENT

Some database engines provide a CREATE DATABASE statement as a command in their SQL dialects. Jet, however, does not. Although you can create tables, fields, indexes, relationships, and queries using Jet SQL, you must use the CreateDatabase method to create.


Comments

You might be able to avoid creating a database in code by using a model database. If you will be distributing an application that will always use the same database structure, you can create an empty version of the database and have the setup program install the empty model.

Although this approach will work in many cases, two common scenarios preclude the use of this technique:

1. If the database schema is not constant, a model will serve little or no purpose.

2. If the database must be secured at the installation point, you will need to have your code create the database using the account that will be the database owner. Although you can change the owner of database objects, you cannot change the owner of the database itself. See Chapter 11, "The Windows Registry and State Information," for additional information about working with secured databases.

4.2 How do I...

Define tables and fields?

Problem

I need a database that is flexible, accurate, and reliable. How do I design my table and column structure to ensure that this is what I get?

Technique


RECORDS AND ROWS--FIELDS AND COLUMNS

The terms row and record are interchangeable, as are the terms column and field. Referring to tables in terms of rows and columns is the generally accepted terminology for most literature on database design and for most database engines, except Jet. The Data Access Objects (DAO) object model and most of the Visual Basic documentation use the terms record and field. This kind of variation in terminology doesn't stop at the database design level. Most server databases, for example, describe the data returned by a query as a result set, whereas the Jet engine named its object a Recordset. Don't let the terminology confuse you. Whether you are dealing with records, rows, columns, or fields, the concepts are still the same.


Building a database structure is a process of examining the data that is useful and necessary for an application, and then breaking it down into a relatively simple row-and-column format. You should understand two points about tables and columns that are the essence of any database:

The simplest model for any database is a flat table. The trouble with flat files is that they waste storage space and are problematic to maintain. Table 4.4 shows a flat table design that could be used to store information about students and classes at a school

Table 4.4. A flat table.

STUDENT ADVISOR COURSE1 DESCRIPTION1 INSTRUCTOR1 COURSE2 DESCRIPTION2 INSTRUCTOR2
B. Williams H. Andrews VB1 Intro to VB C. MacDonald DAO1 Intro to DAO S. Garrett
L. Duncan P. Lowell DAO1 Intro to DAO S. Garrett SQL1 Jet SQL K. Olson
H. Johnson W. Smith API1 API Basics W. Smith OOP1 VB Objects T. Carter
F. Norris J. Carter VB1 Intro to VB C. MacDonald API1 API Basics W. Smith

Several problems arise with this flat table:

The solution to these problems is a technique known in relational database parlance as normalization. Normalization is the process of taking a wide table with lots of columns but few rows and redesigning it as several narrow tables with fewer columns but more rows. A properly normalized design enables you to use storage space efficiently, eliminate redundant data, reduce or eliminate inconsistent data, and ease the data maintenance burden.

Several forms of normalization will be discussed shortly, but one cardinal rule absolutely must be followed:

YOU MUST BE ABLE TO RECONSTRUCT THE ORIGINAL FLAT VIEW OF THE DATA.

If you violate this rule, you will have defeated the purpose of normalizing the design.


NORMALIZATION IS NOT A PANACEA

Don't be misled into thinking that all database design woes can be cured with proper normalization. In fact, the opposite can be true. Taken to extremes, normalization can cause as many problems as it cures. Although you might be able to cure every type of data anomaly that could possibly occur, you will send performance on a downward spiral if your design requires more than two or three relational joins in a query to reconstruct a flat view of your data.

Consider this scenario:

You are designing a customer database. It's a well-known fact that in the United States, a postal zip code defines a specific city and state, and a nine-digit zip code defines a specific delivery point. You could, then, store only a zip code in the customer table and eliminate the city and state columns that would typically be required. However, every time the city and state needed to be retrieved, the database engine would have to perform an additional join. This might or might not be acceptable in your situation.

Now take this scenario to an additional level of detail. It's also true that although millions of people live in the United States, there are a limited number of first and last names and only 26 possible middle initials. Theoretically, you could create a foreign key column in place of the normal last name column and do the same for the first name. This level of normalization, however, steps into the realm of the ridiculous. It is pointless complexity that adds no real benefit for data accuracy.


Forms of Normalization

Relational database theorists have divided normalization into several rules, called normal forms:

Additionally, for a database to be in second normal form, it must be in first normal form, and so on. Fourth and fifth normal forms also exist, but these are rarely applied. In fact, it might be practical at times to violate even the first three forms of normalization (see the sidebar "Normalization Is Not a Panacea").

First Normal Form

First normal form requires that a table does not contain repeating groups. A repeating group is a set of columns, such as the CourseID, Description, and Instructor columns in Table 4.4. Repeating groups are removed by creating a separate table from the columns that repeat.


NOTE If you have a set of columns with names that end in numbers, such as the CourseID1 and CourseID2 columns in the example, it's a clear sign that you have encountered repeating groups. At this point, you need to think about removing the columns to a separate table.

Table 4.5 is a revised version of the sample table, with the repeating groups for the course moved to their own table. (Note that in this table and the following several tables, primary key columns have been put in bold type. The primary key concept is explained a little later in the chapter.)

Table 4.5. First normal form.

STUDENTS STUDENTCOURSES
SCStID
StID SCCourseID
StName SCCourseDesc
StAdvisorName SCCourseInstrName

The repeating group has been eliminated by creating a second table. The student can now enroll in any number of courses (including no courses). Although the repeating group is gone, we can still reconstruct the original table by joining the two via the new SCStID column in the StudentCourses table. This column is a foreign key that matches the value of the StID column in the Students table.

TABLE AND COLUMN NAMING CONVENTIONS

A naming convention has been applied to these table and column names:

Naming conventions have an annoying tendency to start religious wars among programmers, but nearly all programmers will--perhaps grudgingly--admit their usefulness. You can adopt this convention or any other convention that suits you or your company. What's important is not the particular convention you choose but that you choose one and follow it faithfully.

Second Normal Form

Second normal form requires that no nonkey attributes depend on a portion of the primary key. To understand this rule, you need to understand the concept of a primary key. A primary key is a column, or set of columns, in a table that uniquely identifies a single record. The primary key for a table is most often an arbitrary value, such as an autoincrement column (Jet refers to this as a counter), although the primary key can be any type of data.


NOTE Proceed with caution if you decide to use anything other than an arbitrary value as the primary key for a table. Even seemingly reliable data such as a social security number can fail if used as a primary key. An arbitrary value provided by the database engine is guaranteed to be unique and independent of the data in the table.

Second normal form really applies only to tables in which the primary key is defined by two or more columns. The essence is that if certain columns can be identified by only part of the primary key, they must be in their own table. The StudentCourses table in Table 4.5 violates second normal form because the course information can be identified without using the SCStID column. Table 4.6 shows the same data reorganized so that it meets the requirements for second normal form.

Table 4.6. Second normal form.

STUDENTS STUDENTCOURSES COURSES
SCStID
StID SCCourseID CourseID
StName CourseDesc
StAdvisorName CourseInstrName

The partial dependence on the primary key has been eliminated by moving the course information to its own table. The relationship between students and courses has at last revealed itself to be a many-to-many relationship (see the following section,"Advanced Design Techniques"). Each student can take many courses, and each course can have many students. The StudentCourses table now contains only the two foreign keys to Students and Courses.

Third Normal Form

Third normal form requires that no attributes depend on other nonkey attributes. This means that all the columns in the table contain data about the entity that is defined by the primary key. The columns in the table must contain data about only one thing. Like second normal form, this is used to remove columns that belong in their own table.

Table 4.7 shows a revised version of these tables, with a few columns added to help illustrate third normal form.

Table 4.7. Detail columns added.

STUDENTS StID STUDENTCOURSES SCStID SCCourseID COURSES CourseID
StFirstName CourseDesc
StLastName CourseInstrName
StAddress CourseInstrPhone
StCity
StState
StZIP
StAdvisorName
StAdvisorPhone

To complete the normalization, we need to look for columns that are not dependent on the primary key of the table. In the Students table, we have two data items about the student's advisor: the name and phone number. The balance of the data pertains only to the student and so is appropriate in the Students table. The advisor information, however, is not dependent on the student. If the student leaves the school, the advisor and the advisor's phone number remain the same. The same logic applies to the instructor information in the Courses table. The data for the instructor is not dependent on the primary key CourseID because the instructor is unaffected if the course is dropped from the curriculum (unless school officials fire the instructor when they drop the course). Table 4.8 shows the revised schema in third normal form.

Table 4.8. Third normal form.

STUDENTS
StID
ADVISORS
AdvID
INSTRUCTORS
InstrID
STUDENTCOURSES
SCStID
COURSES
CourseID
SCCourseID
StAdvID AdvFirst InstrFirst CourseInstrID
StFirst AdvLast InstrLast CourseDesc
StLast AdvPhone InstrPhone
StAddress
StCity
StState
StZIP

The database is now in third normal form:

Advanced Design Techniques

Look again at Table 4.8. You can see two types of relationships between tables:

A third possible type of relationship exists between tables: a one-to-one relationship. Table 4.8 reveals a possible use of a one-to-one relational design. Both the Advisors table and the Instructors table contain identical lists of columns. In a real-world database, each of these tables would contain additional columns specific to the role of advisor or instructor and would also contain additional information about the individual faculty member who has that role. If you examine Table 4.4, you will notice that faculty members can act in both roles--as advisors and instructors. Table 4.9 shows a more detailed view of the advisor and instructor data.

Table 4.9. Advisors and instructors.

ADVISORS INSTRUCTORS
AdvID InstrID
AdvFirst InstrFirst
AdvLast InstrLast
AdvPhone InstrPhone
AdvGradeLevel InstrSpecialty

For this example, it is assumed that advisors handle students by grade level (undergraduate or graduate) and that instructors have a specialty area that they teach. For example, in a computer science department, an instructor might specialize in teaching classes related to a particular language.

Much of the data in these two tables is shared. You could duplicate the columns in both tables, or you could further subdivide these tables, as shown in Table 4.10.

Table 4.10. The school faculty.

FACULTY ADVISORS INSTRUCTORS
FacID AdvFacID InstrFacID
FacFirst AdvGradeLevel InstrSpecialty
FacLast
FacPhone

The columns that are shared by both tables have been removed to the Faculty table. The Advisors and Instructors tables now contain only a foreign key to the faculty table and the columns that relate specifically to the role of advisor or instructor. The foreign key columns in this case also act as the primary key for these tables because there must be one (and only one) row in the Faculty table for any advisor or instructor. This is a one-to-one relationship. The Advisors and Instructors tables define extensions to the Faculty table for subsets of the data in that table.

Designing the tables so that they use the shared Faculty table allows for the reuse of the code required to manage that data and common querying of all members of the school staff.

The design of the database for the mythical school is nearly complete, but one set of data is still missing. All but the smallest of organizations generally employ a hierarchical management structure. If the school is a large university, it probably has several campuses, each of which have several colleges. Each college is probably further divided into several departments, and even those departments might be subdivided. The trouble with hierarchical organizations is that you often can't know in advance how many levels will exist within the hierarchy. A solution to this problem does exist, however. Table 4.11 expands the view of the faculty information.

Table 4.11. The school faculty.

FACULTY DEPARTMENTS
FacID DeptID
FacDeptID DeptName
FacFirst DeptParentDeptID
FacLast
FacPhone

A foreign key to the Departments table has been added to the Faculty table. This enables a faculty member to be assigned to a department. The Departments table has three columns: DeptID, the primary key; DeptName, the department name; and the key to establishing the hierarchical relationship, the DeptParentDeptID column. This column is a foreign key, but the key points back into the Departments table. This relationship might be easier to understand if you look at some sample data, as shown in Table 4.12.

Table 4.12. The Departments table.

DEPTID DEPTNAME DEPTPARENTDEPTID
1 Minnesota State University Null
2 Institute of Technology 1
3 College of Liberal Arts 1
4 College of Medicine 1
5 Department of Internal Medicine 4
6 Oncology Department 5

Looking at the sample data, you can see that the College of Medicine is directly under the university, the Department of Internal Medicine is under the College of Medicine, and the Oncology Department is under the Department of Internal Medicine. This type of structure can be reassembled as a flat table using a self-join. In a self-join, a table is included twice in the same query.


NOTE The TreeView control is an excellent choice as a tool for displaying hierarchical data.

Creating Tables and Columns with Visual Basic

A well-designed database schema is critical, but that's only half the work of creating a database. You still need to create the actual database objects. You can use two methods to create database objects in a Jet database:

1. Create the TableDef object by using the CreateTableDef method.

2. Create the Field objects by using the CreateField method, and add them to the TableDef object using the Append method.

3. Add the TableDef object to the collection by using the Append method.

Steps

Open and run project HT402.vbp. A sample database based on the tables shown in Table 4.8 and Table 4.10 has been created and saved as HT402.mdb. You can open the sample database and inspect it, or you can create a new database by using the File | New menu command. First, create the Students, Courses, and StudentCourses tables (as shown in Table 4.8) and then create the Faculty, Advisors, and Instructors tables (as shown in Table 4.10). To create the tables, select Table | Add. Figure 4.2 shows the Create TableDef form with the Students table in progress.

Figure 4.2. Creating the Students table.


NOTE This example and the two examples that follow in How-To 4.3 and How-To 4.4 use the Microsoft Windows Common Dialog 6.0, the Microsoft Windows Common Controls 6.0, and the Microsoft DAO 3.51 Object Library components.
1. Create a new project called HT402.vbp. Add BMain.bas to the project. This standard module contains procedures to open an existing database and create a new database. These procedures are based in large part on the example presented in How-To 4.1.

2. Add frmMain.frm to the project. This is based on a form created by the VB Application Wizard. The wizard-generated code manages the split view for the tree and list panes of the main form. For simplicity, a considerable amount of the wizard-generated code was removed. The right pane supports only the report view, and all the toolbar buttons and menu controls were removed, along with their associated event procedures. Menu controls and associated code were then added for the File and Table menus. Table 4.13 shows the menu controls that were added for the example. In the declarations section, an object variable is declared for the CDBExplorer class:

Private mcdbExp As CDBExplorer

Table 4.13. Menu controls for frmMain.

NAME CAPTION
mnuFile &File
mnuFileOpen &Open
mnuFileNew &New
mnuFileBar1 -
mnuFileClose &Close


NOTE Because of space limitations, all the wizard-generated code that manages the split Explorer view for the left and right panes of the main form has not been included here.
3. The following code controls the File menu for the main form. The mnuFileOpen_Click and mnuFileNew_Click event procedures call routines in BMain.bas to open or create a database and then create and initialize the mcdbExp object variable. When initialized using the ExploreDatabase method, the CDBExplorer class accepts a database name, a reference to a TreeView control, and a reference to a ListView control as parameters. Code within the class then handles most of the management of the tree and list panes of the form.

Private Sub mnuFileOpen_Click()
` open a database
On Error GoTo ProcError
  Dim strDBName As String
  Screen.MousePointer = vbHourglass
  strDBName = GetOpenDBName(dlgCommonDialog)
  If Len(strDBName) Then
    Set mcdbExp = Nothing
    Set mcdbExp = New CDBExplorer
    mcdbExp.ExploreDatabase strDBName, tvTreeView, lvListView
  End If
  ` no node is selected by default, so we
  ` select the root node here
  SelectRootNode
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox Err.Description
  Resume ProcExit
End Sub
Private Sub mnuFileNew_Click()
` create a new database
On Error GoTo ProcError
  Dim strDBName As String
  Screen.MousePointer = vbHourglass
  ` get the filename
  strDBName = GetNewDBName(dlgCommonDialog)
  ` kill it if it exists
  ` note that GetDBName prompts to confirm overwrite
  On Error Resume Next
  Kill strDBName
  ` create the database
  CreateDB strDBName
  ` explore it
  Set mcdbExp = New CDBExplorer
  mcdbExp.ExploreDatabase strDBName, tvTreeView, lvListView
  SelectRootNode
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox Err.Description
  Resume ProcExit
End Sub
Private Sub mnuFileClose_Click()
  `unload the form
  Unload Me
End Sub
4. The following code passes the Expand and NodeClick events for the tree pane on to the CDBExplorer class:

Private Sub tvTreeView_Expand(ByVal Node As ComctlLib.Node)
` Expand the node
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  ` the class does the work
  mcdbExp.ExpandNode Node
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub tvTreeView_NodeClick(ByVal Node As ComctlLib.Node)
` Display the properties of the selected node in the listview
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  ` the class does the work
  mcdbExp.ListProperties Node
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
5. Add the CDBExplorer.cls class module to the project. Code in this class module does most of the work of mapping database objects to nodes in the tree pane of the form. This was developed as a class module to provide a degree of isolation between the database engine and the user interface in the form. The class presents a hierarchical view of a database, including tables, fields, indexes, queries, and relationships in a tree.

The class works by examining the Expand and NodeClick events of a TreeView control. When a node in the tree is expanded, the class populates that branch of the tree (empty dummy nodes are initially written to unexpanded nodes so that the node will be expandable on the form). After a node is selected by the user, the class determines what database object is associated with the node by examining the position of the node in the tree; then the class displays the properties of the associated object in the list pane. The property list works by iterating the Properties collection common to all DAO objects (except collections) and adding them as items in the ListView control. In this How-To, mnuTableAdd and mnuTableDelete call the AddTable and DeleteTable methods of the class. These methods add or remove a TableDef object and refresh the TableDefs branch of the tree on frmMain.

Public Sub AddTable()
  Load frmCreateTableDef
  Set frmCreateTableDef.Database = mdb
  frmCreateTableDef.Show vbModal
  ` refresh the tabledefs node
  ExpandNode mtvw.Nodes("TableDefs")
End Sub
Public Sub DeleteTable(strTableDefName As String)
  ` delete the TableDef
  mdb.TableDefs.Delete strTableDefName
  ` refresh the tree
  ExpandNode mtvw.Nodes("TableDefs")
End Sub


NOTE Because of the length of the code in the CDBExplorer class, it was not possible to present all of it here. Source code comments in the class and the CDBExplorer.html file included in the project as a related file on the CD-ROM provide additional details about the class.
6. Add a new form to the project and save it as frmCreateTableDef.frm. Add the objects and properties shown in Table 4.14. Except for lblTableDefName, txtTableDefName, and the cmd command button array, all controls should be drawn within the fraFields frame.

Table 4.14. frmCreateTableDef objects and properties.

OBJECT PROPERTY VALUE
Form Name frmCreateTableDef
Caption Create TableDef
BorderStyle 3-Fixed Dialog
Label Name lblTableDefName
Caption &Table Name
TextBox Name txtTableDefName
Frame Name fraFields
Caption Fields
Label Name lblFieldName
Caption &Name
TextBox Name txtFieldName
Label Name lblFieldType
Caption &Data Type
ComboBox Name cboFieldDataType
Style 2-Dropdown List
Label Name lblFieldSize
Caption Field &Size
TextBox Name txtFieldSize
CommandButton Name cmdAdd
Caption &Add
ListView Name lvwFields
CommandButton Name cmd
Index 0
Caption OK
Default True
CommandButton Name cmd
Index 1
Caption Cancel
Cancel True

7. Add the following code to the declarations section. The database object is used to create the TableDef object. The two constants are indexes into the cmd CommandButton control array.

Option Explicit
` database object
Private mdb As Database
` command button array index constants
Private Const cmdOK = 0
Private Const cmdCancel = 1
8. Add the Form_Load event procedure. This procedure populates the field type list, sets up the list view headers, and disables the frame and OK button. The frame is enabled after a table name is provided. OK is enabled when a table name is provided and at least one field has been added to the field list.

Private Sub Form_Load()
` set up form
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  ` set up fields controls
  cmdAdd.Enabled = False
  ` fill the data types combo
  With cboFieldDataType
    ` Note: not all field types are
    ` included here
    .Clear
    .AddItem "Boolean"
    .AddItem "Counter"
    .AddItem "Date/Time"
    .AddItem "Long Integer"
    .AddItem "Text"
    .AddItem "Memo"
  End With
  cboFieldDataType.Text = "Text"
  ` set up list view
  lvwFields.View = lvwReport
  With lvwFields.ColumnHeaders
    .Add , "Name", "Name"
    .Item("Name").Width = 2000
    .Add , "Type", "Data Type"
    .Add , "Size", "Size"
  End With
  ` disable the entire fields frame
  fraFields.Enabled = False
  ` disable OK button
  cmd(cmdOK).Enabled = False
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
9. Add the cboFieldDataType_Click procedure. This enables or disables the field size text box, depending on the type of field selected.

Private Sub cboFieldDataType_Click()
  If cboFieldDataType.Text = "Text" Then
    lblFieldSize.Enabled = True
    txtFieldSize.Enabled = True
  Else
    txtFieldSize.Text = ""
    lblFieldSize.Enabled = False
    txtFieldSize.Enabled = False
  End If
End Sub
10. Add the cmdAdd_Click event procedure. This procedure uses the data in the FieldName, DataType, and Size controls to add the field to the field list in the ListView control. It then enables the OK button and returns focus to the FieldName control.

Private Sub cmdAdd_Click()
` add to the listview
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  Dim li As ListItem
  Dim strFieldName As String
  Dim strFieldDataType As String
  strFieldName = txtFieldName.Text
  strFieldDataType = cboFieldDataType.Text
  Set li = lvwFields.ListItems.Add _
    (, strFieldName, strFieldName)
  With li
    .SubItems(1) = strFieldDataType
    ` only add size if applicable
    If strFieldDataType = "Text" Then
      .SubItems(2) = txtFieldSize.Text
    Else
      .SubItems(2) = "N/A"
    End If
  End With
  ` prep for new entry
  txtFieldName.Text = ""
  txtFieldName.SetFocus
  ` enable the OK button
  cmd(cmdOK).Enabled = True
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
11. Add the Change event procedures for the txtTableDefName and txtFieldName controls. These enable or disable other controls on the form based on the current values.

Private Sub txtTableDefName_Change()
` Enable/disable controls
  cmd(cmdOK).Enabled = False
  fraFields.Enabled = False
  If Len(txtTableDefName) > 0 Then
    fraFields.Enabled = True
    If lvwFields.ListItems.Count > 0 Then
      cmd(cmdOK).Enabled = True
    End If
  End If
End Sub
Private Sub txtFieldName_Change()
  If Len(txtFieldName.Text) > 0 Then
    cmdAdd.Enabled = True
  Else
    cmdAdd.Enabled = False
  End If
End Sub
12. Add the cmd_Click event procedure. This procedure adds the table if OK is chosen or unloads the form if Cancel is chosen.

Private Sub cmd_Click(Index As Integer)
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  Select Case Index
    Case cmdOK
      ` add the table
      AddTable
    Case cmdCancel
      ` just unload the form
  End Select
  Unload Me
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
13. Create the AddTable procedure. AddTable creates the TableDef object, then extracts the field information from the ListView to create and add each Field object. After the fields have been added to the table, the table is added to the database using the Append method of the TableDefs collection.

Private Sub AddTable()
  Dim li As ListItem
  Dim td As TableDef
  Dim fld As Field
  Dim lngType As Long
  Dim strFieldName As String
  Dim strFieldDataType As String
  Set td = mdb.CreateTableDef(txtTableDefName.Text)
  ` add the fields
  For Each li In lvwFields.ListItems
    ` get the name
    strFieldName = li.Text
    ` get the data type
    strFieldDataType = li.SubItems(1)
    Select Case strFieldDataType
      Case "Boolean"
        lngType = dbBoolean
      Case "Counter"
        lngType = dbLong
      Case "Date/Time"
        lngType = dbDate
      Case "Long Integer"
        lngType = dbLong
      Case "Text"
        lngType = dbText
      Case "Memo"
        lngType = dbMemo
    End Select
    ` check field type
    If lngType = dbText Then
      ` text, create with size
      Set fld = td.CreateField _
        (strFieldName, dbText, CInt(li.SubItems(2)))
    Else
      ` other, create without size
      Set fld = td.CreateField(strFieldName, lngType)
      If strFieldDataType = "Counter" Then
        fld.Attributes = fld.Attributes Or dbAutoIncrField
      End If
    End If
    td.Fields.Append fld
    Set fld = Nothing
  Next  ` ListItem
  ` append the tabledef
  mdb.TableDefs.Append td
End Sub
14. Add the Database property. This is used by the AddTable procedure and must be set before the form is shown.

Public Property Set Database(db As DAO.Database)
  Set mdb = db
End Property

How It Works

The AddTable procedure in frmCreateTableDef is the critical procedure for this How-To. This routine creates the table using the name provided on the form, then iterates the items in the list to create and append the fields. When all the fields have been added, the table is appended to the database. The balance of the code on the form serves only to manage and coordinate the user interface.

Comments

Much of the code provided on the CD-ROM supports the main Explorer form and class module, but the code in frmCreateTableDef does all the work of creating a table and its fields. It is helpful, but not necessary, to fully understand the code in the class module and the wizard-generated code in the main form.

The sample application is not limited to creating the sample database described in this How-To. You can inspect or modify any Jet database using the project.

4.3 How do I...

Define the primary key and other indexes?

Problem

I know that a primary key is an important component in a proper relational database design and that indexes can significantly improve database performance. How do I choose fields to index and create the indexes for those fields?

Technique

Database indexes can be broadly grouped into two categories:

Many developers consider indexes--particularly indexes that act as constraints--to be part of the database schema or overall table design. In reality, however, indexes serve only to enforce the constraints that must be applied to the data. The constraints, or rules, form the database design. Indexes serve as a tool to implement those constraints. It is possible (although not recommended) to create tables that do not have primary keys or unique indexes and still have a fully functional relational design, but it is much more efficient to have the database engine enforce rules at that level.

Establishing indexes on tables is a two-step process. First you must determine what columns need to be indexed and the type of indexes the columns require, and then you must create the indexes using the properties and methods provided by the database engine.

Constraints

In How-To 4.2, you learned about primary keys and relationships between tables, including one-to-many, many-to-many, and one-to-one relationships. Although you can create a table without a primary key, this technique is not recommended. In most situations, it is also recommended that an arbitrary value, such as a number provided by the database engine, be used as the primary key. For those tables that act as the junction table of a many-to-many relationship between tables, the combination of the two foreign key columns typically acts as the primary key. In a one-to-one relationship, the foreign key column alone is the primary key. Only one primary key can be defined for a table, although you can define additional unique indexes.

A primary key imposes some constraints on the data in the columns included in the index:

Indexing for Performance

In addition to imposing constraints on your data, indexes can be added strictly to improve performance. The database engine can optimize SELECT queries if it has useful indexes available. Determining what constitutes a useful index can be more of an art than a science, but the following guidelines are appropriate in most situations:

To obtain the best performance in your own applications, you should experiment with various indexing strategies and use profiling techniques to determine which indexes provide the greatest advantage.


WHEN NOT TO INDEX

Don't think that you can index every column to gain optimum performance. Although indexes accelerate data retrieval, they slow inserts, updates, and deletes because the database engine not only has to update the tables but also must update the indexes.

Additionally, the database engine might not find all indexes useful, especially on small tables. If you have tables with very few rows (such as lookup tables of coded values, tables of United States states, and the like), it is likely that the database engine can perform a table scan (read every row in the table) faster than it can find a row using an index.

Finally, some situations can force a table scan, in which case all indexes are ignored.


Defining Indexes

Indexes are created by using the CreateIndex method of the TableDef object in a three-step process:

1. Call CreateIndex to create an Index object.

2. Create the fields in the index by using the CreateField method, and then use the Append method to add them to the Fields collection of the index.

3. Use the Append method of the Indexes collection to add the index to the TableDef object.


NOTE If you think this process looks remarkably similar to that of creating a table using DAO code, you're right--the processes are nearly identical.

Steps

Open and run HT403.vbp. You can create the indexes shown in Table 4.15 by choosing the Index | Add command and using the form shown in Figure 4.3.

Figure 4.3. The Create Index form.


NOTE The database file HT403.mdb contains the tables without the indexes. Also included is HT403A.mdb. This is the same file with all the indexes already created for you. You can examine this file with the Explorer form if you don't want to create all the indexes shown in Table 4.15.

Table 4.15. Indexes in HT403.mdb.

TABLE INDEX PROPERTIES FIELDS
Advisors apkAdvisors Primary AdvFacID
Courses apkCourses Primary CourseID
idxCourseIstrID
CourseInstrID
Faculty apkFaculty Primary FacID
idxFacLast FacLast
Instructors apkInstructors Primary InstrFacID
StudentCourses apkStudentCourses Primary SCStID
SCCourseID
idxSCStID SCStID
idxSCCourseID SCCourseID
Students apkStudents Primary StID
idxStAdvID StAdvID
idxStLast StLast
idxStState StState

The indexes shown in Table 4.15 are recommended based on the guidelines listed in this How-To. Each table has a primary key, all foreign key columns are indexed, and several additional columns are indexed as likely candidates for use as query selection or sort columns.


INDEX NAMES

The following naming convention was used to determine the index names shown in Table 4.15:


This project is an extended version of the project developed in How-To 4.2. Code was added to the main form and class module to launch frmCreateIndex, which handles the balance of the code to create indexes.

1. Create a new Standard EXE project, and save it as HT403.vbp. Add BMain.bas to the project. This module contains code used to open or create a new database and is based largely on the code developed in How-To 4.1.

2. Add frmMain to the project. This form is based on an Explorer-style form generated by the VB Application Wizard. The wizard form was modified for this project, as described in How-To 4.2. In addition to the modifications added for How-To 4.2, the menu controls in Table 4.16 were added to create the Index menu.

Table 4.16. The Index menu controls.

NAME CAPTION
mnuIndex &Index
mnuIndexAdd &Add
mnuIndexDelete &Delete

3. Three event procedures support the Index menu. The top-level menu mnuIndex_Click event enables or disables the delete command based on the currently selected object. The add command calls on the services of the CDBExplorer class via the mcdbExp module-level object variable to create a new index, and the delete command uses the same object to delete an index.




Private Sub mnuIndex_Click()
On Error GoTo ProcError
  If mcdbExp Is Nothing Then
    ` no database open
    mnuIndexAdd.Enabled = False
    mnuIndexDelete.Enabled = False
  Else
    ` enable add
    mnuIndexAdd.Enabled = True
    ` only enable delete if an Index is selected
    If mcdbExp.NodeType(tvTreeView.SelectedItem) = _
      "Index" Then
      mnuIndexDelete.Enabled = True
    Else
      mnuIndexDelete.Enabled = False
    End If
  End If
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuIndexAdd_Click()
On Error GoTo ProcError
  mcdbExp.AddIndex
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuIndexDelete_Click()
` Note: mnuIndex_Click already determined
` that an index is selected in the tree
On Error GoTo ProcError
  Dim strTableDefName As String
  Dim strIndexName As String
  ` get the index name
  strIndexName = tvTreeView.SelectedItem.Text
  ` get its parent table name
  strTableDefName = tvTreeView.SelectedItem.Parent.Parent.Text
  mcdbExp.DeleteIndex strTableDefName, strIndexName
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
4. Add the CDBExplorer.cls class module to the project. This is the same class as that developed in How-To 4.2, with methods added to support creating and deleting indexes. The class manages the population of the items in the tree and list views of the Explorer-style main form. Additional information about the class module can be found in CDBExplorer.html, which is included as a related file in the project on the CD. The two procedures added to this class to support the creation and deletion of indexes are the AddIndex and DeleteIndex methods. AddIndex uses the frmCreateIndex form to create the index, but it first attempts to determine whether the current item in the tree is located within the branch of a table. If so, it passes the table name to the index creation form, saving the user a step in data entry. The DeleteIndex method accepts a table name and an index name as parameters and constructs a call to the Delete method of the Indexes collection of the appropriate TableDef object. Both procedures also refresh the tree.

Public Sub AddIndex()
  Dim obj As Object
  Set obj = GetDAOObjectFromNode(mtvw.SelectedItem)
  Select Case TypeName(obj)
    Case "TableDef"
      ` initialize the form with a table name
      frmCreateIndex.Initialize mdb, obj.Name
    Case "Indexes"
      frmCreateIndex.Initialize _
        mdb, mtvw.SelectedItem.Parent.Text
    Case "Index"
      frmCreateIndex.Initialize mdb, _
        mtvw.SelectedItem.Parent.Parent.Text
    Case "Field"
      ` if it's a table field, get the table name
      ` the great-grandparent node tells the type
      If mtvw.SelectedItem.Parent.Parent.Parent.Text _
        = "TableDefs" Then
        ` get the name from the grandparent node
        frmCreateIndex.Initialize _
          mdb, _
          mtvw.SelectedItem.Parent.Parent.Text
      Else
        frmCreateIndex.Initialize mdb
      End If
    Case Else
      frmCreateIndex.Initialize mdb
  End Select
  frmCreateIndex.Show vbModal
  ` check cancel flag
  If Not frmCreateIndex.Cancelled Then
    ` expand the tabledef node
    ExpandNode _
      mtvw.Nodes(frmCreateIndex.TableDefName)
    ` now expand the index node for the tabledef
    ExpandNode _
      mtvw.Nodes(frmCreateIndex.TableDefName & "Indexes")
  End If
End Sub
Public Sub DeleteIndex( _
  strTableDefName As String, _
  strIndexName As String)
  ` delete the index from the indexes collection of the
  ` tabledef provided
  mdb.TableDefs(strTableDefName).Indexes.Delete strIndexName
  ` refresh the tree
  ExpandNode mtvw.Nodes(strTableDefName & "Indexes")
End Sub
5. Add a new form to the project, create the objects and properties shown in Table 4.17, and save the form as frmCreateIndex.frm.

Table 4.17. Objects and properties of frmCreateIndex.

OBJECT PROPERTY VALUE
Form Name frmCreateIndex
Caption Create Index
BorderStyle 3-Fixed Dialog
Label Name lblTableDefName
Caption &Table Name
ComboBox Name cboTableDefName
Style 2-Dropdown List
Label Name lblIndexName
Caption &Index Name
TextBox Name txtIndexName
Frame Name fraIndex
Caption Index
Draw the following controls within the fraIndex frame:
Label Name lblFieldName
Caption &Field Name
ComboBox Name cboFieldName
Style 2-Dropdown List
CommandButton Name cmdAddField
Caption &Add
Label Name lblFields
Caption Field &List
ListBox Name lstFields
CheckBox Name chkPrimary
Caption &Primary
CheckBox Name chkUnique
Caption &Unique
Draw the following two command buttons below the fraIndex frame at the lower-right corner of the form:
CommandButton Name cmd
Index 0
Caption OK
Default True
CommandButton Name cmd
Index 1
Caption Cancel
Cancel True


NOTE Figure 4.3, which appears at the beginning of this section, shows the visual layout of the completed form.
6. Add the following code to the declarations section of the form. Several module-level variables are created. The database object mdb is used to create the index. The mblnCancel flag is used to mark that the user cancelled the addition of the index. Several flag variables are used to control when the OK button should be enabled or disabled--each of the flags must be true before OK can be enabled and the index created. The mstrTableDefName variable stores the name of the table in which the index was created so that when control returns to the class module and the main form, the proper collection can be refreshed. Finally, the two constants are the indexes into the cmd CommandButton control array.

Option Explicit
` database object
Private mdb As Database
` cancel flag
Private mblnCancel As Boolean
` flags for controlling the OK button
Private mblnHasTableDefName As Boolean
Private mblnHasIndexName As Boolean
Private mblnHasFields As Boolean
` tabledefname for property get
Private mstrTableDefName As String
` command button array constants
Private Const cmdOK = 0
Private Const cmdCancel = 1
7. Add the Initialize method. This procedure is used when the form is loaded, but before it is shown, to set up module-level variables and populate controls on the form.

Public Sub Initialize( _
  db As DAO.Database, _
  Optional strTableDefName As String = "")
  ` initialize the form
  ` NOTE: must be called before the form is shown
  Set mdb = db
  ` populate the table combo
  GetTables
  ` set an initial table name if provided
  If strTableDefName <> "" Then
    cboTableDefName.Text = strTableDefName
    ` fill the field list
    GetFields (strTableDefName)
  End If
End Sub
8. Add the public TableDefName and Cancelled properties. These are used after the form is dismissed and control returns to the main form and class to determine which, if any, branch of the tree should be refreshed.

Public Property Get TableDefName() As String
  TableDefName = mstrTableDefName
End Property
Public Property Get Cancelled() As Boolean
  Cancelled = mblnCancel
End Property
9. The EnableOK and EnableIndex procedures check several flags and enable or disable the OK button and the index frame, based on the current status of the form.

Private Sub EnableOK()
  If mblnHasTableDefName _
    And mblnHasIndexName And mblnHasFields Then
    cmd(cmdOK).Enabled = True
  Else
    cmd(cmdOK).Enabled = False
  End If
End Sub
Private Sub EnableIndex()
  If mblnHasTableDefName And mblnHasIndexName Then
    fraIndex.Enabled = True
  Else
    fraIndex.Enabled = False
  End If
End Sub
10. Add the GetTables and GetFields procedures. These routines populate the table and field list combo boxes.

Private Sub GetTables()
` fill the table list combo
  Dim td As TableDef
  With cboTableDefName
    ` clear what (if anything) is there
    .Clear
    For Each td In mdb.TableDefs
      ` check for system table
      If (td.Attributes And dbSystemObject) = 0 Then
        ` not a system table, add it
        .AddItem td.Name
      End If
    Next  ` TableDef
  End With
End Sub
Private Sub GetFields(strTableDefName As String)
` fill the field list combo
  Dim fld As Field
  With cboFieldName
    ` clear it
    .Clear
    For Each fld In mdb.TableDefs(strTableDefName).Fields
      ` add it
      .AddItem fld.Name
    Next  ` Field
  End With
End Sub
11. Add the Form_Load event procedure. This routine performs some initial setup of the controls on the form.

Private Sub Form_Load()
` set up controls
  ` disabled until a name is set and
  ` at list one field is in the field list
  cmd(cmdOK).Enabled = False
  ` disabled until a field is chosen
  cmdAddField.Enabled = False
  ` disable the entire fraIndex frame
  ` until a table and index name are chosen
  fraIndex.Enabled = False
End Sub
12. The Click and Change event procedures for the table name, IndexName, FieldName, and CheckBox controls set module-level variables and enable or disable the index frame and OK button depending on the status of the data. Before the index frame is enabled, a table name and an index name must be provided. To create an index, at least one field must have been added.

Private Sub cboTableDefName_Click ()
` set up controls and status
  ` copy it to the module-level variable
  ` for later property get
  mstrTableDefName = cboTableDefName.Text
  ` text it and set flags
  If mstrTableDefName <> "" Then
    ` enable the Index frame
    mblnHasTableDefName = True
  Else
    mblnHasTableDefName = False
  End If
  EnableIndex
  EnableOK
End Sub
Private Sub txtIndexName_Change()
` set control and status flags
  If txtIndexName.Text <> "" Then
    mblnHasIndexName = True
  Else
    mblnHasIndexName = False
  End If
  EnableIndex
  EnableOK
End Sub
Private Sub cboFieldName_Click()
` enable/disable add field button
  If cboFieldName.Text <> "" Then
    ` enable the add field button
    cmdAddField.Enabled = True
  Else
    cmdAddField.Enabled = False
  End If
End Sub
Private Sub chkPrimary_Click()
` if it's primary, it must be unique
` set control status to indicate the
` user doesn't need to deal with the
` unique check box if primary is set
  If chkPrimary Then
    chkUnique = 1
    chkUnique.Enabled = False
  Else
    chkUnique.Enabled = True
  End If
End Sub
13. Create the Click event procedure for the cmdAddField button. This code adds the current field in the combo box to the list, removes it from the combo box, and returns the focus to the combo box.

Private Sub cmdAddField_Click()
` add to list and remove from combo
  lstFields.AddItem cboFieldName.Text
  cboFieldName.RemoveItem cboFieldName.ListIndex
  ` set status flag
  mblnHasFields = True
  EnableOK
  ` return to field name combo
  cboFieldName.SetFocus
End Sub
14. Add the cmd_Click event procedure. This procedure creates the index if the OK button is clicked, or it unloads the form (setting the Cancelled flag) if the Cancel button is clicked.

Private Sub cmd_Click(Index As Integer)
` add the index or unload the form
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  Select Case Index
    Case cmdOK
      ` add the index
      CreateIndex
      ` set cancel flag
      mblnCancel = False
      Unload Me
    Case cmdCancel
      ` set cancel flag and unload
      mblnCancel = True
      Unload Me
  End Select
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
15. Add the CreateIndex procedure. This code creates the index object by reading the data entered on the form. The index is created by first calling the CreateIndex method, then looping through the fields in the list box, calling CreateField and Append for each. Finally, the Append method adds the index to the table.

Private Sub CreateIndex()
` create the index
` called only from cmd(cmdOK) click
  Dim td As TableDef
  Dim idx As Index
  Dim fld As Field
  Dim intListIndex As Integer
  ` get a reference to the tabledef and
  ` create the index
  Set td = mdb.TableDefs(cboTableDefName.Text)
  Set idx = td.CreateIndex(txtIndexName.Text)
  ` add the fields
  For intListIndex = 0 To lstFields.ListCount - 1
    lstFields.ListIndex = intListIndex
    Set fld = idx.CreateField(lstFields.Text)
    idx.Fields.Append fld
    Set fld = Nothing
  Next  ` item in list
  ` set primary or unique flags
  If chkPrimary = 1 Then
    idx.Primary = True
  ElseIf chkUnique = 1 Then
    idx.Unique = True
  End If
  ` append the index
  td.Indexes.Append idx
End Sub

HOW IT WORKS

Although additional code was added to the main Explorer form to coordinate the user interface, the CreateIndex procedure in frmCreateIndex does all the work of creating the index objects in this example. The procedure extracts the data provided on the form to create an index, adds the fields from the list box, and then appends the index to the Indexes collection of the selected table.

The only information that must be provided to the form is supplied by the Initialize procedure as the db parameter. The optional strTableDefName parameter is a convenience added for the benefit of the users (so that they don't need to select the table name again if they have already chosen one on the Explorer form). Because the interaction between the forms takes place through a public interface, this form could be plugged into any database management application.

Comments

If you worked through this How-To and How-To 4.2, you probably discovered that the procedures for creating an index using DAO code are nearly identical to those for creating a table. As you will see in the next How-To, the procedure for creating a relation is also very similar.


ANOTHER WAY TO CREATE AN INDEX

You can use SQL statements rather than DAO code to create indexes for your tables. Constraints can be created using the CREATE TABLE statement or the ALTER TABLE...ADD CONSTRAINT statement. Indexes can also be created using the CREATE INDEX statement. SQL statements are simple to use and require only a single line of code to execute, but they do not expose all the available properties of an index. Chapter 2, "Accessing a Database with Data Access Objects," provides the details of using SQL statements to create and manage database objects.


4.How do I...

Define relations between tables?

Problem

I know that if I define relations for my database, the Jet engine will enforce referential integrity. How do I define relations with Visual Basic?

Technique

Like indexes, defined relationships are a tool you can use to enforce rules and improve application performance. How-To 4.2 described the different types of relationships between tables: one-to-one, one-to-many, and many-to-many. Building the database schema with related tables is only the first step. You also need to enforce those relationships. The best way to do that is to let the database engine do it for you by creating Relation objects.

Defining a Relation enforces three rules to maintain referential integrity between tables:

Creating the Relation

Creating a Relation object with DAO code is similar to creating a table or index. The creation is carried out in four steps:

1. Use the CreateRelation method to obtain a reference to a Relation object.

2. Assign the Table and ForeignTable properties.

3. Create and add each of the Field objects to the Fields collection of the Relation object. For each field, you must set the Name and ForeignName properties.

4. Add the Relation to the Relations collection of the database by using the Append method.

In addition to creating the relationship and adding the fields, you can specify some additional properties that affect how the database engine treats the relationship:


BE CAREFUL WITH CASCADING UPDATES AND DELETES

The concept of specifying cascading updates and deletes seems powerful and convenient at first glance, but it can be dangerous if not used with caution. Consider the following scenario:

You have a lookup table of United States states that includes the state name and two-letter postal code. A unique index is defined on the postal code so that it can be used as the one side of a relationship. This table is then used to enforce that any value entered for a state as part of a set of address columns in another table is valid. This setup is good so far--the database engine will now validate any state postal code entered in the database. If, however, you created this relationship with cascading updates and deletes, you could inadvertently change every address in one state to another state with a single update, or delete every address in a state with a single delete. If you were to run the query DELETE FROM States;, you would delete every row in your database that has a state column!

This is a somewhat contrived and extreme example. But the point is that by using cascading updates and deletes, you hand off work to the database engine, and you might forget later that the database engine is doing the work for you. An alternative to this approach is to define the relationship without specifying cascading updates or deletes. When you try to perform an operation that violates referential integrity constraints, a trappable error will be raised. You can then examine that error and decide whether to cancel the change or manually perform the cascade by running additional update or delete queries. See Chapter 3, "Creating Queries with SQL," for additional information on building and executing update or delete queries.


In addition to specifying cascading updates and deletes, you can also indicate that the relationship is one-to-one or one-to-many. Don't let yourself be confused by the difference. One-to-one relationships are really just a special case of one-to-many relationships. Instead of allowing multiple rows on the many side, the database engine allows only one. Many-to-many relationships are defined using two one-to-many relationships.

STEPS

Open and run project HT404.vbp. Open the database HT404.mdb. You can use the Relation | Add menu command to create the relationships shown in Table 4.18. Figure 4.4 shows the form used to create a relationship.

Figure 4.4. The Create Relation form.


NOTE File HT404A.mdb is identical to HT404.mdb except that all the relations have already been created for you. If you do not want to create all the relationships shown in the table, you can open HT404A.mdb and inspect the objects using the Explorer form.

Table 4.18. Relations in HT404.mdb.

NAME TABLE FOREIGN TABLE FIELD NAME FOREIGN NAME TYPE CASCADE
fkAdvFacID Faculty Advisors FacID AdvFacID 1-1 Deletes
fkInstrFacID Faculty Instructors FacID InstrFacID 1-1 Deletes
fkCourse- Instructors Courses InstrFacID CourseInstrID 1-Many N/A
InstrID
fkSCStID Students StudentCourses StID SCStID 1-Many N/A
fkSCCourseID Courses StudentCourses CourseID SCCourseID 1-Many N/A
fkStAdvID Advisors Students AdvFacID StAdvID 1-Many N/A

1. Create a new Standard EXE project and save it as HT404.vbp. Add BMain.bas to the project. This module contains code derived from the example in How-To 4.1 used to open or create a database.

2. Add frmMain.frm to the project. This is the same form used for How-To 4.3. Add the menu controls shown in Table 4.19 for the Relation menu.

Table 4.19. The Relation menu controls.

NAME CAPTION
mnuRelation &Relation
mnuRelationAdd &Add
mnuRelationDelete &Delete

3. Three event procedures control the Relation menu. The top-level mnuRelation_Click event procedure determines which of the other menu controls should be enabled based on the currently selected object in the TreeView control on the form. The Add command uses the mcdbExp object to show the form used to create a relationship and update the TreeView control. The Delete command calls on the DeleteRelation method of the mcdbExp object to delete the currently selected relationship.

Private Sub mnuRelation_Click()
On Error GoTo ProcError
  If mcdbExp Is Nothing Then
    ` no database open
    mnuRelationAdd.Enabled = False
    mnuRelationDelete.Enabled = False
  Else
    ` enable add
    mnuRelationAdd.Enabled = True
    ` only enable delete if an Index is selected
    If mcdbExp.NodeType(tvTreeView.SelectedItem) = _
      "Relation" Then
      mnuRelationDelete.Enabled = True
    Else
      mnuRelationDelete.Enabled = False
    End If
  End If
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuRelationAdd_Click()
On Error GoTo ProcError
  mcdbExp.AddRelation
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuRelationDelete_Click()
On Error GoTo ProcError
  Dim strRelationName As String
  ` get the name
  strRelationName = tvTreeView.SelectedItem.Text
  mcdbExp.DeleteRelation strRelationName
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
4. Add the class module CDBExplorer.cls to the project. This is the same class module used in How-To 4.3, but with code added to support creating and deleting relationships in the database. The AddRelation method loads, initializes, and shows frmCreateRelation and then refreshes the node in the Explorer form tree for relationships. The DeleteRelation method takes the name of a Relation object as a parameter and deletes the Relation object from the Relations collection of the database.

Public Sub AddRelation()
` load the form to create a relation
  Load frmCreateRelation
  ` pass it the database reference
  Set frmCreateRelation.Database = mdb
  frmCreateRelation.Show vbModal
  ` refresh the tabledefs node
  ExpandNode mtvw.Nodes("Relations")
End Sub
Public Sub DeleteRelation(strRelationName As String)
` delete a relation
  ` delete it
  mdb.Relations.Delete strRelationName
  ` refresh the relations node
  ExpandNode mtvw.Nodes("Relations")
End Sub
5. Add a new form to the project, create the objects and properties shown in Table 4.20, and save the form as frmCreateRelation.frm.

Table 4.20. Objects and properties of frmCreateRelation.

OBJECT PROPERTY VALUE
Form Name frmCreateRelation
Caption Create Relation
Border Style 3-Fixed Dialog
Label Name lblTableDefName
Caption Table
ComboBox Name cboTableDefName
Style 2-Dropdown List
Label Name lblForeignTableDefName
Caption Foreign Table
ComboBox Name cboForeignTableDefName
Style 2-Dropdown List
Frame Name fraRelation
Caption Relation
Draw the following controls within the fraRelation frame:
Label Name lblRelationName
Caption Relation Name
TextBox Name txtRelationName
Label Name lblFieldName
Caption Field Name
ComboBox Name cboFieldName
Style 2-Dropdown List
Label Name lblForeignName
Caption Foreign Name
ComboBox Name cboForeignName
Style 2-Dropdown List
Label Name lblOneTo
Caption One To:
Line Name Line1
OptionButton Name optOneTo
Caption One
Index 0
OptionButton Name optOneTo
Caption Many
Index 1
Label Name lblReferentialIntegrity
Caption Referential Integrity
Line Name Line2
CheckBox Name chkRef
Caption Cascade Updates
Index 0
CheckBox Name chkRef
Caption Cascade Deletes
Index 1
Draw the following two command buttons at the bottom right of the form below the fraRelation frame:
CommandButton Name cmd
Caption OK
Default True
Index 0
CommandButton Name cmd
Caption Cancel
Cancel True
Index 1

Figure 4.4 shows the visual design of the form at runtime.

6. Add the following code to the declarations section of the form. Module-level variables are defined to store the database object, the table and foreign table names, and the field name and foreign name properties.

Three pairs of constants are also defined as indexes into the three control arrays on the form.

Option Explicit
` database
Private mdb As Database
` table
Private mstrTableDefName As String
` foreign table
Private mstrForeignTableDefName As String
` relation name
Private mstrRelationName As String
` field name
Private mstrFieldName As String
` foreign name
Private mstrForeignName As String
` control array constants
Private Const optOneToOne = 0
Private Const optOneToMany = 1
Private Const chkRefCascadeUpdates = 0
Private Const chkRefCascadeDeletes = 1
Private Const cmdOK = 0
Private Const cmdCancel = 1
7. Add the Database property procedure. This property is used to enable the CDBExplorer class to pass a database object to the form. After the database has been provided, the table and foreign table combo boxes are populated with lists of table names.

Public Property Set Database(db As DAO.Database)
` set database object and set up form
  ` assign the database object
  Set mdb = db
  ` populate the table combo boxes
  GetTables cboTableDefName
  GetTables cboForeignTableDefName
End Property
8. Add the EnableOK and EnableRelation procedures. These procedures examine the current state of the data on the form to determine whether the fraRelation frame and the OK button should be enabled or disabled.

Private Sub EnableOK()
` to create a relation, you need the following
` a table name
` a foreign table name
` a relation name
` a field name
` a foreign name for the field
` additionally, CreateRelation will fail if the
` field data types do not match correctly
  If mstrTableDefName = "" Or _
      mstrForeignTableDefName = "" Or _
      mstrRelationName = "" Or _
      mstrFieldName = "" Or _
      mstrForeignName = "" Then
    cmd(cmdOK).Enabled = False
  Else
    cmd(cmdOK).Enabled = True
  End If
End Sub
Private Sub EnableRelation()
` enable/disable the relation frame
  If _
      mstrTableDefName = "" Or _
      mstrForeignTableDefName = "" _
      Then
    fraRelation.Enabled = False
  Else
    fraRelation.Enabled = True
  End If
End Sub
9. Add the GetTables and GetFields procedures. These procedures populate a combo box with a list of tables or fields by examining the TableDefs collection of the database or the Fields collection of a table.

Private Sub GetTables(cbo As ComboBox)
` fill the table list combo
  Dim td As TableDef
  With cbo
    ` clear what (if anything) is there
    .Clear
    For Each td In mdb.TableDefs
      ` check for system table
      If (td.Attributes And dbSystemObject) = 0 Then
        ` not a system table, add it
        .AddItem td.Name
      End If
    Next  ` TableDef
  End With
End Sub
Private Sub GetFields(cbo As ComboBox, strTableDefName As String)
` fill the field list combo
  Dim fld As Field
  With cbo
    ` clear it
    .Clear
    For Each fld In mdb.TableDefs(strTableDefName).Fields
      ` add it
      .AddItem fld.Name
    Next  ` Field
  End With
End Sub
10. Add the Form_Load event procedure. The relation frame and OK button are disabled by this procedure.

Private Sub Form_Load()
On Error GoTo ProcError
  ` disable the relations frame
  fraRelation.Enabled = False
  ` disable the OK button
  cmd(cmdOK).Enabled = False
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
11. Add the Click event procedures for the cboTableDefName and cboForeignTableDefName ComboBox controls. These procedures store the values of the combo boxes in the module-level variables and call the GetFields procedure to populate the field lists. They then call both EnableOK and EnableRelation to enable or disable both the relationship frame container and the OK button.

Private Sub cboTableDefName_Click()
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  mstrTableDefName = cboTableDefName.Text
  If mstrTableDefName <> "" Then
    GetFields cboFieldName, mstrTableDefName
  End If
  EnableOK
  EnableRelation
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub cboForeignTableDefName_Click()
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  mstrForeignTableDefName = cboForeignTableDefName.Text
  If mstrForeignTableDefName <> "" Then
    GetFields cboForeignName, mstrForeignTableDefName
  End If
  EnableOK
  EnableRelation
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
12. Add the Click events for the cboFieldName and cboForeignName controls. The current values in the combo boxes are passed to the module-level variables, and the EnableOK procedure is called to enable or disable the OK button.

Private Sub cboFieldName_Click()
On Error GoTo ProcError
  mstrFieldName = cboFieldName.Text
  EnableOK
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub cboForeignName_Click()
On Error GoTo ProcError
  mstrForeignName = cboForeignName.Text
  EnableOK
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
13. Add the txtRelation_Change event. This procedure passes the contents of the text box to the module-level variable and calls the EnableOK procedure to enable or disable the OK button.

Private Sub txtRelationName_Change()
On Error GoTo ProcError
  mstrRelationName = txtRelationName
  EnableOK
ProcExit:
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
14. Add the cmd_Click procedure. This procedure either calls the CreateRelation procedure and unloads the form, or it simply unloads the form.

Private Sub cmd_Click(Index As Integer)
` create the relation or unload
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  Select Case Index
    Case cmdOK
      ` create relation and unload
      CreateRelation
      Unload Me
    Case cmdCancel
      ` just unload
      Unload Me
  End Select
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
15. Add the CreateRelation procedure. This procedure uses the steps described earlier in this How-To to create the Relation object based on the values saved in the module-level variables. The procedure calls CreateRelation, creates and appends a Field object to the Fields collection, and finally uses the Append method to add the Relation to the Relations collection.

Private Sub CreateRelation()
` create the relation
` called only from cmd(cmdOK) click event
  Dim rel As Relation
  Dim fld As Field
  Dim lngAttributes As Long
  ` set up attributes
  If optOneTo(optOneToOne) Then
    lngAttributes = dbRelationUnique
  End If
  If chkRef(chkRefCascadeUpdates) Then
    lngAttributes = lngAttributes Or dbRelationUpdateCascade
  End If
  If chkRef(chkRefCascadeDeletes) Then
    lngAttributes = lngAttributes Or dbRelationDeleteCascade
  End If
  ` create the relation
  Set rel = mdb.CreateRelation( _
      mstrRelationName, _
      mstrTableDefName, _
      mstrForeignTableDefName, _
      lngAttributes)
  Set fld = rel.CreateField(mstrFieldName)
  ` set the foreign name
  fld.ForeignName = mstrForeignName
  ` append the field to the relation
  rel.Fields.Append fld
  ` append the relation to the database
  mdb.Relations.Append rel
End Sub

How It Works

If you've worked through the previous examples in this chapter, the method and the code used to create relationships will look quite familiar. Again, a single procedure--in this case, the CreateRelation procedure in frmCreateRelation--does all the real work of creating the relationship. Based on values entered by the user, the procedure creates the Relation object. It then adds the desired field and assigns the ForeignName property, and finally it appends the field and the relation to their respective collections. The rest of the code in the form coordinates the user interface.


NOTE This example does not implement the capability to create relationships with multiple fields. If you need to create such a relationship, just repeat the code that creates and appends the field for each field in the relationship.

Comments

The Jet database engine also enables you to use SQL statements to create relationships between tables by using a CONSTRAINT clause in a CREATE TABLE or ALTER TABLE statement. See Chapter 3, "Creating Queries with SQL," for details on using SQL statements to create relationships with SQL statements.

4.5 How do I...

Use the Jet database engine to enforce business rules?

Problem

I need to make sure that certain rules are followed when data is entered into my database. How do I get the database engine to enforce these rules for me?

Technique

Various rules can be applied against tables and columns in a database:

The collective term for these types of restrictions on data is business rules. You can enforce rules in your database in two ways:

Although in certain situations the rules will be too complex to be entered using the available properties for TableDef and Field objects, you should allow the database engine to enforce as many of your rules as it is capable of enforcing.

If you have worked through the examples in How-To 4.2 through 4.4, you have already been enforcing some simple rules in your database:

In addition to these constraints on data, you can specify an additional property for tables and three additional properties for columns that further restrict the data that can be entered:

Required and AllowZeroLength are both Boolean properties. If the Required property is set to True, Null values will not be allowed. If the AllowZeroLength property is set to True, the column will allow a zero-length string as a valid value.

The ValidationRule property is a string and can be any valid Visual Basic expression. It cannot, however, contain a reference to a user-defined function, SQL aggregate functions, a query, or, in the case of a table, columns in another table.

The ValidationText property can be any string expression and is provided as the description of the trappable error that results when the rule is violated.

Steps

Open and run project HT405 .vbp. The form shown in Figure 4.5 appears. Choose File | Open and open database HT405.mdb. This is the school database developed and refined in How-To 4.2 through How-To 4.4. Click the Add Rules button to apply the rules shown in Table 4.21 to the database.

Figure 4.5. The Create Rules form.

Table 4.21. Business rules for HT405.mdb.

TABLE FIELD PROPERTY VALUE
Advisors AdvGradeLevel Required True
Advisors ValidationRule IN (`Freshman', `Sophomore', `Junior', `Senior')
ValidationText Grade level must be Freshman, Sophomore, Junior, or Senior
Courses CourseDesc Required True
Faculty FacFirst Required True
FacLast Required True
Students StFirst Required True
Students StLast Required True

In addition to the field-level rules in Table 4.21, the following rule applies to the Students table:

ValidationRule
  IIf(
    (
      (Not IsNull([StAddress])) Or
      (Not IsNull([StCity])) Or
      (Not IsNull([StState])) Or
      (Not IsNull([StZIP]))
    ),
    (
      IIf(
        (
          (Not IsNull([StAddress])) And
          (Not IsNull([StCity])) And
          (Not IsNull([StState])) And
          (Not IsNull([StZIP])
         )
       ), True, False)
    ), True)
ValidationText
  If provided, the address must be complete. 

This rather cumbersome-looking expression enforces the rule that if any of the address columns (StAddress, StCity, StState, StZIP) contain data, they all must contain data. Because Jet restricts the ValidationRule property to a single expression, the nested IIf statements must be used. The outer IIf returns True if any of the columns contains data; the inner IIf returns True only if they all contain data.


A MISLEADING STATEMENT IN THE HELP FILE

The following statement appears in the Visual Basic help file topic for the ValidationRule property:

For an object not yet appended to the Fields collection, this 
property is read/write.

This would seem to imply that after the field has been created, the property can no longer be assigned. In fact, the ValidationRule and ValidationText properties for both tables and fields can be assigned after the objects have been created, and the Required property can be assigned after a field has been created--all as demonstrated in the sample application.


1. Create a new Standard EXE project and name it HT405.vbp.

2. Change the name of Form1 to frmMain, and create the objects and properties shown in Table 4.22.

Table 4.22. Objects and properties for frmMain.

OBJECT PROPERTY VALUE
Form BorderStyle 3-Fixed Dialog
Caption Create Rules
CommonDialog Name dlg
CommandButton Name cmdAddRules
Caption Add Rules
Menu Name mnuFile
Caption &File
Menu Name mnuFileOpen
Caption &Open
Shortcut Ctrl-O
Menu Name mnuFileBar
Caption -
Menu Name mnuFileExit
Caption E&xit
Shortcut Ctrl-Q

3. Add the following code to the declarations section of the form:

Option Explicit
Private mdb As Database
4. Add the cmdAddRules_Click event procedure. This procedure calls the AddRules subroutine described here.

Private Sub cmdAddRules_Click()
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  AddRules
  MsgBox "Rules Added"
  cmdAddRules.Enabled = False
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
5. Add the mnuFileOpen_Click procedure. This procedure calls the GetOpenDBName function show next, opens the database using the filename returned, and enables the cmdAddRules button.

Private Sub mnuFileOpen_Click()
On Error GoTo ProcError
  Dim strDBName As String
  Screen.MousePointer = vbHourglass
  ` use the common dialog to get the db name
  strDBName = GetOpenDBName(dlg)
  If Len(strDBName) Then
    Set mdb = DBEngine(0).OpenDatabase(strDBName)
    cmdAddRules.Enabled = True
  End If
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
6. Create the mnuFileExit_Click event. This procedure unloads the form, ending the application.

Private Sub mnuFileExit_Click()
On Error GoTo ProcError
  Screen.MousePointer = vbHourglass
  ` close the database and unload the form
  mdb.Close
  Unload Me
ProcExit:
  Screen.MousePointer = vbDefault
  Exit Sub
ProcError:
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
7. Create the GetOpenDBName function. This function sets up the Common Dialog control and returns the filename selected by the user as its return value.

Private Function GetOpenDBName(dlg As CommonDialog) As String
` Get the desired name using the common dialog
On Error GoTo ProcError
  Dim strFileName As String
  ` set up the file save dialog file types
  dlg.InitDir = App.Path
  dlg.DefaultExt = "mdb"
  dlg.DialogTitle = "Open Database"
  dlg.Filter = "VB Databases (*.mdb)|*.mdb"
  dlg.FilterIndex = 1
  ` set up flags
  dlg.Flags = _
    cdlOFNHideReadOnly Or _
    cdlOFNFileMustExist Or _
    cdlOFNPathMustExist
  ` setting CancelError means the control will
  ` raise an error if the user clicks Cancel
  dlg.CancelError = True
  ` show the SaveAs dialog
  dlg.ShowOpen
  ` get the selected name
  strFileName = dlg.filename
ProcExit:
  GetOpenDBName = strFileName
  Exit Function
ProcError:
  strFileName = ""
  Resume ProcExit
End Function
8. Create the AddRules routine. This routine assigns the Required, ValidationRule, and ValidationText properties described previously. Each of the values is directly assigned to the property. The various With...End With blocks add some efficiency by eliminating extra object references.

Private Sub AddRules()
  Dim td As TableDef
  Dim fld As Field
  ` Advisors table
  ` AdvGradeLevel field
  Set fld = mdb.TableDefs("Advisors").Fields("AdvGradeLevel")
  With fld
    ` require entry
    .Required = True
    ` require a value in a list
    .ValidationRule = _
        "IN (`Freshman', `Sophomore', `Junior', `Senior')"
.ValidationText = _
        "Grade level must be Freshman, " & _
        "Sophomore, Junior or Senior"
End With
  Set fld = Nothing
  ` Courses table
  ` CourseDesc field
  mdb.TableDefs("Courses").Fields("CourseDesc").Required = True
  ` Faculty table
  Set td = mdb.TableDefs("Faculty")
  With td
    ` FacFirst required
    .Fields("FacFirst").Required = True
    ` FacLast required
    .Fields("FacLast").Required = True
  End With
  Set td = Nothing
  ` Students table
  Set td = mdb.TableDefs("Students")
  With td
    ` first and last names are required
    .Fields("StFirst").Required = True
    .Fields("StLast").Required = True
    ` table rule - if any part of the
    ` address is provided, all of it
    ` must be provided
    ` the outer IIf evaluates if any field is not null
    ` the inner IIf evaluates if all fields are not null
    .ValidationRule = _
      "IIf(" & _
        "(" & _
          "(Not IsNull([StAddress])) Or " & _
          "(Not IsNull([StCity])) Or " & _
          "(Not IsNull([StState])) Or " & _
          "(Not IsNull([StZIP])) " & _
        "), " & _
        "(IIf(" & _
          "(" & _
            "(Not IsNull([StAddress])) And " & _
            "(Not IsNull([StCity])) And " & _
            "(Not IsNull([StState])) And " & _
            "(Not IsNull([StZIP])) " & _
          "), " & _
          "True, False)" & _
        "), " & _
        "True)"
    .ValidationText = _
        "If provided, the address must be complete."
  End With
  Set td = Nothing
End Sub

How It Works

This How-To provides the final refinement to the database that has been developed throughout the chapter by adding some basic business-rule enforcement at the level of the database engine. The rules are established by obtaining references to the appropriate table and field objects and by setting the ValidationRule, ValidationText, and Required properties.

As you can see in the examples, it can be difficult to implement even relatively simple rules due to the limitations of these properties. Thus, you might need to supplement the properties provided by the database engine with additional validation code. Chapter 1, "Accessing a Database with Bound Controls," and Chapter 2, "Accessing a Database with Data Access Objects," provide additional information on using Visual Basic code to enforce rules on the data in your database.

Comments

Unless you specify otherwise, field validation rules are applied when the record is updated. If you want the rule to be applied as soon as the entry is applied to the field, set the ValidateOnSet property of the Field object to True.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.