| Click here to support our advertisers

SOFTWARE FOR SALE
BOOKS FOR SALE
SEARCH CENTRAL
JOB BANK
CLASSIFIEDS
DIRECTORIES
REFERENCE Online Library
LEARNING CENTER
JOURNAL
NEWS CENTRAL
DOWNLOADS
COMMUNITY
CALENDAR
ABOUT US
Journal:
Get the weekly email highlights from the most popular journal for developers! Current issue
> developer.com developerdirect.com htmlgoodies.com javagoodies.com jars.com intranetjournal.com javascripts.com | All Categories : C/C++ vcg06.htm Defining the Characteristics of Data Objects Jet Data Access and Remote Data Objects Instances of Data Objects Persistent Member Objects Recordset Objects Created from Virtual Tables Dynamic Recordset Objects Static Recordset Objects Consistency Issues with Recordset Objects Understanding the Properties and Methods of the DAO DBEngine Object Defining the Workspace and Database Objects Properties and Methods of the Workspace Object Properties of the Database Object Methods Applicable to the Database Object Connecting to an Existing Jet Database Using the TableDefs Collection and TableDef Objects The Attributes Property of TableDef Objects Understanding Flags and Intrinsic Symbolic Constants Mapping Database Member Objects with the CDaoTableDef Collection Mapping the Fields and Indexes Collections Using the QueryDefs Collection and QueryDef Objects Creating Tables with C++ Code Creating and Using Recordset Objects Properties of Recordset Objects Methods Applicable to Recordset Objects and Collections Summary # - 6 - Understanding the Access Database Engine and DAO The Microsoft Jet Data Access Object (DAO) will become Visual C++ 4.0's primary method of connecting to and manipulating data that is stored in desktop and client-server databases. DAO is the term used by Microsoft and by this book to describe the container (the base or master class) for all the data-related objects discussed in this chapter. The first part of this book gave you a brief introduction to the DAO and its member objects. This chapter describes the structure of the Jet DAO in detail, because the member objects of the DAO constitute the foundation on which the majority of your Visual C++ database applications are built. This chapter features examples that use the DAO's member objects to create instances of objects with C++ code and display the properties of the objects in list boxes. By the time you complete this rather lengthy chapter, it's very likely that you will have learned more than you ever wanted to know about data-related objects—or, more simply, data objects. NOTE There is not a one-to-one mapping between DAO objects and the MFC DAO classes. Wherever possible, I will show where a DAO object's functionality is found in an MFC DAO class member that is different. ### Defining the Characteristics of Data Objects In the object-oriented terminology of OLE, objects are containers for properties, methods, and other objects. Object properties are member function pairs of a programmable object; you can set or return information about the state of a programmable object, such as the value of a data item in the field of a table. One member function sets the data, and another member function returns the data—thus the term pair. An object method is a single member function of an object. Methods perform an action on the object, such as changing the object's color, size, or shape. All member functions are said to be encapsulated in an object. You don't need to know the internal operations of the object to create an instance of the object and to manipulate the object in C++ code. All you need is a description of the properties and methods exposed by the object. NOTE Technically, you should be able to alter any property of a programmable object by assigning an appropriate value to the Set member of the function pair. The ability to set property values under specific conditions depends on the type of object and the application in which the object is used. Access 1.x, for example, had many objects whose properties could be set only in design mode. Access 2.0 and Access 7.0 have far fewer of these "frozen" objects. Visual C++ 4.0 doesn't even have a design mode, as do Visual Basic and Access. The Jet DAO is an OLE Automation in-process server that provides an object-oriented wrapper for the DLLs that comprise the Jet database engine. OLE Automation provides indirect access to properties and methods of programmable objects through a set of predefined interfaces. As a Visual C++ 4 programmer, you don't have to take any special steps to use the DAO features. Figure 6.1 shows AppWizard creating a DAO database project called DataDict. Figure 6.1. Visual C++ 4.0's AppWizard creating the DataDict project. OLE Automation server applications are selective about which programmable objects and member functions are accessible to other applications. Making member functions of OLE Automation server applications accessible to OLE Automation container applications is called exposing the member function. OLE Automation servers have two classes of functions, Public and Private. Only Public functions are exposed to OLE Automation client applications, such as Visual C++. Once you create a reference to an OLE Automation server object, Visual C++'s Class Browser provides a convenient list of the collections and objects exposed by the server, plus the member functions of each object. Figure 6.2 shows the CDaoRecordset constructor in the Visual C++ 4 Browser. The syntax for the selected method or property appears to the right of the ? button, which opens the help topic for the property or method, as shown in Figure 6.3. Figure 6.2. Visual C++ 4.0's Browser displaying the syntax for the CDaoRecordset::CDaoRecordset() constructor. Figure 6.3. The online help topic for the CDaoRecordset::CDaoRecordset() constructor. The Data Access Object classes in Visual C++ 4.0 are implemented by the seven main CDao... classes. (See Chapter 13, "Understanding MFC's DAO Classes," for more information on the MFC DAO classes.) These classes include all the objects that let you create, connect to, and manipulate the supported database types. This book uses the term compound object to describe an object that contains other objects to maintain consistency with OLE's compound document terminology. Like OLE's compound documents, compound objects have a hierarchical structure. Objects that are contained within other objects are called member objects of the container object. Visual C++ 4.0 treats member objects as properties of the container object. Figure 6.4 illustrates the hierarchy of Visual C++ 4.0's DAO database classes. Access 2.0 and 7.0 have Forms, Reports, Scripts (macros), and Modules Documents collections that aren't supported in Visual C++ 4.0. In Visual C++ 4.0, Container objects and Documents collections are used to secure Jet databases in conjunction with System.mdw workgroup files that you create with Microsoft Access. (Visual C++ 4.0 can't create a workgroup file, previously called a system file.) Note that the CDaoFieldExchange class isn't derived from CObject. Figure 6.4. The hierarchy of Visual C++ 4.0's Jet 3.0 Data Access Object. The following sections describe the version of the Jet DAO included with Visual C++ 4.0, how you create member objects of the data access object, and one method of classifying these member objects (by their persistency). As is the case for many other disciplines, the taxonomy of database objects isn't a settled issue. Detailed information on the properties and methods of the data objects discussed in the following sections appears later in this chapter. CAUTION You might find this chapter to be somewhat difficult. It has a grand mix of DAO terminology and MFC DAO terminology. For example, there is such a thing as a DAO Recordset, and there is also an MFC DAO CDaoRecordset class. Most of the methods and properties that are contained in the DAO Recordset are found as either member functions or member variables in the MFC DAO CDaoRecordset class object. However, that one-to-one relationship doesn't always carry through. Some of the native DAO methods and properties are simply not supported in the MFC DAO classes. I will tell you when a feature of DAO isn't directly supported. For unsupported features, you can call directly to the DAO engine. See Books Online, MFC Technical Notes, Number 54 for more information about interacting directly with DAO. It will infrequently be necessary to directly interact with DAO. #### Jet Data Access and Remote Data Objects Visual Basic programmers will realize that there are actually three different versions of DAO. Visual C++ 4 is shipped only with DAO 3.0, a 32-bit version of DAO. MFC 4's DAO classes are only 32-bit. For programmers who are still developing 16-bit applications, DAO is just not available. Here are the three versions of DAO (each of which can be found in your Windows 95 \Program Files\Common Files\Microsoft Shared\Dao folder): The Microsoft DAO 3.0 Object Library is the standard 32-bit OLE Automation "wrapper" (Dao3032.dll) for the Jet 3.0 database engine (MSJT3032.DLL in \Windows\System). Dao3032.dll is included with Visual C++ 4.0 and can be used only with 32-bit applications. Microsoft Access 7.0 uses the DAO 3.0 Object Library and the Jet 3.0 database engine. The Microsoft DAO 2.5/3.0 Compatibility Library is an alternative 32-bit OLE Automation type library (Dao2532.dll). Dao2532.dll isn't included with Visual C++ 4.0. The Microsoft DAO 2.5 Object Library is a 16-bit OLE Automation wrapper (Dao2516.dll) for the Jet 2.0 database engine of Access 2.0 (Msajt200.dll). Dao2516.dll isn't included with Visual C++ 4.0 and is of no use to Visual C++ programmers because DAO isn't supported by the 16-bit version(s) of MFC. Figure 6.5 shows the relationships between the Jet database engine used by Microsoft Access 1.1, 2.0, and 7.0 (Access 95), and Visual C++ 4.0. Figure 6.5. A simplified comparative diagram of the 16-bit and 32-bit implementations of the Jet database engine. With Visual C++ 4, the programmer is limited to using DAO 3.0. With DAO 3.0, you can create, open, or attach tables from 1.0, 1.1, 2.5, and 3.0 Jet databases. There's not too much in the way of limitations. Follow these guidelines: Use the CompactDatabase() member function of the CDaoWorkspace class to convert Jet databases from one version to another for use with Visual C++ 4.0. The CompactDatabase() function doesn't convert Access-specific objects, such as forms, reports, macros, and modules, from one version to another. Don't use the CompactDatabase() method to convert Jet 1.0, 1.1, or 2.5 .MDB files to version 3.0 if you plan to use the Jet 3.0 .MDB file with Access 7.0. Access 7.0 can't open an .MDB file that you convert from an earlier version to version 3.0 with Visual C++ 4.0's CompactDatabase() method. Visual C++ 4.0 can't create workgroup (System.mdw) files that are necessary to secure .MDB databases. You need a copy of Access 7.0 to create a 32-bit workgroup file or a copy of Access 2.0 to create a 16-bit system file (SYSTEM.MDA). Workgroup or system files usually reside on a workgroup or file server, together with shared .MDB files. Don't convert existing 16-bit Access system files (SYSTEM.MDA) to 32-bit workgroup files (System.mdw) until all users of the system file have converted to Jet 3.0 databases. You can attach a 16-bit SYSTEM.MDA file to a Jet 3.0 database without difficulty. NOTE Use Access's database conversion feature to upgrade versions of .MDB files that contain Access-specific objects. Converting Access 1.0 and 1.1 files is a two-way process. However, once you use Access to convert a version 1.0 or 1.1 .MDB file to Access 2.0 format, or convert a 1.0, 1.1, or 2.0 file to Access 7.0 format, the process isn't reversible. It's a safer practice to use the appropriate version of Access for all Jet database conversion operations. #### Instances of Data Objects You create an instance of the Data Access Object when you create an application that uses DAO and then reference that application's DAO object(s). Each time you add a reference (such as DataDict's dialog boxes, shown later), you add a reference to the object, not the object itself. With the data access object, a single CDaoDatabase object is created, and it is referenced throughout your application. You create an instance of the DAO when you use an MFC DAO class that creates a DAO connection. Here's how you create an instance of the CDaoDatabase object data type (object class) for an existing database: You declare an object variable with CDaoDatabase db. You instantiate (create a pointer to) the new Database object with the CDaoDatabase::Open() member function. NOTE When you declare a class variable of one of the object data types, Visual C++ initially doesn't initialize any specific database parameters to this variable. NOTE It's a generally accepted programming practice (GAPP) to place variable declaration statements at the beginning of C++ procedures and to add inline comments that describe the use of the variable in your code. Unlike generally accepted accounting practices (GAAPs), which are promulgated by the AICPA (American Institute of Certified Public Accountants), GAPPs aren't certified by a standardizing body. GAPPs arise by tradition. Declaring variables at the beginning of your procedure keeps variable declarations in a single, known location. Typically, variables are declared in a header (.H) file if they're utilized in more than one source file and in the source file if that file is the only location where the variable is to be referenced. #### Persistent Member Objects Persistent member objects are objects whose properties are contained in a file. Persistent objects are often called physical objects. The properties of persistent member objects exist independently of your application. The following member objects of the Database object are persistent: TableDef, Field, and Index objects are persistent table, field, and index definitions that are stored in one or more table(s) or files in the database. Jet databases store definitions in system tables of the .MDB file. If a database table isn't indexed, no Index objects exist for the specified Table object. (The Indexes collection of the TableDef object has a Count property value of 0.) The FILE.DDF and FIELD.DDF files store table and index definitions for Btrieve databases. Without FILE.DDF and FIELD.DDF, you can't create a Visual C++ Database object for a Btrieve database. QueryDef objects are persistent objects that are available only when you connect to a Jet database. QueryDef objects, which represent an SQL statement that is translated by the Access query optimizer, are stored in a system table of the Access .MDB file. QueryDef objects don't contain data from the tables that are involved in the query. Changes your application makes to persistent member objects appear in each instance of a persistent data object in your application, as well as in instances of the same member object in other applications that are running in a multiuser environment. However, changes to persistent objects made by other applications may not appear until the object is reopened or refreshed. Concurrency and consistency of persistent objects are discussed in a forthcoming section. #### Recordset Objects Created from Virtual Tables Although the data in tables is persistent, the Jet representation of the data is impersistent. A Recordset is a virtual table that is a temporary image (copy) of all or a part of a table or the resulting set of columns and rows returned by running an SQL query against one or more tables. Instances of Recordset objects are referenced by an object variable of the Recordset type and don't have a Name property. Recordset objects are stored in RAM and have no physical manifestation; you can't copy a Recordset object directly to a disk file. If the image is larger than the amount of free RAM available to your application, portions of the virtual table are paged to a temporary file that usually is located in your \Windows\Temp folder (as pointed to by the TEMP = environment variable). The Recordset object exists only for the duration of the life of the variable of the Recordset data type that points to the virtual table. You can choose between creating the dynamic and static Recordset objects described in the following two sections. ##### Dynamic Recordset Objects Dynamic Recordset objects, like persistent member objects, reflect changes made by others in a multiuser environment to the persistent objects of the database. Thus, your application sees the most current version of the physical tables that underlie your member objects. Your application can alter the data values of most, but not all, dynamic virtual tables; the exceptions are discussed later in this chapter. The following are the two types of dynamic Recordset objects that are based on virtual tables: Recordset objects of the Table type represent the data contained in a single table and are the default Recordset type. Table-type Recordset objects use the Seek method to locate a specific record in an indexed field. You can't use the Find... methods with Table-type Recordset objects. Recordset objects of the Dynaset type represent data contained in a single table or the result of executing an SQL SELECT query or a persistent QueryDef object against one or more tables. Dynaset-type Recordset objects are the most common type of Recordset object used by Visual C++ developers. NOTE The Recordset object replaces the Table, Dynaset, and Snapshot objects defined by Access 1.x. The advantage of using Recordset objects of the Dynaset type is that the Recordset is populated initially with only a subset of the underlying records. For example, when you open a large Recordset object of the Dynaset type, only the first 100 rows are retrieved from the underlying table(s). Subsequent Move... operations on the record pointer of the Recordset retrieve additional groups of 100 rows as needed. Applying the MoveLast method to a Recordset retrieves all the rows, which might take a substantial amount of time if your Recordset contains thousands of rows. ##### Static Recordset Objects A Recordset object of the Snapshot type is the Database object's sole static member. A Snapshot-type Recordset object captures a static image of a persistent member object or a Dynaset object. The data values contained in Snapshot-type Recordset objects are read-only at all times. You can apply any of the methods for Dynaset-type Recordset objects to conventional Snapshot objects, except methods that add, delete, or update data. You specify a Snapshot-type Recordset by substituting the intrinsic dbOpenSnapshot constant for the nType argument of the CDaoRecordset::Open() method. NOTE Intrinsic global database constants that begin with db are discussed in the section "Understanding Flags and Intrinsic Symbolic Constants." Snapshot-type Recordset objects retrieve every row from underlying table or query, so opening a Snapshot-type Recordset object on a large table or query result set causes a performance hit. However, operations on Snapshot-type Recordsets with 100 or fewer rows usually are faster than those of the Dynaset type. #### Consistency Issues with Recordset Objects As noted in the two preceding sections, all instances of data objects based on persistent objects, and all instances of dynamic data objects based on virtual tables, reflect changes made to the object when any application changes the value of the data contained in a persistent object. Changes to the structure of a persistent object also are reflected in other instances of the object, but structural changes to database objects are drastic operations that should occur infrequently. For example, if you change the Value property of a data item in the Field object of a Recordset object of the Table type in one instance of a Database object, the Value property of the corresponding data item changes in all other instances of the same Database object. However, the record pointers that determine the current record of each Recordset object of the Table or Dynaset type are independent of one another and of the record pointers of other instances of these objects. Thus, the new value would be apparent only to others in a multiuser environment whose open form coincidentally displays the changed data item. To ensure that the Recordset contains data that is consistent with underlying persistent objects, you apply the Refresh method. The Refresh method re-creates the Recordset object to which it is applied; if the Recordset is based on a QueryDef object, Refresh re-executes the query. NOTE You should apply the Refresh method to a Recordset object periodically to ensure that the Recordset object of instances of a Data control object sharing a multiuser Database object reflect current data. Applying the Refresh method to a Data control before and after updating or deleting records with transaction processing applications is good database programming practice. You must use the Refresh method with a Data control to open a database whose Connect and/or RecordSource properties you specify in run mode. ### Understanding the Properties and Methods of the DAO DBEngine Object The DBEngine object isn't directly exposed with the MFC DAO classes. Instead, it's accessed through a number of member functions that are in the CDaoWorkspace class. DBEngine has several properties and methods that are useful for advanced database applications. Table 6.1 lists the properties of the DBEngine object. Table 6.1. Properties of the DBEngine object.
> DAO Property | CDaoWorkspace Member Property Function | Notes/Parameter Information
> DefaultUser | SetDefaultUser() | Takes a pointer to a string variable of 20 characters or less containing the default user ID (UID) to be used for all Workspace sessions if no user ID is provided. The default value is Access's default Admin user, "Admin."
> DefaultPassword | SetDefaultPassword() | Takes a pointer to a string variable of 14 characters or less containing the default password (PWD) to be used for all Workspace sessions. The default value is an empty string ("").
> IniPath | SetIniPath() | A pointer string containing
> | GetIniPath() | the name of a Windows registry subkey that specifies the location of the System.mdw workgroup file (Jet 3.0) or System.mda system file (Jet 1.x and 2.0) for secure Access applications.
> LoginTimeout | GetLoginTimeout() SetLoginTimeout() | Gets or sets the number of seconds before an error is generated when you're attempting to log in to an ODBC data source. The default is 20 seconds.
> Version | GetVersion() | Returns a CString object that will contain the version number of the Jet database engine.
> You must set DBEngine property values before opening a CDaoWorkspace object that depends on the property values. Ordinarily, you place CDaoWorkspace.PropertyFunction() calls before you open the database. Table 6.2 lists the methods applicable to the DBEngine object. Again, these methods are accessed using the CDaoWorkspace class object member functions. The most commonly used methods of the DBEngine object are Idle, CompactDatabase, and RepairDatabase. Table 6.2. Methods for the DBEngine object.
> Method | CDaoWorkspace Member Method Function | Notes/Parameter Information
> Idle | Idle() | Allows the Jet database engine to catch up on background processing when there is substantial processing activity going on simultaneously in your multiuser application. The only valid parameter is dbFreeLocks.
> CompactDatabase | CompactDatabase() | Compacts a Jet database that isn't open in any application to save file space or to convert a Jet database from one version to another. Don't use Jet's CompactDatabase method to change the version of .MDB files that are also used by Access applications.
> RepairDatabase | RepairDatabase() | Repairs a damaged database (if possible). This function takes a single pointer to a string containing the name of the database to be repaired. It's recommended that you subsequently call the CompactDatabase() function after repairing the database.
> CreateWorkspace | Create() | Creates a new named CDaoWorkspace object with a specified user ID and password.
> RegisterDatabase | None | Creates an entry in the registry (32-bit) or in the ODBC.INI file (16-bit) for a named ODBC data source. This functionality isn't directly supported by the CDaoWorkspace object.
> The RegisterDatabase() method isn't supported by the DAO MFC classes. If you need to use a DAO functionality that isn't supported by the DAO MFC classes, you can call the functionality directly. For more information, refer to Technical Note 54, "Calling DAO Directly While Using MFC DAO Classes," which can be found on Books Online on the Visual C++ 4 distribution CD. The full syntax of the CompactDatabase method is either this:
>     static void PASCAL CompactDatabase( LPCTSTR lpszSrcName,

>     LPCTSTR lpszDestName, LPCTSTR lpszLocale = dbLangGeneral,

>     int nOptions = 0 );
> or, if you want to specify a password, this:
>     static void PASCAL CompactDatabase( LPCTSTR lpszSrcName,

>     LPCTSTR lpszDestName, LPCTSTR lpszLocale,

>     int nOptions, LPCTSTR lpszPassword );
> Note that if you want to use a password, you must specify the locale string and options parameters. If you specify the same path and filename for lpszSrcName and lpszDestName, the compacted file replaces the original file. The lpszLocale argument specifies the collating order of the file. The default value is dbLangGeneral (;LANGID=0x0409;CP=1252;COUNTRY=0). The nOptions argument lets you specify the type of the file and whether to encrypt or decrypt the file with the constants listed in Table 6.3. Table 6.3. Constants (flags) for the nOptions argument of the CompactDatabase method.
> Constant | Value | Purpose
> dbVersion10 | 1 | Compacts to a Microsoft Access 1.0 .MDB file.
> dbVersion11 | 8 | Compacts to a Microsoft Access 1.1 .MDB file.
> dbVersion20 | 16 | Compacts to a Jet 2.5 (Access 2.0) .MDB file.
> dbVersion30 | 32 | Compacts to a Jet 3.0 (Access 7.0) .MDB file.
> dbEncrypt | 2 | Encrypts the compacted file.
> dbDecrypt | 4 | Decrypts an encrypted file.
> The syntax of the RepairDatabase() method function is
>     static void PASCAL RepairDatabase(LPCTSTR lpszName);
> where lpszName is the well-formed path to and the filename of the Jet .MDB database file you want to repair. In most cases, Jet databases are secured only in multiuser applications. Thus, use of the DefaultUser(), DefaultPassword(), and IniPath() property functions and the Idle() and CreateWorkspace() method functions is covered in Chapter 19, "Running Visual C++ Database Applications on a Network." The LoginTimeout property and the RegisterDatabase method are two of the subjects of Chapter 3, "Using Visual C++ Data Access Functions," and Chapter 20, "Creating Front Ends for Client-Server Databases." ### Defining the Workspace and Database Objects Most of the preceding sections of this chapter have been devoted to defining terms and classifying data objects by their behavior. This might have confused you, because both MFC DAO terms and "regular" DAO terms are spread throughout this chapter. Generally, if you're not sure whether a term is MFC or DAO, you can search in the Visual C++ Books Online to determine the usage of a particular term. The remainder of this chapter provides a detailed analysis of the properties of and the methods that apply to the CDaoWorkspace and CDaoDatabase objects and to member objects of the CDaoDatabase object. It also tells you how to use these properties and methods in your Visual C++ 4.0 database applications. Before you can get or set the properties of a Database object or apply methods to a Database object, you need to create a variable of the CDaoDatabase type. You can create a class of type CDaoDatabase with the following two lines of Visual C++ code:
>     CDaoDatabase dbName;

