• Visual Basic and the Microsoft Jet Engine
  • What Is the Microsoft Jet Database Engine?
  • Advantages of Microsoft Jet over the Data Control Object
  • Microsoft Jet Data Objects
    • Figure 9.1.
  • The DBEngine Data Object
  • Listing 9.1. Creating the ShowType support routine.
  • Listing 9.2. Coding the cmdDBEngine_Click event.
    • Figure 9.2.
  • Listing 9.3. Coding the cmdDBRepair_Click event.
    • Figure 9.3.
  • Listing 9.4. Coding the cmdDBCompact_Click event.
  • Listing 9.5. Coding a DBRegistration routine.
    • Figure 9.4.
  • The Workspace Data Object
  • Listing 9.6. Coding the cmdWorkspace_Click event.
    • Figure 9.5.
  • Listing 9.7. Coding the cmdNewWorkSpace_Click event.
    • Figure 9.6.
  • Listing 9.8. Coding the cmdCreateDB_Click event.
    • Figure 9.7.
  • Listing 9.9. Coding the cmdOpenDB_Click event.
    • Figure 9.8.
  • The Database Data Object
  • Listing 9.10. Coding the cmdDBProperties_Click event.
    • Figure 9.9.
  • Listing 9.11. Coding the cmdRecordset_Click event.
    • Figure 9.10.
  • Listing 9.12. Coding the cmdExecute_Click event.
    • Figure 9.11.
  • Listing 9.13. Coding the cmdMakeUDP_Click event.
    • Figure 9.12.
  • The TableDef Data Object
  • Listing 9.14. Adding the TableDef button.
    • Figure 9.13.
  • Listing 9.15. Coding the cmdCreateTable_Click event.
    • Figure 9.14.
  • Listing 9.16. Coding the cmdModifyTable_Click event.
    • Figure 9.15.
  • Listing 9.17. Coding the cmdAttachTable_Click event.
    • Figure 9.16.
  • The Field Data Object
  • Listing 9.18. Coding the cmdFields_Click event.
    • Figure 9.17.
  • The Index Data Object
  • Listing 9.19. Coding the cmdIndex_Click event.
    • Figure 9.18.
  • The QueryDef Data Object
  • Listing 9.20. Coding the cmdQuery_Click event.
    • Figure 9.19.
  • Listing 9.21. Coding the cmdRunningQDs_Click event.
    • Figure 9.20.
    • Figure 9.21.
  • The ODBCDirect Connection Data Object
  • Listing 9.22. Coding the cmdConnection_Click event.
    • Figure 9.22.
  • The Recordset Data Object
  • Listing 9.23. Coding the cmdRecordsets_Click event.
    • The Relation Data Object
      • Figure 9.23.
  • Listing 9.24. Coding the cmdRelation_Click event.
    • Figure 9.24.
  • Summary
  • Quiz
  • Exercise

Chapter Nine

Visual Basic and the Microsoft Jet Engine

Today you'll learn the details of the heart of the Visual Basic database system--Microsoft Jet, the part of Visual Basic that handles all database operations. Whether you are reading a Microsoft Access-format database, accessing a FoxPro file, or connecting to a back-end database server using ODBC, Microsoft Jet is there. You can also use Visual Basic to create a link between an existing Microsoft Jet database and data in non-Microsoft Jet databases. This process of attaching external data sources provides an excellent way to gain the advantages of the Microsoft Jet data access object layer without having to convert existing data to Microsoft Jet format.

Today you will learn about several object collections that exist in Visual Basic Microsoft Jet databases, including the new ODBCDirect objects available in the Microsoft Jet 3.5 data engine. The objects covered in this chapter include the following:

• The DBEngine object
• The Workspace object
• The Database object
• The TableDef object
• The Field object
• The Index object
• The Relation object
• The Connection object
• The Recordset object

Throughout this lesson, you will build a single Visual Basic project that illustrates the various data access objects you learn about today. You can apply the Visual Basic coding techniques you learn today in future Visual Basic database projects.

What Is the Microsoft Jet Database Engine?

The idea behind Microsoft Jet is that you can use one interface to access multiple types of data. Microsoft designed Microsoft Jet to present a consistent interface to the user regardless of the type of data the user is working with. Consequently, you can use the same Microsoft Jet functions that you use to access an ASCII text file or Microsoft Excel spreadsheet to also perform data operations on Microsoft Access databases.

The Microsoft Jet engine is not a single program; it is a set of routines that work together. The Microsoft Jet engine talks to a set of translation routines. These routines convert your Microsoft Jet request into a request that the target database can understand. Translation routines exist for Microsoft Access databases and for non-Microsoft Access ISAM files such as dBASE, FoxPro, Paradox, and so on. A translation set even exists to handle ODBC data sources using the Microsoft Jet interface. In theory, you could access any data file format through the Microsoft Jet engine, as long as some set of translation routines is made available to the engine.

---

NOTE: The detailed inner workings of the Microsoft Jet engine go beyond the scope of this guide. If you want to learn more about how the Microsoft Jet interface works, you can obtain copies of several white papers Microsoft has released on the topic of Microsoft Jet and the data access object layer. You can get these papers through various online sources and through the Microsoft Developers Network CDs.

---

Advantages of Microsoft Jet over the Data Control Object

So far, you have learned to use the data control object to perform database administrative tasks. The data-access objects (DAOs) addressed in this chapter perform all of the services that the data control does, as well as many more. The data-access objects give you complete control over database management.

If possible, use the data control object to manage your data. It is much easier to use because many of the administrative functions are handled for you. You can always add DAO in your code to work with the data control object.

Microsoft Jet Data Objects

Microsoft Jet is organized into a set of data-access objects. Each of the objects has collections, properties, and methods:

• Collections: Data-access objects that contain the same type of objects.
• Properties: The data contained within an object (control button, form, and so on) that defines its characteristics. You set an object's properties.
• Methods: The procedures that can be performed on an object. You invoke a method.

The Microsoft Jet data access objects exist in a hierarchy, which means that a top-down relationship exists between the objects. You learn the various Microsoft Jet data-access objects in the order they reside in the hierarchy. As you push deeper into the object hierarchy, you move toward more specific data objects. For example, the first data object in the hierarchy is the DBEngine data-access object. All other data-access objects exist underneath the DBEngine data-access objects.

