Microsoft Jet Database Engine Programmer's Guide - Chapter 3
(blank title page)
Data Definition and Integrity
This chapter discusses how to create, delete, and maintain tables, indexes, queries, relationships, and referential integrity rules with the Microsoft Jet database engine.
As discussed in Chapter 2, "Introducing Data Access Objects," Microsoft Jet tables, queries, and other objects are exposed to programming languages through a hierarchical system of objects and collections of objects referred to as Data Access Objects (DAO). For example, a Database object contains a collection of TableDef objects, which further contain collections of Field objects, which in turn contain collections of Property objects, and so on. The DAO object model maps those objects onto a programming interface you can control by using Microsoft Visual Basic, Visual Basic for Applications, Microsoft Visual C++, or Microsoft Visual J++.
You can create tables and define relationships between tables entirely through the native DAO interface. Using DAO gives you a great deal of flexibility and control over how your database objects are created. With DAO, you write code to create a new object, assign values to that object's properties to specify a data type, length, validation rule, and so on, and then append that object to a collection within the database's object hierarchy.
Many people are familiar with the industry-standard Structured Query Language (SQL) and its data definition language (DDL) subset. Microsoft Jet supports an SQL dialect that is compliant with the SQL ANSI-89 standard, with a few enhancements. Many of the features of Microsoft Jet that can be manipulated through DAO can also be controlled with SQL DDL statements. This chapter shows both methods and explains the differences.
Using the Code Examples in This Chapter
You can use the code examples in this chapter to help you understand the concepts discussed, or you can modify them and use them in your own applications.
The code examples are located in the JetBook\Samples subfolder on the companion CD-ROM. The code examples for Microsoft Access 97 are in JetSamples.mdb, and the corresponding code examples for Microsoft Visual Basic version 5.0 and other applications that support Visual Basic for Applications are referenced in JetSamples.vbp. Both JetSamples.mdb and JetSamples.vbp use tables and queries in NorthwindTables.mdb, also located in the JetBook\Samples subfolder.
To use the code examples, copy the sample files to your hard disk. Be sure to copy NorthwindTables.mdb as well so that you can use the code examples to work with data.
See Also For more information about copying and using the code examples from the companion CD-ROM, see "Using the Companion CD-ROM" in the Preface.
Creating a Database
As a developer, you may be familiar with creating a new Microsoft Jet database file either interactively through the Microsoft Access user interface, or through the Visual Data Manager utility (Visdata.exe) that ships with Visual Basic. You can also create a new Microsoft Jet database file programmatically by using DAO.
For example, your application may need to build a new database when it's first installed, or perhaps your application needs to create a database to archive or export data. The following code fragment creates a new database by using Visual Basic, where strNewDbName is the path and name for the new database:
Dim dbs As Database Set dbs = CreateDatabase(strNewDbName, dbLangGeneral, dbVersion30)
Note The previous example is taken from the CreateNewDatabase function, which is available on the companion CD-ROM.
This example uses the CreateDatabase method to build a new, empty database. The arguments to CreateDatabase specify the path and name for the new database, the language used for sorting and string comparison, and the version of the new database.
The version argument is specified as dbVersion30 when a Microsoft Jet version 3.5 database is created because it uses the same file and table format as Microsoft Jet version 3.0. However, databases created with the Microsoft Access 97 user interface can't be opened with Microsoft Access 95. This is because a Microsoft Access 97 database's Visual Basic for Applications project (the set of all code modules in a database, which includes modules associated with forms and reports, as well as standard and class modules) can't run in the version of Visual Basic for Applications used in Microsoft Access 95. Additionally, some database objects created with Microsoft Access 97 may use application-defined properties and have features that are not supported in earlier versions of Microsoft Access.
If you use the CreateDatabase method to create a database specified as dbVersion30 and use only DAO methods to create tables, relationships, and queries, both Microsoft Access 95 and 97 treat the database as a native database the first time it's opened. However, both versions of Microsoft Access add an application-defined property named AccessVersion to the Properties collection of a database the first time it's opened. Microsoft Access 97 also adds a system table named MSysModules2 to contain the database's Visual Basic for Applications project, which is treated as an invalid database object by Microsoft Access 95. After the first time a database is opened in either version, it's no longer treated as a native database in the other version.
For a Microsoft Access version 2.0 database, the AccessVersion property returns "02.00." For a Microsoft Access 95 database, the AccessVersion property returns a string that always begins with "06" followed by a period and two digits. For a Microsoft Access 97 database, the AccessVersion property always begins with "07" followed by a period and two digits. In both Microsoft Access 95 and 97, the final two digits returned by the AccessVersion property may vary depending on the dynamic-link library (DLL) used to create a database's Visual Basic for Applications project. For this reason, if you want to determine the version that produced or is using a database, your code should ignore the final two digits. The following function returns the version of Microsoft Access used to create or open a database by examining only the first two digits returned for the AccessVersion property. This function takes a String argument that specifies the path to the database.
Function FindVersion(strDbPath As String) As String Dim dbs As Database Dim strVersion As String Const conPropertyNotFound As Integer = 3270 On Error GoTo Err_FindVersion ' Open the database and return a reference to it. Set dbs = OpenDatabase(strDbPath) ' Check the value of the AccessVersion property. strVersion = dbs.Properties("AccessVersion") ' Return the two leftmost digits of the value of the AccessVersion property. strVersion = Left(strVersion, 2) ' Based on the value of the AccessVersion property, return a string indicating ' the version of Microsoft Access used to create or open the database. Select Case strVersion Case "02" FindVersion = "2.0" Case "06" FindVersion = "7.0" Case "07" FindVersion = "8.0" End Select Exit_FindVersion: On Error Resume Next dbs.Close Set dbs = Nothing Exit Function Err_FindVersion: If Err.Number = conPropertyNotFound Then MsgBox "This database hasn't previously been opened with Microsoft Access." Else MsgBox "Error: " & Err & vbCrLf & Err.Description End If Resume Exit_FindVersion End Function
If you create a dbVersion30 database by using DAO methods and then add forms and reports by using the CreateForm or CreateReport functions (these functions are available only in code run from Microsoft Access), a Visual Basic for Applications project is added that corresponds to the version of Microsoft Access used to run the code. You can also add modules and insert code in a database by using Visual Basic code in Microsoft Access 97. Doing so also creates a Visual Basic for Applications project that is specific to Microsoft Access 97.
The Database Owner
If you don't create and reference a new Workspace object when you are using the CreateDatabase method, the default Workspace object is used. The effect of using the default Workspace object depends on which host application is running the Visual Basic code. For example, in Microsoft Access, the default Workspace object is identical to the one used to log on to the application database that is executing the code. The new database is owned by the user account that is used to log on to Microsoft Access before the CreateDatabase method is used. If the Logon dialog box hasn't been activated in Microsoft Access, the database owner is the default Admin user account.
See Also For information about using Microsoft Jet security, see Chapter 10, "Managing Security."
In Visual Basic version 5.0, the UserName property of the default Workspace object is set to Admin and the Name property is set to #DefaultWorkspace#. Because the user doesn't log on to the database independently of the code executed by a Visual Basic application, there is no user Workspace object for Visual Basic to inherit as there is with Microsoft Access. This situation also applies when you are using the CreateDatabase method in Visual Basic for Applications code with Microsoft Word, Microsoft Excel, or Microsoft PowerPoint.
Additionally, for any application that is using DAO, you can set the DefaultUser and DefaultPassword properties of the DBEngine object to specify the default user account and password that will be used when a new Workspace object is created.
Note Microsoft Jet 3.5 creates Microsoft Jet Workspace objects by default. However, if you specify the DefaultType property of the DBEngine object as dbUseODBC, or specify dbUseODBC when using the CreateWorkspace method, an ODBCDirect Workspace object is created. The Name and UserName property issues discussed in this section don't apply to ODBCDirect Workspace objects. For more information about using ODBCDirect features, see Chapter 9, "Developing Client/Server Applications."
To create a secured Workspace object, provide a user name other than the default for the user argument of the CreateWorkspace method. You can then create a new database in the Databases collection for that Workspace object. The owner of the new database is the user specified when the workspace is created. Microsoft Jet security requires that the owner of a secured database create the database while logged on to the appropriate secured Workspace object. The owner of the database can be specified only when the database is first created.
The owner of the new database can establish permissions on objects in the new database. To determine the owner of a database, check the value of the Owner property for the MSysModules Document object.
Important If you want to secure a database, don't create the Workspace object by using "Admin" as the value of the user argument of the CreateWorkspace method. The Admin user is the default user, and if the Admin user is the owner of a database, any user can open the database and alter objects or permissions on objects. To create a secured database, you must always use a nondefault user account.
The following example creates a secured workspace and then creates a database within that workspace. In this example, strWrkName is the name of the new secured workspace, strUserName is the name of the user account under which the workspace is created, strPassword is the user's password, and strDbName is the name of the new database.
Dim wrkSecured As Workspace Dim dbs As Database ' Create secured workspace. Set wrkSecured = _ DBEngine.CreateWorkspace(strWrkName, strUserName, strPassword) ' Create new secured database. Set dbs = _ wrkSecured.CreateDatabase(strDbName, dbLangGeneral, dbVersion30)
If you specify a user account that doesn't yet exist, Microsoft Jet returns an error when you attempt to create the new workspace. To create a new user account automatically when an unknown user name is passed to the CreateWorkspace method, you can handle the error by including the following code in the error handler. You create the new user account on the default Workspace object. In this example, strGroupName is the name of the group to which the new user is added. The code also adds the new user to the Users group.
Dim wrk As Workspace, usr As User Dim strPID As String, intC As Integer ' Return reference to default workspace. Set wrk = DBEngine(0) strPID = InputBox("Enter an alphanumeric PID between 4 and 20 " _ & "characters. Be sure to write this PID down in a safe " _ & "place, along with your user name and password.") ' Create user in Users collection of default workspace. Set usr = wrk.CreateUser(strUserName, strPID, strPassword) wrk.Users.Append usr ' Create user in Users collection of specified group. Set usr = wrk.Groups(strGroupName).CreateUser(strUserName) wrk.Groups(strGroupName).Users.Append usr
Note The previous examples are taken from the CreateSecuredDatabase function, which is available on the companion CD-ROM.
A new database can be created programmatically with DAO only; there is no SQL DDL method of creating a new database. A database, however, can't be deleted programmatically through DAO. If you want to delete a database programmatically, use the Visual Basic for Applications Kill statement to remove the file at the file-system level of the operating system.
MS Jet vs. Other Data Source Types
To Microsoft Jet, a "database" has different meanings depending on the source of the data. While a Microsoft Jet database is physically represented as an .mdb file, the database for other data sources is just a logical construction. For example, a dBASE or Microsoft FoxPro database is nothing more than a directory on a hard disk that contains one or more .dbf or .cdx files, or both. You must still "open" the database by using a Database object, but the database is not a physical object.
Because a non-Microsoft Jet database isn't a physical object, you don't use the CreateDatabase method to create the files that make up that type of database. For example, you can create new dBASE or FoxPro tables by using the CreateTableDef, CreateField, and CreateIndex methods as described in the "Creating Tables and Indexes in Installable ISAM Databases" section later in this chapter.
Creating and Modifying Tables
In Chapter 2, "Introducing Data Access Objects," you were introduced to the concept of using DAO to add a new table to a database by appending a Field object to the Fields collection of a TableDef object, and then appending that TableDef object to the TableDefs collection of the database. In the same way, you can add field-level and table-level validation rules, or specify indexes and inter-table relationships entirely through DAO code or SQL DDL statements.
When you use the Microsoft Access user interface to add fields to a table or to specify validation rules and default values in a property sheet, Microsoft Access is using DAO behind the scenes to tell the underlying Microsoft Jet database engine how to build or modify your table.
Anything that a user can do through the Microsoft Access user interface can be done with code. In some cases, properties and capabilities of Microsoft Jet that are available to you as a DAO programmer are not available through Microsoft Access. For example, through DAO you can specify fixed-length text fields for your tables. Microsoft Access allows only variable-length fields.
Creating Tables by Using SQL DDL
Even the most basic TableDef object in a Microsoft Jet database must contain at least one field. It's not necessary to specify a primary key, validation rules, or any of the other optional properties such as Description or DefaultValue. The table is nothing without its fields.
Using SQL DDL statements, the simplest statement you can use to create a new table in a database is as follows:
CREATE TABLE MyTable (MyField TEXT);
The CREATE TABLE statement creates a table called MyTable with one field called MyField. MyField is built as a variable-length text field with a maximum length of 255 characters. The field is not required, and may contain Null values. It's not indexed.
Communicating with Microsoft Jet by Using SQL DDL
You can pass SQL DDL statements to Microsoft Jet in three ways.
In Microsoft Access, you can use the RunSQL action or the RunSQL method of the DoCmd object to execute an SQL statement that acts on the current database. For example, the CREATE TABLE statement from the previous example can be executed in Microsoft Access by using the RunSQL method as follows:
DoCmd.RunSQL "CREATE TABLE MyTable (MyField TEXT);"
The DoCmd object and its methods are not available in other Microsoft Jet host languages such as Visual C++ and Visual J++, or when you are using Visual Basic for Applications in Visual Basic 5.0, Microsoft Excel 97, PowerPoint 97, or Word 97. In all of these languages and in Microsoft Access 97, you can execute SQL statements by using the Execute method on a Database object declared in code. In the following example, strDbPath is the path to the database, strTableName is the name of the new table, strFieldName is the name of the new field, and strFieldType is the type of field to create.
Dim dbs As Database Dim strSQL As String Set dbs = OpenDatabase(strDbPath) strSQL = "CREATE TABLE " & strTableName & " (" & strFieldName _ & " " & strFieldType & ");" ' Execute SQL command. dbs.Execute strSQL
Note To use any DAO method, such as the Execute method, when you are using Visual Basic for Applications in Microsoft Excel, PowerPoint, or Word, you must first install the Data Access Objects for Visual Basic component by rerunning Setup. Then you can use the References command (Tools menu) in the Visual Basic Editor to set a reference to the Microsoft DAO 3.5 object library.
Another way to execute an SQL statement is to create a permanent DAO QueryDef object that contains the SQL statement, and then use the Execute method to run the QueryDef object in code. The following example creates a QueryDef object that includes a CREATE TABLE statement and then executes that QueryDef object. In this example, strDbPath is the path to the database and strQueryName is the name of the new QueryDef object:
Dim dbs As Database, qdf As QueryDef Dim strSQL As String Set dbs = OpenDatabase(strDbPath) strSQL = "CREATE TABLE MyTable (MyField TEXT);" Set qdf = dbs.CreateQueryDef(strQueryName) ' Execute query. qdf.Execute
Note Another method of creating a table by using SQL is to use a make-table query. For a complete discussion of using make-table and other queries, see Chapter 4, "Queries."
See Also For information about creating QueryDef objects, see the "Microsoft Jet QueryDef Objects" section later in this chapter.
For the remainder of this chapter, SQL DDL examples are given with the text of the SQL statement only. Remember, however, that to communicate your SQL statements to Microsoft Jet, you must execute the DDL statement by using one of the previously described techniques.
Creating Tables by Using DAO
The DAO equivalent for the SQL DDL statement CREATE TABLE MyTable (MyField TEXT); is as follows, where strDbPath is the path to the database:
Dim dbs As Database, tdf As TableDef Dim fld As Field Set dbs = OpenDatabase(strDbPath) ' Create new TableDef object. Set tdf = dbs.CreateTableDef("MyTable") ' Create new Field object. Set fld = tdf.CreateField("MyField", dbText) tdf.Fields.Append fld dbs.TableDefs.Append tdf
The DAO method and the SQL DDL method create identical tables. The method you use depends on what you want to do, and the style of programming you prefer. In most cases, DAO is more powerful and flexible than SQL DDL.
The DAO techniques used to create and modify tables are similar to the way Microsoft Jet uses hierarchical collections in other contexts. For example, you can append a Field object to the Fields collection by using DAO in ways similar to those used to add a user-defined property to a form in a Microsoft Access application. In addition, certain types of Microsoft Access-specific properties, such as the ValidationRule and DefaultValue properties of fields, can be set only through the Microsoft Access user interface or through DAO in code.
Specifying Field Properties
The previous examples show a simple case of building a table with a single field by using default property values. Usually, you would specify many additional properties and override certain default values.
This example, which uses DAO, creates a table with four fields and specifies various nondefault values for the fields (ID, Name, Response, and Class), where strDbPath is the path to the database:
Dim dbs As Database, tdf As TableDef Dim fldID As Field, fldName As Field Dim fldResponse As Field, fldClass As Field Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.CreateTableDef("MarketingSurvey") ' Create automatically incrementing field. Set fldID = tdf.CreateField("ID", dbLong) fldID.Attributes = dbAutoIncrField fldID.Required = True ' Create text field. Set fldName = tdf.CreateField("Name", dbText) With fldName .Required = True .Size = 40 .AllowZeroLength = True .DefaultValue = "Unknown" End With ' Create memo field. Set fldResponse = tdf.CreateField("Response", dbMemo) ' Create text field with validation rule. Set fldClass = tdf.CreateField("Class", dbText, 10) With fldClass .Required = True .ValidationRule = "In('A','B','X')" .ValidationText = "Enter one of A, B, or X." End With With tdf .Fields.Append fldID .Fields.Append fldName .Fields.Append fldResponse .Fields.Append fldClass End With dbs.TableDefs.Append tdf
When you use Microsoft Jet SQL DDL statements, you can specify only the field names and data types. Microsoft Jet SQL doesn't support ANSI SQL constructs that set validation rules or default values (for example, field-level CHECK or DEFAULT clauses). You can't use SQL DDL to fill in the ValidationRule and DefaultValue properties of a field, for example. The closest you can come to re-creating the previous table by using only SQL DDL statements is:
CREATE TABLE MarketingSurvey (ID SHORT, Name TEXT (40), Response MEMO, Class TEXT (10));
Using the Attributes Property to Specify Additional Field Characteristics
In addition to standard field properties, you can use the Attributes property to specify additional characteristics for fields. For example, in Microsoft Access 97 table Design view, you create a Hyperlink field by setting the value in the Data Type column to Hyperlink. However, there is no DAO Type property setting for Field objects that corresponds to a Hyperlink field. This is because, at the Jet database engine level, a Hyperlink field is actually a Memo field that uses the dbHyperlinkField setting of the Attributes property to identify itself to Microsoft Access. While you can use DAO to create a Hyperlink field and work with its data, its hyperlink functionality is only available when the database is opened with Microsoft Access 97.
You can also use the Attributes property to create a field whose value is automatically incremented to a unique Long value for new records. Create a Field object of type Long, and set the Attributes property for the field to dbAutoIncrField. The resulting field is equivalent to a Microsoft Access AutoNumber field.
The following example creates a table named Hyperlinks that contains an automatically incrementing field and a hyperlink field, where strDbPath is the path to the database:
Dim dbs As Database, tdf As TableDef Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.CreateTableDef("Hyperlinks") ' Create and append fields in one step. With tdf .Fields.Append .CreateField("ID", dbLong) .Fields("ID").Attributes = dbAutoIncrField .Fields.Append .CreateField("Hyperlink", dbMemo) .Fields("Hyperlink").Attributes = dbHyperlinkField End With dbs.TableDefs.Append tdf
See Also For information about using other Attributes property settings with Field objects, search the DAO Help index for "Attributes property."
Built-in vs. Application-Defined Properties
The ValidationRule property, along with other properties such as Size, Required, and DefaultValue, are known as built-in properties.
In addition to these built-in properties, you can specify a variety of other custom properties, which you define yourself, or application-defined properties, which are defined by an application that's using Microsoft Jet, such as Microsoft Access. For example, when you build a table by using the Microsoft Access user interface, you may want to set the Caption property or Description property for a field. However, because these properties aren't part of the built-in properties available in a Field object's Properties collection, DAO may return an error if you try to set these properties from Visual Basic. In the following example, strDbPath is the path to the database:
Dim dbs As Database Dim tdf As TableDef, fld As Field Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.CreateTableDef("CustomerSurvey") Set fld = tdf.CreateField("ID", dbLong) fld.Attributes = dbAutoIncrField fld.Description = "Survey Identification Number" '<<<Error occurs here. tdf.Fields.Append fld dbs.TableDefs.Append tdf
This code doesn't execute as written because there is no built-in Description property for a Field object in a Microsoft Jet table. This property is set by Microsoft Access whenever a user enters a field description in a table's Design view, though it's not created by default. If you want to use DAO to create a Description property for a field, you must use the CreateProperty method to create the property, and then append the new property to the Properties collection of the field.
The following procedure creates a new table and calls the SetCustomProperty function to set the Description property for a field. The SetCustomProperty function attempts to set the property, and if necessary creates a new Property object and appends it to the Properties collection:
Sub SetFieldDescription() Dim dbs As Database, tdf As TableDef, fld As Field Dim strValue As String, strDbPath As String strDbPath = "C:\JetBook\Samples\NorthwindTables.mdb" ' Return reference to current database. Set dbs = OpenDatabase(strDbPath) ' Create new table. Set tdf = dbs.CreateTableDef("Inventory") Set fld = tdf.CreateField("ID", dbLong) fld.Attributes = dbAutoIncrField tdf.Fields.Append fld dbs.TableDefs.Append tdf strValue = "Survey ID Number" If SetCustomProperty(fld, "Description", dbText, strValue) Then Debug.Print "Property set successfully." Else Debug.Print "Property not set successfully." End If dbs.Close Set dbs = Nothing End Sub Function SetCustomProperty(obj As Object, strName As String, _ intType As Integer, varSetting As Variant) As Boolean Dim prp As Property Const conPropNotFound As Integer = 3270 On Error GoTo Error_SetCustomProperty ' Explicitly refer to Properties collection. obj.Properties(strName) = varSetting obj.Properties.Refresh SetCustomProperty = True Exit_SetCustomProperty: Exit Function Error_SetCustomProperty: If Err = conPropNotFound Then ' Create property, denote type, and set initial value. Set prp = obj.CreateProperty(strName, intType, varSetting) ' Append Property object to Properties collection. obj.Properties.Append prp obj.Properties.Refresh SetCustomProperty = True Else MsgBox "Error: " & Err & vbCrLf & Err.Description SetCustomProperty = False End If Resume Exit_SetCustomProperty End Function
See Also For a complete discussion of built-in, user-defined, and application-defined properties, see "DAO Properties" in Chapter 2, "Introducing Data Access Objects."
A field-level validation rule is enforced directly by Microsoft Jet. It doesn't matter whether you attempt to update the field through Microsoft Access, a custom Visual Basic program, or an ODBC call from a third-party program such as PowerBuilder.
The validation rule must be an expression that evaluates either to True or False. If the validation rule doesn't evaluate to True for the data entered in a field, an error occurs and the contents of the ValidationText property of the field are made available to the calling program.
How this message is conveyed to the user depends on the context of the calling program. For example, if the user attempts to enter an invalid value in the Microsoft Access Datasheet view of a table, Microsoft Access beeps, displays the validation text in a message box, and waits for the user to respond. If a Visual Basic or Visual Basic for Applications code routine violates the validation rule, a trappable run-time error occurs.
Because Microsoft Jet is independent of its host applications, the validation rule may not refer to features that are available from a specific application. You can't use a custom function in the validation rule expression. Nor can you make reference to objects in the user environment, such as to Microsoft Access forms or controls.
It may seem reasonable to have a validation rule expression such as the following:
Between 10 And MyUpperLimit()
However, even if you defined a function called MyUpperLimit within a Microsoft Access module or within your Visual Basic code, the validation rule expression is still not valid.
By default, field validation rules aren't evaluated until the entire record is updated. However, in many situations you can improve your application's performance by using DAO code that sets the ValidateOnSet property of a field to True, so that the field's validation rule is evaluated when the field's Value property (its data) is set. For more information about using the ValidateOnSet property, search the DAO Help index for "ValidateOnSet property."
You may want to create a validation rule for a particular field that depends on the values of one or more of the other fields in the table. You can create a table-level validation rule to compare one field to another.
Suppose you want to specify a rule for a Marketing Survey table that requires that all surveys using the name TestAccount have a Class field value of X. You can't validate the Name field without knowing the value of the Class field, and vice versa. Only when a record is either updated or inserted is all the information available to evaluate the rule.
The validation rule must contain an expression that can be evaluated as either True or False. If the expression evaluates to True, the update is allowed. If the expression evaluates to False, Microsoft Jet generates a trappable run-time error.
The following example uses DAO to add a table-level validation rule, where strDbPath is the path to the NorthwindTables database:
Function AddTableLevelValidationRule() As Boolean ' This procedure checks for an existing table-level validation rule and ' validation text on the Orders table and prompts the user to create new ones. Dim dbs As Database, tdf As TableDef Dim strValRule As String, strValText As String Dim strDbPath As String On Error GoTo Err_AddTableLevelValidationRule Set dbs = OpenDatabase("C:\JetBook\Samples\NorthwindTables.mdb") Set tdf = dbs.TableDefs("Orders") If Len(tdf.ValidationRule) Then If MsgBox("The following validation rule already exists: " & vbCrLf _ & vbCrLf & tdf.ValidationRule & vbCrLf & vbCrLf _ & "Delete this rule and create a new one?", vbYesNo) = vbYes Then tdf.ValidationRule = "" Else GoTo Exit_AddTableLevelValidationRule End If End If strValRule = "([RequiredDate]>=[OrderDate]) And ([ShippedDate]>=[OrderDate])" tdf.ValidationRule = strValRule If Len(tdf.ValidationText) Then If MsgBox("The following validation text already exists: " & vbCrLf _ & vbCrLf & tdf.ValidationText & vbCrLf & vbCrLf _ & "Delete this validation text and create new text?", vbYesNo) = _ vbYes Then tdf.ValidationText = "" Else GoTo Exit_AddTableLevelValidationRule End If End If strValText = "Both the Required Date and the Shipped Date must be " & _ "the same date or later than the Order Date." tdf.ValidationText = strValText AddTableLevelValidationRule = True Exit_AddTableLevelValidationRule: On Error Resume Next dbs.Close Set dbs = Nothing Exit Function Err_AddTableLevelValidationRule: MsgBox "Error: " & Err & vbCrLf & Err.Description AddTableLevelValidationRule = False Resume Exit_AddTableLevelValidationRule End Function
Note that you can test the properties as well as set them from DAO code. The previous example checks that no current table-level validation rule and validation text exist before adding the new ones.
Because table-level validation rules can't be evaluated until all the fields in a new record are populated, the validation rule is not checked until just before the new record is inserted in the table, or just before the existing record is updated.
The validation rule in the previous example uses the built-in IIf (Immediate If) function. While you can't use your own custom functions within a validation rule, you can make use of a wide variety of built-in functions, including date functions and string-manipulation functions such as InStr, Len, and Mid.
Modifying Existing Tables
When you want to make structural changes to existing tables, you use the same techniques that you used to create the table. In SQL DDL, you use the appropriate ALTER TABLE statement to modify a table, or the DROP TABLE statement to delete it. In DAO, you either append or delete a field from the Fields collection of a TableDef object, or delete the TableDef object from the TableDefs collection of the database.
Making Structural Changes by Using SQL DDL
By using SQL DDL, you can remove a table from the database with the following statement:
DROP TABLE MyTable;
Note that this action is subject to all the security restrictions and referential integrity constraints that are established for the database. If the table is part of one or more relationships that are defined in the database, you can't delete the table until you have deleted the relationship. For more information, see the "Creating and Deleting Relationships by Using SQL DDL" section later in this chapter.
Similarly, you can add or remove a field (column) from an existing table by using the following statements:
ALTER TABLE MyTable ADD COLUMN NewColumn TEXT (20); ALTER TABLE MyTable DROP COLUMN NewColumn;
When you use SQL DDL statements, you have no control over the order in which new fields are added to a table. Fields are inserted at the end of the list of fields when an ALTER TABLE ADD COLUMN statement is used. Each additional field created by an ALTER TABLE ADD COLUMN statement that follows the original CREATE TABLE statement is placed after the most recently created existing field.
CREATE TABLE YourTable (A CHAR (10));
yields the field A in the first field slot.
ALTER TABLE YourTable ADD COLUMN G CHAR(10);
yields the field A in the first field slot and G in the second slot.
ALTER TABLE YourTable ADD COLUMN F CHAR(10);
yields the field A in the first field slot, G in the second slot, and F in the third slot.
You can't alter the data type of a field once it's created, or change its name. The only way to convert an existing field from one data type to another is to add a new field, run an update query to populate the field with values from the original field, and then drop the original field. The same limitation applies to changes made through DAO; you can change a field's name through DAO, but not its data type, and you can work around this limitation by using the same method.
Making Structural Changes by Using DAO
Using DAO to make table modifications gives you much more flexibility than using SQL DDL statements.
Deleting a table by using DAO requires you to delete the TableDef object from the database's TableDefs collection. In the following example, dbs is a Database object and strTableName is the name of the table to delete:
The following code creates a new field and specifies it as the second field in the table (ordinal position of 1, with numbering beginning at 0). In this example, strDbPath is the path to the database, strTableName is the name of the table, strFieldName is the name of the field, and intType is a constant specifying what type of field to create:
Dim dbs As Database Dim tdf As TableDef Dim fld As Field Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.TableDefs(strTableName) Set fld = tdf.CreateField(strFieldName, intType) fld.OrdinalPosition = 1 tdf.Fields.Append fld
The equivalent code to delete a field from a table is:
Dim dbs As Database Dim tdf As TableDef Dim fld As Field Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.TableDefs(strTableName) tdf.Fields.Delete strFieldName
Altering Field and Table Properties by Using DAO
You can modify the following field and table properties after the table is created.
Applies to this object
TableDef and Field
TableDef and Field
Field, only when accessed with the Recordset object
TableDef and Field(For an example of adding a table-level validation rule to an existing table, see the "Table-Level Validation" section earlier in this chapter.)
TableDef and Field
Field, only when accessed with the Recordset object after using the AddNew or Edit method
Unlike SQL DDL, which gives you no way to change a field's name, you can use DAO to change the Field object's Name property. In the following example, strDbPath is the path to the database, strTableName is the name of the table, strFieldName is the existing name for the field, and strNewFieldName is the new name for the field:
Dim dbs As Database Dim tdf As TableDef Dim fld As Field Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.TableDefs(strTableName) tdf.Fields(strFieldName).Name = strNewFieldName
Properties other than those listed in the table above can't be changed. For example, the following code fails because you can't change the Type property of a field once it has been created:
Set tdf = dbs.TableDefs(strTableName) tdf.Fields(strFieldName).Type = dbInteger '<<< Error occurs here.
Linking Tables from External Sources
In addition to the native Microsoft Jet database format (.mdb), Microsoft Jet allows you to work with external data in a variety of formats. You can open tables from external data sources directly — by opening table-type Recordset objects in code. For information about how to open tables directly, see "Linking, Opening, and Importing External Tables" in Chapter 8, "Accessing External Data."
The most flexible way to work with external data sources is to link tables to the local database (referred to as "attaching tables" in Microsoft Access versions 2.0 and earlier, and in Visual Basic version 3.0). The linked tables appear in the TableDefs collection of the database, and, if security restrictions defined for the source database don't prevent you from doing so, you can freely query and update them as if they were local tables.
For example, it's common in Microsoft Access applications to place all tables in a back-end database located centrally on a network server, and place the remaining application-specific objects, such as forms, reports, and modules, in a separate front-end database. Links are defined in the front-end database to the tables in the back-end database, and then a copy of the front-end database is distributed to each user. Although the front-end database doesn't contain any tables, it looks and acts as if the linked tables are local.
This section describes how you can use DAO to add the linked tables to the local TableDefs collection of your database.
See Also For more information about working with external data sources, see Chapter 8, "Accessing External Data."
Linking Tables by Using DAO
To link to an external table by using DAO, you must create and append a TableDef object to a database's TableDefs collection that specifies the following items:
The name you want displayed for the linked table in the local database
The name of the table as it exists in its original location
The database format and location of the external table, and any additional information required to open the database where the external table resides
The following code opens a specified database and creates a new table. It then opens a second specified database and creates a link to the new table in the first database. Note that this code works only when both databases are Microsoft Jet databases. In this example, strDbPathBase is the path to the database containing the base table, strBaseTableName is the name of the new base table, strFieldName is the name of the field in the base table, intType is a constant specifying what type of field to create, strDbPathLinked is the path to the linked table, and strLinkedTableName is the name of the new linked table:
Dim dbsBase As Database, dbsLinked As Database Dim tdfBase As TableDef, tdfLinked As TableDef, fld As Field ' Open the first database and create a new TableDef object. Set dbsBase = OpenDatabase(strDbPathBase) Set tdfBase = dbsBase.CreateTableDef(strBaseTableName) Set fld = tdfBase.CreateField(strFieldName, intType) tdfBase.Fields.Append fld dbsBase.TableDefs.Append tdfBase dbsBase.Close Set dbsBase = Nothing ' Open the second database and create a new TableDef object. Set dbsLinked = OpenDatabase(strDbPathLinked) Set tdfLinked = dbsLinked.CreateTableDef(strLinkedTableName) ' Create a link to the new table in first database. tdfLinked.SourceTableName = strBaseTableName tdfLinked.Connect = ";DATABASE=" & strDbPathBase dbsLinked.TableDefs.Append tdfLinked dbsLinked.Close dbsLinked = Nothing
Depending on the database format you are linking to, the contents of the Connect property of the new TableDef object may change. The Connect property in this example begins with:
Note that nothing precedes the semicolon. This is the default value used when you are linking to tables in Microsoft Jet-format databases. If you are linking to a table in another format, such as FoxPro, you must specify the name of the installable ISAM driver that Microsoft Jet should use. For example:
The connection to an external ODBC source may require more information:
In the Microsoft Access user interface, you can view the connection information used to link a table by opening the table in Design view, and then viewing the text in the table's Description property.
See Also For more information about how to specify different database formats by using the Connect property, search the DAO Help index for "Connect property."
Creating and Modifying Table Indexes
So far in this chapter, we have discussed only creating tables and adding fields to them. For your tables to be useful, however, they must provide indexes for quickly finding and ordering particular records. Without indexes, it's necessary to search through each record in the table to find any given value.
A special form of index, the primary key index, serves to uniquely identify each record in the table. You can designate one field or set of fields in a table as a primary key. All values in the primary key must be unique and not Null, and there can be only one primary key for a table.
Another special form of index is the unique index. If you designate one field or combination of fields as a unique index, Microsoft Jet allows only one occurrence of that particular value or combination of values to be entered in your table. The unique index also serves to uniquely identify each record in the table.
This section assumes you know which types of indexes you need, and explains how to build and modify them.
See Also For information about how Microsoft Jet uses indexes for efficient data retrieval, see Chapter 4, "Queries."
Creating and Deleting Indexes by Using SQL DDL
You can designate one or more indexes for a table either at the time the table is built, or after the fact.
Adding a Primary Key Index with SQL DDL
By using SQL DDL statements, you can specify a CONSTRAINT clause to create a primary key index at the time the table is created:
CREATE TABLE Parking_Tickets (Ticket_ID INTEGER CONSTRAINT PrimaryKey PRIMARY KEY, Violator_Name TEXT (30), Violation_Type TEXT (10));
This DDL statement creates a primary key index for the Ticket_ID field and gives it the name "PrimaryKey." Although the primary key index can be given any name, PrimaryKey is a good choice because that's the name Microsoft Access assigns by default when you create a primary key for a table in Design view.
Adding a Unique Index with SQL DDL
By using SQL DDL statements, you can specify a CONSTRAINT clause to add a unique index at the time the table is created:
CREATE TABLE Member_List (FirstName TEXT (10), LastName TEXT (20), DateOfBirth DATETIME, CONSTRAINT UniqueKey UNIQUE (FirstName, LastName, DateOfBirth));
This DDL statement creates a unique index based on the combination of the FirstName, LastName, and DateOfBirth fields. The combined values of all three fields in the index must be unique, even if two or more records have the same value in just one of the fields.
Adding a Nonunique Index with SQL DDL
You can't use the CREATE TABLE statement to add a nonunique index. To add a nonunique index to an existing table, you can use the CREATE INDEX statement:
CREATE INDEX Ix_Violation_Type ON Parking_Tickets (Violation_Type) WITH DISALLOW NULL;
Because the index is not specified as unique, any number of records within the Parking_Tickets table can have the same Violation_Type value. After the Ix_Violation_Type index is created, searches for records involving the Violation_Type field are noticeably faster. The example uses the optional WITH DISALLOW NULL clause, which prohibits Null values in the indexed field. You can also use the WITH IGNORE NULL clause to prevent records with Null values in the indexed field or fields from being included in the index.
An index can consist of combinations of more than one field:
CREATE TABLE Order_Item (OrderID INTEGER, LineItem INTEGER, ProductType TEXT (10), ProductCode TEXT (20), CONSTRAINT PrimaryKey PRIMARY KEY (OrderID, LineItem)); CREATE INDEX Ix_ProdTypeCode ON Order_Item (ProductType, ProductCode);
Deleting an Index with SQL DDL
You can delete an index from a table with the DROP INDEX statement:
DROP INDEX Ix_Violation_Type ON Parking_Tickets;
Note that it may or may not be possible to delete an index at any given time, depending on the inter-table relationships you've defined. To set up referential integrity between two tables, the fields used to link tables must be indexed. For more information about setting up table relationships and their effect on indexes, see the "Referential Integrity and Relationships" section later in this chapter.
Creating and Deleting Indexes by Using DAO
As discussed in Chapter 2, "Introducing Data Access Objects," a TableDef object can have one or more Index objects in its Indexes collection. Each Index object is in turn made up of one or more Field objects in its Fields collection.
Make sure you don't confuse the Fields collection of the TableDef object itself with the Fields collection of any given Index object. All the Field objects in an Index object's Fields collection must also be Field objects in the Fields collection of the TableDef object, but each Index object has its own Fields collection. These collections exist as a hierarchy of objects and collections.
Unlike some database systems, such as FoxPro, with Microsoft Jet you can't create an index on an expression, such as a subset of a field or a calculated field.
It's common to designate one field, or a combination of fields, as the primary key of a table at the time it's created. The following function creates a primary key based on one or more fields in a table. The third argument to the function is a parameter array in which you can specify one or more field names to be included in the primary key:
Function AddPrimaryKeyToTable(tdf As TableDef, strPKName As String, _ ParamArray varFieldName() As Variant) As Boolean ' This procedure creates a table with a primary key. Dim fld As Field Dim idxPK As Index Dim intC As Integer Const conTableExists As Integer = 3010 On Error GoTo Err_AddPrimaryKeyToTable ' Create index. Set idxPK = tdf.CreateIndex(strPKName) For intC = 0 To UBound(varFieldName()) ' Create field in index. Set fld = idxPK.CreateField(varFieldName(intC)) idxPK.Fields.Append fld Next ' Set Primary property of index. idxPK.Primary = True ' Append index. tdf.Indexes.Append idxPK AddPrimaryKeyToTable = True Exit_AddPrimaryKeyToTable: Exit Function Err_AddPrimaryKeyToTable: MsgBox "Error: " & Err & vbCrLf & Err.Description AddPrimaryKeyToTable = False Resume Exit_AddPrimaryKeyToTable End Function
Note that you can use any valid Microsoft Jet name for the primary key. When you add a primary key to a table in the Microsoft Access user interface, the index is automatically named PrimaryKey, but you can change the name.
See Also For information about what constitutes a valid Microsoft Jet name, see "Guidelines for Naming DAO Objects" in Chapter 2, "Introducing Data Access Objects."
In most cases, the name you give an index is not significant. However, if you are opening table-type Recordset objects in code and want to specify the Index property of the Recordset object, you must know and specify the actual index name.
See Also For information about the various types of Recordset objects and how to use them, see Chapter 5, "Working with Records and Fields."
You can delete an index from a table by using the Delete method on the Indexes collection of a TableDef object. In the following example, strDbPath is the path to the database, strTableName is the name of the table containing the index, and strIndex is the name of the index:
Dim dbs As Database Dim tdf As TableDef Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.TableDefs(strTableName) tdf.Indexes.Delete strIndex
Note For performance reasons, you can't create more than thirty-two indexes on a table in a Microsoft Jet database.
An Index object can have one or more combinations of several property settings that influence its behavior. For example, the Unique property can be set, which ensures that only one record with a particular value (or combination of values, if the index consists of multiple fields) can exist. You can set the Primary property to True to designate the index as the primary key of the table. Because a primary key must uniquely identify each record, setting an index's Primary property to True automatically sets its Unique property to True.
Because a primary key is by definition a unique index, here's how the Primary property and the Unique property differ.
The main differences are that the fields that make up the primary key of a table can't contain Null values, and that only one primary key can be designated per table. These restrictions don't apply to an index with the Unique property set to True. In addition to setting the Unique property of an index, you can set the IgnoreNulls property to True, which means Null values are not added to the index; consequently, using the Seek method to find a Null value always fails.
The following code adds an SSN# field and then creates a unique index for SSN#. The index forces all non-Null values to be unique, but allows Null values when the value of the SSN# field isn't known. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database Dim tdf As TableDef, idx As Index Dim fld As Field Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.TableDefs("Employees") Set fld = tdf.CreateField("SSN#", dbText, 11) tdf.Fields.Append fld ' Create index on SSN# field. Set idx = tdf.CreateIndex("IX_SSN#") Set fld = idx.CreateField("SSN#") With idx .Fields.Append fld .Unique = True .IgnoreNulls = True End With tdf.Indexes.Append idx
Indexes and ODBC Views
Earlier in this chapter, linking to tables from external data sources such as FoxPro or Microsoft SQL Server to a Microsoft Jet database file was discussed. To link to external tables, you specify the original source of the table in the Connect property of a new TableDef object and then append the TableDef object to the TableDefs collection of the database.
In addition to linking to tables in external databases, you can also link to views in SQL data sources, such as Microsoft SQL Server or Oracle. A view is a named SQL SELECT statement executed on the server that joins one or more tables and selects one or more fields.
Normally, a linked view is not updatable even if it's based on an updatable table. For a linked table to be updatable, it must contain a primary key or a unique index.
When you link to a view rather than to a table, there is no index available and Microsoft Jet doesn't have enough information to treat the view as updatable. To make the linked view updatable, you must create a pseudo index for the TableDef object of the linked view. The pseudo index tells Microsoft Jet which of the fields in the linked view comprise the primary key or unique index in the table underlying the view. The index exists within the Microsoft Jet database only. Nothing is created on the server. In previous versions of DAO, a pseudo index was referred to as an index specification.
The following code creates a view named USCustomers in a SQL Server database. In this case, the SQL Server database is the Microsoft Access Northwind database that's been upsized to SQL Server by using the Microsoft Access 97 Upsizing Tools:
Dim wrk As Workspace, con As Connection Dim strSQL As String, strConnect As String ' Create ODBCDirect workspace. Set wrk = DBEngine.CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC) ' Initialize connection string. strConnect = "ODBC;DATABASE=NorthwindSQL;UID=sa;" _ & "DSN=NorthwindSQLDSN" ' Open connection. Set con = wrk.OpenConnection("NorthwindConnection", , , strConnect) strSQL = "CREATE VIEW USCustomers AS " _ & "(SELECT * FROM Customers WHERE Country = 'USA')" ' Execute SQL statement. con.Execute strSQL
This code uses objects in the ODBCDirect object model. For more information about ODBCDirect, see Chapter 9, "Developing Client/Server Applications."
The Microsoft Access Upsizing Tools are located in the Upsizing folder on the companion CD-ROM.
The following code links to a view on the server and then creates an index on the view to make it updatable. In this example, strDbPath is the path to the database:
Dim dbs As Database Dim tdf As TableDef Set dbs = OpenDatabase(strDBPath) ' Create linked table based on SQL view. Set tdf = dbs.CreateTableDef("USCustomersLinked") ' Specify SQL view as source table. tdf.SourceTableName = "USCustomers" ' Connect to SQL database. tdf.Connect = "ODBC;DATABASE=NorthwindSQL;UID=sa;" _ & "DSN=NorthwindSQLDSN" dbs.TableDefs.Append tdf dbs.TableDefs.Refresh ' Create primary key on linked table. dbs.Execute "CREATE INDEX PrimaryKey " & _ "ON USCustomersLinked (CustomerID) WITH PRIMARY"
The newly linked view, USCustomersLinked, can now be updated as though it were a regular table.
See Also For more information about the updatability of external data sources, see Chapter 8, "Accessing External Data." For more information about working with Microsoft SQL Server databases, see Chapter 9, "Developing Client/Server Applications."
Creating Tables and Indexes in Installable ISAM Databases
As noted earlier, for database data sources other than Microsoft Jet, such as FoxPro or dBASE, a "database" is a directory that contains a number of table files; there is no need to create a physical database for these data sources.
You can use DAO to create new tables, fields, and indexes for these external databases. You can't, however, store QueryDef objects in any database type other than a native Microsoft Jet .mdb file.
See Also For an example that creates an external table, see "Creating an External Table" in Chapter 8, "Accessing External Data."
MS Jet QueryDef Objects
As its name implies, a QueryDef object is the definition of a query, either saved permanently in a Microsoft Jet database, or created as a temporary object. A QueryDef object doesn't contain any data or perform an action; it's merely the set of instructions Microsoft Jet uses to select information or perform an action, such as the deletion or insertion of records in a database table.
Not everything that can be stored in a QueryDef object is what is usually thought of as a "query." In addition to SQL SELECT statements, which select, join, and order information from one or more tables, a QueryDef object can contain an SQL DELETE, INSERT, or CREATE TABLE statement.
Some QueryDef objects return their results in the form of a set of records. These objects perform similarly to what are known as views in other relational database systems. You can create an SQL statement and save it as a permanent QueryDef object in a database, and then use that QueryDef object as if it were a table. You can update it or join it to another table or QueryDef object.
Other QueryDef objects can perform an action, such as running an SQL DELETE statement, or modify the database structure, such as the CREATE TABLE statements used throughout this chapter. A special form of the QueryDef object, known as a pass-through query, is used to communicate directly with an ODBC database such as SQL Server or Oracle. The contents of a pass-through QueryDef object are not evaluated by Microsoft Jet, but are passed through to the ODBC database, which then interprets the text contained in the SQL property of the QueryDef object.
See Also For a full discussion of how to use the various forms of QueryDef objects and how to optimize their performance, see Chapter 4, "Queries." For more information about creating and using pass-through queries, see Chapter 9, "Developing Client/Server Applications."
This section covers how to create and manipulate QueryDef objects in a database.
Note that a QueryDef object can be created only in a native Microsoft Jet database. A QueryDef object can't be stored in a FoxPro or SQL Server database, for example.
There is no SQL DDL statement that can be used to create permanent QueryDef objects. All QueryDef objects must be created either interactively, through a product such as Microsoft Access, or programmatically by using DAO.
Creating a Permanent QueryDef Object
A QueryDef object is based on an SQL statement. To create a new permanent QueryDef object, you must provide a value for the name argument of the CreateQueryDef method. You can specify the SQL statement in the sqltext argument of the CreateQueryDef method, or in the SQL property of the newly created QueryDef object.
The following code creates a QueryDef object. In this example, dbs is a Database object, strQueryName is the name for the new QueryDef object, and strSQL is the SQL statement on which the QueryDef object is based:
Set qdf = dbs.CreateQueryDef(strQueryName) ' Set SQL property. qdf.SQL = strSQL
You may initialize the strSQL variable with an SQL statement such as the following, which returns a result set containing total sales for each employee for May 1996:
strSQL = "SELECT Orders.EmployeeID, " & _ "Sum((UnitPrice * Quantity) - Discount) AS ExtendedPrice " & _ "FROM Orders INNER JOIN [Order Details] ON " & _ "Orders.OrderID = [Order Details].OrderID " & _ "WHERE (((Orders.OrderDate) " & _ "Between #5/1/96# And #5/31/96#)) " & _ "GROUP BY Orders.EmployeeID;"
Note that unlike with other collections, such as Properties, Fields, or Indexes, it's not necessary to explicitly append the QueryDef object to the QueryDefs collection of the database. Simply assigning values for the name argument and the SQL property immediately creates the QueryDef object.
You can immediately use this new QueryDef object in your application. You can create a Recordset object from it, join it to another query or table, base a report on it, and so on.
Setting and Retrieving QueryDef Object Properties
Some properties of a QueryDef object can't be set in code. For example, the Type property is a read-only property that indicates the type of SQL statement used to create the QueryDef object. The value of the Type property may be one of dbQSelect, dbQCrosstab, dbQMakeTable, and so on, depending on the value of the SQL property of the QueryDef object.
The ReturnsRecords property is always created when you add a new QueryDef object to the database, but it has meaning only in the context of an SQL pass-through query. If you are creating a pass-through QueryDef object, you should specify whether the query performs an action (such as DELETE or INSERT), or whether it returns its result as a set of records. Microsoft Jet uses this setting to determine whether to create a Recordset object to contain the results of the pass-through query when it's executed. It's also used to indicate whether the QueryDef object can be used in an SQL SELECT statement to join to another table or QueryDef object.
Chapter 2, "Introducing Data Access Objects," explained that a QueryDef object can be both a permanent object stored in the database and a temporary object created and executed within code. Certain properties, such as RecordsAffected, have a value only after the QueryDef object is executed. For example, the following code creates a permanent QueryDef object that deletes records from the Orders table, and then executes the QueryDef object. After the QueryDef object executes, the RecordsAffected property contains the number of records that were deleted. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database Dim qdf As QueryDef Dim lngReturn As Long Set dbs = OpenDatabase(strDbPath) ' Delete any existing QueryDef object with same name. On Error Resume Next dbs.QueryDefs.Delete "DeleteOldOrders" On Error GoTo 0 Set qdf = dbs.CreateQueryDef("DeleteOldOrders") qdf.SQL = "DELETE FROM Orders WHERE OrderDate < #9/1/94#;" ' Execute query. qdf.Execute ' Return number of affected records. lngReturn = qdf.RecordsAffected
Creating Parameter Queries
The Parameters collection of a QueryDef object identifies the parameters for a parameter query. The following example creates a new parameter query and prompts the user to input values for the parameters, then displays the records that match their criteria. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database Dim qdf As QueryDef, rst As Recordset Dim strSQL As String, strResults As String Set dbs = OpenDatabase(strDbPath) ' Define SQL string with PARAMETERS clause. strSQL = "PARAMETERS [Enter Start of Customer Company Name] Text, " & _ "[Enter the City] Text; " & _ "SELECT Customers.* " & _ "FROM Customers WHERE " & _ "(((Customers.CompanyName) Like " & _ "[Enter Start of Customer Company Name] & '*') " & _ "AND ((Customers.City)=[Enter the City]));" ' Create QueryDef object. Set qdf = dbs.CreateQueryDef("qryCustSearch") qdf.SQL = strSQL ' Prompt user for parameter values. qdf.Parameters("[Enter Start of Customer Company Name]") = _ InputBox("Enter the first few letters of the customer's company name.") qdf.Parameters("[Enter the City]") = _ InputBox("Enter the customer's city.") ' Open Recordset object on QueryDef object. Set rst = qdf.OpenRecordset Do Until rst.EOF strResults = strResults & rst!CompanyName & vbCrLf rst.MoveNext Loop ' Show customers returned. If Not rst.BOF And rst.EOF Then MsgBox "Customers matching your criteria: " & vbCrLf & strResults End If
Unlike a TableDef object, in which you have to explicitly append each Field object to the Fields collection, the Fields and Parameters collections for QueryDef objects are automatically derived by Microsoft Jet based on the contents of the SQL property of the QueryDef object.
See Also For information about using permanent QueryDef objects, including how to supply parameter values programmatically, see Chapter 4, "Queries."
Deleting a QueryDef Object
You can delete a permanent QueryDef object by using the Delete method on a QueryDefs collection. In this example, dbs is a Database object and strQueryName is the name of a query in that database:
Temporary QueryDef Objects
It's not always necessary to create a permanent QueryDef object in order to execute an SQL statement, or to create a Recordset object in code. For example, you can pass an SQL string directly to the Execute method of a Database object in order to delete or insert records in a table. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database Set dbs = OpenDatabase(strDbPath) dbs.Execute "DELETE FROM Orders WHERE OrderDate < #9/1/94#;"
Microsoft Jet automatically parses the SQL statement and creates a temporary QueryDef object in the database, executes the temporary QueryDef object, and then deletes the QueryDef object.
The advantage to this method is that you avoid adding many permanent QueryDef objects to the database. The code becomes more readable because you don't have to refer to an external tool such as Microsoft Access to find out what the QueryDef object is supposed to accomplish.
However, when you use a temporary QueryDef object, you can't take advantage of certain properties of a permanent QueryDef object, such as the RecordsAffected property. To work around this problem, you can use the RecordsAffected property on the Database object. In addition, there is a slight decrease in performance each time the temporary QueryDef object is created and deleted; it must be parsed and "compiled" into its internal storage format each time this code is executed. A permanent QueryDef object is parsed and compiled once, when it's first executed, so subsequent executions can begin more quickly. Also, because the temporary QueryDef object is not named, it can't be used in a SELECT statement to join to another table or QueryDef object.
For multiuser databases, there are advantages to creating a QueryDef object that is not permanently saved in a database. You may want to do this, for example, if you are building up a SELECT query on the fly in code based on user input. This way, you don't need to create uniquely named QueryDef objects in a multiuser database. To create a temporary QueryDef object, you simply supply a zero-length string ("") for the name argument of the CreateQueryDef method. You can specify the SQL statement in the sqltext argument of the CreateQueryDef method or as the SQL property setting of the newly created QueryDef object.
A common situation in which it's to your advantage to create a temporary QueryDef object is when you have to execute it repeatedly, but it's not needed on a permanent basis. The following example creates a temporary parameter QueryDef object that repeatedly builds a Recordset object until the user clicks Cancel. In this example, strDbPath is the path to the database:
Dim dbs As Database Dim qdf As QueryDef Dim rst As Recordset Dim strCust As String, strMsg As String Set dbs = OpenDatabase(strDbPath) ' Create temporary QueryDef object. Set qdf = dbs.CreateQueryDef("") ' Construct SQL string. qdf.SQL = "PARAMETERS [Which CustomerID] Text; " & _ "SELECT Count(*) AS RecordCount, Orders.CustomerID " & _ "FROM Orders GROUP BY Orders.CustomerID " & _ "HAVING Orders.CustomerID Like [Which CustomerID];" ' Initialize variable. strCust = " " ' Prompt user for input. Do While Len(strCust) > 0 strCust = InputBox("Enter first letter or letters of customer name.") If Len(strCust) > 0 Then ' Append wildcard character to input. strCust = strCust & "*" ' Provide value for paramter. qdf![Which CustomerID] = strCust ' Open Recordset object on QueryDef object. Set rst = qdf.OpenRecordset strMsg = strMsg & vbCrLf & "Orders for Customer(s) like " & strCust & ": " ' Loop through Recordset object to append values to string. Do Until rst.EOF strMsg = strMsg & vbCrLf & rst!CustomerID & ": " & rst!RecordCount rst.MoveNext Loop strMsg = strMsg & vbCrLf End If Loop ' Display string if it is not empty. If Len(strMsg) > 0 Then MsgBox strMsg End If
If the action specified by the QueryDef object is going to be performed only once, there is no need to create a temporary QueryDef object. You can pass the SQL statement directly to the Execute method of a Database object, for example, or use the SQL SELECT statement as the argument to an OpenRecordset method. In the following example, strDbPath is the path to the database and strTableName is the name of the table whose records are to be counted:
Dim dbs As Database Dim rst As Recordset Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset("SELECT Count(*) AS RecordCount FROM " _ & strTableName & ";")
Referential Integrity and Relationships
So far this chapter has discussed individual table objects in isolation. With Microsoft Jet, you can use Relation objects to define how tables relate to one another. By creating relationships between tables, you can ensure that the data in your tables remains logically consistent. You can, for example, make sure that no orders are taken for nonexistent customers. You can prevent someone from deleting an employee record if there are corresponding entries in a Tasks table.
The rules that govern relationships between tables are known as referential integrity constraints. For relationships in which referential integrity is enforced, you can specify whether you want cascading updates and cascading deletes to be automatically performed on related records. If you enable cascading updates, changing a record in the primary table causes the change to be automatically made to all related records in the related table or tables. If you enable cascading deletes, deleting a record in the primary table causes the deletion of all related records in the related table or tables.
In addition to flagging attempts to violate referential integrity as errors, with Microsoft Jet you can specify what action should be taken when an attempt occurs. For example, by using cascading deletes, your application can automatically delete all of the related task's entries when you delete an employee record. Cascading updates can be used to change foreign key values in related tables when the primary key of a primary table is changed (a foreign key is a field that refers to the primary key field in another table).
After you establish the referential integrity rules governing the relationships between the tables in your database, the burden of enforcing these rules is removed from your application's code. You don't have to check explicitly for conditions that violate referential integrity. Microsoft Jet informs you when a violation attempt occurs.
So, whether your database program is updated by a user modifying the tables from within Microsoft Access, or by an ODBC call from a custom third-party application, the logical consistency of the data in your tables is preserved.
Creating and Deleting Relationships by Using SQL DDL
In the section "Creating and Modifying Table Indexes," one form of the CONSTRAINT clause used in the SQL DDL language was discussed. In those examples, CONSTRAINT clauses are used to create single-field or multifield indexes for a table, either at the time the table is created (with CREATE TABLE) or after the fact (with ALTER TABLE).
Another use of the CONSTRAINT clause is to create a relationship (or reference) from a foreign key of one table to the primary key of another table. With this constraint in place, it's impossible to enter a value in the foreign key field of a table that doesn't already exist in the primary key field of the primary table.
In the following example, two indexes are created. The first is the primary key index for TaskID, and the second is a foreign key index for the EmployeeID field:
CREATE TABLE Tasks (TaskID INTEGER CONSTRAINT PrimaryKey PRIMARY KEY, EmployeeID INTEGER CONSTRAINT FK_EmployeeID REFERENCES Employees (EmployeeID), DeptID TEXT (10), TaskCompleted DateTime);
The next example creates a new table called Departments. The DeptID field is the primary key for the Departments table. The DeptID field is the foreign key in the Tasks table used in the example above:
CREATE TABLE Departments (DeptID TEXT (10) CONSTRAINT PrimaryKey PRIMARY KEY, Description TEXT (50) );
When these statements are executed, Microsoft Jet creates two new indexes, PrimaryKey and FK_EmployeeID, in the Indexes collection of the newly created Tasks TableDef object. In addition to the new foreign key indexes, a new Relation object is created in the database. As discussed in Chapter 2, "Introducing Data Access Objects," a database contains a Relations collection, which stores information about all the inter-table relationships defined for your database. Each Relation object contains several properties, such as Table and ForeignTable, which identify the tables that participate in the relationship. Each Relation object also contains a Fields collection, which identifies the Field objects that make up the Relation object.
You can create a relationship for an existing table by using the ALTER TABLE statement. The following example creates a new relationship between the Tasks table and the Departments table:
ALTER TABLE Tasks ADD CONSTRAINT FK_DeptID FOREIGN KEY (DeptID) REFERENCES Departments (DeptID);
Setting a referential integrity foreign key constraint is the only form of relationship that can be established through SQL DDL. To set other Relation object types, such as cascading updates or cascading deletes, you must create the Relation object through DAO.
There is no way to modify an existing relationship. You must delete the relationship and then re-create it.
To delete a relationship by using SQL DDL, use an ALTER TABLE statement with the DROP CONSTRAINT clause. The following SQL DDL statement deletes the relationship created between the Tasks table and the Departments table in the previous example:
ALTER TABLE Tasks DROP CONSTRAINT FK_DeptID
Creating and Deleting Relationships by Using DAO
Relation objects can be created by using DAO in much the same way you can create an Index object or a TableDef object. You can append a new Relation object to the Relations collection of the database after first appending one or more Field objects to the Fields collection of the new Relation object. In addition, you can set the appropriate Attributes property for the Relation object to specify that the Relation object perform cascading updates or cascading deletes to maintain referential integrity.
The following example deletes and re-creates the relationship between the Employees table and the Orders table in the NorthwindTables database. In the following example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database Dim rel As Relation Dim fld As Field Set dbs = OpenDatabase(strDbPath) ' Delete existing relationship. On Error Resume Next For Each rel In dbs.Relations If rel.Table = "Employees" And rel.ForeignTable = "Orders" Then dbs.Relations.Delete rel.Name End If Next rel On Error GoTo 0 Set rel = dbs.CreateRelation("EmployeesOrders") With rel ' Set Table property to table on "one" side of relation. .Table = "Employees" ' Set ForeignTable property to table on "many" side of relation. .ForeignTable = "Orders" End With Set fld = rel.CreateField("EmployeeID") ' Specify name of foreign key field in foreign table. fld.ForeignName = "EmployeeID" rel.Fields.Append fld dbs.Relations.Append rel
This code first creates a new Relation object by using the CreateRelation method of the Database object. The Table property specifies the "one" table in the one-to-many relationship; the ForeignTable property specifies the "many" table. Setting these two properties ensures that no EmployeeID value can be entered in the Orders table if that value doesn't already exist in the Employees table.
You can also set the Attributes property for a Relation object when you create the Relation object. For example, the NorthwindTables database includes a Suppliers table and a Products table. A relationship based on the SupplierID field exists between the two tables. To specify that cascading deletes be performed when a record is deleted from the Suppliers table, set the Attributes property to the built-in constant value dbRelationDeleteCascade. This attribute specifies that if a record is deleted from the Suppliers table, all records in the Products table that have a matching SupplierID field are also deleted. In this way, if a supplier goes out of business, you can automatically delete that supplier's products from the Products table when you delete the supplier from the Suppliers table. To specify cascading updates, set the Attributes property to dbRelationUpdateCascade.
Multiple attributes can be specified for a relationship by adding the various constant values used to set the Attributes property. For example, the following code specifies an optional left join between two tables, where rel is the name of a relationship:
rel.Attributes = dbRelationLeft + dbRelationUnique + _ dbRelationDontEnforce
Microsoft Access Users Designating a Relation object attribute as "left" or "right" has no effect on referential integrity. It's used in Microsoft Access to show the type of join that's created by default when the tables are added to a new query in Design view.
In the SQL DDL example shown earlier in this chapter, creating a new relationship between two tables involves creating an index on the foreign key field. When you create a new Relation object by using DAO, Microsoft Jet automatically creates an index on the foreign key field or fields, even if one already exists. If you create a relationship between two tables with referential integrity enforced, avoid creating an index on the foreign key, as it will be a duplicate index. The foreign key index is necessary for enforcing referential integrity, so don't delete it. The index that's created on the foreign key when you create a relationship appears in the Indexes collection of the foreign table.
Microsoft Access Users Because the foreign key index that Microsoft Jet creates is associated with a relationship, it's not displayed in the Indexes dialog box when you view the table in Design view in Microsoft Access.
It's possible to create a relationship that's based on more than one field. To create a Relation object with two fields, create two Field objects in the Fields collection of the new Relation object. Set the ForeignName property of each Field object to the name of the corresponding field in the foreign table, then append both fields to the Fields collection of the Relation object. For example, suppose you have two tables that both contain FieldA and FieldB. You can create a relationship based on both fields if you want to enforce referential integrity for both fields at the same time. You would create both fields in the Fields collection of a Relation object, and set the ForeignName property appropriately.
Note that the primary key in the primary table does not necessarily need to include both FieldA and FieldB. The primary key can be based on both fields, or on just one of the fields.
You can't modify an existing relationship from DAO. You must delete the relationship and then re-create it. To delete a relationship, use the Delete method on the database's Relations collection.
Other Relationship Types
So far, only Relation objects created between two permanent tables that exist in the same database have been discussed. With Microsoft Jet, you can create relationships between tables and QueryDef objects, or between tables in a local database and tables in external databases.
It's not possible to create a cascading update or cascading delete relationship between a table and a QueryDef object. Nor is it possible to enforce referential integrity between a table and a query. Enforced referential integrity is valid only for relationships between tables. Creating a relationship between a table and a query has no effect on the structure of your database.
The primary benefit of creating a relationship between a table and a query or between a local table and a linked table is for documentation purposes and for interactive use with a product such as Microsoft Access. Microsoft Access can make certain assumptions about the relationships between your tables if you have defined these non-enforceable relationships. For example, if you create a relationship between a table and a query, and then add that table and query to a new query in query Design view, Microsoft Access automatically draws in the join line between the table and the query.