>     dbName.Open(ConnectArguments);
> #### Properties and Methods of the Workspace Object The Workspace is defined as a session of the DBEngine object. A session is an instance of the DBEngine object for a single user, who is identified by a user ID and password, including the default Admin user ID and empty password. Technically, Open() and Create() are methods of the CDaoWorkspace object, because you can't apply a method, such as OpenDatabase, to an object for which a reference doesn't exist. Thus, the following syntax is better at keeping with the principles of object-oriented programming:
>     CDaoWorkspace wsName;

>     wsName.Create("database.mdb"); // Create deafult workspace

>     // Or call Append(...) to append to an existing workspace

>     CDaoDatabase dbName(wsName);
> An additional benefit of creating the default CDaoWorkspace object variable is that you have a pointer for implementing security features and performing transactions for the session with desktop databases, such as Jet, that include security features and support transactions. Jet (Access) security features are one of the subjects of Chapter 19. The use of Jet transactions is covered in Chapter 15, "Designing Online Transaction-Processing Systems." The IsolateODBCTrans, Name, and UserName properties of the Workspace object, which are seldom used in Visual C++ database applications, are described in Chapters 19 and 20. For completeness, Table 6.4 lists the methods of the Workspace object. Table 6.4. Methods applicable to the Workspace object.
> Method | CDaoWorkspace Member Function | Purpose/Parameters
> Open | Open() | Opens an existing workspace object.
> Create | Create() | Creates a new workspace object.
> CreateGroup | Not implemented | Creates a new Group object that you can append to the Groups collection or to a User object.
> CreateUser | Not implemented | Creates a new User object that you can append to the Users collection or to a Group object.
> BeginTrans | BeginTrans() | Specifies the beginning of a series of related operations that update data values in one or more persistent objects that underlie one or more Recordset objects.
> CommitTrans | CommitTrans() | Specifies the end of persistent data object update operations that constitute a single transaction and causes the updates to be applied to the table object(s) underlying the Recordset(s).
> Rollback | RollBack() | If the database type supports transactions (wsName.Transactions = TRUE), cancels the updating of the table objects(s) underlying the Recordset(s) by a preceding CommitTrans statement.
> Close | Close() | Closes the specified CDaoWorkspace and closes all Database objects opened in CDaoWorkspace.
> #### Properties of the Database Object The Database object has 11 properties whose values you can read to determine the characteristics of the database as a whole. The majority of these properties are read-only at all times. Table 6.5 lists the properties of the Database object in the approximate order of the frequency with which you're likely to use them. The last five properties listed aren't supported by MFC's implementation of DAO. You can access them by calling DAO directly, as noted in Visual C++ 4's Technical Note 54. Table 6.5. Properties of the Database object.
> Property | CDaoDatabase Member Function | Purpose/Parameters
> Name | GetName() | Returns a CString pointer that contains a well-formed path to the open database and the name of the open database.
> Connect | GetConnect() | Returns a CString pointer that contains the value of the string used to establish a connection to the database. The Connect property is an empty string for Access databases.
> Updatable | CanUpdate() | Indicates if the database has been opened in read-write (TRUE) or read-only (FALSE) mode. The Updatable property itself is read-only. (See the section "Connecting to an Existing Jet Database.")
> Transactions | CanTransact() | Indicates whether the database supports the Rollback transaction processing statement that lets you undo a group of changes to data values in the database's tables (TRUE). Access and most client-server databases support the rolling back of transactions.
> QueryTimeout | GetQueryTimeout() | Specifies or indicates the length of time in seconds before a time-out error occurs when you execute a query against a client-server database via an ODBC driver. The default value is 60 seconds.
> Version | GetVersion() | Returns an Integer flag that indicates the version number of the Jet engine that created the database (see Table 6.4).
> CollatingOrder | Not directly supported | An Integer flag that indicates the language whose rules are used by the database to sort text fields. The default value is 256 (for English and most Western European languages). The CollatingOrder property is read-only, except when you use the CreateDatabase() and CompactDatabase() methods. (These two methods expect the CollatingOrder flag to be a long integer.)
> Replicable | Not directly supported | When set to TRUE, creates a replicable database from which you can create additional replicas for distribution to users. Once this is made replicable, you can't set the Replica property to FALSE. Returns TRUE for a replicable database.
> DesignMasterID | Not directly supported | A globally-unique ID (GUID) that identifies the replicable database from which user replicas are created.
> ReplicaID | Not directly supported | A GUID that identifies each database replica.
> V1xNullBehavior | Not directly supported | If set to TRUE, zero-length strings ("") in Jet (Access) 1.x fields of the Text and Memo type are converted to Null values.
> NOTE The replication properties in Table 6.5—Replicable, DesignMasterID, and ReplicaID—don't appear in the Properties list of the online help topic for the Database object. Replication is limited to Jet 3.0 (Access 7.0) databases. The KeepLocal property applies to objects contained in a replicable database, not to the Database object itself. #### Methods Applicable to the Database Object The Database object has many more methods than properties. Many of Visual C++ DAO's methods of the Database object are now methods of the CDaoWorkspace, CDaoTableDef, CDaoRecordset, and CDaoQueryDef objects. The OpenRecordset and CreateQueryDef methods are Database object methods, because data objects of these types can (and usually do) act on more than one table of the Database object. However, these two methods are attached to different DAO MFC objects (see Table 6.6). The methods that you can apply to Database objects are listed in Table 6.6. They are listed in groups of related methods rather than in alphabetical order. The DAO MRC class is also listed because it varies with each of these methods. Table 6.6. Methods applicable to the Database object.
> Method | DAO Class and Member Function | Purpose/Parameters
> CreateTableDef | CDaoTableDef::Create() | Used in a Set statement to create a new persistent TableDef object that defines a newly created table in a database.
> CreateRelation | CDaoDatabase::CreateRelation() | Used in a Set statement to establish a relationship between the primary key field of a base table and the foreign key field of a related table.
> OpenRecordset | CDaoRecordset::Open() | Used in a Set statement to create a new Recordset object of the Table, Dynaset, or Snapshot type. OpenRecordset is the most commonly used method of the Database object.
> Refresh | CDaoRecordset::Requery() | Updates collections of persistent objects of the database, such as the QueryDefs and TableDefs collections, to reflect the current content of the collection.
> CreateQueryDef | CDaoQueryDef::Create() | Used in a Set statement to create a new persistent QueryDef object based on an SQL statement.
> Execute | CDaoQueryDef::Execute() | Executes a QueryDef or SQL statement that doesn't return records, such as an UPDATE, INSERT, or DELETE query. Access uses the term action query to indicate a query that doesn't return records.
> CreateProperty | Not directly supported | Adds a user-defined property of a name you specify to the Database object and sets the data type and the initial value of the user-defined property.
> MakeReplica | Not directly supported | Creates a user replica from the design-master replica of a replicable database. You specify the location and filename of the new replica and whether the replica is updatable or read-only with arguments of the MakeReplica method.
> Synchronize | Not directly supported | Synchronizes the Database object with changes made to a replica of the database, which is specified by location and filename. You can receive changes, export changes, or perform bidirectional updates (the default), depending on the value of the argument.
> Close | CDaoDatabase::Close() | Closes a Database object and frees resources consumed by the object.
> All member objects of the CDaoDatabase object must be closed before you can close the CDaoDatabase object. If you've declared your CDaoDatabase object variable with local scope, you don't need to use the Close() function, because the CDaoDatabase object and its member object are closed by the destructor when the variables go out of scope at the termination of the procedure in which the variables were declared. #### Connecting to an Existing Jet Database Here is the full, generalized syntax of the Open() function that you use to connect to and create a named reference to an existing database:
>     CDaoDatabase::Open(

>         LPCTSTR lpszName,

>         BOOL bExclusive = FALSE,

>         BOOL bReadOnly = FALSE,

>         LPCTSTR lpszConnect = _T("") );
> Table 6.7 lists the arguments used with the Open() function. Table 6.7. The arguments of the Open() function.
> Argument | Purpose
> lpszName | Specifies the well-formed path (drive and directory) to the location of the database file(s), except for ODBC data sources. Only the path to the directory that contains the table and index files is required when you connect to dBASE, FoxPro, and Paradox databases. All the files of these databases must be located in the same directory. You can use the uniform naming convention (UNC) to specify database files that are located on a network server. For example, you can use \\\\Servername{\\Folder\\Sharename as the value. For databases connected through the ODBC API, use the data source name (DSN) of an ODBC data source that is registered in your registry or your \WINDOWS\ODBC.INI file. Alternatively, you can leave this argument empty for ODBC databases and provide the required connect string in the lpszConnect argument.
> bExclusive | An Integer flag that determines if the database is to be opened in exclusive (TRUE) or shared (FALSE) mode. The default value is FALSE.
> bReadonly | An Integer flag that determines if the database is to be opened in read-only (TRUE) or read-write (FALSE) mode. The default value is FALSE.
> lpszConnect | A pointer to a character string whose content depends on the type of database you intend to open. The lpszConnect argument isn't used for Jet databases, but it's required for other desktop databases. The lpszConnect argument is necessary to prevent the Login dialog of ODBC databases from appearing when you specify an ODBC data source.
> The following code fragment opens the Jet 2.5 Stdreg32.mdb sample database included with Visual C++ 4.0 for exclusive, read-only access:
>     CDaoDatabase  db;

>         db.Open("Stdreg32.mdb",  TRUE, TRUE)
> The following code creates a new Jet 2.5 (Access 2.0) database in the current directory:
>     CDaoDatabase  db;

>         db.Create("new.mdb",_

>             dbLangGeneral, dbVersion25)
> To make a new Jet database useful, you must add tables to the database's TableDefs collection and then add fields and indexes to each table's Fields and Index collections. The following sections describe the process more fully. ### Using the TableDefs Collection and TableDef Objects The DAO TableDefs collection contains a member TableDef object for each table in the database. TableDefs are handled with the CDaoTableDef DAO MFC class. TableDef members qualify as objects because each TableDef has its own set of properties and has methods that are common to all members of the collection. The properties of the TableDef object describe each table in the database. Table 6.8 lists the properties of the TableDef object in the order in which the values might appear in a data dictionary. Table 6.8. Properties of the TableDef object.
> Property | Member Function | Description
> Name | GetName() | Returns a CString object that contains the name of the table.
> Connect | GetConnect() | Returns a CString object that contains the connect string used to attach tables to a Jet database. You can attach tables only to Jet .MDB databases.
> SourceTableName | GetSourceTableName() | Returns a CString object that contains the name of an attached table. You can use the Name property to create an alias for an attached table.
> ValidationRule | GetValidationRule() | Returns a CString object that contains a rule for maintaining table-level domain integrity, expressed as a string containing the WHERE criterion of a Jet SQL SELECT statement (without the WHERE SQL reserved word), such as InvoiceDate >= OrderDate. InvoiceDate and OrderDate are fields of the same table. Multiple validation rules must use the SQL AND conjunction. (Jet 2.5 and 3.0 databases only.)
> ValidationText | GetValidationText() | Returns a CString object that contains the message that will appear if an attempted update to the table would violate the ValidationRule property. (Jet 2.5 and 3.0 databases only.)
> ConflictTable | Not directly supported | The name of a "side table" that reports conflicts when an attempt is made to synchronize database replicas. Conflict tables are named TableName_conflict, where TableName is the value of the Name property of the TableDef. (Jet 3.0 databases only.)
> DateCreated | GetDateCreated() | Returns a COleDateTime object that contains the date and time that the table was created.
> LastUpdated | GetDateLastUpdated() | Returns a COleDateTime object that contains the date and time of the last modification to the data in the table.
> Updatable | CanUpdate() | Returns TRUE if you have read-write access to the table and FALSE if you have read-only access.
> Attributes | GetAttributes() | A long integer that contains the value of the option and status flags listed in Table 6.9.
> #### The Attributes Property of TableDef Objects The value of the Attributes property consists of the sum of the value of the flags listed in Table 6.9. A flag uses the individual bits of a long int (16 bits) to indicate if a particular attribute applies to the table. The values (bits set) of individual attributes can be represented by the decimal equivalent (value) of the position of the bit in the attribute flag or by the value of a symbolic constant. Table 6.9 shows the decimal and hexadecimal (hex, prefaced with 0x) values of each attribute value. Using hex values for the value of flags makes the concept of setting bits more evident. Always use the symbolic value when using these attributes, however. Table 6.9. The flags used to indicate the attributes of table objects.
> Attribute | Value | Description
> dbSystemObject | -2147483646 0x80000002 | Indicates that the table is a system table (read-only).
> dbHiddenObject | 1 0x1 | Indicates that the table is a temporary hidden table that Jet uses for internal purposes (read-only).
> dbAttachedTable | 1073741824 0x40000000 | Indicates that the table is a desktop database table (not an ODBC table) attached to an Access database. If the attached table is an Access table (a table from an Access database that is attached to the Access table of your Database object), the table has been opened in shared mode.
> dbAttachedExclusive | 65536 0x10000 | Indicates that an attached Access table has been opened for exclusive use by your application.
> dbAttachedODBC | 536870912 0x20000000 | Indicates that the table is an ODBC table attached to an Access database.
> dbAttachSavePWD | 131072 0x20000 | Indicates that the user name and password for the database from which the table is attached are included in the value of the Connect property. This means that the user of your application doesn't need to enter the user name and password for a secure database each time your application attaches the table.
> NOTE The negative decimal value of dbSystemObject appears because the return value for GetAttributes() is in unsigned long int format. Thus, values of 0x80000000 and greater represent negative long int values, and values of 0x8000 represent negative int values. Had Microsoft defined GetAttributes() as returning a DWORD instead, these values would be correctly defined as nonnegative values. #### Understanding Flags and Intrinsic Symbolic Constants The values of flags in Windows applications are most commonly defined using #define statements. In DAO, these flags are actually defined as variables with the attribute of const in the file dbdaoint.h. In Visual C++, you ordinarily declare global constants in the header (.H) file, in the format of #define symbol value. In dbdaoint.h, these values are typically defined as
>     const long dbAttachSavePWD = 131072;
> Usually symbols are in uppercase. (Notice, for example, that the identifiers in Table 6.9 are mixed case because they're not #defined values.) The symbolic constants for Visual C++'s data access object, each of which are prefaced by db, are global constants defined in dbdaoint.h. Figure 6.6 shows the definition of the variable dbOpenDynaset and shows where it's defined and used. You can use the Object Browser to determine the numeric value of any of the db... constants by double-clicking the constant name in the left pane or by double-clicking the listing under the Definitions heading in the right pane. Figure 6.6. A symbolic global database constant from the Microsoft DAO 3.0 Object Library. If more than one attribute is applicable to the table, the attributes are combined with the logical or operator (|), which performs an operation similar to decimal addition. (The difference between the | and + operators is that the | operator doesn't perform carry operations on bits involved in the addition.) Thus, if an attached Jet table includes the password for the database in the Connect property (0x20000) and is opened for exclusive use (0x10000), the value of the Attributes property is 0x30000, or decimal 196608. This value can be created by the statement
>     dbName.Attributes = dbAttachedSavePWD | dbAttachExclusive;
> Using masks to determine the value of flags is discussed in the next section. TIP It's good programming practice to always use symbolic constants to represent the values of flags, even when the decimal value of the flag is a small number. Although you might have to type more characters, using symbolic constants for flags makes your code more understandable, both to you and to others. In the unlikely event that a future version of an application will assign different values to attribute flags, you can substitute the new values for the constants in one location of your application. If an OLE Automation server defines new values for its symbolic constants, values are updated automatically when you create a reference to the server. #### Mapping Database Member Objects with the CDaoTableDef Collection You can use the members of the CDaoTableDef object to map the tables in the database. Mapping database tables is the first step in creating a data dictionary for a database. You can create the starting form of a simple data dictionary for any desktop database supported by Visual C++ 4.0. Use list boxes to display the properties of each TableDef object and add a few lines of Visual C++ code to populate (the object-oriented term for "fill") the list boxes. For the most part, this book tries to use complete, self-contained sample database applications rather than code snippets to illustrate Visual C++ database application design and programming techniques. Some of the more complex sample applications are constructed in stages; the first stage is completed in one chapter, and other features are added to the application in successive chapters. You start the first sample application, DataDict, in this section, and then add features that relate to QueryDef objects. Figure 6.7 shows the design of the TabelDefs dialog box of the DataDict application. Figure 6.7. The design of DataDict's TableDef form. NOTE All the source files for the DataDict application are included on the CD that comes with this book. If you installed the files that are on the CD, the DataDict files are located in your CHAPTR06 folder, unless you specified a different location during the installation process. Each Visual C++ sample application in this book is included in a separate subfolder named CHAPTR##, where ## is the chapter number. (Put a 0 before the number for chapters 2 through 9.) An identical structure of folders is also found on the CD itself, so that if you aren't rebuilding the sample programs, you won't need to take up valuable disk space for these projects. The TableDefs collection, like other Database object collections, has only one property, Count. You access this property using the CDaoDatabase::GetTableDefCount() member function. You use the Count property to index a for() loop to fill the list boxes with each table's attributes. The list boxes in DataDict's dialog box are basically standard, but I've linked their current selections so that changing the current selection on any one of the list boxes will change the other five list boxes to match the new current selection made by the user, as shown in Listing 6.1. Each list box will scroll independently without affecting the current selection. Listing 6.1. The code to populate the list boxes of the IDD_DATA_DICTIONARY dialog box.
>     // DataDictionary.cpp : implementation file

>     //

>     #include "stdafx.h"

>     #include "test.h"

>     #include "DataDictionary.h"

>     #ifdef _DEBUG

>     #define new DEBUG_NEW

>     #undef THIS_FILE

>     static char THIS_FILE[] = __FILE__;

>     #endif

>     /////////////////////////////////////////////////////////////////////////////

>     // DataDictionary dialog

>     DataDictionary::DataDictionary(CWnd* pParent /*=NULL*/)

>         : CDialog(DataDictionary::IDD, pParent)

>     {

>         //{{AFX_DATA_INIT(DataDictionary)

>         m_Database = _T("");

>         //}}AFX_DATA_INIT

>         m_pDatabase = NULL;

>         m_pTableDef = NULL;

>     }

>     void DataDictionary::DoDataExchange(CDataExchange* pDX)

>     {

>         CDialog::DoDataExchange(pDX);

>         //{{AFX_DATA_MAP(DataDictionary)

>         DDX_Control(pDX, IDC_SHOW_SYSTEM_TABLES, m_SystemTables);

>         DDX_Control(pDX, IDC_LIST6, m_List6);

>         DDX_Control(pDX, IDC_LIST5, m_List5);

>         DDX_Control(pDX, IDC_LIST4, m_List4);

>         DDX_Control(pDX, IDC_LIST3, m_List3);

>         DDX_Control(pDX, IDC_LIST2, m_List2);

>         DDX_Control(pDX, IDC_LIST1, m_List1);

>         DDX_Text(pDX, IDC_DATABASE, m_Database);

>         //}}AFX_DATA_MAP

>     }

>     BEGIN_MESSAGE_MAP(DataDictionary, CDialog)

>         //{{AFX_MSG_MAP(DataDictionary)

>         ON_BN_CLICKED(IDC_MAP_TABLES, OnMapTables)

>         ON_LBN_SELCHANGE(IDC_LIST1, OnSelchangeList1)

>         ON_LBN_SELCHANGE(IDC_LIST2, OnSelchangeList2)

>         ON_LBN_SELCHANGE(IDC_LIST3, OnSelchangeList3)

>         ON_LBN_SELCHANGE(IDC_LIST4, OnSelchangeList4)

>         ON_LBN_SELCHANGE(IDC_LIST5, OnSelchangeList5)

>         ON_LBN_SELCHANGE(IDC_LIST6, OnSelchangeList6)

>         //}}AFX_MSG_MAP

>     END_MESSAGE_MAP()

>     /////////////////////////////////////////////////////////////////////////////

>     // DataDictionary message handlers

>     BOOL DataDictionary::OnInitDialog()

>     {

>         CDialog::OnInitDialog();

>         // TODO: Add extra initialization here

>         // Set up your CDaoTabledef object!

>         m_Database = m_pDatabase->GetName();

>         // Update the list boxes... Forces a refresh of dialog, too!

>         OnMapTables();

>         return TRUE;  // Return TRUE unless you set the focus to a control

>                       // EXCEPTION: OCX Property Pages should return FALSE

>     }

>     void DataDictionary::OnMapTables()

>     {

>         // TODO: Add your control notification handler code here

>         int nIndex = 0;

>         CDaoTableDefInfo tabledefinfo;

>         CString Formatted;

>         m_List1.ResetContent();

>         m_List2.ResetContent();

>         m_List3.ResetContent();

>         m_List4.ResetContent();

>         m_List5.ResetContent();

>         m_List6.ResetContent();

>         for (nIndex = 0; nIndex < m_pDatabase->GetTableDefCount(); ++nIndex)

>         {

>             m_pDatabase->GetTableDefInfo(nIndex, tabledefinfo, AFX_DAO_ALL_INFO);

>             if ((tabledefinfo.m_lAttributes & dbSystemObject) == 0 ||

>                 m_SystemTables.GetCheck() != 0)

>             {

>                 m_List1.AddCString(tabledefinfo.m_strName);

>                 m_List2.AddCString(tabledefinfo.m_dateCreated.Format("%X %x"));

>                 m_List3.AddCString(tabledefinfo.m_dateLastUpdated.Format("%X %x"));

>                 m_List4.AddCString(tabledefinfo.m_bUpdatable ? "Updatable" :

>                     "NotUpdatable");

>                 Formatted.Format("0x%8.8X", tabledefinfo.m_lAttributes);

>                 m_List5.AddCString(Formatted);

>                 m_List6.AddCString(tabledefinfo.m_strValidationRule.IsEmpty() ?

>                     " - None defined " :

>                     "\"" + tabledefinfo.m_strValidationRule + "\"");

>             }

>         }

>         m_List1.SetCurSel(1);

>         m_List2.SetCurSel(1);

>         m_List3.SetCurSel(1);

>         m_List4.SetCurSel(1);

>         m_List5.SetCurSel(1);

>         m_List6.SetCurSel(1);

>         // Force an update of the dialog box!

>         UpdateData(FALSE);

>     }

>     // All of the OnSelchangedList?() functions cause each

>     // list box to follow the last selection in any other

>     // list box. An interesting concept...

>     void DataDictionary::OnSelchangeList1()

>     {

>         // TODO: Add your control notification handler code here

>         m_List2.SetCurSel(m_List1.GetCurSel());

>         m_List3.SetCurSel(m_List1.GetCurSel());

>         m_List4.SetCurSel(m_List1.GetCurSel());

>         m_List5.SetCurSel(m_List1.GetCurSel());

>         m_List6.SetCurSel(m_List1.GetCurSel());

>     }

>     void DataDictionary::OnSelchangeList2()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List2.GetCurSel());

