The Microsoft Jet Data Access Object (DAO) is Visual Basic 4.0's primary method of connecting to and manipulating data stored in desktop and client/server databases. DAO is the term used by Microsoft and in this book to describe the container
(the base or master class) for all of the data-related objects discussed in this chapter. Chapter 1, "Positioning Visual Basic 4.0 in the Database Front-End Market," gives you a brief introduction to the DAO and its member objects. This chapter,
"Understanding the Jet 3.0 Data Access Object," 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 Basic database applications are built. This
chapter features examples that use the DAO's member objects to create instances of objects with VBA 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'll have
learned more than you ever wanted to know about data-related, or more simply, data objects.
In the object-oriented terminology of OLE 2+, 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 color, size, or shape of an object. All the member functions are said to be encapsulated in the object. You don't need to know the internal operations of the object to create an instance
of and to manipulate the object in VBA code; all you need is a description of the properties and methods exposed by the object.
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, has many objects whose properties can be set only in design mode. Access 2.0, Access 95, and Visual Basic 4.0 have far fewer of these "frozen" objects. The ability to alter the value of properties of a few of the Visual Basic data access objects either in design mode or in run mode depends on whether you are using the Standard Edition or the Professional/Enterprise Edition of Visual Basic.
Visual Basic 4.0 gives you direct access to all of the member functions of its native programmable objects, such as form and control objects. 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 pre-defined interfaces. To access an OLE Automation server application, you create a reference to the object
in the References dialog, which you open with the Tools | References command. The Available References list box includes an entry for each OLE Automation server application that is registered in the Windows 95 or Windows NT Registry, or in
the registration database (REG.DAT) of Windows 3.1+. Figure 2.1 highlights the reference for the 32-bit Microsoft DAO 3.0 Object Library. Two other 32-bit data-related references appear in Figure 2.1: Microsoft DAO 2.5/3.5 Compatibility Library and
Microsoft Remote Data Object 1.0. The use of these data objects is the subject of the next section, "Bitness of Microsoft Jet Data Access Objects."
OLE Automation server applications are selective about which programmable objects and their 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 Basic. Once
you create a reference to an OLE Automation server object, Visual Basic's Object Browser provides a convenient list of the collections and objects exposed by the server, plus the member functions of each object. Figure 2.2 shows in the Methods/Properties
list the CreateDatabase method of the Workspace object of the Jet 3.0 DAO. 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. (See Figure 2.3.)
The syntax example for the CreateDatabase method displayed by the Object Browser in Figure 2.2 conflicts with the syntax shown in the online help topic of Figure 2.3, which is the correct version. There are a number of such conflicts involving the Jet 3.0 DAO. It's a better practice to rely on the syntax examples from online help than to trust those provided by the Object Browser.
The Data Access Object class in Visual Basic 4.0 is implemented by the DBEngine object and includes 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 contained within other objects are called member
objects of the container object. Visual Basic 4.0 treats member objects as properties of the container object. Figure 2.4 illustrates the hierarchy of Visual Basic 4.0's database objects. The collections shown in Figure 2.4 technically are not
objects; as mentioned in Chapter 1, collections are lists of the member objects of a particular class. The Databases, Tables, and Relationships containers are collections of Database, Table and QueryDef, and saved Relationships Document objects,
respectively. Access 2.0 and 95 also have Forms, Reports, Scripts (macros), and Modules Documents collections that are not supported in Visual Basic 4.0. In Visual Basic 4.0, Container objects and Documents collections are used to secure Jet databases in
conjunction with System.mdw workgroup files you create with Microsoft Access. (Visual Basic 4.0 can't create a workgroup file, previously called a system file.)
Figure 2.4. The hierarchy of Visual Basic 4.0's Jet 3.0 Data Access Object.
The following sections describe the three versions of the Jet DAO included with the Professional and Enterprise versions of Visual Basic 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 is not a settled issue. Detailed information on the properties and methods of the data objects discussed in the following sections appears later in this
chapter.
Depending on the edition of Visual Basic 4.0 you use, you have the choice of creating references to one or more of the following OLE Automation servers for data objects:
Figure 2.5 is a simplified diagram showing the relationships between 16-bit and 32-bit implementations of the Jet database engine used by Microsoft Access 1.1, 2.0 and 95, and Visual Basic 3.0 and 4.0.
Following are the rules for choosing the type of database and DAO for your Visual Basic 4.0 applications:
Use the database conversion feature of Microsoft Access to upgrade versions of .mdb files that contain Access-specific objects. Conversion of 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 95 format, the process is not reversible. As a rule, it is a safer practice to use the appropriate version of Access for all Jet database conversion operations.
You create an instance of the Data Access Object when you add a data control object to a Visual Basic form. An instance of an object is a reference (pointer) to the object, not the object itself. With the data access object, a single Database member of
the default Workspace object, Workspaces(0), becomes a property of the data control. You address the Database object of a data control with VBA statements that refer to DataControlName.Database.PropertyName. Only the data control object
automatically creates an instance of the DAO, which is called DataControlName in the preceding statement.
You create an instance of the DAO when you use a member of the DBEngine object's Workspaces collection. You create an instance of the Database object data type (object class) for an existing database in a two-step process, as follows:
The Dim reserved word is used in the examples of this chapter to create object variables. You also can use Public and Private to declare object variables of form or global scope, respectively, in the Declarations section of a VBA module. It is common to declare variables of the Workspace and Database types as Public variables to prevent the objects represented from unintentionally going out of scope.
The Set dbName = OpenDatabase(ConnectArguments) statement is similar to the Set dbName = CurrentDB() statement of Access 1.x and Access 95, except that there is no construct in Visual Basic 4.0
comparable to Access's "current database," which you must open in order to use all but a few Access functions. You must explicitly open an existing or a new database in Visual Basic 4.0—thus, Access 2.0's Set dbName =
DBEngine.Workspaces(0).Databases(0) or Set dbName = DBEngine(0)(0) instruction, which opens the current database, is not valid in Visual Basic 4.0.
When you declare a variable of one of the object data types, Visual Basic initially assigns the default Nothing value to the variable. Nothing is a keyword that indicates that the variable has not been initialized; the Nothing value for objects is similar to the Null value for the Variant data type. The Nothing value corresponds to the subtype 0 (Empty) Variant data type. You can use comparison operators with the Nothing value to determine if an object variable has been initialized, as in If objVariable = Nothing Then . . . You release the resources devoted to an object variable by assigning the object variable the Nothing value with a statement such as objVariable = Nothing.
You can create multiple instances of the same member object. Each instance of a member object is independent of any other instances of the object. For example, you can create multiple instances of the same database object in a single application by
adding a Data control for a single database to more than one form of a project. The Database object (property) of each data access object is identical for each instance, but the properties of member objects of each instance of the Database object, such as
the Recordset property of the Data control, are independent of other instances.
It is a generally accepted programming practice (GAPP) to place Dim. . .As statements at the beginning of a VBA procedure 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 are not certified by a standardizing body. GAPPs arise by tradition; declaring variables at the beginning of your procedure corresponds to the use of header (.H) files that precede source code files (.C or .CPP) in C and C++ code.
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:
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.
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 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 do not have a Name property. Recordset objects are stored in RAM and have no physical manifestation; you cannot 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 a \Windows\Temp folder. 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 two sections that follow.
Dynamic Recordset objects, like persistent member objects, reflect changes made by others in a multiuser environment to the persistent objects of the database. Therefore, 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. Following are the two types of dynamic Recordset objects that are based on virtual
tables:
The Recordset object replaces the Table, Dynaset, and Snapshot objects defined by Visual Basic 3.0 and Access 1.x. Although you can continue to use these obsolete objects if you reference the DAO 2.5/3.0 Compatibility Library (instead of the DAO 3.0 Object Library), use of obsolete object types is strongly discouraged. The next version of Visual Basic is unlikely to support these obsolete objects.
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 of the rows, which may take a
substantial amount of time if your Recordset contains thousands of rows.
The Data control returns control to your application after retrieving the first row. Additional rows needed to populate the visible elements of bound controls, such as the DBGrid control, are retrieved from the underlying persistent objects, a process called background population. As you move the record pointer, additional rows are retrieved to fill the visible elements.
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 intType argument of the OpenRecordset method.
Intrinsic global database constants that begin with db are discussed in the "Understanding Flags and Intrinsic Symbolic Constants" section, later in this chapter.
Snapshot-type Recordset objects retrieve every row from an 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 Recordset objects with
100 or fewer rows usually are faster than those of the Dynaset type.
As noted in the last two 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 and should therefore only 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. Therefore, the new value
would only be apparent 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 recreates the Recordset object to which it is applied; if the Recordset object is based on a QueryDef object, Refresh re-executes the query.
You should apply the Refresh method periodically to a Recordset object 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 a 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.
The DBEngine object has several properties and methods that are useful for advanced database applications. Table 2.1 lists the properties of the DBEngine object.
| Property | Purpose |
| DefaultUser | A write-only 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. (Formerly the strUserName argument of the obsolete SetDefaultWorkspace statement of Visual Basic 3.0.) |
| DefaultPassword | A write-only 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 ("").(Formerly the strPassword argument of the obsolete SetDefaultWorkspace statement of Visual Basic 3.0.) |
| IniPath (32-bit) | The full path and name of the Registry setting 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. The standard registry key for Visual Basic 4.0 is SystemDB=d:\path\filename.mdw in HKEY_USERS\Software\VB and VBA Program Settings\AppName. |
| IniPath (16-bit) | The well-formed path and filename of an Appname[.ini] file that provides the location of the System.mda system file for secure 16-bit Jet databases (SystemDB=) setting or parameters required for installable ISAMs used with other desktop databases. (Formerly the strIniPath argument of the obsolete SetDataAccessOption statement of Visual Basic 3.0.) |
| LoginTimeout | Gets or sets the number of seconds before an error is generated when attempting to log in to an ODBC data source. The default is 20 seconds. |
| Version | Gets the version number of the Jet database engine in use as a String (read-only). |
You must set DBEngine property values before opening a Workspace object that depends on the property values. Ordinarily, you place DBEngine.PropertyName = Value statements in the Form_Load event handler of the
opening form of your application.
Table 2.2 lists the methods applicable to the DBEngine object. The most commonly-used methods of the DBEngine object are Idle, CompactDatabase, and RepairDatabase.
| Method | Purpose |
| 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. (Replaces the obsolete FreeLocks statement of Visual Basic 3.0). |
| CompactDatabase | Compacts a Jet database that is not open in any application to save file space or to convert a Jet database from one version to another. Do not use Jet's CompactDatabase method to change the version of .mdb files that also are used by Microsoft Access applications. |
| RepairDatabase | Repairs, if possible, a damaged database. The syntax is dbEngine.RepairDatabase(strPathFile) |
| CreateWorkspace | Creates a new named Workspace object with a specified user ID and password. Although named, the new Workspace object is not persistent. |
| RegisterDatabase | Creates an entry in the Registry (32-bit) or in the ODBC.INI file (16-bit) for a named ODBC data source. |
The CompactDatabase, RepairDatabase, and RegisterDatabase methods replace obsolete Visual Basic 3.0 Database methods of the same name. The full syntax of the CompactDatabase method is this:
DBEngine.CompactDatabase(strOldPathFile, strNewPathFile, _ [strLocale[, intOptions]])
If you specify the same path and filename for strOldPathFile and strNewPathFile, the compacted file replaces the original file. The strLocale argument specifies the collating order of the file; the default value is dbLangGeneral
(;LANGID=0x0409;CP=1252;COUNTRY=0). The intOptions argument lets you specify the type of the file and whether to encrypt or decrypt the file with the constants listed in Table 2.3.
| Constant | Value | Purpose |
| dbVersion10 | 1 | Compact to a Microsoft Access 1.0 .mdb file |
| dbVersion11 | 8 | Compact to a Microsoft Access 1.1 .mdb file |
| dbVersion20 | 16 | Compact to a Jet 2.5 (Access 2.0) .mdb file |
| dbVersion30 | 32 | Compact to a Jet 3.0 (Access 95) .mdb file |
| dbEncrypt | 2 | Encrypt the compacted file |
| dbDecrypt | 4 | Decrypt an encrypted file |
The syntax of the RepairDatabase method is
DBEngine.RepairDatabase(strPathFile)
where strPathFile is the well-formed path to and the filename of the Jet .mdb database file you want to repair.
In the majority of cases, Jet databases are secured only in multiuser applications. Use of the DefaultUser, DefaultPassword, and IniPath properties and the Idle and CreateWorkspace methods are covered in Chapter 18, "Running Visual Basic Database
Front-Ends on Networks." The LoginTimeout property and RegisterDatabase method are two of the subjects of Chapter 19, "Understanding the Open Database Connectivity API," and Chapter 20, "Creating Front-Ends for Client/Server
Databases."
Most of the preceding sections of this chapter have been devoted to defining terms and classifying data objects by their behavior. The remainder of this chapter provides a detailed analysis of the properties of and the methods that apply to the
Workspace and Database objects and to member objects of the Database object, and how you use these properties and methods in your Visual Basic 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 either to create a named variable of the Database object data type or add a Data control to a form. As mentioned earlier in the chapter, you
can create a reference (pointer) to a named Database object with the following two lines of Visual Basic code:
Dim dbName As Database Set dbName = OpenDatabase(ConnectArguments)
The Workspace object 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, the OpenDatabase() and CreateDatabase() are methods of the Workspace object, because you cannot apply a method, such as OpenDatabase, to an object for which a reference does not exist. Therefore, the following syntax is in better keeping with
the principles of object-oriented programming:
Dim wsName As Workspace Dim dbName As Database Set wsName = DBEngine.Workspaces(0) Set dbName = wsName.OpenDatabase(ConnectArguments)
An additional benefit of creating the default Workspace 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. The features Jet (Access) security is one of the subjects of Chapter 18 and the use of Jet transactions is covered in Chapter 13, "Designing Online Transaction-Processing Systems." The Workspace object's IsolateODBCTrans,
Name, and UserName properties, which are seldom used in Visual Basic database applications, are described in Chapters 18 and 20. For completeness, Table 2.4 lists the methods of the Workspace object.
| Method | Purpose |
| OpenDatabase | Opens an existing database of any database type supported by Jet 3.0. |
| CreateDatabase | Creates a new Jet database of a version specified by the intOptions argument. |
| CreateGroup | Creates a new Group object that you can append to the Groups collection or to a User object. |
| CreateUser | Creates a new User object that you can append to the Users collection or to a Group object. |
| 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 | Specifies the end of persistent data object update operations that constitute a single transaction and cause the updates to be applied to the table(s) underlying the Recordset object(s). |
| Rollback | If the database type supports transactions (dbName.Transactions = True), cancels updating of the table objects(s) underlying the Recordset(s) by a preceding CommitTrans statement. |
| Close | Closes the specified Workspace, if all Database objects opened in the Workspace have been closed. |
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 2.5 lists the properties of the Database object, in the
approximate order of the frequency with which you are likely to use the properties. The last five properties listed in Table 2.5 are new in Visual Basic 4.0.
| Property | Purpose |
| Name | Returns a string containing the well-formed path to and the name of the open database. You can set or read the value of the Name property of the data control object. (See the section that follows, "Connecting to an Existing Jet Database.") |
| Connect | Returns the value of the string used to establish a connection to the database. The Connect property of a Database object is read-only and is an empty string for Access databases. You can set or read the Connect property of a data control object. (See "Connecting to an Existing Jet Database.") |
| Updatable | Indicates if the database has been opened in read-write (True) or read-only (False) mode. The Updatable property itself is read-only. (See "Connecting to an Existing Jet Database.") |
| Transactions | Indicates if the database supports the Rollback transaction processing statement, which 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 | 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. |
| CollatingOrder | An Integer flag that indicates the language whose rules are used by the database to sort text fields. The default value is 256 (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 CollatingOrder flag to be a Long integer.) |
| Version | Returns an Integer flag that indicates the version number of the Jet engine that created the database (see Table 2.4). |
| Replicable | When set True, creates a replicable database from which you can create additional replicas for distribution to users. Once made replicable, you cannot set the Replica property to False. Returns True for a replicable database. |
| DesignMasterID | A globally-unique ID (GUID) that identifies the replicable database from which user replicas are created. |
| ReplicaID | A GUID that identifies the replica database. |
| V1xNullBehavior | If set True, zero-length strings ("") in Jet (Access) 1.x fields of the Text and Memo type are converted to Null values. |
The replication properties, Replicable, DesignMasterID, and ReplicaID, of Table 2.5 do not appear in the Properties list of the online help topic for the Database object. Replication is limited to Jet 3.0 (Access 95) databases. The KeepLocal property applies to objects contained in a replicable database, not to the Database object itself. Replication of Jet 3.0 databases is one of the subjects of Chapter 12, "Extending Your Use of the Data Access Object."
You can use the value of the CollatingOrder property as the value of the optional intCompare argument of the InStr() and StrComp() functions, as shown in the following code fragment. Setting intCompare to the value of the
CollatingOrder property, other than to -1 (which represents an unknown or unsupported order), overrides the default text comparison method specified by the Option Compare statement in the Declarations section of a module.
Dim intCompare As Integer Dim intInStr As Integer Dim varStrComp As Variant intCompare = dbName.CollatingOrder intInStr = InStr(strSource, strTest, intCompare) varStrComp = StrComp(str1, str2, intCompare)
Using the value of the CollatingOrder property as the optional intCompare argument of the InStr() and StrComp() functions has the same effect as the default Option Compare Database statement in Access VBA. The only default sort options you can specify in Visual Basic are Binary (the default) and Text.
The Database object previously had many more methods than properties, but many of Visual Basic 3.0's methods of the Database object now are methods of the Workspace object. 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. The methods that you can apply to Database objects are listed in Table 2.6. The methods are listed in groups of related methods, rather than in the
conventional alphabetical order.
| Method | Purpose |
| CreateTableDef | Used in a Set statement to create a new persistent TableDef object that defines a newly-created table in a Database. |
| 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 | 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 | Updates collections of persistent objects of the Database, such as the QueryDefs and TableDefs collections, to reflect the current content of the collection. |
| CreateQueryDef | Used in a Set statement to create a new persistent QueryDef object based on an SQL statement. |
| Execute | Executes a QueryDef or SQL statement that does not return records, such as an UPDATE, INSERT, or DELETE query. Access uses the term action query to indicate a query that does not return records. |
| CreateProperty | Adds a user-defined property of a name you specify to the Database object and sets the data type and initial value of the user-defined property. |
| MakeReplica | 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 | 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 intExchange argument. |
| Close | Closes a Database object and frees resources consumed by the object. All member objects of the Database object must be closed before you can close the Database object. If you have declared your Database object variable with local scope, you do not need to use the Close method, because the Database object and its member object are closed when the variables go out of scope at the termination of the procedure in which the variables were declared. |
The full, generalized syntax of the OpenDatabase() method that you use to connect to and create a named reference to an existing database is as follows:
Set dbName = [wsName.]OpenDatabase(strDBPath["\" & _ strDBFile.ext][, fExclusive [, fReadOnly[, strConnect]]])
Table 2.7 lists the arguments used with the OpenDatabase method and the purpose of each of the arguments.
| Argument | Purpose |
| strDBPath | 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 of strDBPath. 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 \WINDOWS\ODBC.INI file. Alternatively, you can leave this argument empty for ODBC databases and provide the required connect string in the strConnect argument. |
| strDBName | Specifies the filename and extension of a Jet database (the .mdb extension is required) or the FILES.DDF data definition file of a Btrieve database. The strDBName component is not used when you connect to dBASE, FoxPro, or Paradox databases, or when you use a connect string to log in to ODBC databases for which you provide a value of the strConnect argument. |
| fExclusive | An Integer flag that determines if the database is to be opened in exclusive (True) or shared (False) mode. The default value is False. |
| fReadonly | 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. |
| strConnect | A character string whose content depends on the type of database you intend to open. The strConnect argument is not used for Jet databases, but it is required for other desktop databases. The strConnect argument is necessary to prevent the Login dialog of ODBC databases from appearing when you specify an ODBC data source. The data source name (DSN) contained in the strConnect argument overrides the DSN you specify as the value of the strDBPath argument. |
The following code fragment opens the Jet 2.5 Biblio.mdb sample database included with Visual Basic 4.0 for exclusive, read-only access:
Dim dbBiblio As Database
Set dbBiblio = _
DBEngine.Workspaces(0).OpenDatabase("biblio.mdb", _
True, True)
The following example creates a new Jet 2.5 (Access 2.0) database in the current directory:
Dim dbNew As Database
Set dbNew = _
DBEngine.Workspaces(0).CreateDatabase("new.mdb", _
dbLangGeneral, dbVersion25)
To make a new Jet database useful you must add tables to the database's TableDefs collection, then add fields and indexes to each table's Fields and Index collections. The sections that follow describe the properties and methods of the TableDef object
and its subsidiary objects.
The TableDefs collection contains a member TableDef object for each table in the database. 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 2.8 lists the properties of the TableDef object in the order in which the values might appear in a data dictionary.
| Property | Description of Property Value |
| Name | Name of the table. |
| Connect | The connect string used to attach tables to a Jet database; you can attach tables only to Jet .mdb databases. |
| SourceTableName | The name of an attached table; you can use the Name property to create an alias for an attached table. |
| ValidationRule | 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 where 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 | A String variable that contains the message to appear if an attempted update to the table would violate the ValidationRule property. (Jet 2.5 and 3.0 databases only.) |
| ConflictTable | Returns 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 | Date and time that the table was created (read-only). |
| LastUpdated | Date and time of the last modification to the data in the table (read-only). |
| Updatable | True if you have read-write access to the table; False for read-only access (read-only). |
| Attributes | A Long integer that contains the value of the option and status flags listed in Table 2.6. |
The value of the Attributes property consists of the sum of the value of the flags listed in Table 2.9. A flag uses the individual bits of an Integer (8 bits) or Long integer (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 2.6 shows the decimal and hexadecimal (hex, prefaced
with &H) values of each attribute value. Using hex values for the value of flags makes the concept of setting bits more evident.
| Attribute | Value | Description |
| dbSystemObject |
-2147483646
&H80000002 | Indicates that the table is a system table (read-only). |
| dbHiddenObject |
1
&H1 | Indicates that the table is a temporary hidden table that Jet uses for internal purposes (read-only). |
| dbAttachedTable |
1073741824
&H40000000 | 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 | Indicates that an attached |
| &H10000 | Access table has been opened for | |
| exclusive use by your application. | ||
| dbAttachedODBC |
536870912
&H20000000 | Indicates that the table is an ODBC table attached to an Access database. |
| dbAttachSavePWD |
131072
&H20000 | Indicates that the user name and password for the database from which the table is attached is included in the value of the Connect property. This means that the user of your application does not need to enter the user name and password for a secure database each time your application attaches the table. |
The negative decimal value of dbSystemObject appears because Long integer flags are in the unsigned long integer format (a double-word or dw data type in Windows terminology). All Object Basic dialects use the twos-complement method to create negative numbers. Therefore, values of &H80000000 and greater represent negative Long integer values, and values of &H8000 represent negative Integer values. Visual Basic does not provide either the unsigned integer or the unsigned long integer data type.
The values of flags in Windows applications are most commonly determined by the use of symbolic global constants. The symbol is the name of the constant. In Visual Basic, you ordinarily declare global constants in the Declarations section of a module
with a Public Const Symbol = Value [As DataType] statement. The symbolic constants for Visual Basic's data access object, each of which are prefaced by db, are intrinsic global constants
defined by the Constants collection of the version of the DAO referenced by your application. Figure 2.6 shows the Object Browser displaying the first few db. . . constants defined by the Microsoft (Jet) 3.0 DAO Object Library. You can use the Object
Browser to determine the numeric value of any of the db. . . constants by selecting the constant name in the Methods/Properties list.
Visual Basic 3.0 used global DB_ constants defined in the DATACONS.TXT file located in the \VB folder. The Microsoft DAO 2.5/3.0 Compatibility Library does not include declarations of the obsolete DB_ constants that, with the exception of the underscore and use of upper case, are identical to the db. . . constants of Jet 3.0.
If more than one attribute is applicable to the table, the attributes are combined with the Or operator, which performs an operation similar to decimal addition. (The difference between Or and + operators is that the Or
operator does not perform carry operations on bits involved in the addition.) Therefore, if an attached Jet table includes the password for the database in the Connect property (&H20000) and is opened for exclusive use (&H10000), the
value of the Attributes property is &H30000 or decimal 196608. This value can be created by the statement dbName.Attributes = dbAttachedSavePWD Or dbAttachExclusive. Using masks to determine the value of flags is discussed in the section
that follows.
It's a good programming practice always to use symbolic constants to represent the values of flags, even when the decimal value of the flag is a small number. Although you may 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 assigns 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.
You can use the member objects of the TableDefs collection 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 Basic 4.0, using list boxes to display the properties of each TableDef object and adding a few lines of Visual Basic code to populate (the object-oriented term for "fill") the list boxes.
For the most part, this book uses complete, self-contained sample database applications, rather than code snippets, to illustrate Visual Basic 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 sections or chapters. You start the first sample application, DataDict, in this section, and then add features that relate to
QueryDef objects. Figure 2.7 illustrates the design of the opening form, TableDef.frm, of the DataDict application.
The .mak, .exe, .frm, and .bas files for the DataDict application are included on the accompanying CD-ROM. If you have installed the files on the accompanying CD-ROM, the DataDict files are located in your \Ddg_vb4\32_bit\Chaptr02 folder, unless you specified a different location during the installation process. Each of the Visual Basic sample applications for this book are installed in separate subfolders of \Ddg_vb4\32_bit\Chaptr##, where ## is the chapter number. (Use a 0 and a single # for Chapters 2 through 9.)
The TableDefs collection, like other Database object collections, has only one property, Count. The value of Count is the number of members of the TableDefs collection. You use the Count property to dimension an array to hold the values of the
properties of each TableDef object or as the limiting value of a loop counter when you iterate the collection with a For. . .Next or Do While. . .Loop structure. You can refer to a member of any collection by an Integer
index value or by the value of the Name property of the member. Listing 2.1, which shows the code needed to populate the list boxes of TableDef.frm, uses the index value because you do not know the names of the tables at this point. The code in Listing 2.1
is designed for expansion of the DataDict application to display properties of other data objects in its list boxes.
Figure 2.7. The design of DataDict's TableDef form.
Listing 2.1. The code to populate the list boxes of TableDef.frm.
Option Explicit
'Form-level variables
Dim strTableName As String 'Name of selected table
Dim fIsFields As Boolean 'Flag, True for Fields
Dim fIsIndexes As Boolean 'Flag, True for Indexes
Dim dbMap As Database 'Declare a Database object variable
Dim intCtr As Integer 'Declare a general-purpose counter
Private Sub Command1_Click()
Set dbMap = DBEngine.Workspaces(0).OpenDatabase(Text1.Text)
Call TableProc
End Sub
Private Sub TableProc()
On Error GoTo TDError 'Set error handler
fIsFields = False 'Reset the fIsFields flag
fIsIndexes = False 'Reset the fIsIndexes flag
Label1.Caption = "Table Name:"
Label2.Caption = "Date Created:"
Label3.Caption = "Last Updated:"
Label4.Caption = "Updatable:"
Label5.Caption = "Attributes:"
Label6.Caption = "Validation Rule:"
frmTableDefs.Caption = "TableDefs Data Dictionary"
Call ClearLists
'Fill the list boxes, selectively display system objects
For intCtr = 0 To dbMap.TableDefs.Count - 1
If (dbMap.TableDefs(intCtr).Attributes And _
dbSystemObject) = 0 Or Check1.Value Then
List1.AddItem dbMap.TableDefs(intCtr).Name
List2.AddItem dbMap.TableDefs(intCtr).DateCreated
List3.AddItem dbMap.TableDefs(intCtr).LastUpdated
List4.AddItem dbMap.TableDefs(intCtr).Updatable
List5.AddItem "&H" & _
Hex$(dbMap.TableDefs(intCtr).Attributes)
List6.AddItem dbMap.TableDefs(intCtr).ValidationRule
End If
Next intCtr
Exit Sub
TDError:
'Provide an standard error handler
MsgBox Error$, , "TableDefs Error"
Exit Sub
End Sub
Private Sub List1_Click()
'Synchronize the selected items in the list boxes
List2.ListIndex = List1.ListIndex
List3.ListIndex = List1.ListIndex
List4.ListIndex = List1.ListIndex
List5.ListIndex = List1.ListIndex
List6.ListIndex = List1.ListIndex
End Sub
Private Sub ClearLists()
'Clear the content of the list boxes
List1.Clear
List2.Clear
List3.Clear
List4.Clear
List5.Clear
List6.Clear
End Sub
When you enter a valid argument (or set of arguments) in the Database text box (Text1) of frmTableDefs and click the Map Database button (Command1), the values of six of the properties of each TableDef object of the Database object appear in the list
boxes (List1. . . List6). Click a table name in List1 to select the corresponding entry in the other five list boxes. Figure 2.8 shows frmTableDefs displaying the TableDef objects of Biblio.mdb. The Validation Rule list box that displays the ValidationRule
property is empty, because none of the tables in Biblio.mdb includes a table-level validation rule.
Figure 2.8. Displaying the TableDefs collection for Biblio.mdb with system tables included.
Tables in Access databases whose names begin with MSys are system tables. Therefore, as an example, you would expect MSysMacros to have the Attributes value of &H8000000 that most of the other MSys. . . tables share, not &H2. MSysMacros is a system table that contains a definition of each macro object you create with Microsoft Access; therefore, MSysMacros is only useful with Access applications. Using the value of the dbSystemObject constant, &H80000002, includes MSysMacros and other Access-specific tables in the system table category. The dbMap.TableDefs(intCtr).Attributes And dbSystemObject) = 0 condition in the TableProc subprocedure excludes system tables from the listings.
TableDef, QueryDef, Recordset, and Relation objects all contain Fields collections. Table 2.10 lists the properties of the Field object. Some properties are valid for only one type of object or certain field data types.
| Property | Description |
| AllowZeroLength | If True, zero-length strings are allowed; otherwise, at least one character must be entered (Text or Memo fields only). |
| Attributes | The sum of flags that determine the characteristics of the field. (See Table 2.11.) |
| CollatingOrder | Specifies the sort order for text fields. |
| DataUpdatable | True if the field allows updates to data. |
| DefaultValue | The value automatically entered in a field when a new record is added. |
| ForeignName | The value of the Name property when the field is included in a relationship with another table. |
| Name | The given name of the field; 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 | The relative position of the field in the table, starting with 1 as the first field. |
| Required | If True, a non-Null entry is required; if AllowZeroLength is False, a character must be entered in a Text field. |
| Size | The size in bytes of the field: fixed for numeric and logical fields, 0 for Memo and OLE Object (LongBinary) fields, and 1–255 characters for Text fields. (Unicode has no affect on the value.) |
| SourceField | The name of the field of an attached table or of a table used in executing a QueryDef object. |
| SourceTable | The name of an attached table or a table used in executing a QueryDef object. |
| Type | An Integer designating the field data type of the field. (See Table 2.12.) |
| ValidateOnSet | 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 | 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 | The text that appears in a message box that appears when a field-level validation rule is broken. |
| Attributes Flag | Value | Description |
| dbFixedField | &H1 | Indicates that the length of the field is fixed, not a Text, Date/Time, Memo, Binary, or Long Binary field. |
| dbVariableField | &H2 | Indicates that the length of the field is variable, not a Number, Date/Time or Boolean field. |
| dbAutoIncrField | &H10 | 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 | &H20 | Indicates that the data in the field or the structure of the field can be modified. |
| Data Type Constant | Value | Field and Fundamental Data Type |
| dbBoolean | 1 | Boolean, Yes/No, Logical (Integer) |
| dbByte | 2 | Number, Byte, tinyint (Integer) |
| dbInteger | 3 | Number, smallint (Integer) |
| dbLong | 4 | Number, int (Long) |
| dbCurrency | 5 | Money, decimal, fixed-point (Currency) |
| dbSingle | 6 | Number, single-precision float (Single) |
| dbDouble | 7 | Number, double-precision float (Double) |
| dbDate | 8 | Date/Time, timestamp (Variant, subtype 7) |
| dbBinary | 9 | Binary, varbinary (Not implemented) |
| dbText | 10 | Text, varchar (String) |
| dbLongBinary | 11 | Binary, long varbinary, OLE Object |
| dbMemo | 12 | Text, long varchar (String) |
Only the TableDef object contains an Indexes collection. Table 2.13 lists the properties of the Index object.
| Property | Description | Data Type |
| Name | The name of the index, unique within a table. | String |
| Fields | The names of field(s) that comprise the index, preceded by a sign that indicates the sort order (+ for ascending, - for descending). If the index has more than one field, the field names are separated by a semicolon. | String |
| Clustered | True if the index represents a clustered index (applies only to ODBC databases); Jet 3.0 doesn't create clustered indexes. | Boolean |
| Foreign | True if the index is on a foreign key field; | Boolean |
| False otherwise. | ||
| Required | True if the index is on a required field; | Boolean |
| False otherwise. | ||
| Ignore | True if the index is on a field that allows | Boolean |
| Nulls | Null values; False otherwise. | |
| Primary | True if the index is the primary key index; | Boolean |
| False otherwise. | ||
| Unique | True if the index prohibits duplicate values; | Boolean |
| False otherwise. The value of the Unique | ||
| property always is True for PrimaryKey indexes. Unique indexes do not permit Null values in the indexed field. |
It's likely that xBase programmers will notice that there is no provision in Visual Basic 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 Basic, you cannot even open an index file of this type, much less maintain it. The Variant data type of Visual Basic takes care of the problems associated with concatenated indexes of different field data types. If you need an index, such as that just described, you need to create a new field (part_char) that consists of the eight characters beginning at the third character of char_field. The value of the Fields property of the Index object to create or maintain an index on this field is +part_char;+date_field.
Listing 2.2 shows the VBA code needed to add the capability to list in the DataDict application properties of the Field and Index objects of each TableDef of a Jet database. Both the FieldsProc and IndexesProc subprocedures use a pointer to the Fields
and Indexes collection objects, respectively.
Listing 2.2. The added procedures needed by the DataDict application to list properties of the Field and Index objects.
Private Sub FieldsProc()
'Display the properties of the selected field
Dim fldMap As Fields 'Declare a Fields object variable
On Error GoTo FieldsError 'Set error handler
strTableName = List1.List(List1.ListIndex)
Call ClearLists
Label1.Caption = "Field Name:"
Label2.Caption = "Data Type:"
Label3.Caption = "Maximum Size:"
Label4.Caption = "Collating Order:"
Label6.Caption = "Position:"
frmTableDefs.Caption = _
"Fields Collection Data Dictionary for " & strTableName
Set fldMap = dbMap.TableDefs(strTableName).Fields
For intCtr = 0 To fldMap.Count - 1 'Fill the list boxes
List1.AddItem fldMap(intCtr).Name
List2.AddItem strFieldType(CVar(fldMap(intCtr).Type))
List3.AddItem fldMap(intCtr).Size
List4.AddItem fldMap(intCtr).CollatingOrder
List5.AddItem "&H" & Hex$(fldMap(intCtr).Attributes)
List6.AddItem fldMap(intCtr).OrdinalPosition
Next intCtr
Exit Sub
FieldsError:
'Provide an standard error handler
MsgBox Error$, , "Fields Error"
Exit Sub
End Sub
Private Sub IndexesProc()
'Display the properties of the selected index
Dim ixsMap As Indexes 'Declare an Indexes object variable
On Error GoTo IndexesError 'Set error handler
Call ClearLists
'Use the Indexes collection
frmTableDefs.Caption = _
"Indexes Collection Data Dictionary for " & strTableName
Label1.Caption = "Index Name:"
Label2.Caption = "Fields:"
Label3.Caption = "Primary:"
Label4.Caption = "Unique:"
Label5.Caption = "Ignore Nulls:"
Label6.Caption = "Foreign:"
Set ixsMap = dbMap.TableDefs(strTableName).Indexes
For intCtr = 0 To ixsMap.Count - 1 'Fill the list boxes
List1.AddItem ixsMap(intCtr).Name
List2.AddItem ixsMap(intCtr).Fields
List3.AddItem ixsMap(intCtr).Primary
List4.AddItem ixsMap(intCtr).Unique
List5.AddItem ixsMap(intCtr).IgnoreNulls
List6.AddItem ixsMap(intCtr).Foreign
Next intCtr
Exit Sub
IndexesError:
'Provide an standard error handler
MsgBox Error$, , "Indexes Error"
Exit Sub
End Sub
Private Sub List1_DblClick()
'Choose the procedure to call based on the data
'presently displayed
If fIsFields Then
Call IndexesProc 'Display indexes
fIsIndexes = True
fIsFields = False
Else
If fIsIndexes Then
Call TableProc
Else
Call FieldsProc 'Display fields
fIsFields = True
fIsIndexes = False
End If
End If
End Sub
Private Function strFieldType(lngType As Variant)
'Return the string value of field data type
Select Case lngType
Case dbBoolean
strFieldType = "Boolean (Yes/No)"
Case dbByte
strFieldType = "Byte (tinyint)"
Case dbInteger
strFieldType = "Integer (smallint)"
Case dbLong
strFieldType = "Long Integer (int)"
Case dbCurrency
strFieldType = "Currency"
Case dbSingle
strFieldType = "Single-precision"
Case dbDouble
strFieldType = "Double-precision"
Case dbDate
strFieldType = "Date/Time"
Case dbText
strFieldType = "Text (String)"
Case dbLongBinary
strFieldType = "Long Binary"
Case dbMemo
strFieldType = "Memo"
End Select
End Function
When you double-click an item in the Table Name list box, the FieldsProc subprocedure displays the properties of members of the Fields collection for the selected table, as shown in Figure 2.9. Double-clicking the Field Name list box displays the
members of the Indexes collection (see Figure 2.10). The Reference1 index on the Au_ID field is created by Jet 2.5 when a relationship is created with a non-indexed foreign key field.
Figure 2.9. Properties of members of the Fields collection of the Publishers table of Biblio.mdb.
Figure 2.10. Properties of members of the Indexes collection of the Title Author table of Biblio.mdb.
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 strSource argument when applying the OpenRecordset method to the Database object, Jet must run the SQL statement through the query optimizer. QueryDef objects are the only method of executing an SQL
passthrough query against a client/server RDBMS. An SQL passthrough query sends the SQL statement directly to the RDBMS for execution. SQL passthrough queries are discussed in Chapters 19 and 20.
You use the following syntax to create a new QueryDef object:
Dim qdfName As QueryDef Set qdfName = dbName.CreateQueryDef([strName][, strSQL])
If you omit the strName argument or supply an empty string (""), you create a temporary (unsaved, impersistent) QueryDef object. If you omit the strSQL argument, you must supply a value for the SQL property before executing the
QueryDef by applying the OpenRecordset or Execute method to the object. The following example creates a QueryDef object named qryPubs that, when executed, returns all of the records of the Publishers table of Biblio.mdb:
Dim qdfPubs As QueryDef
Set qdfPubs = dbBiblio.CreateQueryDef("qryPubs", _
"SELECT * FROM Publishers")
QueryDef objects have many properties in common with TableDef objects. Table 2.14 lists the properties of the QueryDef object. The Name and SQL properties appear first in Table 2.14, because these are the two most commonly used properties when creating
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 be an alias created by the SQL AS reserved word. The SourceTable and SourceField property values of Field of QueryDef objects reflect the table name and field name, respectively, of the source tables for the query.
| Property | Description |
| Name | The given name for a query; the default value is an empty string. |
| SQL | The SQL statement to execute the query. Requires Jet SQL syntax unless the query is an SQL passthrough query, in which case the SQL syntax is that for the server RDBMS. |
| Connect | Used only to create an SQL passthrough query; a connect string beginning with "ODBC;" is required. |
| DateCreated | The date the QueryDef was created with the CreateQueryDef method of the Database object. |
| LastUpdated | The date the QueryDef was last updated by modifying the value of one or more of its properties. |
| LogMessages | 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 | 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 | The number of records affected by successful execution of an APPEND, UPDATE, or DELETE query. |
| ReturnsRecords | For SQL passthrough queries (only), set to True if executing a SELECT query or a stored procedure that returns records; otherwise, set to False. |
| Type | Specifies the type of query by the value of an Integer flag. (See Table 2.15.) |
| Updatable | True if the properties of the QueryDef can be altered, which is ordinarily the case. |
| 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 Passthrough |
| 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 of Table 2.15 lists decimal, not hexadecimal, values—Type flags for QueryDef objects are not used in combination (except for SQL passthrough queries that don't return records).
Altering the value of one or more properties, such as the SQL property, of a persistent QueryDef object is the recommended method to replace the OpenQueryDef statement of Visual Basic 3.0, which is obsolete.
Table 2.16 lists, in the order of most frequent use, the methods of the QueryDef object.
| Method | Purpose |
| OpenRecordset | Opens a Recordset object over the result set of a SELECT query or of an SQL passthrough query that returns records. |
| Execute | Executes a query that does not return records, such as an action or DDL query (replaces the ExecuteSQL method of Visual Basic 2.0 and 3.0). |
| CreateProperty | 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. The general syntax of the OpenRecordset method of the QueryDef object is this:
Dim rsName As Recordset Set rsName = qdfName.OpenRecordset([intType[, intOptions]])
The value of the intType argument can be either dbOpenDynaset (2) or dbOpenSnapshot (4); you cannot open a Table-type Recordset over a QueryDef result set. Table 2.17 lists the values of flags for the intOptions argument; constants marked
with an X in the RS column of the table apply to the OpenRecordset method. The following example creates a forward-scrolling Recordset of the Snapshot type from the qryPubs query:
Dim rssPubs As Recordset
Set rssPubs = qdfPubs.OpenRecordset(dbOpenSnapshot, _
dbForwardOnly)
The general syntax of the Execute method of the QueryDef object is this:
qdfName.Execute([intOptions])
Table 2.17 includes the values of flags for the intOptions argument of the Execute method; only constants marked with an X in the EX column of the table apply to the Execute method.
| Constant | RS | EX | Value | Purpose |
| dbDenyWrite | X | X | &H1 | Denies write permission to others while the query executes or a Recordset is open. |
| dbDenyRead | X | &H2 | Denies read permission to others while the query executes or a Recordset is open. | |
| dbReadOnly | X | &H4 | Creates a read-only (not updatable) Recordset. | |
| dbAppendOnly | X | &H8 | Allows only INSERT queries (for data entry). | |
| dbInconsistent | X | X | &H10 | 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 | &H20 | Requires consistent updates (cannot change values in the primary key field of a query with a one-to-many relationship). |
| dbSQLPassThrough | X | X | &H40 | Specifies an SQL passthrough operation. |
| dbFailOnError | X | &H80 | Rolls back updates if an error occurs (creates a trappable error). | |
| dbForwardOnly | X | &H100 | 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 | &H200 | 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 Basic database applications. It is 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 for interactive entry of parameter values in Access applications.
You can add new tables to a Database object with Visual Basic code by using the TableDefs collection 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 2.18 lists the methods applicable to the TableDefs, QueryDefs, Fields, and Indexes collections. Only Table objects contain the Indexes collection.
| Method | TDF | QDF | FLD | IDX | Purpose of the Method |
| 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; therefore, you can use SQL statements to perform the same operations as those that use members of the TableDefs, Fields, Indexes and Relations collections. Visual Basic 4.0's
online help provides examples of the use of each of these SQL reserved words.
As noted earlier in this chapter, the Recordset object comes in three different types: Table, Dynaset, and Snapshot. The general syntax for creating a Recordset object over a table in database is this:
Dim rstName As Recordset Set rstName = dbName.OpenRecordset(strTableName, [intType[, intOptions]])
If you don't specify the intType argument, Jet opens a Table-type Recordset (intType = dbOpenTable). The flags for the optional intOptions argument are listed in Table 2.17. To open a Recordset based on an SQL query, use the
following syntax:
Dim rsdName As Recordset Set rsdName = dbName.OpenRecordset(strSQL, [intType[, intOptions]])
In this case, Jet opens a Dynaset-type Recordset unless you set the value of intType to dbOpenSnapshot or set one of the intOption flags to create a Snapshot-type Recordset. The syntax for opening a Recordset over a QueryDef result set is
provided in the preceding section, titled "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.
Table 2.19 lists the properties that are applicable to the Table, Dynaset, and Snapshot types of Recordset objects. The entries under the headings TBL, DS, and SS of Table 2.19 indicate whether the property is applicable to Recordset objects of the
Table, Dynaset, or Snapshot type, respectively. Related properties listed in Table 2.19 are grouped by function and all properties that are new to Jet 2.x and 3.0 are noted by an asterisk (*).
| Property | TBL | DS | SS | Description of Property Value |
| Name | X | X | X | The name of the Recordset (read-only). Same as the Name property of the corresponding TableDef object for the Table type. |
| !Field.Value | X | X | X | The value (content) of the data in the Field of the current record. |
| Type | X | X | X | The type of Recordset object defined by the integer dbOpenTable, dbOpenDynaset, or dbOpenSnapshot constants. |
| DateCreated | X | Date and time that a Table object was originally created (read-only). | ||
| LastUpdated | X | Date and time that the last change was made to the data in a Table object (read-only). | ||
| Updatable | 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* | X | X | True if the Requery method is supported; False otherwise. (You must recreate the Recordset to refresh its content.) | |
| Transactions | X | X | True if the Table or Dynaset type supports rolling back of transactions; False otherwise (read-only). | |
| Bookmarkable | 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 | X | X | X | A binary value that indicates (points to) a specific record. A Bookmark's value is set and returned as a String or subtype 8 Variant. |
| LastModified | X | X | A Bookmark value that points to the record in the Table object that was updated most recently (read-only). | |
| Index | 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 | X | X | The name(s) 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 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 | 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. |
| Absolute | X | X | A Long value that sets or | |
| Position* | gets the position of the record | |||
| pointer (the first record is 0) | ||||
| Percent | X | X | A Single value that sets | |
| Position* | or gets the position of the record | |||
| pointer as a percentage of the | ||||
| total number of records (0.00 to | ||||
| 100.00) | ||||
| BOF | X | X | X | True when the record pointer is positioned before (above) the first record of a Recordset; False otherwise (read-only). |
| EOF | 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* | X | 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* | X | String value that gets or sets the Bookmark value of the first record to be cached. | ||
| NoMatch | X | X | X | False 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); True otherwise. |
| Filter | 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. | |
| Validation | X | X | X | A table-level validation rule |
| Rule* | (read-only). | |||
| Validation | X | X | X | The text that appears in a |
| Text* | message box when the validation | |||
| rule is broken (read-only). | ||||
| EditMode* | X | X | Integer flags that indicate the editing status: dbEditNone (no editing in progress), dbEditInProgress (edited data is in the copy buffer), or dbEditAdd (AddNew method applied and tentative append record is in the copy buffer). | |
| LockEdits | X | X | Set to True to lock pages that are in the process of being updated (pessimistic locking); set to False to lock pages only while the Update method applies the edits to the table (optimistic locking). |
You can read the value of each of the properties listed in Table 2.19 that are applicable to a Recordset object with a statement of the following form:
varPropertyValue = rsName.PropertyName
Similarly, you can set the value of those properties (not indicated in the Description column of Table 2.19 as "read-only") with the following syntax:
rsName.PropertyName = varPropertyValue
Values of Recordset and other data access member objects always are of the Variant data type with a subtype appropriate to the characteristics of the value of the property. Examples of the use of all of the Recordset properties applicable to
Recordset objects of the Dynaset type appear in the next chapter.
As mentioned earlier in this chapter, collections of Database objects, including the Recordsets collection, have only one property, Count.
Table 2.20 lists the methods applicable to Recordset objects in the same format as that used in Table 2.19 to list the properties of Recordset objects.
| Method | TBL | DS | SS | Purpose of Method |
| Clone | X | X | X | Creates a duplicate Recordset object with an independent record pointer. |
|
Open
Recordset* | X | X | X | Opens a new Recordset based on the Recordset to which the method is applied. |
|
Copy
QueryDef* | 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 | X | X | Recreates the content of the Recordset by re-executing the underlying query. | |
| Close | X | X | X | Closes the Recordset. |
| 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 | X | X | Appends a new, empty record to a Table or Dynaset type (equivalent to xBase's APPEND BLANK). | |
| Delete | X | X | Deletes the current record from a Table or Dynaset type. | |
| Update | X | X | Causes pending updates to a Table or Dynaset type to be executed. (Removes the lock on the record or page.) | |
| Cancel | X | X | Cancels pending updates if | |
| Update* | applied prior to the Update method. | |||
| Move* | X | X | X | Moves the record pointer a specified number (Long) of rows; if the strBookmark option is used, moves are relative to the bookmarked position. |
| MoveFirst | X | X | X | Positions the record pointer at the beginning of the Recordset. |
| 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 | X | X | X | Same as the MoveNext method, except it moves in the opposite direction. |
| MoveLast | X | X | X | Positions the record pointer at the end of the Recordset. |
| 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 | 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 | X | X | Same as the FindFirst method, except it tests records after the current matching record. | |
| FindPrevious | X | X | Same as the FindFirst method, except it tests records before the current matching record. | |
| FindLast | X | X | Same as the FindFirst method, except it starts at the end of the first record and proceeds backward until a match is found. |
The generalized syntax of statements that apply methods to Recordset objects is this:
rsName.MethodName [Argument1 [, Argument2 [, Argument#]]]
Some methods, such as Update, have no arguments; other methods, such as FindFirst, have several arguments, some of which are optional. The next chapter provides the syntax for most Recordset methods and uses many of the methods listed in Table 2.20 in
the code examples for the Data control.
You need a thorough understanding of the member objects that constitute Visual Basic 4.0's Data Access Object class to develop commercial-quality Visual Basic database applications. This chapter began by diagramming the hierarchy of the 16-bit and
32-bit Data Access Objects, then 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 3, "Using Visual Basic's Data Control," which completes Part I of this book, shows you how to use Visual Basic's Data control to manipulate the data that is contained in the data objects you define as properties of a Data control.
Using the Data control object with other bound controls requires a minimum amount of VBA code to create a useful database application. The new data-bound OLE Controls included with the Professional version of Visual Basic 4.0 greatly expands the repertoire
of Visual Basic 3.0's data-aware controls.