---

NOTE: Throughout the rest of this chapter you will see the phrases "data-access objects" and "data objects." They both refer to the data-access object layer of the Microsoft Jet engine.

---

If you do not already have Visual Basic up and running, start it now and begin a new Standard EXE project. Make sure that your system can reference the Microsoft Jet 3.5 Data Access Object Library.

---

WARNING: If you don't have a reference to the data-access object layer in your project, you cannot access any of the features of the Microsoft Jet database engine.

---

If you can't tell whether your reference to the data access object is activated, select Project | References... from the Visual Basic main menu. Use Figure 9.1 as a reference.

Figure 9.1. Reviewing the data-access object reference.

Throughout this chapter you'll be using the Microsoft Jet 3.5 data engine. This is the most recent version of the data engine available. You can use older versions of the data engine to maintain compatibility with earlier Visual Basic projects, but it is recommended that you use Microsoft Jet 3.5 for all future projects.

The DBEngine Data Object

The DBEngine data object is the default data object for all access to the database operations under Visual Basic. Even if you do not explicitly use the DBEngine object, your program is still accessing all other data objects by way of the DBEngine object because it is invoked by default when Visual Basic begins any database work.

---

TIP: Even though Visual Basic does not require that you explicitly use the DBEngine data object, you should use the object in all your future Visual Basic projects to ensure maximum compatibility with any future versions of Visual Basic.

The DBEngine Object Collections The DBEngine object contains three different object collections. Each of these collections in turn contains other data-access objects. To put it another way, the DBEngine is the top level of the DAO hierarchy, and it contains the following collections:

• Workspaces: A collection of all the defined Workspace objects. The next section of this chapter covers Workspace objects. The Workspace collection is the default collection for the DBEngine object.
• Errors: A collection of the most recent database-related errors encountered in this session. Error objects are covered later in this chapter.
• Properties: A collection of all the properties of the DBEngine object.

The DBEngine Object Properties Like all Visual Basic objects, you can list the properties of the object by accessing the Properties collection. Let's write a short bit of code to list (enumerate) all the properties of the DBEngine data access object.

Before coding the DBEngine routines, you need to add a support routine to your form. This routine makes it easier to read the output of the rest of the routines in this chapter. Create a new function called ShowType and enter the code from Listing 9.1.

Listing 9.1. Creating the ShowType support routine.

Now you're ready to start DAO programming!

First, add a single button to the bottom of the current form. Set its Name property to cmdDBEngine and its Caption property to DBEngine. Now double-click the button to bring up the cmdDBEngine_Click event window and enter the code shown in Listing 9.2.

Listing 9.2. Coding the cmdDBEngine_Click event.

In Listing 9.2, you first tell Visual Basic to ignore any errors it might receive while enumerating the DBEngine properties. Then you declare an object variable to hold the properties of the DBEngine object and a string variable to hold the constructed display message. You then use the Visual Basic 5 For..Each loop to list each of the properties of the DBEngine object and build a display message string.

Save the form as FRMMSJET.FRM and the project as PRJMSJET.VBP. When you run the project, you see a single button at the bottom of the form. Click that button to force Visual Basic to enumerate the properties of the DBEngine data-access object. Your screen should look like Figure 9.2.

Figure 9.2. The enumerated DBEngine properties.

Setting the DBEngine Properties You can set the properties of the DBEngine object in your program, too. For example, you can use the IniPath property to point to a special ISAM driver needed to process the related database:

---

NOTE: In Microsoft Jet 2.5, the IniPath property actually points to an INI file in the <WINDOWS> folder on the workstation. In Microsoft Jet 3.0 and 3.5, the IniPath property is used to point to a location in the workstation's System Registry.

---

The DefaultUser and DefaultPassword properties are covered when you learn about the Workspace data-access object. The DBEngine Object Methods We'll cover six of the Visual Basic methods that are associated with the DBEngine data-access object:

• RepairDatabase is used to fix corrupted Microsoft Jet database files.
• CompactDatabase is used to clean up, and also convert, existing Microsoft Jet databases.
• RegisterDatabase is used to create a link between an external data source and an existing Microsoft Jet database.
• Idle is used to force Visual Basic to pause processing while the DBEngine updates the contents of any existing data access objects.
• SetOption is used to modify one or more of the Microsoft Jet Registry settings at runtime.
• CreateWorkspace is used to establish a workspace for accessing one or more databases. You'll learn about this method in the section on Workspace objects later in this chapter.

Using the RepairDatabase Method You can use the RepairDatabase method to fix corrupted Microsoft Jet database files. The default syntax to invoke this method is

DBEngine.RepairDatabase databasename

Add another command button to the current project. Place it at the bottom of the screen. Set its Name property to cmdDBRepair and its Caption property to DBRepair. Add a CommonDialog control to the form and then enter the code in Listing 9.3.

Listing 9.3. Coding the cmdDBRepair_Click event.

Private Sub cmdDBRepair_Click()
    `
    ` fix a corrupted db
    `
    Dim strDBName As String
    `
    CommonDialog1.ShowOpen
    strDBName = CommonDialog1.filename
    `
    If strDBName <> "" Then
        DBEngine.RepairDatabase strDBName
        MsgBox strDBName & " Repaired"
    End If
    `
End Sub 

The code in Listing 9.3 declares a local variable for the database name and then prompts the user to enter the name of a database to repair. After checking to make sure a database name was entered, the code executes the RepairDatabase method and reports the results.

Save and run the program. When you click the Repair button, locate and select the DBREPAIR.MDB database (see Figure 9.3).

Figure 9.3. Entering a database to repair.

The repair method executes and the final message box appears.

---

WARNING: The RepairDatabase method overwrites the existing file with the repaired database file. You should make a backup copy of your database files before you execute the RepairDatabase method.

---

Using the CompactDatabase Method The CompactDatabase method cleans out empty space in Microsoft Jet databases and performs general optimization chores that improve access speed. You can also use the CompactDatabase method to convert older versions of Microsoft Jet databases to newer versions.

The syntax for this method is