>         m_List3.SetCurSel(m_List2.GetCurSel());

>         m_List4.SetCurSel(m_List2.GetCurSel());

>         m_List5.SetCurSel(m_List2.GetCurSel());

>         m_List6.SetCurSel(m_List2.GetCurSel());

>     }

>     void DataDictionary::OnSelchangeList3()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List3.GetCurSel());

>         m_List2.SetCurSel(m_List3.GetCurSel());

>         m_List4.SetCurSel(m_List3.GetCurSel());

>         m_List5.SetCurSel(m_List3.GetCurSel());

>         m_List6.SetCurSel(m_List3.GetCurSel());

>     }

>     void DataDictionary::OnSelchangeList4()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List4.GetCurSel());

>         m_List2.SetCurSel(m_List4.GetCurSel());

>         m_List3.SetCurSel(m_List4.GetCurSel());

>         m_List5.SetCurSel(m_List4.GetCurSel());

>         m_List6.SetCurSel(m_List4.GetCurSel());

>     }

>     void DataDictionary::OnSelchangeList5()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List5.GetCurSel());

>         m_List2.SetCurSel(m_List5.GetCurSel());

>         m_List3.SetCurSel(m_List5.GetCurSel());

>         m_List4.SetCurSel(m_List5.GetCurSel());

