
Creating a database with Data Access Object (DAO) code can be done with a single method. This How-To shows you how.
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.
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.
This How-To shows you how to use Visual Basic to create the relations for your database.
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.
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:
| 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 |
| 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.
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
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
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
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 STATEMENTSome 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:
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 COLUMNSThe 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
| 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 PANACEADon'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.)
| 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.
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.
| 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.
| 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.
| 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.
| 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.
| 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.
| 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.
| 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:
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.
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.
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
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
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.
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
Option Explicit ` database object Private mdb As Database ` command button array index constants Private Const cmdOK = 0 Private Const cmdCancel = 1
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
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
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
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
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
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
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.
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 INDEXDon'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:
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 | 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 NAMESThe 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.
Table 4.16. The Index menu controls.
NAME CAPTION mnuIndex &Index mnuIndexAdd &Add mnuIndexDelete &Delete
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
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
| 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.
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
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
Public Property Get TableDefName() As String TableDefName = mstrTableDefName End Property Public Property Get Cancelled() As Boolean Cancelled = mblnCancel End Property
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
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
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
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
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
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
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
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 INDEXYou 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.
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:
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 DELETESThe 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.
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.
| 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 |
Table 4.19. The Relation menu controls.
NAME CAPTION mnuRelation &Relation mnuRelationAdd &Add mnuRelationDelete &Delete
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
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
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.
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
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
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
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
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
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
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
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
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
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.
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 | 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 FILEThe 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.
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
Option Explicit Private mdb As Database
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
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
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
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
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.
© Copyright, Macmillan Computer Publishing. All rights reserved.