In this line, oldDatabase is the name (including path) of the database to be compacted; NewDatabase is the name (including path) of the new, compacted database; and locale is the language in which the data is written. Options can be added to encrypt or decrypt a database, as well as to change versions. Multiple options must be joined with the plus (+) sign.

Add another button to the PRJMSJET.VBP project. Set its Name property to cmdDBCompact and its Caption property to &DBCompact. Enter the code in Listing 9.4 into the cmdDBCompact_Click event window. This code compacts any Microsoft Jet database.

Listing 9.4. Coding the cmdDBCompact_Click event.

The code in Listing 9.4 declares its local variables and then prompts the user to enter the database file to compact or convert. If no filename is entered, the routine skips to the exit. If a filename is entered, the user is prompted to enter a target filename. If no name is entered, the program returns to try the whole thing again. After getting the filename, the user is prompted to supply the target MSJH version number. The value entered is checked and the user is returned to the input box if an invalid option was entered. Finally, the user is asked whether the database should be encrypted. After that, the CompactDatabase method is invoked.

Save your work and execute this program. You are prompted to enter the name of the database to compact. Enter the path and name for DBREPAIR.MDB. You then must enter a database to compact to. You can just accept the filename suggested to you. Next, enter the version. Answer Yes when you are prompted with the encryption question. The new database is now compacted and saved.

---

WARNING: If you plan to run your database application using any 16-bit data tool, you'll need to store the database in the Microsoft Jet 2.5 version. Only Microsoft Jet 2.5 can run on both 32- and 16-bit platforms.

---

Using the RegisterDatabase Method The RegisterDatabase method enables you to register an ODBC data source for Microsoft Jet access. The Visual Basic documentation encourages programmers to rely on the Windows Control Panel ODBC Setup utility rather than using the RegisterDatabase method. If, however, you want to perform the ODBC registration process within your Visual Basic program, you can use the RegisterDatabase method to do so.

The easiest way to provide ODBC registration capabilities in your program is to supply a limited number of parameters and force Windows to present the ODBC registration dialog for you--a fairly easy task. For this example, add a new command button to the bottom of the form. Set its Name property to cmdDBRegister and its Caption property to DBRegister. Add the code in Listing 9.5.

Listing 9.5. Coding a DBRegistration routine.

The preceding code first tells Visual Basic to ignore any reported errors, and then it supplies a set of parameters for creating an ODBC data source. The parameters for the RegisterDatabase method are

• SourceName: The name that will be used as the database name for the OpenDatabase method.
• DriverName: The name of an ODBC driver installed and available on your work- station.
• SilentFlag: Setting this to False forces Windows to present the ODBC registration dialog box. If it is set to True, Windows attempts to register the ODBC data source without prompting the user with the ODBC registration dialog box.
• AttributeList: A list of attribute settings for the ODBC source. Examples of attributes include any server device name, database name, and any other parameters required by the back-end database server.

---

WARNING: The Microsoft Visual Basic documentation tells you to create an Attributes list with each attribute separated by a CR-LF pair. This is not correct. You should delimit each attribute entry with a CHR(0) in order for the RegisterDatabase routine to work properly.

---

Save and run the project. When you click the DBRegister button, you see the Windows ODBC Registration dialog box appear with some of the parameters already entered. You can complete the information and click OK to register the ODBC data source on your system. Refer to Figure 9.4 as an example.

Figure 9.4. Registering an ODBC data source.

Completing an ODBC registration inserts data into the HKEY_USERS\DEFAULT\ODBC\ODBC.INI section of the Windows Registry on 32-bit systems. The data is added to the ODBC.INI file in the <WINDOWS> folder on 16-bit systems. You can add features to the earlier cmdDBRegister_Click example by prompting the user to enter the SourceName and DriverName. You could also fill out all values within the program and set the SilentFlag to True. In this way, you could use the routine to install new ODBC connections for Visual Basic applications without requiring the user to know anything at all about ODBC or Microsoft Jet.

---

WARNING: Failure to register an ODBC data source properly can result in un- expected errors and possible loss of data. Be sure to test your RegisterDatabase routines completely before using them on live data.

---

The SetOption Method The SetOption method of the DBEngine object allows you to override performance values in the Registry at runtime. You can use this option to perform runtime tuning of the Microsoft Jet engine. Table 9.1 shows the values you can adjust using the SetOption method.

Table 9.1. Tuning values for the SetOption method of the DBEngine.

For example, to adjust the value of the LockRetry setting, you could use the following code:

DBEngine.SetOption dbLockRetry = dbLockRetry * 1.5

Any changes made to the Registry settings are in effect only as long as your program is running. They are not saved to the Windows Registry. The Idle Method The Idle method forces Visual Basic to pause while the DBEngine catches up on any changes that have been made to all the open data-access objects. This method becomes useful when you have a lot of database traffic or a lot of data-access objects in a single program. The syntax is simple:

DBEngine.Idle

The Workspace Data Object

The Workspace data object identifies a database session for a user. Workspaces are created each time you open a database using Microsoft Jet. You can explicitly create Workspace objects to manage database transactions for users and to provide a level of security during a database session. Even if you do not explicitly create a Workspace object, Visual Basic 5.0 creates a default Workspace each time you begin database operations.

---

NOTE: Although you can create Workspace data objects, you can't save them. Workspace objects are temporary. They cease to exist as soon as your program stops running or as soon as you close your last data access object.

---

The Workspace object contains three collections, two properties, and eight methods. The Workspaces collection contains one property (Count) and one method (Refresh). The Workspaces collection enables you to access multiple Workspace objects. The Workspace object enables you to access the properties, collections, and methods of the named Workspace object. The Workspace Object Collections The Workspace data-access object contains three object collections:

• Databases: A collection of all the Database objects opened for this Workspace object. This is the default collection.
• Groups: A collection of all the defined Group objects that have access to this Workspace.
• Users: A collection of all the defined User objects that have access to this Workspace.

---

NOTE: You can only access the Group and User objects if the Microsoft Jet security is activated. You can only activate Microsoft Jet security through Microsoft Access. Although Visual Basic cannot initiate database security, you can manage the security features using Visual Basic 5.0. Security features are covered on Day 21, "Securing Your Database Applications."