>         m_List6.SetCurSel(m_List5.GetCurSel());

>     }

>     void DataDictionary::OnSelchangeList6()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List6.GetCurSel());

>         m_List2.SetCurSel(m_List6.GetCurSel());

>         m_List3.SetCurSel(m_List6.GetCurSel());

>         m_List4.SetCurSel(m_List6.GetCurSel());

>         m_List5.SetCurSel(m_List6.GetCurSel());

>     }
> The DataDictionary dialog box is displayed using the code shown in the following code fragment. The only item of information passed to the DataDictionary dialog box is a pointer to the current CDaoDatabase object (in bold), which you can retrieve from the view class:
>     void CTestView::OnViewTabledefs()

>     {

>         // TODO: Add your command handler code here

>         DataDictionary   dd;

>     //    Pass the database object pointer:

>         dd.m_pDatabase = m_pSet->m_pDatabase;

>         dd.DoModal();

>     }
> The Database text box at the bottom of the dialog box shows the currently open database. This program could have been written to prompt the user for a database to open, but for the purposes of this program, I didn't add this feature. Clicking on the Map Tables button will refresh the list boxes. This allows the Show System Tables check box to take effect when it's checked or unchecked. When using the program, you can click a table name in any of the list boxes, and the corresponding selection in the other five list boxes will also change. Figure 6.8 shows the dialog box that displays the TableDef objects of Stdreg32.mdb. The Validation Rule list box, which displays the Validation Rule property, shows - None defined because none of the tables in Stdreg32.mdb includes a table-level validation rule. Figure 6.8. Displaying the TableDefs collection for Stdreg32.mdb with system tables included. NOTE Tables in Access databases whose names begin with MSys are system tables. For example, you would expect MSysMacros to have the Attributes value of 0x8000000 that most of the other MSys... tables share, not 0x00000002. MSysMacros is a system table that contains a definition of each macro object that you create with Microsoft Access; thus, MSysMacros is useful only with Access applications. Using the value of the dbSystemObject constant, 0x80000002, includes MSysMacros and other Access-specific tables in the system table category. The following condition: if ((tabledefinfo.m_lAttributes & dbSystemObject) == 0 || m_SystemTables.GetCheck() != 0) excludes system tables from the list boxes when the Show System Tables check box isn't checked. #### Mapping the Fields and Indexes Collections TableDef, QueryDef, Recordset, and Relation (CDaoTableDef, CDaoQueryDef, and CDaoRecordset) objects all contain Fields collections. In MFC's implementation of DAO, Fields collections are managed with a CDaoFieldInfo object, which is a structure. Table 6.10 lists the properties of the Field object and the CDaoFieldInfo object members. Some properties are valid for only one type of object or certain field data types. Table 6.10. Properties of the Field object.
> Property | CDaoFieldInfo Member | Description
> AllowZeroLength | m_bAllowZeroLength | If TRUE, zero-length strings are allowed. Otherwise, at least one character must be entered. (Text or Memo fields only.)
> Attributes | m_lAttributes | The sum of flags that determine the characteristics of the field (see Table 6.11).
> CollatingOrder | m_lCollatingOrder | Specifies the sort order for text fields.
> DataUpdatable | Not directly supported | TRUE if the field allows updates to data.
> DefaultValue | m_strDefaultValue | The value automatically entered in a field when a new record is added.
> ForeignName | m_strForeignName | The value of the Name property when the field is included in a relationship with another table.
> Name | m_strName | The given name of the table. For attached tables and tables used in the execution of a QueryDef object, the value can be an alias (created by the AS SQL reserved word for QueryDef objects).
> OrdinalPosition | m_nOrdinalPosition | The relative position of the field in the table, starting with 1 as the first field.
> Required | m_bRequired | If TRUE, a non-Null entry is required. If AllowZeroLength is FALSE, a character must be entered in a Text field.
> Size | m_lSize | The size in bytes of the field: fixed for numeric and logical fields, 0 for Memo and OLE Object (LongBinary) fields, and 1 to 255 characters for Text fields. (Unicode has no effect on the value.)
> SourceField | m_strSourceField | The name of the field of an attached table or of a table used in executing a QueryDef object.
> SourceTable | m_strSourceTable | The name of an attached table or a table used in executing a QueryDef object.
> Type | m_nType | An Integer designating the field's data type (see Table 6.12).
> ValidateOnSet | Not directly supported | If TRUE, tests the ValidationRule property immediately upon entry. Otherwise, the test occurs when the record pointer is moved from the current record. (Applies only to Recordset objects.)
> ValidationRule | m_strValidationRule | A field-level validation rule consisting of the WHERE clause of an SQL criterion, without the WHERE reserved word and the [Table.]Field identifier, as in <= Date.
> ValidationText | m_strValidationText | The text that appears in a message box that is displayed when a field-level validation rule is broken.
> Table 6.11. The value of flags for the Attributes property of a field.
> Attributes Flag | Value | Description
> dbFixedField | 0x01 | Indicates that the length of the field is fixed, not a Text, Date/Time, Memo, Binary, or long varbinary field.
> dbVariableField | 0x02 | Indicates that the length of the field is variable, not a Number, Date/Time, or Boolean field.
> dbAutoIncrField | 0x10 | Indicates that the field is of the AutoIncrement (formerly Counter) field data type, which is automatically incremented when you add new records to the table or recordset.
> dbUpdatableField | 0x20 | Indicates that the data in the field or the structure of the field can be modified.
> Table 6.12. Values of global symbolic constants for field data types.
> Data Type Constant | Value | Field | Fundamental Data Type
> dbBOOL | 1 | Boolean, Yes/No, Logical | int
> dbByte | 2 | Number, Byte, tinyint | int
> dbInteger | 3 | Number, smallint | int
> dbLong | 4 | Number, int | long
> dbCurrency | 5 | Money, decimal, fixed-point | A COleCurrency object
> dbSingle | 6 | Number, single-precision float | float
> dbDouble | 7 | Number, double-precision float | double
> dbDate | 8 | Date/Time, timestamp | A COleDateTime object
> dbBinary | 9 | Binary, varbinary | Not implemented in Visual C++ 4
> dbText | 10 | Text | CString
> dbLongBinary | 11 | Binary, long varbinary, OLE object | OLE object
> dbMemo | 12 | Text, long varchar | CString
> dbGUID | 15 | A GUID value | A GUID object
> Only the TableDef object contains an Indexes collection. In MFC's implementation of DAO, the Indexes collection is a struct called CDaoIndexInfo. Table 6.13 lists the properties of the Index object. Datatypes that are new to Jet 2.x and 3.0 are noted by an asterisk (*). Table 6.13. The properties of the Index object.
> Property | CDaoIndexInfo Member | Data Type | Description
> Name | m_strName | CString | The name of the index, unique within a table.
> Fields | m_pFieldInfos | CDaoIndexFieldInfo* | A pointer to a CDaoIndexFieldInfo object. The names of fields that comprise the index are preceded by a sign indicating the sort order (+ for ascending and - for descending). If the index has more than one field, the field names are separated by a semicolon.
> Clustered | m_bClustered | BOOL | TRUE if the index represents a clustered index (except for ODBC databases). Jet 3.0 doesn't create clustered indexes.
> Foreign | m_bForeign | BOOL | TRUE if the index is on a foreign key field. FALSE otherwise.
> Required | m_bRequired | BOOL | TRUE if the index is on a required field. FALSE otherwise.
> IgnoreNulls | m_bIgnoreNulls | BOOL | TRUE if the index is on a field that allows Null values. FALSE otherwise.
> Primary | m_bPrimary | BOOL | TRUE if the index is the primary key index. FALSE otherwise.
> Unique | m_bUnique | BOOL | TRUE if the index prohibits duplicate values. FALSE otherwise. The value of the Unique property is always TRUE for PrimaryKey indexes. Unique indexes don't permit Null values in the indexed field.
> NOTE xBase programmers probably will notice that there is no provision in Visual C++ 4.0 to create an index based on values returned by a function that uses a field value as an argument. Indexes on .DBF files created with expressions such as INDEX ON SUBSTR(char_field, 3,8) + DTOS(date_field) are common in xBase applications. Not only can you not create such an index with Visual C++, but you can't even open an index file of this type, much less maintain it. Listing 6.2 shows the C++ code needed to add the capability to list in the DataDict application properties of the Field objects of a given TableDef of a Jet database. This is done as a second dialog box and is very similar to the one shown in Listing 6.1. Listing 6.2. The added procedures needed by the DataDict application to list properties of the Field objects.
>     // Fields.cpp : implementation file