---

The Workspace Object Properties Three Workspace object properties exist: the workspace name, the workspace user name, and the Isolate ODBC Trans property. The Isolate ODBC Trans property can be used to control the number of ODBC connections used during the database session.

---

NOTE: ODBC connections are covered in depth in Week 3 of this guide. For now, just remember that you can control the number of connections used by the session by altering the Isolate ODBC Trans property of the Workspace object.

---

When you begin a database operation, Visual Basic 5.0 creates a default workspace with the name #Default Workspace # and the user name admin. Let's add some code to the CH1001.VBP project to enumerate the default Workspace properties.

Add a new button to the form. Set its Name property to cmdWorkspaces and its Caption property to &Workspaces. Enter the code in Listing 9.6 into the cmdWorkspaces_Click code window.

Listing 9.6. Coding the cmdWorkspace_Click event.

The code in Listing 9.6 should look familiar to you. It is almost identical to the code used to enumerate the DBEngine properties. The only change that has been made is that you now have two For ... Each loops in the routine. The outer loop walks through all defined workspaces in the Workspace collection. The inner loop walks through all the properties of the selected Workspace object.

Save and run the program. When you click on the Workspace button, the program lists all the properties of the object. Your screen should look like Figure 9.5.

Figure 9.5. Enumerating the Workspace object properties.

Creating a New Workspace Object You can create new Workspace objects using the CreateWorkspace method of the DBEngine. Even though Visual Basic 5 creates and uses a default Workspace object when you first begin database operations, you should create an explicit, named Workspace from within Visual Basic. When you create a unique Workspace object, you isolate all your database operations into a single session. You can then group a set of database transactions into a single session to improve database integrity and security.

Let's add a new command button to the project that will create a new Workspace object. Set the button's Name property to cmdNewWorkSpace and set its Caption property to &New WS. Add the code in Listing 9.7 into the cmdNewWorkSpace_Click code window.

Listing 9.7. Coding the cmdNewWorkSpace_Click event.

The code in Listing 9.7 establishes local variables and then initializes them to the correct values. Notice that you can use any unique name you like for the Workspace object, but you must use valid User and Password parameters. These values must already exist in the system security file or as the default values if Microsoft Access security is not active. Because you do not use Microsoft Access security here, this example used the default admin user name and empty password.

You used the CreateWorkspace method to create a valid Workspace object. You can now use this object throughout your program. As an option, you can add the new object to the Workspaces collection, by using the Append method. After adding the new object, you can force Visual Basic to display the Workspaces collection to see your results.

---

WARNING: It is not a good idea to append your workspace definitions to the Workspaces collection in a production environment. In rare cases, someone could "listen in" on a network connection that uses workspaces and hack one or more of the valid names, users, and passwords for secured tables. This can be done by locating and walking through the Workspaces collection. To prevent troubles, it is a good idea to never append workspaces to the Workspaces collection.

---

Save and run the project. After you click the New WS button, you see two workspaces displayed on the form. Check your screen against the one in Figure 9.6.

Figure 9.6. The results of adding a new Workspace object.

Using the Workspace Object Methods The Workspace object methods fall into several related groups. Table 9.2 shows the Workspace methods in their respective groups.

Table 9.2. Workspace methods.

You learn more about the Transaction group on Day 17, "Multiuser Considerations," and the Security group is covered on Day 21. The ODBCDirect methods are covered in another section in this chapter. That leaves the Microsoft Jet database methods: CreateDatabase, OpenDatabase, and Close. Using the Microsoft Jet Database Methods The two database-related Workspace methods are CreateDatabase and OpenDatabase. You use the CreateDatabase method to create a new database, and you use the OpenDatabase method to open an existing database.

Let's first add a command button to create a new database. Set the button's Name property to cmdCreateDB and its Caption property to CreateDB. Add the code in Listing 9.8 to the cmdCreateDB_Click code window.

Listing 9.8. Coding the cmdCreateDB_Click event.

The code in Listing 9.8 declares some variables, initializes them, and then goes on to create a workspace for this session. It then creates the new Database object and, finally, shows you all the databases that are a part of the current workspace. Database objects are covered in greater detail in the next section of today's lesson. It is important to note here that you create a Workspace object before you create the database to make sure that the Database object becomes a part of the Workspace object. Now all activity on that database is a part of the Workspace. As you can see from the code, you can open more than one database in the same workspace and group the database operations together.

It is also important to note the clean-up code added at the end of the routine. When you finish using DAO objects, you need to close them and release the memory they occupied by setting the program variables to Nothing. If you do not do this, you risk running out of memory in DAO-intensive applications.

Save and run the project. When you click on the CreateDB button, the program creates the new databases and shows the results on the form. Your screen should look like Figure 9.7.

Figure 9.7. Creating a new database.

You can also open the same database in two different workspaces. This is handy when you want to provide read/write access in one operation, but only want to provide read-only access in another operation. As an example, add a new command button and set its Name property to cmdOpenDB and its Caption property to &OpenDB. Add the code in Listing 9.9 to the cmdOpenDB_Click code window.

Listing 9.9. Coding the cmdOpenDB_Click event.

The code in Listing 9.9 declares and initializes several variables for the two Workspace and Database object pairs, along with some temp objects for the collection enumeration at the end of the routine. Then each workspace is created and appended to the collection, and the single database is opened once under each workspace session. Finally, all the workspaces and all their databases are listed on the screen. Note that you do not have to use different user names and passwords for the two Workspace objects.

Save and run the project. When you click the OpenDB button, the program opens the database under two different workspaces and shows the results. Notice that the #Default Workspace# appears in the list. It always exists in the Workspaces collection. Check your screen against Figure 9.8.

Figure 9.8. The results of the OpenDatabase method in two workspaces.

Creating and Opening Non-Microsoft Jet Databases You can only create Microsoft Jet-format databases using the CreateDatabase method. The other ISAM-type databases (dBASE, FoxPro, Paradox, and Btreive) all use a single directory or folder as the database object. To create non-Microsoft Jet databases, you have to create a new directory or folder on the disk drive. You can then use the OpenDatabase method to open the non-Microsoft Jet database. When it is opened, you can add tables and indexes using the existing Visual Basic data objects and methods. You'll learn about opening non-Microsoft Jet databases in the next section.