>     //

>     #include "stdafx.h"

>     #include "test.h"

>     #include "Fields.h"

>     #ifdef _DEBUG

>     #define new DEBUG_NEW

>     #undef THIS_FILE

>     static char THIS_FILE[] = __FILE__;

>     #endif

>     /////////////////////////////////////////////////////////////////////////////

>     // Fields dialog

>     Fields::Fields(CWnd* pParent /*=NULL*/)

>         : CDialog(Fields::IDD, pParent)

>     {

>         //{{AFX_DATA_INIT(Fields)

>         m_Database = _T("");

>         //}}AFX_DATA_INIT

>         m_pDatabase = NULL;

>     }

>     void Fields::DoDataExchange(CDataExchange* pDX)

>     {

>         CDialog::DoDataExchange(pDX);

>         //{{AFX_DATA_MAP(Fields)

>         DDX_Control(pDX, IDC_LIST6, m_List6);

>         DDX_Control(pDX, IDC_LIST5, m_List5);

>         DDX_Control(pDX, IDC_LIST4, m_List4);

>         DDX_Control(pDX, IDC_LIST2, m_List2);

>         DDX_Control(pDX, IDC_LIST3, m_List3);

>         DDX_Control(pDX, IDC_LIST1, m_List1);

>         DDX_Text(pDX, IDC_DATABASE, m_Database);

>         //}}AFX_DATA_MAP

>     }

>     BEGIN_MESSAGE_MAP(Fields, CDialog)

>         //{{AFX_MSG_MAP(Fields)

>         ON_LBN_SELCHANGE(IDC_LIST1, OnSelchangeList1)

>         ON_LBN_SELCHANGE(IDC_LIST2, OnSelchangeList2)

>         ON_LBN_SELCHANGE(IDC_LIST3, OnSelchangeList3)

>         ON_LBN_SELCHANGE(IDC_LIST4, OnSelchangeList4)

>         ON_LBN_SELCHANGE(IDC_LIST5, OnSelchangeList5)

>         ON_LBN_SELCHANGE(IDC_LIST6, OnSelchangeList6)

>         ON_BN_CLICKED(IDC_MAP_TABLES, OnMapTables)

>         //}}AFX_MSG_MAP

>     END_MESSAGE_MAP()

>     /////////////////////////////////////////////////////////////////////////////

>     // Fields message handlers

>     BOOL Fields::OnInitDialog()

>     {

>         CDialog::OnInitDialog();

>         // TODO: Add extra initialization here

>         // Set up your CDaoTabledef object!

>         // Update the list boxes... Forces a refresh of dialog, too!

>         OnMapTables();

>         return TRUE;  // Return TRUE unless you set the focus to a control

>                       // EXCEPTION: OCX Property Pages should return FALSE

>     }

>     void Fields::OnSelchangeList1()

>     {

>         // TODO: Add your control notification handler code here

>         m_List2.SetCurSel(m_List1.GetCurSel());

>         m_List3.SetCurSel(m_List1.GetCurSel());

>         m_List4.SetCurSel(m_List1.GetCurSel());

>         m_List5.SetCurSel(m_List1.GetCurSel());

>         m_List6.SetCurSel(m_List1.GetCurSel());

>     }

>     void Fields::OnSelchangeList2()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List2.GetCurSel());

>         m_List3.SetCurSel(m_List2.GetCurSel());

>         m_List4.SetCurSel(m_List2.GetCurSel());

>         m_List5.SetCurSel(m_List2.GetCurSel());

>         m_List6.SetCurSel(m_List2.GetCurSel());

>     }

>     void Fields::OnSelchangeList3()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List3.GetCurSel());

>         m_List2.SetCurSel(m_List3.GetCurSel());

>         m_List4.SetCurSel(m_List3.GetCurSel());

>         m_List5.SetCurSel(m_List3.GetCurSel());

>         m_List6.SetCurSel(m_List3.GetCurSel());

>     }

>     void Fields::OnSelchangeList4()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List4.GetCurSel());

>         m_List2.SetCurSel(m_List4.GetCurSel());

>         m_List3.SetCurSel(m_List4.GetCurSel());

>         m_List5.SetCurSel(m_List4.GetCurSel());

>         m_List6.SetCurSel(m_List4.GetCurSel());

>     }

>     void Fields::OnSelchangeList5()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List5.GetCurSel());

>         m_List2.SetCurSel(m_List5.GetCurSel());

>         m_List3.SetCurSel(m_List5.GetCurSel());

>         m_List4.SetCurSel(m_List5.GetCurSel());

>         m_List6.SetCurSel(m_List5.GetCurSel());

>     }

>     void Fields::OnSelchangeList6()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List6.GetCurSel());

>         m_List2.SetCurSel(m_List6.GetCurSel());

>         m_List3.SetCurSel(m_List6.GetCurSel());

>         m_List4.SetCurSel(m_List6.GetCurSel());

>         m_List5.SetCurSel(m_List6.GetCurSel());

>     }

>     void Fields::OnMapTables()

>     {

>         // TODO: Add your control notification handler code here

>         int nIndex = 0;

>         CDaoFieldInfo   fieldinfo;

>         CString Formatted;

>         CDaoTableDef td(m_pDatabase);

>         m_List1.ResetContent();

>         m_List2.ResetContent();

>         m_List3.ResetContent();

>         m_List4.ResetContent();

>         m_List5.ResetContent();

>         m_List6.ResetContent();

>         td.Open(_T("Student"));

>         m_Database = td.GetName();

>         for (nIndex = 0; nIndex < td.GetFieldCount(); ++nIndex)

>         {

>             // First, get the necessary information to work with!

>             td.GetFieldInfo(nIndex, fieldinfo, AFX_DAO_ALL_INFO);

>             // Process and format the data

>             m_List1.AddString(fieldinfo.m_strName);

>             switch(fieldinfo.m_nType)

>             {

>                 case dbBoolean:

>                     m_List2.AddString("Boolean");

>                     break;

>                 case     dbByte:

>                     m_List2.AddString("Byte");

>                     break;

>                 case     dbInteger:

>                     m_List2.AddString("Short");

>                     break;

>                 case     dbLong:

>                     m_List2.AddString("Long");

>                     break;

>                 case     dbCurrency:

>                     m_List2.AddString("Currency");

>                     break;

>                 case     dbSingle:

>                     m_List2.AddString("Single");

>                     break;

>                 case     dbDouble:

>                     m_List2.AddString("Double");

>                     break;

>                 case     dbDate:

>                     m_List2.AddString("Date/Time");

>                     break;

>                 case     dbText:

>                     m_List2.AddString("Text");

>                     break;

>                 case     dbLongBinary:

>                     m_List2.AddString("Long Binary (OLE Object)");

>                     break;

>                 case     dbMemo:

>                     m_List2.AddString("Memo");

>                     break;

>                 case     dbGUID:

>                     m_List2.AddString("A GUID");

>                     break;

>                 default:

>                     m_List2.AddString("Unknown field type");

>                     break;

>             }

>             Formatted.Format("%d", fieldinfo.m_lSize);

>             m_List3.AddString(Formatted);

>             Formatted.Format("0x%8.8X", fieldinfo.m_lCollatingOrder);

>             m_List4.AddString(Formatted);

>             Formatted.Format("0x%8.8X", fieldinfo.m_lAttributes);

>             m_List5.AddString(Formatted);

>             Formatted.Format("%d", (long)fieldinfo.m_nOrdinalPosition);

>             m_List6.AddString(Formatted);

>         }

>         m_List1.SetCurSel(0);

>         m_List2.SetCurSel(0);

>         m_List3.SetCurSel(0);

>         m_List4.SetCurSel(0);

>         m_List5.SetCurSel(0);

>         m_List6.SetCurSel(0);

>         // Force an update of the dialog box!

>         UpdateData(FALSE);

>     }
> When you double-click an item in the Field Name list box, the Fields class CDialog procedure displays the properties of members of the Fields collection for the predefined table, as shown in Figure 6.9. Figure 6.9. Properties of members of the Fields collection of the Students table of Stdreg32.mdb. Listing 6.3 shows the C++ code needed to add the capability to list in the DataDict application properties of the Index objects of a given table of a Jet database. This is done as a third dialog box and is very similar to the one shown in Listing 6.2. Listing 6.3. The added procedures needed by the DataDict application to list properties of Index objects.
>     // Indexes.cpp : implementation file

>     //

>     #include "stdafx.h"

>     #include "test.h"

>     #include "Indexes.h"

>     #ifdef _DEBUG

>     #define new DEBUG_NEW

>     #undef THIS_FILE

>     static char THIS_FILE[] = __FILE__;

>     #endif

>     /////////////////////////////////////////////////////////////////////////////

>     // Indexes dialog

>     Indexes::Indexes(CWnd* pParent /*=NULL*/)

>         : CDialog(Indexes::IDD, pParent)

>     {

>         //{{AFX_DATA_INIT(Indexes)

>         m_Database = _T("");

>         //}}AFX_DATA_INIT

>         m_pDatabase = NULL;

>     }

>     void Indexes::DoDataExchange(CDataExchange* pDX)

>     {

>         CDialog::DoDataExchange(pDX);

>         //{{AFX_DATA_MAP(Indexes)

>         DDX_Control(pDX, IDC_LIST6, m_List6);

>         DDX_Control(pDX, IDC_LIST5, m_List5);

>         DDX_Control(pDX, IDC_LIST4, m_List4);

>         DDX_Control(pDX, IDC_LIST3, m_List3);

>         DDX_Control(pDX, IDC_LIST2, m_List2);

>         DDX_Control(pDX, IDC_LIST1, m_List1);

>         DDX_Text(pDX, IDC_DATABASE, m_Database);

>         //}}AFX_DATA_MAP

>     }

>     BEGIN_MESSAGE_MAP(Indexes, CDialog)

>         //{{AFX_MSG_MAP(Indexes)

>         ON_LBN_SELCHANGE(IDC_LIST1, OnSelchangeList1)

>         ON_LBN_SELCHANGE(IDC_LIST2, OnSelchangeList2)

>         ON_LBN_SELCHANGE(IDC_LIST3, OnSelchangeList3)

>         ON_LBN_SELCHANGE(IDC_LIST4, OnSelchangeList4)

>         ON_LBN_SELCHANGE(IDC_LIST5, OnSelchangeList5)

>         ON_LBN_SELCHANGE(IDC_LIST6, OnSelchangeList6)

>         //}}AFX_MSG_MAP

>     END_MESSAGE_MAP()

>     /////////////////////////////////////////////////////////////////////////////

>     // Indexes message handlers

>     BOOL Indexes::OnInitDialog()

>     {

>         CDialog::OnInitDialog();

>         // TODO: Add extra initialization here

>         // Set up your CDaoTabledef object!

>         // Update the list boxes... Forces a refresh of dialog, too!

>         OnMapTables();

>         return TRUE;  // Return TRUE unless you set the focus to a control

>                       // EXCEPTION: OCX Property Pages should return FALSE

>     }

>     void Indexes::OnSelchangeList1()

>     {

>         // TODO: Add your control notification handler code here

>         m_List2.SetCurSel(m_List1.GetCurSel());

>         m_List3.SetCurSel(m_List1.GetCurSel());

>         m_List4.SetCurSel(m_List1.GetCurSel());

>         m_List5.SetCurSel(m_List1.GetCurSel());

>         m_List6.SetCurSel(m_List1.GetCurSel());

>     }

>     void Indexes::OnSelchangeList2()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List2.GetCurSel());

>         m_List3.SetCurSel(m_List2.GetCurSel());

>         m_List4.SetCurSel(m_List2.GetCurSel());

>         m_List5.SetCurSel(m_List2.GetCurSel());

>         m_List6.SetCurSel(m_List2.GetCurSel());

>     }

>     void Indexes::OnSelchangeList3()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List3.GetCurSel());

>         m_List2.SetCurSel(m_List3.GetCurSel());

>         m_List4.SetCurSel(m_List3.GetCurSel());

>         m_List5.SetCurSel(m_List3.GetCurSel());

>         m_List6.SetCurSel(m_List3.GetCurSel());

>     }

>     void Indexes::OnSelchangeList4()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List4.GetCurSel());

>         m_List2.SetCurSel(m_List4.GetCurSel());

>         m_List3.SetCurSel(m_List4.GetCurSel());

>         m_List5.SetCurSel(m_List4.GetCurSel());

>         m_List6.SetCurSel(m_List4.GetCurSel());

>     }

>     void Indexes::OnSelchangeList5()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List5.GetCurSel());

>         m_List2.SetCurSel(m_List5.GetCurSel());

>         m_List3.SetCurSel(m_List5.GetCurSel());

>         m_List4.SetCurSel(m_List5.GetCurSel());

>         m_List6.SetCurSel(m_List5.GetCurSel());

>     }

>     void Indexes::OnSelchangeList6()

>     {

>         // TODO: Add your control notification handler code here

>         m_List1.SetCurSel(m_List6.GetCurSel());

>         m_List2.SetCurSel(m_List6.GetCurSel());

>         m_List3.SetCurSel(m_List6.GetCurSel());

>         m_List4.SetCurSel(m_List6.GetCurSel());

>         m_List5.SetCurSel(m_List6.GetCurSel());

>     }

>     void Indexes::OnMapTables()