The Database Data Object

The Database data object has 5 collections, 8 properties, and 16 methods. The Database object contains all the tables, queries, and relations defined for the database. It is also part of the Databases collection of the Workspace object. The Database object is created whenever you open a database with the OpenDatabase method. Database objects continue to exist in memory until you use the Close method to remove them.

---

WARNING: Do not confuse the Database object with the database file. The Database object is a Visual Basic program construct used to access the physical database file. Throughout this section, you will hear about the Database object.

---

The Collections of the Database Object The Database object has five collections:

• TableDefs is the collection of Table objects that contain the detailed definition of each data table in the database. This is the default collection.
• QueryDefs is the collection of SQL queries stored in the database.
• Relations is the collection of database integrity relationship definitions stored in the database.
• Recordsets is the collection of active Recordsets opened from this database. Recordsets include any Tables, Dynasets, or Snapshots currently open. Recordsets are temporary objects and are not stored with the database file.
• Containers is the collection of all TableDefs, QueryDefs, and Relations stored in the physical database file. You can use the Containers collection to enumerate all the persistent (stored) objects in the database.

The data-access objects are described in later sections of this chapter. This section focuses on the properties and methods associated with the Database data-access object. The Properties of the Database Object The Database object has eight properties. To illustrate these properties, add another command button to the CH1001.VBP project. Set its Name property to cmdDBProperties and its Caption property to DB Properties. Enter the code in Listing 9.10 into the cmdDBProperties_Click code window.

Listing 9.10. Coding the cmdDBProperties_Click event.

In Listing 9.10, you opened an existing Microsoft Jet database in the default workspace (but did not explicitly declare a session). Then you enumerated the properties of the Database object. Save and run the project. Click the DBProperties button and compare your screen to the one in Figure 9.9.

Figure 9.9. The results of enumerating Database object properties.

Table 9.3 lists the Database object properties and their meanings.

Table 9.3. Database object properties.

Let's modify the routine to open a non-Microsoft Jet database in order to compare the differences in the property values between Microsoft Jet and non-Microsoft Jet databases. Change the code to match the following example and run the program again to review the results:

You can see from this code snippet that the database name has been set to just the application path and that the OpenDatabase method has been altered to open the directory folder as if it were a Text database. Make the changes to your program, save it, and run it. When you click the DBProperties button this time, you see different property values.

---

TIP: This last coding example points out a very important fact about the Microsoft Jet database engine. While the Microsoft Jet engine treats the Microsoft Access database as a single file with many tables inside that file, the Microsoft Jet engine treats all other ISAM-type databases quite differently. To Microsoft Jet, the directory folder is the database and the ISAM files are the data tables. This is why it is a good idea to keep all ISAM-type data files in the same directory folder.

---

The Methods of the Database Object The Database object has 11 methods, but we won't cover all of them here. Table 9.4 shows the Database object methods grouped in a logical fashion.

Table 9.4. The Database object methods.

You'll learn about the Security methods in Day 20 and the NewPassword method is covered in Day 21. The Child Object methods are covered later in this chapter. That leaves the OpenRecordset, Execute, CreateProperty, and Close methods for review here. The OpenRecordset Method of the Database Object You use the OpenRecordset method to access data in existing tables in the database. You can use OpenRecordset to create Dynaset, Snapshot, or Table data objects.

The format of the OpenRecordset method is as follows:

In this syntax, Database is the name of the database that will be used to create the Recordset. Type indicates whether the Recordset created is a Table (dbOpenTable), a Dynaset (dbOpenDynaset), or a Snapshot (dbOpenSnapshot). A Table type is created if you don't specify a type. You can also add options for security and record viewing. See Visual Basic online help for a complete description of these options.

Add a new command button to the project. Set its Name property to cmdOpenRS and its Caption property to. Add the code in Listing 9.11 in the cmdOpenRS_Click code window.

Listing 9.11. Coding the cmdRecordset_Click event.

The code in Listing 9.11 creates three Recordsets, one of each type, and then displays the list of open Recordsets on the form. Save and run the form. Compare your results with those in Figure 9.10.

Figure 9.10. The results of the OpenRecordset method.

---

NOTE: The Recordset created with this method is a very extensive object itself. You'll learn more about the Recordset object's properties and methods later in this chapter.

---

Using the Execute Method You can use the Execute method on a database to perform SQL action queries. The Execute method updates the RecordsAffected property of the Database object with the total number of records found or updated by the SQL statement.

---

NOTE: An action query is an SQL statement that performs an action on a database (add, edit, or delete records; create or remove data tables; and so on). Action SQL queries are covered in detail on Day 13, "Creating Databases with SQL."

---

Add a new command button to your project. Set its Name property to cmdExecute and its Caption property to Execute. Add the code in Listing 9.12 to the cmdExecute_Click event.

Listing 9.12. Coding the cmdExecute_Click event.

The code in Listing 9.12 opens a database and performs an SQL action query that deletes records from a table. The routine displays the RecordsAffected property to show you how many records were deleted, and then it closes the database.

Save and run the project. Click Execute and compare your on-screen results with the screen in Figure 9.11.

Figure 9.11. The results of the Execute method.

Using the CreateProperty Method Visual Basic lets you create user-defined properties (UDPs) for most data-access objects. These UDPs get stored with the database and can be read and updated by your Visual Basic program. In this example, you use the CreateProperty method to add a UDP to a database.

---

WARNING: The capability to create and store UDPs is only available when you use the Microsoft Jet version 3.0 or later database format. If you are not using Microsoft Jet 3.0 or later, you can't complete the example in this exercise.

---

Add a command button to the project. Set its Name property to cmdMakeUDP and its Caption property to MakeUDP. Add the code in Listing 9.13 to the cmdMakeUDP_Click window.

Listing 9.13. Coding the cmdMakeUDP_Click event.

The routine in Listing 9.13 adds two user-defined properties to the database. Notice that you attempt to delete the properties first. That way you can run this example several times without getting an error. Notice that you also used two different code structures to create the properties. Either one is correct.

Save and run the project. When you click the MakeUDP button, you should see a screen similar to Figure 9.12.