>     {

>         // TODO: Add your control notification handler code here

>         int nIndex = 0;

>         CDaoIndexInfo    indexinfo;

>         CString Formatted;

>         CDaoTableDef td(m_pDatabase);

>         m_List1.ResetContent();

>         m_List2.ResetContent();

>         m_List3.ResetContent();

>         m_List4.ResetContent();

>         m_List5.ResetContent();

>         m_List6.ResetContent();

>         td.Open(_T("Student"));

>         m_Database = td.GetName();

>         for (nIndex = 0; nIndex < td.GetIndexCount(); ++nIndex)

>         {

>             // First, get the necessary information to work with!

>             td.GetIndexInfo(nIndex, indexinfo, AFX_DAO_ALL_INFO);

>             // Process and format the data

>             m_List1.AddString(indexinfo.m_strName);

>             Formatted.Format("%d", (long)indexinfo.m_nFields);

>             m_List2.AddString(Formatted);

>             m_List3.AddString(indexinfo.m_bPrimary ? "Primary" : "Not-primary");

>             m_List4.AddString(indexinfo.m_bUnique ? "Unique" : "Not-unique");

>             m_List5.AddString(indexinfo.m_bIgnoreNulls ?

>                  "Ignore Nulls" : "Nulls significant");

>             m_List6.AddString(indexinfo.m_bForeign ? "Foreign" : "Not-foreign");

>         }

>         m_List1.SetCurSel(0);

>         m_List2.SetCurSel(0);

>         m_List3.SetCurSel(0);

>         m_List4.SetCurSel(0);

>         m_List5.SetCurSel(0);

>         m_List6.SetCurSel(0);

>         // Force an update of the dialog box!

>         UpdateData(FALSE);

>     }
> Figure 6.10 shows the properties of the members of the Indexes collection of Access 7's Stdreg32.mdb database file. Figure 6.10. Properties of members of the Indexes collection of the Title Author table of Stdreg32.mdb. ### Using the QueryDefs Collection and QueryDef Objects QueryDef objects are Jet's equivalent of the stored procedures offered by most client-server RDBMSs. Like stored procedures, QueryDef objects executed against Jet databases are optimized to achieve maximum performance. If you create a Recordset object by specifying an SQL statement as the value of the Source argument when applying the OpenRecordset method to the Database object, Jet must run the SQL statement through the query optimizer. MFC's implementation of QueryDef objects is done using the MFC class CDaoQueryDef. QueryDef objects are the only method of executing an SQL pass-through query against a client-server RDBMS. An SQL pass-through query sends the SQL statement directly to the RDBMS for execution. You use the following syntax to create a new CDaoQueryDef class object:
>     CDaoQueryDef( CDaoDatabase* pDatabase);
> The parameter pDatabase is a pointer to an open CDaoDatabase object. The following example creates a QueryDef object named qryPubs that, when executed, returns all the records of the Students table of Stdreg32.mdb:
>     CDaoDatabase db;

>         db.Open("Stdreg32.mdb");

>         CDaoQueryDef qd(db);

>         qd.Create(_T("Students"), _T("SELECT * FROM Students")
> QueryDef objects have many properties in common with TableDef objects. Table 6.14 lists the properties of the QueryDef object. The Name and SQL properties appear first in Table 6.14 because these are the two most commonly used properties when you create a QueryDef object. Chapter 5, "Learning Structured Query Language," describes the Jet and ANSI dialects of SQL. QueryDef objects have a Fields collection that is identical to the Fields collection of the TableDef object, except that the Name property of a Field object can use an alias created by the SQL AS reserved word. The SourceTable and SourceField property values of Fields of QueryDefs reflect the table name and field name, respectively, of the source tables for the query. Table 6.14 shows where the property is found in MFC's DAO implementation. Table 6.14. Properties of the QueryDef Object.
> Property | MFC DAO Location | Description
> Name | CDaoQueryDef::GetName() | The given name for a query. The default value is an empty string.
> SQL | CDaoQueryDef::GetSQL() | The SQL statement to execute the query. Requires Jet SQL syntax unless the query is an SQL pass-through query, in which case the SQL syntax is that for the server RDBMS.
> Connect | CDaoQueryDef::SetConnect() | Used only to create an SQL pass-through query. A connect string beginning with ODBC; is required.
> DateCreated | CDaoQueryDef::GetDateCreated() | The date that the QueryDef was created with the CreateQueryDef method of the Database object, in MFC's DAO, CDaoQueryDef::Create().
> LastUpdated | CDaoQueryDef::GetDateLastUpdated() | The date that the QueryDef was last updated by modifying the value of one or more of its properties.
> LogMessages | Not supported directly | Set to TRUE to create a table in the database to which ODBC messages are added if the ODBC driver for your client server RDBMS supports logging messages. (You must use the CreateProperty method to add this property to a QueryDef object.)
> ODBCTimeout | CDaoQueryDef::SetODBCTimeout() | Sets the number of seconds before Jet generates an error when attempting to execute a query against a client-server RDBMS. (Default value is 60 seconds.)
> RecordsAffected | CDaoQueryDef::GetRecordsAffected() | The number of records affected by successful execution of an APPEND, UPDATE, or DELETE query.
> ReturnsRecords | CDaoQueryDef::GetReturnsRecords() | For SQL pass-through queries (only). Returns TRUE if executing a SELECT query or a stored procedure that returns records. Otherwise, returns FALSE.
> Type | CDaoQueryDef::GetType() | Specifies the type of query by the value of an Integer flag (see Table 6.15).
> Updatable | CDaoQueryDef::CanUpdate() | TRUE if the properties of the QueryDef can be altered, which is usually the case.
> Table 6.15. Values of the Type flag for the QueryDef object (from dbdaoint.h).
> Constant | Value | Type of Query | SQL Reserved Words
> dbQSelect | 0 | Select | SELECT
> dbQCrosstab | 16 | Crosstab | TRANSFORM...PIVOT
> dbQDelete | 32 | Delete | DELETE
> dbQUpdate | 48 | Update | UPDATE
> dbQAppend | 64 | Append | INSERT
> dbQMakeTable | 80 | Make-table | INSERT INTO
> dbQDDL | 96 | Data-definition | CREATE ...
> dbQSQLPassThrough | 112 | SQL pass-through |
> dbQSetOperation | 128 | Union |
> dbQSPTBulk | 144 | Used with dbQSQLPassThrough to specify a query that doesn't return records (112 + 144 = 256) |
> dbQAction | 240 | Action | DELETE, UPDATE, INSERT, INSERT INTO, CREATE ...
> The Value column lists decimal, not hexadecimal, values because Type flags for QueryDef objects aren't used in combination (except for SQL pass-through queries that don't return records). Table 6.16 lists, in the order of most frequent use, the methods of the QueryDef object. Since a CDaoQueryDef class object doesn't return records, you must create a CDaoRecordset object to retrieve records from the database when using a CDaoQueryDef object. Table 6.16. Methods applicable to the QueryDef object.
> Method | MFC's DAO Function | Purpose
> OpenRecordset | Not directly supported | Opens a Recordset object over the result set of a SELECT query or an SQL pass-through query that returns records. Use CDaoRecordset::Open(CDaoQueryDef* pQueryDef, int nOpenType = dbOpenDynaset, int nOptions = 0);, which takes a pointer to a CDaoQueryDef object.
> Execute | Execute() | Executes a query that doesn't return records, such as an action or DDL query. (Replaces the ExecuteSQL method of Visual C++ 2.0 and 3.0.)
> CreateProperty | Not directly supported | Lets you add the LogMessages property or a user-defined property to a QueryDef.
> Applying the OpenRecordset method to a QueryDef object that returns records executes the query and creates a Recordset over the query result set. Here is the general syntax of the OpenRecordset method of the QueryDef object:
>     CDaoDatabase db;

>     CDaoRecordset rs;

>         db.Open("Stdreg32.mdb");

>         CDaoQueryDef qd(db);

>         qd.Create(_T("Students"), _T("SELECT * FROM Students")

>         rs.Open(qd, /*nOpenType*/ dbOpenDynaset, /*nOptions*/ 0 );
> The value of the nOpenType argument can be either dbOpenDynaset (2) or dbOpenSnapshot (4); you can't open a Table-type Recordset over a QueryDef result set. Table 6.17 lists the values of flags for the nOptions argument. Constants marked with an X in the RS column apply to the OpenRecordset method. The general syntax of the Execute method of the QueryDef object is
>     qdfName.Execute([nOptions]);
> Table 6.17 also includes the values of flags for the nOptions argument of the Execute method. Only constants marked with an X in the EX column apply to the Execute method. Table 6.17. Values of flags for the nOptions argument of the CDaoRecordset class object.
> Constant | RS | EX | Value | Purpose
> dbDenyWrite | X | X | 0x1 | Denies write permission to others while the query executes or while a Recordset is open.
> dbDenyRead | X |  | 0x2 | Denies read permission to others while the query executes or while a Recordset is open.
> dbReadOnly | X |  | 0x4 | Creates a read-only (nonupdatable) Recordset.
> dbAppendOnly | X |  | 0x8 | Allows only INSERT queries (for data entry).
> dbInconsistent | X | X | 0x10 | Permits inconsistent updates, the ability to change values in the primary key field of a query with a one-to-many relationship (default).
> dbConsistent | X | X | 0x20 | Requires consistent updates. (Can't change values in the primary key field of a query with a one-to-many relationship.)
> dbSQLPassThrough | X | X | 0x40 | Specifies an SQL pass-through operation.
> dbFailOnError |  | X | 0x80 | Rolls back updates if an error occurs (creates a trappable error).
> dbForwardOnly | X |  | 0x100 | Creates a forward-scrolling (only) Recordset of the Snapshot type. Forward scrolling provides very fast performance with the MoveNext method of the Recordset.
> dbSeeChanges | X | X | 0x200 | Causes a trappable error if another user simultaneously changes the data to be updated by the query.
> QueryDef objects also have a Parameters collection that define user-replaceable criteria for the query. Parameter objects have Name, Type (field data type), and Value properties. With the exception of the Remote Data Object, use of replaceable parameters in queries is of limited utility in Visual C++ database applications. It's more straightforward to alter the SQL property of a QueryDef than to use members of the Parameters collection. The Parameters collection is intended primarily for use in interactive entry of parameter values in Access applications. ### Creating Tables with C++ Code You can add new tables to a Database object with Visual C++ code by using the TableDefs collection (CDaoTableDef) and adding a new TableDef object that describes the new table. You must add at least one Field object to a TableDef object before appending the new TableDef to the TableDefs collection. You also can append new Index objects to the Indexes collection of a TableDef object. Table 6.18 lists the methods applicable to the TableDefs (the TDF column), QueryDefs (QDF), Fields (FLD), and Indexes (IDX) collections. Only Table objects contain the Indexes collection. Table 6.18. Methods applicable to the TableDefs, QueryDefs, Fields, and Indexes collections.
> Method | TDF | QDF | FLD | IDX | Purpose
> Refresh | X | X | X | X | Requeries the structure of the underlying persistent objects to make the values of the members of the collection current.
> Append | X | X | X | X | Adds a new member to the collection to create a new table, or adds a new field or index to an existing table of a database.
> Delete | X | X |  | X | Deletes a TableDef object from a TableDefs collection (deletes the corresponding table from the database) or deletes an Index object from an Indexes collection (deletes the corresponding index in the database).
> Using SQL's Data Definition Language, introduced in Jet 2.0, is a much more straightforward method of creating new tables, adding indexes, and establishing relationships between tables. Jet 2+ supports SQL's CREATE TABLE, CONSTRAINT, REFERENCES, CREATE INDEX, ALTER TABLE, DROP TABLE, and DROP INDEX reserved words, so you can use SQL statements to perform the same operations used by members of the TableDefs, Fields, Indexes and Relations collections. Visual C++ 4.0's Books Online help provides examples of the use of each of these SQL reserved words. ### Creating and Using Recordset Objects As noted earlier in this chapter, the Recordset object comes in three different types: Table, Dynaset, and Snapshot. When you create a project with AppWizard, you get to choose from these three choices. Here is the general syntax for creating a Recordset object (CDaoRecordset) over a table in database:
>     CDaoDatabase db;

>     CDaoRecordset rs;

>         db.Open("Stdreg32.mdb");

>         CDaoTableDef td(db);

>         td.Create(_T("Students"));

>         rs.Open(td, /*nOpenType*/ dbOpenDynaset, /*nOptions*/ 0 );
> If you don't specify the nOpenType argument, Jet opens a Table-type Recordset (nOpenType = dbOpenTable). The flags for the optional nOptions argument are listed in Table 6.17. To open a Recordset based on an SQL query, use the following syntax:
>     CDaoDatabase db;

>         db.Open("Stdreg32.mdb");

>         CDaoRecordset rs(db);

>         rs.Open(AFX_DAO_USE_DEFAULT_TYPE, _T("SELECT * FROM Students")));
> In this case, Jet opens a Dynaset-type Recordset unless you set the value of nOpenType to dbOpenSnapshot or set one of the nOption flags to create a Snapshot-type Recordset. The syntax for opening a Recordset over a QueryDef result set was provided in the section "Using the QueryDefs Collection and QueryDef Objects." The following sections describe in detail the properties and methods that apply to Recordset objects of each of the three types. #### Properties of Recordset Objects Table 6.19 lists the properties that are applicable to the Table, Dynaset, and Snapshot types of Recordset objects The entries in the TBL, DS, and SS columns in Table 6.11 indicate whether the property is applicable to Recordsets of the Table, Dynaset, or Snapshot type, respectively. Related properties listed in Table 6.19 are grouped by function, and properties that are new to Jet 2.x and 3.0 are noted by an asterisk (*). Table 6.19. The properties of the Recordset object.
> Property | CDaoRecords et Member | TBL | DS | SS | Descriptio n
> Name | CDaoRecordset::GetName() | X | X | X | Name of the Recordset (read-only). Same as the Name property of the corresponding TableDef object for the Table type.
> !Field.Value | CDaoRecordset::GetFieldValue() | X | X | X | The value (content) of the data in the Field of the current record.
> Type | CDaoRecordset::GetType() | X | X | X | The type of Recordset object defined by the integer dbOpenTable, dbOpenDynaset, or dbOpenSnapshot constants.
> DateCreated | CDaoRecordset::GetDateCreated() | X |  |  | The date and time that a Table object was originally created (read-only).
> LastUpdated | CDaoRecordset::GetDateLastUpdated() | X |  |  | The date and time that the last change was made to the data in a Table object (read-only).
> Updatable | CDaoRecordset::CanUpdate() | X | X |  | TRUE if access to the underlying data is read-write; FALSE if access is read-only. The Updatable property of Snapshot types is, by definition, FALSE. (If the Database object is opened read-only, all Table and Dynaset types in the database are read-only.)
> Restartable* | CDaoRecordset::CanRestart() |  | X | X | TRUE if the Requery method is supported; FALSE otherwise. (You must recreate the Recordset to refresh its content.)
> Transactions | CDaoRecordset::CanTransact() | X | X |  | TRUE if the Table or Dynaset type supports rolling back of transactions; FALSE otherwise (read-only).
> Bookmarkable | CDaoRecordset::CanBookmark() | X | X | X | TRUE if you can use the Bookmark property to specify a particular record in the Recordset; FALSE otherwise (read-only). Only Jet databases support Bookmarks.
> Bookmark | CDaoRecordset::SetBookmark() | X | X | X | A binary value that indicates (points to) a specific record. A Bookmark's value is set and returned as a CString.
> LastModified | CDaoRecordset::GetLastModifiedBookmark() | X | X |  | A Bookmark value that points to the record in the Table object that was updated most recently (read-only).
> Index | CDaoRecordset::GetIndexInfo() | X |  |  | The name of the active index of the table that determines the order in which the records of the table appear. The value of the Index property can be a predetermined index name (such as PrimaryKey) or the name of a field on which the index was created.
> Sort | CDaoRecordset::m_strSort |  | X | X | The names of one or more fields of a Dynaset or Snapshot object that determine the order in which the records of a Dynaset or Snapshot object that is created from the sorted Dynaset or Snapshot object appear. The sort statement consists of the ORDER BY clause of an SQL statement without the ORDER BY reserved words.
> RecordCount | CDaoRecordset::GetRecordCount() | X | X | X | The number of records in a Recordset (read-only). In a multiuser environment, the value of RecordCount may be an approximate number, depending on the frequency with which the Recordset is updated. You can obtain an accurate, instantaneous value of the RecordCount property by applying the MoveLast method immediately before reading the value of the RecordCount property.
> AbsolutePosition* | CDaoRecordset::GetAbsolutePosition() |  |  |  |
> | CDaoRecordset::SetAbsolutePosition() |  | X | X | A Long value that sets or gets the position of the record pointer (the first record is 0).
> PercentPosition* | CDaoRecordset::GetPercentPosition() |  |  |  |
> | CDaoRecordset::SetPercentPosition() |  | X | X | A Single value that sets or gets the position of the record pointer as a percentage of the total number of records (0.00 to 100.00).
> BOF | CDaoRecordset::IsBOF() | X | X | X | TRUE when the record pointer is positioned before (above) the first record of a Recordset; FALSE otherwise (read-only).
> EOF | CDaoRecordset::IsEOF() | X | X | X | TRUE when the record pointer is positioned after (below) the last record of a Recordset; FALSE otherwise (read-only). When both BOF and EOF return TRUE, there are no records in the Recordset (rsName.RecordCount = 0).
> CacheSize* | CDaoRecordset::GetCacheSize() |  |  |  |
> | CDaoRecordset::SetCacheSize() |  | X |  | A Long value (between 5 and 1200) that sets or gets the number of records from an ODBC data source (only) that are stored in memory (cached). 100 is the typical setting.
> CacheStart* | CDaoRecordset::GetCacheStart() |  |  |  |
> | CDaoRecordset::SetCacheStart() |  | X |  | A CString value that gets or sets the Bookmark value of the first record to be cached.
> NoMatch | CDaoRecordset::Seek() | X | X | X | Seek() returns a nonzero value if a record meets the Seek method's criteria (Table objects only) or one of the Find... method's criteria (Dynaset and Snapshot types only). Zero otherwise.
> Filter | CDaoRecordset::m_strFilter |  | X | X | One or more criteria that determine which records appear in a Dynaset or Snapshot type. The Filter property is the WHERE clause of an SQL statement without the WHERE reserved word.
> ValidationRule* | CDaoRecordset::GetValidationRule() | X | X | X | A table-level validation rule (read-only).
> ValidationText* | CDaoRecordset::GetValidationText() | X | X | X | The text that appears in a message box when the validation rule is broken (read-only).
> EditMode* | CDaoRecordset::GetEditMode() |  |  |  |
> | CDaoRecordset::SetEditMode() | X | X |  | int flags that indicate the editing status: dbEditNone (no editing in progress), dbEditInProgress (edited data are in the copy buffer), or dbEditAdd (AddNew method applied and tentative append record is in the copy buffer).
> LockEdits | CDaoRecordset::GetLockingMode() |  |  |  |
> | CDaoRecordset::SetLockingMode() | X | X |  | A nonzero value when locking is pessimistic, or a zero when optimistic locking is used.
> You can obtain the value of each of the properties listed in Table 6.19 that returns a value using the CDaoRecordset::Get...() form of the member function. Similarly, you can set the value of those properties (not indicated in the Description column as "read-only") using the CDaoRecordset::Set...() form of the member function. I've listed both the set and get forms where applicable in this table. Values of Recordset and other data access member objects are always of an appropriate data type for the data that will be returned. As mentioned earlier in this chapter, collections of Database objects, including the Recordsets collection, have only one property, Count. #### Methods Applicable to Recordset Objects and Collections Table 6.20 lists the methods applicable to Recordset objects in the same format as that used in Table 6.19 to list the properties of Recordsets. Properties that are new to Jet 2.x and 3.0 are noted by an asterisk (*). Table 6.20. Methods applicable to Recordset objects.
> Method | CDaoRecords et Member | TBL | DS | SS | Purpose
> Clone | Not directly supported | X | X | X | Creates a duplicate Recordset object with an independent record pointer.
> OpenRecordset* | CDaoRecordset::Open() | X | X | X | Opens a new Recordset based on the Recordset to which the method is applied.
> CopyQueryDef* | Not directly supported |  | X | X | Returns a copy of the QueryDef used to create the Recordset. Returns Null if the Recordset isn't based on a QueryDef.
> Requery | CDaoRecordset::Requery() |  | X | X | Recreates the content of the Recordset by re-executing the underlying query.
> Close | CDaoRecordset::Close() | X | X | X | Closes the Recordset.
> Edit | CDaoRecordset::Edit() | X | X | X | Prepares a Field object of a current record for updating. (Places a lock on the record or page if the value of the LockEdits property is TRUE.)
> AddNew | CDaoRecordset::AddNew() | X | X |  | Appends a new, empty record to a Table or Dynaset type. (Equivalent to xBase's APPEND BLANK.)
> Delete | CDaoRecordset::Delete() | X | X |  | Deletes the current record from a Table or Dynaset type.
> Update | CDaoRecordset::Update() | X | X |  | Causes pending updates to a Table or Dynaset type to be executed. (Removes the lock on the record or page.)
> CancelUpdate* | CDaoRecordset::CancelUpdate() | X | X |  | Cancels pending updates if applied prior to the Update method.
> Move* | CDaoRecordset::Move() | X | X | X | Moves the record pointer a specified number (Long) of rows.
> MoveFirst | CDaoRecordset::MoveFirst() | X | X | X | Positions the record pointer at the beginning of the Recordset.
> MoveNext | CDaoRecordset::MoveNext() | X | X | X | Positions the record pointer to the next record in the index (for Table objects) or sort order (for sorted Dynaset or Snapshot types).
> MovePrevious | CDaoRecordset::MovePrev() | X | X | X | Same as the MoveNext method, except in the opposite direction.
> MoveLast | CDaoRecordset::MoveLast() | X | X | X | Positions the record pointer at the end of the Recordset.
> Seek | CDaoRecordset::Seek() | X |  |  | Uses the current index of a Table object to position the record pointer to the first record that meets the criteria argument(s) of the method.
> FindFirst | CDaoRecordset::FindFirst() |  | X | X | Tests each record, beginning with the first record, of a Dynaset or Snapshot type for conformity with the criteria argument(s) of the method.
> FindNext | CDaoRecordset::FindNext() |  | X | X | Same as the FindFirst method, except that it tests records after the current matching record.
> FindPrevious | CDaoRecordset::FindPrev() |  | X | X | Same as the FindFirst method, except that it tests records before the current matching record.
> FindLast | CDaoRecordset::FindLast() |  | X | X | Same as the FindFirst method, except that it starts at the end of the first record and proceeds backward until a match is found.
> Here is the generalized syntax of statements that apply methods to Recordset objects:
>     rsName.MethodName([Argument1 [, Argument2 [, Argument#]]]);
Some methods, such as Update, have no arguments. Other methods, like FindFirst, have several arguments, some of which are optional. The next chapter provides the syntax for most CDaoRecordset classes and uses many of the methods listed in Table 6.20. ### Summary You need a thorough understanding of the member objects that constitute Visual C++ 4.0's Data Access Object class to develop commercial-quality Visual C++ database applications. This chapter began by diagramming the hierarchy of the 32-bit Data Access Objects, and then it progressed to a detailed explanation of the member functions of each of the data-related member objects. The DataDict sample application introduced you to the code you use to address collections and member objects at each level of the hierarchy. This chapter also is intended to serve as a reference source for the properties and the methods of objects of the Jet 3.0 Data Access Object. Chapter 13 discusses each of the major MFC DAO class. Chapter 14, "Using MFC's DAO Classes," contains examples of the MFC DAO classes in programs. Contact reference@earthweb.com with questions or comments. Copyright 1998 EarthWeb Inc., All rights reserved. PLEASE READ THE ACCEPTABLE USAGE STATEMENT. Copyright 1998 Macmillan Computer Publishing. All rights reserved. |