Figure 9.12. The results of the CreateProperty method.

The TableDef Data Object

The TableDef data object contains all the information needed to define a Base table object in the Database. You can access Base table objects using the OpenRecordset method. You use TableDef objects to create and maintain Base tables. TableDef objects have 3 collections, 5 methods, and 10 properties. The TableDef Collections The TableDef object has three collections:

• Fields is the collection that contains all the information about the database fields defined for the TableDef object. This is the default object.
• Indexes is the collection that contains all the information about the database indexes defined for the TableDef object.
• Properties is the collection that contains all the information about the current TableDef object.

Details of the Field and Index objects are covered later in this chapter. The CreateTableDef Method and the TableDef Properties The TableDef properties are set when the table is created. The values of the properties differ depending on whether the TableDef object is a native Microsoft Jet object or an attached object. Listing 9.14 shows the properties of a native Microsoft Jet TableDef object.

Add another button to the project. Set its Name property to cmdTableDef and its Caption property to TableDef. Add the code in Listing 9.14 to the cmdTableDef_Click event.

Listing 9.14. Adding the TableDef button.

The code in Listing 9.14 opens a database and then "walks through" all the table definitions in the database, listing the properties of each table. Save and run the project. Click the TableDef button and compare your screen with the one in Figure 9.13.

Figure 9.13. Viewing the TableDef properties.

---

NOTE: You also see several internal data tables in this listing. The tables that start with "MSYS" are used by Microsoft Jet to keep track of indexes, relationships, table definitions, and so on. Do not attempt to read, delete, or modify these tables. Doing so can permanently damage your database.

---

The actual properties you see on your screen my be different. There are many properties of the TableDef object. Most of them are easy to understand. You can search the Visual Basic online documentation for detailed listings on each of the properties.

---

NOTE: You may see one or more properties in your TableDefs that are not documented in the Visual Basic online documents. This is because the Microsoft Jet DAO language allows programmers to invent and store their own custom properties. You may be looking at properties invented by some other application (Microsoft Access, MS Project, custom applications, and so on).

---

The TableDef Methods Along with the CreateTable method of the database, there are five methods that you can apply to the TableDef object:

• OpenRecordset enables you to open a Table, Dynaset, or Snapshot Recordset from the TableDef object.
• RefreshLink updates and refreshes any attached table links for the TableDef object.
• CreateProperty enables you to create and store a user-defined property. See the UDP example under the Database object elsewhere in this chapter.
• CreateIndex enables you to add an index to the TableDef object. This method is covered in "The Index Data Object" section later in this chapter.
• CreateField enables you to add a new field to an existing TableDef object. You learn more about this method in "The Field Data Object" section.

Creating a New Table in the Database The code in Listing 9.15 enables you to create a very simple database and table. Add another command button to the form. Set its Name property to cmdCreateTable and its Caption property to &CreateTable. Add the code in Listing 9.15 to the cmdCreateTable_Click event.

Listing 9.15. Coding the cmdCreateTable_Click event.

The code in Listing 9.15 creates a new database (erasing any old one first), creates a new table object, creates a single field object for the table, and then appends the new objects to their respective collections. Finally, the properties of the new table are displayed. Save and run the project. Check your results against Figure 9.14.

Figure 9.14. The results of adding a new table.

Modifying and Deleting Existing Tables You can add new fields or delete existing fields by using the Append or Delete methods on the TableDef object. Add a command button with the Name property cmdModifyTable and a Caption property of Modify Table. Add the code in Listing 9.16 to the cmdModifyTable_Click event.

Listing 9.16. Coding the cmdModifyTable_Click event.

In Listing 9.16, you call the previous code section to create the table again. Then you add a new field using the Append method, and delete that field using the Delete method. Save and run the project, and check your final results against Figure 9.15.

Figure 9.15. The results of adding and deleting fields.

Attaching External Data You can attach an existing external, non-Microsoft Jet database table to an existing Microsoft Jet-format database. Attaching tables in this way gives you access to the external data using the standard Visual Basic data-access object interface. It also enables you to mix Microsoft Jet and non-Microsoft Jet data in the same database, which is great for handling queries that combine data from both sources.

---

NOTE: You can create and store queries on the attached external data, too. Queries are covered later in this chapter.

---

You cannot open a table-type Recordset on an attached table. You must use the Dynaset or Snapshot objects for accessing attached tables. Even though you must use Dynaset data objects, attached tables respond faster than external data links.

Let's illustrate attachments by adding another command button to the form. Set its Name property to cmdAttachTable and its Caption property to Attach Table. Add the code in Listing 9.17 to the cmdAttachTable_Click event.

Listing 9.17. Coding the cmdAttachTable_Click event.

The code in Listing 9.17 calls the routine that creates your test database and then opens the created database and creates a new table definition. This time, instead of creating field definitions to append to the new table definition, you create an attachment to another external database. Attachments always have two parts: the Connect string and the SourceTableName.

The Connect string contains all information needed to connect to the external database. For desktop (ISAM-type) databases, you need to supply the driver name (dBASE III, Paradox 3.x, and so on) and the device/path where the data file is located. For back-end database servers, you might need to supply additional parameters.

The SourceTableName contains the name of the data table you want to attach to the Microsoft Jet database. For desktop databases, this is the database filename in the device location (NAMES.DBF, CUSTOMERS.DBF, and so on). For back-end database servers, this is the data table name that already exists in the server database.

Save and run the project. When you click the Attach Table button, you see a few message dialogs flash by. The final dialog lists all the tables in the database (see Figure 9.16).

Figure 9.16. Viewing the attached tables dialog.

Notice that the FoxProAttachment table now appears. You can now manipulate this table like any native Microsoft Jet data table object.

---

NOTE: You also see several internal data tables in this listing. The tables that start with "MSYS" are used by Microsoft Jet to keep track of indexes, relationships, table definitions, and so on. Do not attempt to read, delete, or modify these tables. Doing so can permanently damage your database.

---

The Field Data Object

The Field object contains all the information about the data table field. In the previous section on TableDef objects, you created and deleted fields. You can also access the Field object to get information on field properties. The Field object has only one collection--the Properties collection. There are 17 properties and 4 methods. The Field Properties There are 17 Field properties. You can use these properties to determine the size and type of a field, and whether it is a native Microsoft Jet field object or an attached field from an external database. In version 3.0 Microsoft Jet formats, you can set the default value for the field, and define and enforce field-level validation rules.

Listing 9.18 shows all the properties for selected fields. Add another button to the form. Set its Name property to cmdFields and its Caption property to &Field. Add the code in Listing 9.18 to the cmdFields_Click event window.

Listing 9.18. Coding the cmdFields_Click event.

The code in Listing 9.18 creates the database and then opens a single table to access one of the fields. The rest of the code loops through the collection to list the properties for the selected field. The results are displayed in the message box. Check your screen against the one in Figure 9.17.

Figure 9.17. The Field properties in the Debug window.

The list of field properties is quite extensive. You are encouraged to check out the Visual Basic documentation for details on some of the less obvious properties. Also remember that you may be seeing properties added by other DAO applications and that there may be no documentation for these custom properties.

The Index Data Object

The Index object is used to contain information on defined indexes for the associated table. Indexes can only be built for native Microsoft Jet data tables (no attached tables allowed). You can use indexes for two purposes: to enforce data integrity rules and to speed access for single-record lookups.

Indexes are always associated with an existing data table. You must create a native Microsoft Jet data table before you can create an index. Listing 9.19 shows how to create an index through Visual Basic code and view its properties.

Add a command button to the form with a Name property of cmdIndex and a Caption property of &Index. Add the code in Listing 9.19 to the cmdIndex_Click event.

Listing 9.19. Coding the cmdIndex_Click event.

The code in Listing 9.19 seems pretty familiar, right? After creating a database and adding a table (handled by cmdCreateTable), you build and add the index. Notice that you first name the index, and then create a Field object for the target index. By adding the Field object and setting some other properties, you have completed the index definition. Finally, you append the index to the collection of indexes for the specific table.

---

TIP: Although you append indexes to a specific table object, the index name is global for the entire database. You cannot create an Index object called Index1 for Table1 and then create another Index1 for Table2. You must have unique index names.

---

Save and run the project. Click the Index button and check your results against those in Figure 9.18.

Figure 9.18. The results of adding an index.

The QueryDef Data Object

The QueryDef object contains information about a stored SQL query. SQL queries can be used as record sources for the Visual Basic data control or as the first parameter in the Recordset object. QueryDef objects run faster than inline SQL queries, because Visual Basic must go through a processing step before executing an SQL query. Stored queries (QueryDef objects) are stored in their processed format. Using QueryDef objects means there is one less processing step to go through before you see your data.

The example in Listing 9.20 creates a simple SELECT SQL query and stores it for later use. After creating the query, you apply it as a record source when creating a Recordset object. Finally, you enumerate the QueryDef properties. Add another button with its Name property set to cmdQuery and its Caption property set to &Query. Add the code in Listing 9.20 to the cmdQuery_Click code window.

Listing 9.20. Coding the cmdQuery_Click event.

Save and run the project. Check your final screen against the one in Figure 9.19.

Figure 9.19. The results of creating a QueryDef object.

The code in Listing 9.20 exposes one very important aspect of creating QueryDef objects that you might not have noticed. There is no Append method to add the QueryDef to the QueryDefs collection. It is added automatically. As soon as you define the QueryDef with a name property, you have added it to the collection.

---

TIP: You can also create a QueryDef that is not added to the QueryDefs collection. Simply execute the CreateQueryDef method with an empty name:

set qd = db.CreateQueryDef("")

You can then fill the SQL property of the query and execute it to get the resulting dataset. When you close the query, it is destroyed instead of being saved to the QueryDefs collection. This is especially handy when you want to execute dynamic SQL statements, but do not want to create and delete QueryDefs at runtime.

---

Getting Results from QueryDefs There are two basic methods for working with QueryDefs--Execute and OpenRecordset. The Execute method is used to perform SQL action queries. Action queries are SQL statements that perform some action on the data table. Examples of action queries are SQL statements that

• Add, modify, or remove table records
• Add indexes or relationship rules
• Add, modify, or remove tables from the database

The other method used when working with QueryDefs is the OpenRecordset method. This method is used to retrieve data from the tables into a programming object for manipulation.

Add another button to the form. Set its Name property to cmdRunningQDs and its Caption to Running QDs. Now enter the code from Listing 9.21 into the cmdRunningQDs_Click event

Listing 9.21. Coding the cmdRunningQDs_Click event.

P

Notice that this code creates and executes two QueryDefs. The first query is an action query--it uses the Execute method. Note also that this first query was never assigned a value for the Name property. It is treated as a temporary query by Microsoft Jet, and it is not appended to the QueryDefs collection.

The second QueryDef selects records from the data table. Because this is not an action query, the OpenRecordset method is used to perform this query. Also, because this query was given a value for the Name property, it is appended automatically to the QueryDefs collection and saved with the database.

Now save and run this code. You see the now familiar CreateTable dialog followed by two more dialogs. The first is the property list for the temporary query. Note that the Name property has been filled by Microsoft Jet with #Temporary QueryDef# and that the RecordsAffected property has been set to 1 (see Figure 9.20).

Figure 9.20. Viewing the property list for a temporary QueryDef.

The next dialog is the property list for the saved QueryDef. This query pulls data from the table into a programming object. Note that the DateCreated and LastUpdated properties are set to valid values (see Figure 9.21).

Figure 9.21. Viewing the property list for a saved QueryDef.

The ODBCDirect Connection Data Object

The Connection object is new to Visual Basic 5.0. This data object is part of the ODBCDirect data access model. This model allows programmers to access ODBC data sources without first defining a Microsoft Jet data object. The ability to open a direct connection to ODBC instead of first opening a Microsoft Jet session provides added flexibility to your programs.

The process of creating and using a Connection object begins at the workspace level. When you create a new workspace, you must explicitly mark it as an ODBCDirect workspace. You can then perform an OpenConnection method to open a new connection to an ODBC data source. Once the connection has been established, you can use the OpenRecordset, Execute, CreateQueryDef, and Close methods with which you are already familiar.

Add a new button to the form and set its Name property to cmdConnection and its Caption to Connection. Now enter the code from Listing 9.22 into the cmdConnection_Click event.

Listing 9.22. Coding the cmdConnection_Click event.

In the code in Listing 9.22, you first create a workspace object with the dbUseODBC parameter added. This creates the ODBCDirect-type workspace. Next, the code performs the Open Connection method on the workspace using the Connect string built-in program variables. This Connect string uses the default Microsoft Access driver that ships with Microsoft Office 95 or later. Notice that you are actually pointing to the guideS5.MDB database used throughout this guide. Another key point to notice is that you are now using Visual Basic DAO to open an Access database. This is not possible if you are using the standard Microsoft Jet ODBC connection.

---

TIP: You can now use ODBCDirect to open any ISAM-type database formats, including dBASE, FoxPro, Paradox, and so on, along with Microsoft Access and the back-end RDBMS formats such as SQL Server and Oracle.

---

Finally, after successfully opening the connection to the database, the Connection object properties are displayed. Unfortunately, the Connection object does not support the use of the Properties collection. This makes coding the property display a bit more labor-intensive than coding the other DAO objects.

Save and run the project. When you press the Connection button, you see the Connection property list appear on your screen (see Figure 9.22).

Figure 9.22. Viewing the Connection object property list.

The Recordset Data Object

By far, the most commonly used objects in Visual Basic programming are the objects that contain datasets. In the Microsoft Jet object model, this object is the Recordset object. Recordset objects can be created from the Database object, the Connection Object, the QueryDef object, and even from another Recordset object. This list of parent objects speaks to the importance of the Recordset as the primary data object in the Microsoft Jet DAO.

The property and method list of the Recordset also reflects its versatility and importance. We have mentioned many of the Recordset's methods in previous chapters. You'll also use the Recordset methods in the next chapter, "Creating Database Programs with Visual Basic Code." The property list of the Recordset object is also quite extensive. Even more important, the exact methods and properties available for the Recordset depend on whether the Recordset was created within an ODBCDirect workspace or a Microsoft Jet workspace.

Rather than take up space in the guide to list these methods and properties, look up the "Recordset Object, Recordset Collection Summary" topic in the Visual Basic 5 help files. This help topic lists every method and property with extensive notes regarding the differences between ODBCDirect and Microsoft Jet. You can also use this help topic as a starting point for exploring the details of each method and property.

However, to illustrate the differences and similarities between ODBCDirect Recordsets and Microsoft Jet Recordsets, add a new button to the form. Set its Name property to cmdRecordsets and its caption to Recordsets. Now add the code from Listing 9.23 to the cmdRecordsets_Click event.

Listing 9.23. Coding the cmdRecordsets_Click event.

When you save and run this routine, you see a long list of Recordset properties for each of the objects. Note that the lists are different. Even when the property names are the same, some of the values are different (see Figure 9.23).

The Relation Data Object

The last data-access object covered today is the Relation data object. This object contains information about established relationships between two tables. Relationships help enforce database referential integrity. Establishing a relationship involves selecting the two tables you want to relate, identifying the field you can use to link the tables together, and defining the type of relationship you want to establish.

Figure 9.23. Viewing Recordset property lists.

---

NOTE: The details of defining relationships are covered next week in the chapters on advanced SQL (Days 15 and 16). For now, remember that you can use the Relation object to create and maintain database relationships within Visual Basic code.

---

The final coding example for today is to create a new database, add two tables, define fields and indexes for those two tables, and then define a relationship object for the table pair. This example calls on most of the concepts you have learned today. Add one more button to the project. Set its Name property to cmdRelation and its Caption property to Re&lation. Add the code in Listing 9.24 to the cmdRelation_Click event window.

Listing 9.24. Coding the cmdRelation_Click event.

The code in Listing 9.24 performs the basic tasks. Create a database and build two tables with two fields each. Construct primary key indexes for both tables. Then create the relationship object.

Save and run the project. When you click the Relation command button, the program creates all the data objects, and then displays the resulting Relation object on the form. Compare your results to the screen in Figure 9.24.

Figure 9.24. Viewing the results of a Relation object.

Notice that you added an attribute to make this relationship enforce cascading updates, which means that any time a value is changed in the lookup table, all the corresponding values in the foreign table are updated automatically too. You can also set delete cascades. If the value is deleted from the lookup table, all corresponding records in the foreign table are deleted.

Summary

In today's lesson, you learned the features and functions of Visual Basic Microsoft Jet data access objects and ODBCDirect access objects. These objects are used within Visual Basic code to create and maintain workspaces, databases, tables, fields, indexes, queries, and relations. You learned the properties, methods, and collections of each object. You also learned how to use Visual Basic code to inspect the values in the properties, and how to use the methods to perform basic database operations.

Quiz

1. What does the Jet in the Microsoft Jet Database Engine stand for?

2. Describe the difference between a property and a method.

3. What is the top-level data-access object (DAO)?

4. What command would you issue to repair a database? Is this a method or a property?

5. What is the syntax of the CompactDatabase method?

6. What happens if you don't declare a Workspace when you open a database?

7. What data object types can be created with the OpenRecordset method?

8. What is the difference between the Execute and the ExecuteSQL methods?

9. Which TableDef method can be used to create a table in an existing database? What syntax does this method follow?

10. Which data-access object would you use to determine the data type of a table column?

11. Can you use the Index data object to build an index for a FoxPro 2.5 database?

12. What information does the QueryDef object store?

Exercise

Assume that you are a systems consultant to a large multinational corporation. You have been assigned the task of building a program in Visual Basic that creates a database to handle customer information. In this database, you need to track CustomerID, Name, Address (two lines), City, State/Province, Zip, Phone, and Customer Type.

Start a new project and add a single command button to a form that executes the code to build this database. Include the following in your code:

• A section that deletes the database if it already exists
• A table for customer information (called Customers) and a table for customer types (called CustomerTypes)
• Primary keys for both tables
• A relationship between the two tables on the Customer Type field
• A message that signifies that the procedure is complete

When you have completed the entry of this code, display the database in Visdata. Add information to both tables. Take note of how the referential integrity is enforced by deleting records from the CustomerTypes table that are used in the Customers table.