|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 9
  - 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

## Day 9

## 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 book. 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.

    Public Function ShowType(varTypeCode As Variant) As String
        `
        ` return friendly name of variable type
        `
        Dim strReturn As String
        `
        Select Case varTypeCode
            Case vbEmpty
                strReturn = "Empty"
            Case vbNull
                strReturn = "Null"
            Case vbInteger
                strReturn = "Integer"
            Case vbLong
                strReturn = "Long"
            Case vbSingle
                strReturn = "Single"
            Case vbDouble
                strReturn = "Double"
            Case vbCurrency
                strReturn = "Currency"
            Case vbDate
                strReturn = "Date"
            Case vbString
                strReturn = "String"
            Case vbObject
                strReturn = "Object"
            Case vbError
                strReturn = "Error"
            Case vbBoolean
                strReturn = "Boolean"
            Case vbVariant
                strReturn = "Variant"
            Case vbDataObject
                strReturn = "dao"
            Case vbDecimal
                strReturn = "Decimal"
            Case vbByte
                strReturn = "Byte"
            Case vbArray
                strReturn = "Array"
            Case Else
                strReturn = "[" & CStr(varTypeCode) & "]"
        End Select
        `
        ShowType = strReturn
        `
    End Function

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.

    Private Sub cmdDBEngine_Click()
        `
        ` show engine properties
        `
        On Error GoTo LocalErr
        `
        Dim objItem As Object
        Dim strMsg As String
        `
        strMsg = ""
        For Each objItem In DBEngine.Properties
            strMsg = strMsg & objItem.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & objItem.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(objItem.Type) & "}"
            strMsg = strMsg & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "DBEngine"
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
    End Sub

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:

    DBEngine.IniPath = _"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\
    ISAM Formats\FoxPro 3.0"

> 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

    DBEngine.CompactDatabase oldDatabase, NewDatabase, locale, options

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.

    Private Sub cmdDBCompact_Click()
        `
        ` compact/convert an MS db
        `
        Dim strOldDBName As String
        Dim strNewDBName As String
        Dim intEncrypt As Integer
        Dim strVersion As String
        Dim intVersion As Integer
        Dim strHeader As String
        `
    DBCompactStart:
        `
        ` init vars
        strOldDBName = ""
        strNewDBName = ""
        strVersion = ""
        strHeader = "Compact Database Example"
        `
        ` get db to read
        CommonDialog1.DialogTitle = "Open Database to Convert"
        CommonDialog1.Filter = "MS Jet | *.mdb"
        CommonDialog1.ShowOpen
        strOldDBName = CommonDialog1.filename
        `
        If Trim(strOldDBName) = "" Then Exit Sub
        `
        ` get new name to write
        CommonDialog1.DialogTitle = "Open Database to Write"
        CommonDialog1.Filter = "MS Jet | *.mdb"
        CommonDialog1.filename = "TDP_Fixed.mdb"
        CommonDialog1.ShowOpen
        strNewDBName = CommonDialog1.filename
        `
        If Trim(strNewDBName) = "" Then GoTo DBCompactStart
        `
        ` get target version (must be same or higher!)
    dbVersion:
        intVersion = 0
        strVersion = InputBox("Enter target version" & vbCrLf & "1.1, 2.0, 2.5,  Â3.0, 3.5", strHeader)
        MsgBox strVersion
        Select Case Trim(strVersion)
            Case "1.1"
                intVersion = dbVersion11
            Case "2.0"
                intVersion = dbVersion20
            Case "2.5"
                intVersion = dbVersion20
            Case "3.0"
                intVersion = dbVersion30
            Case "3.5"
                intVersion = dbVersion30
            Case Else
                MsgBox "Invalid version!", vbCritical, "Version Error"
                GoTo dbVersion
        End Select
        `
        ` encryption check
        intEncrypt = MsgBox("Encrypt this Database?", vbInformation + vbYesNo, strHeader)
        If intEncrypt = vbYes Then
            intEncrypt = dbEncrypt
        Else
            intEncrypt = dbDecrypt
        End If
        `
        ` now try to do it!
        DBEngine.CompactDatabase strOldDBName, strNewDBName, dbLangGeneral, intVersion + intEncrypt
        MsgBox "Process Completed"
        `
    End Sub

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.

    Private Sub cmdDBRegister_Click()
        `
        ` invoke ODBC registration
        `
        On Error Resume Next
        `
        Dim strDSN As String
        Dim strDriver As String
        Dim blnQuiet As Boolean
        Dim strAttrib As String
        Dim strDelim As String
        `
        strDelim = Chr(0)
        strDSN = "TDPSample"
        strDriver = "SQL Server"
        blnQuiet = False
        strAttrib = "SERVER=\\SQLSERVER2" & strDelim
        strAttrib = strAttrib & "DATABASE=ProductionData" & strDelim
        strAttrib = strAttrib & "DESCRIPTION=Sample ODBC Registration" & strDelim
        `
        DBEngine.RegisterDatabase strDSN, strDriver, blnQuiet, strAttrib
        `
    End Sub

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.

Constant | Description
dbPageTimeout | PageTimeout key
dbSharedAsyncDelay | SharedAsyncDelay key
dbExclusiveAsyncDelay | ExclusiveAsyncDelay key
dbLockRetry | LockRetry key
dbUserCommitSync | UserCommitSync key
dbImplicitCommitSync | ImplicitCommitSync key
dbMaxBufferSize | MaxBufferSize key
dbMaxLocksPerFile | MaxLocksPerFile key
dbLockDelay | LockDelay key
dbRecycleLVs | RecycleLVs key
dbFlushTransactionTimeout | FlushTransactionTimeout key

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 book. 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.

    Private Sub cmdWorkspaces_Click()
        `
        ` show workspaces
        `
        On Error GoTo LocalErr
        `
        Dim objWS As Workspace
        Dim objItem As Object
        Dim strMsg As String
        `
        strMsg = ""
        For Each objWS In DBEngine.Workspaces
            For Each objItem In objWS.Properties
                strMsg = strMsg & objItem.Name
                strMsg = strMsg & " = "
                strMsg = strMsg & objItem.Value
                strMsg = strMsg & " {"
                strMsg = strMsg & ShowType(objItem.Type) & "}"
                strMsg = strMsg & vbCrLf
            Next
            `
            MsgBox strMsg, vbInformation, "Workspaces"
            `
        Next
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

    Private Sub cmdNewWorkSpace_Click()
        `
        ` create a new workspace
        `
        Dim ws As Workspace
        Dim strWSName As String
        Dim strWSUser As String
        Dim strWSPassword As String
        `
        ` init vars
        strWSName = "ws" & App.EXEName
        strWSUser = "admin"
        strWSPassword = ""
        `
        ` create it
        Set ws = DBEngine.CreateWorkspace(strWSName, strWSUser, strWSPassword)
        `
        ` append to collection
        DBEngine.Workspaces.Append ws
        `
        ` show them all
        cmdWorkspaces_Click
        `
    End Sub

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.

Group | Method
Transactions | `BeginTrans`, `CommitTrans`, `Rollback`
Security | `CreateUser`, `CreateGroup`
Microsoft Jet | `CreateDatabase`, `OpenDatabase`, `Close`
ODBCDirect | `OpenConnection`, `Close`

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.

    Private Sub cmdCreateDB_Click()
        `
        ` create a new database
        `
        On Error Resume Next
        `
        Dim dbOne As Database
        Dim dbTwo As Database
        Dim ws As Workspace
        Dim dbTemp As Database
        `
        Dim strDBNameOne As String
        Dim strDBNameTwo As String
        Dim strWSName As String
        Dim strWSUser As String
        Dim strWSPassword As String
        Dim strMsg As String
        `
        ` init vars
        strDBNameOne = App.Path & "\CreateDBOne.mdb"
        strDBNameTwo = App.Path & "\CreateDBTwo.mdb"
        strWSName = App.EXEName
        strWSUser = "admin"
        strWSPassword = ""
        `
        ` erase dbs if they exist
        Kill strDBNameOne
        Kill strDBNameTwo
        `
        ` create workspace
        Set ws = DBEngine.CreateWorkspace(strWSName, strWSUser, strWSPassword)
        `
        ` create new jet db
        Set dbOne = ws.CreateDatabase(strDBNameOne, dbLangGeneral, dbVersion30)
        Set dbTwo = ws.CreateDatabase(strDBNameTwo, dbLangGeneral, dbVersion30)
        `
        ` now show db collection
        For Each dbTemp In ws.Databases
            strMsg = strMsg & "Name: " & dbTemp.Name & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "CreateDB"
        `
        ` now clean up your work
        dbOne.Close
        dbTwo.Close
        ws.Close
        `
        Set dbOne = Nothing
        Set dbTwo = Nothing
        Set ws = Nothing
        `
    End Sub

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.

    Private Sub cmdOpenDB_Click()
        `
        ` open the same db in two workspaces
        `
        On Error Resume Next
        `
        Dim wsReadWrite As Workspace
        Dim wsReadOnly As Workspace
        Dim dbReadWrite As Database
        Dim dbReadOnly As Database
        Dim wsTemp As Workspace
        Dim dbTemp As Database
        `
        Dim strWSrwName As String
        Dim strWSroName As String
        Dim strDBName As String
        Dim strWSUser As String
        Dim strWSPassword As String
        Dim strMsg As String
        `
        ` init vars
        strWSrwName = "wsReadWrite"
        strWSroName = "wsReadOnly"
        strWSUser = "admin"
        strWSPassword = ""
        strDBName = App.Path & "\..\..\data\books5.mdb"
        `
        ` create workspaces
        Set wsReadWrite = DBEngine.CreateWorkspace(strWSrwName, strWSUser, ÂstrWSPassword)
        Set wsReadOnly = DBEngine.CreateWorkspace(strWSroName, strWSUser, ÂstrWSPassword)
        `
        ` add them to the workspaces collection
        DBEngine.Workspaces.Append wsReadWrite
        DBEngine.Workspaces.Append wsReadOnly
        `
        ` open database in both ws
        Set dbReadWrite = wsReadWrite.OpenDatabase(strDBName)
        Set dbReadOnly = wsReadOnly.OpenDatabase(strDBName, , True)
        `
        ` now show ws collection
        For Each wsTemp In DBEngine.Workspaces
            strMsg = strMsg & "Workspace: " & wsTemp.Name & vbCrLf
            For Each dbTemp In wsTemp.Databases
                strMsg = strMsg & vbTab & "Database: " & dbTemp.Name & vbCrLf
            Next
        Next
        `
        MsgBox strMsg, vbInformation, "OpenDB"
        `
        ` cleanup code
        dbReadOnly.Close
        dbReadWrite.Close
        wsReadOnly.Close
        wsReadWrite.Close
        `
        Set dbReadOnly = Nothing
        Set dbReadWrite = Nothing
        Set wsReadOnly = Nothing
        Set wsReadWrite = Nothing
        `
    End Sub

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.

    Private Sub cmdDBProperties_Click()
        `
        ` show all database properties
        `
        On Error GoTo LocalErr
        `
        Dim ws As Workspace
        Dim db As Database
        Dim objItem As Property
        `
        Dim strDBName As String
        Dim strMsg As String
        `
        ` use db created earlier
        strDBName = App.Path & "\CreateDBOne.mdb"
        `
        ` open db in default ws
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        `
        ` enumerate all the properties of the db
        strMsg = ""
        For Each objItem In db.Properties
            strMsg = strMsg & objItem.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & objItem.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(objItem.Type) & "}"
            strMsg = strMsg & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "DBProperties"
        strMsg = ""
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

Property | Type/Value | Meaning/Use
Name | String | The name of the physical database file or the name of the ODBC data source.
Connect | String | If the data source is not a Microsoft Jet database, this property contains additional information needed to connect to the data using Microsoft Jet.
Transactions | True/False | If set to `True`, this data source supports the use of the `BeginTrans`, `CommitTrans`, and `Rollback` methods.
Updatable | True/False | If set to `True`, Visual Basic can provide updates to this data source. If set to `False`, this is a read-only data source.
Collating Order | Numeric | This value controls the order in which Microsoft Jet sorts or indexes the records. It is set by the `locale` parameter of the `CreateDatabase` method.
Query Time Out | Numeric (seconds) | This is the amount of time Microsoft Jet waits before reporting an error while waiting for the results of a query.
Version | String | Indicates the Microsoft Jet version used to create the database.
Records Affected | Numeric | Shows the number of records affected by the last database operation on this file.
ReplicaID | Numeric | This is the unique ID number of this copy of the replicated database. This is set when you initiate replication services (see Day 20, "Database Replication").
ReplicaMaster | Numeric | This is the unique ID value that identifies the Replica Master for this database (see Day 20).
Connection | Object | This is a reference to the ODBCDirect object that can be used to access this database. See the section later in this chapter on ODBCDirect data-access objects.

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:

    `
        ` use db created earlier
        `strDBName = App.Path & "\CreateDBOne.mdb"
        strDBName = App.Path
        `
        ` open db in default ws
        Set ws = DBEngine.Workspaces(0)
        `Set db = ws.OpenDatabase(strDBName)
        Set db = ws.OpenDatabase(strDBName, False, False, "Text;")
        `

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.

Group | Methods
Replication | `MakeReplica`, `PopulatePartial`, `Synchronize`
Security | `NewPassword`
Child Objects | `CreateQueryDef`, `CreateTableDef`, `CreateRelation`
Database Objects | `OpenRecordset`, `Execute`, `CreateProperty`, `Close`

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:

    Set Variable = Database.OPENRECORDSET(Source, Type, options)

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.

    Private Sub cmdOpenRS_Click()
        `
        ` open record sets
        `
        On Error Resume Next
        `
        Dim ws As Workspace
        Dim db As Database
        Dim rsTable As Recordset
        Dim rsDynaset As Recordset
        Dim rsSnapshot As Recordset
        Dim rsTemp As Recordset
        `
        Dim strDBName As String
        Dim strRSTable As String
        Dim strRSDynaset As String
        Dim strRSSnapshot As String
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\..\..\data\books5.mdb"
        strRSTable = "Buyers"
        strRSDynaset = "Publishers"
        strRSSnapshot = "Authors"
        `
        ` create ws and open db
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        `
        ` create rs objects
        Set rsTable = db.OpenRecordset(strRSTable, dbOpenTable)
        Set rsDynaset = db.OpenRecordset(strRSDynaset, dbOpenDynaset)
        Set rsSnapshot = db.OpenRecordset(strRSSnapshot, dbOpenSnapshot)
        `
        ` enumerate recordsets in collection
        strMsg = ""
        For Each rsTemp In db.Recordsets
            strMsg = strMsg & rsTemp.Name & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "OpenRS"
        `
    End Sub

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.

    Private Sub cmdExecute_Click()
        `
        ` execute an SQL statement
        `
        Dim ws As Workspace
        Dim db As Database
        `
        Dim strDBName As String
        Dim strSQL As String
        Dim lngRecords As Long
        `
        ` init vars
        strDBName = App.Path & "\..\..\data\books5.mdb"
        strSQL = "DELETE FROM NewAuthors WHERE AUID<10"
        lngRecords = 0
        `
        ` open db in default ws
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        `
        ` perform SQL & get results
        db.Execute strSQL, dbFailOnError
        lngRecords = db.RecordsAffected
        `
        ` show results
        MsgBox CStr(lngRecords), vbInformation, "Deleted Records"
        `
        ` clean up
        db.Close
        ws.Close
        Set db = Nothing
        Set ws = Nothing
        `
    End Sub

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.

    Private Sub cmdMakeUDP_Click()
        `
        ` add user-defined properties
        `
        On Error Resume Next
        `
        Dim ws As Workspace
        Dim db As Database
        Dim pr As Property
        Dim prTemp As Property
        `
        Dim strDBName As String
        Dim strUDPName As String
        Dim intUDPType As Integer
        Dim varUDPValue As Variant
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\CreateDBOne.mdb"
        `
        ` open ws and db
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        `
        ` add first UDP
        strUDPName = "DBAdmin"
        intUDPType = dbText
        varUDPValue = "D.B. Guru"
        `
        db.Properties.Delete strUDPName
        Set pr = db.CreateProperty(strUDPName, intUDPType, varUDPValue)
        db.Properties.Append pr
        `
        ` add second UDP
        strUDPName = "Programmer"
        intUDPType = dbText
        varUDPValue = "V.B. Coder"
        `
        db.Properties.Delete strUDPName
        Set pr = db.CreateProperty(strUDPName)
        pr.Type = intUDPType
        pr.Value = varUDPValue
        db.Properties.Append pr
        `
        ` now show results
        For Each prTemp In db.Properties
            strMsg = strMsg & prTemp.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & prTemp.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(prTemp.Type) & "}"
            strMsg = strMsg & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "MakeUDP"
        `
        ` cleanup
        db.Close
        ws.Close
        Set db = Nothing
        Set ws = Nothing
        `
    End Sub

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.

    Private Sub cmdTableDef_Click()
        `
        ` show tabledef properties
        `
        On Error GoTo LocalErr
        `
        Dim ws As Workspace
        Dim db As Database
        Dim td As TableDef
        Dim pr As Property
        `
        Dim strDBName As String
        Dim strTDName As String
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\..\..\data\books5.mdb"
        strTDName = "NewTable"
        `
        ` open ws and db
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        `
        ` now enumerate the empty table defs
        strMsg = ""
        For Each td In db.TableDefs
            For Each pr In td.Properties
                strMsg = strMsg & pr.Name
                strMsg = strMsg & " = "
                strMsg = strMsg & pr.Value
                strMsg = strMsg & " {"
                strMsg = strMsg & ShowType(pr.Type) & "}"
                strMsg = strMsg & vbCrLf
            Next
            `
            MsgBox strMsg, vbInformation, "TableDefs"
            strMsg = ""
            `
        Next
        `
        db.Close
        ws.Close
        Set pr = Nothing
        Set td = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

    Private Sub cmdCreateTable_Click()
        `
        ` create a new table in a database
        `
        On Error Resume Next
        `
        Dim ws As Workspace
        Dim db As Database
        Dim td As TableDef
        Dim fl As Field
        Dim pr As Property
        `
        Dim strDBName As String
        Dim strTDName As String
        Dim strFLName As String
        Dim intFLType As Integer
        Dim strMsg As String
        `
        ` init values
        strDBName = App.Path & "\NewDB.mdb"
        strTDName = "NewTable"
        strFLName = "NewField"
        intFLType = dbText
        `
        ` erase db if it's there
        Kill strDBName
        `
        ` open ws and create db
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.CreateDatabase(strDBName, dbLangGeneral, dbVersion30)
        `
        ` create a new table
        Set td = db.CreateTableDef(strTDName)
        `
        ` create a new field in table
        Set fl = td.CreateField(strFLName, intFLType)
        `
        ` add new objects to collections
        td.Fields.Append fl
        db.TableDefs.Append td
        `
        ` now show new table properties
        On Error GoTo LocalErr
        strMsg = ""
        For Each pr In td.Properties
            strMsg = strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type) & "}"
            strMsg = strMsg & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "CreateTable"
        `
        ` clean up
        db.Close
        ws.Close
        Set pr = Nothing
        Set td = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

    Private Sub cmdModifyTable_Click()
        `
        ` modify an existing table
        `
        On Error Resume Next
        `
        Dim ws As Workspace
        Dim db As Database
        Dim td As TableDef
        Dim fl As Field
        `
        Dim strDBName As String
        Dim strTDName As String
        Dim strFLName As String
        Dim intFLType As Integer
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\NewDB.mdb"
        strTDName = "NewTable"
        strFLName = "FollowDate"
        intFLType = dbDate
        `
        ` first create table with other subroutine
        cmdCreateTable_Click
        `
        ` now open ws & db & td
        Set ws = DBEngine.Workspaces(0)
        Set db = OpenDatabase(strDBName)
        Set td = db.TableDefs(strTDName)
        `
        ` add a new field
        Set fl = td.CreateField(strFLName, intFLType)
        td.Fields.Append fl
        `
        ` make list of fields
        strMsg = "Appended Field:"
        For Each fl In td.Fields
            strMsg = strMsg & vbTab & fl.Name & vbCrLf
        Next
        `
        ` now delete the new field
        td.Fields.Delete strFLName
        `
        ` make list again
        strMsg = strMsg & "Deleted Field:"
        For Each fl In td.Fields
            strMsg = strMsg & vbTab & fl.Name & vbCrLf
        Next
        `
        ` show list
        MsgBox strMsg, vbInformation, "Deleted Field"
        `
        ` clean up
        db.Close
        ws.Close
        Set fl = Nothing
        Set td = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
    End Sub

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.

    Private Sub cmdAttachTable_Click()
        `
        ` attach a non-jet table to database
        `
        Dim ws As Workspace
        Dim db As Database
        Dim td As TableDef
        `
        Dim strDBName As String
        Dim strATName As String
        Dim strATDBType As String
        Dim strATDBName As String
        Dim strATSrcName As String
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\NewDB.mdb"
        strATName = "FoxProAttachment"
        strATDBName = App.Path
        strATDBType = "FoxPro 2.5;"
        strATSrcName = "Customer.dbf"
        `
        ` call routine to create table
        cmdCreateTable_Click
        `
        ` now open ws & db
        Set ws = DBEngine.Workspaces(0)
        Set db = OpenDatabase(strDBName)
        `
        ` add a new tabldef
        Set td = db.CreateTableDef(strATName)
        `
        ` define the new def as an attachment
        td.Connect = strATDBType & "DATABASE=" & strATDBName
        td.SourceTableName = strATSrcName
        `
        ` append attachment to collection
        db.TableDefs.Append td
        `
        ` show list of tables
        strMsg = ""
        For Each td In db.TableDefs
            strMsg = strMsg & td.Name & vbCrLf
        Next
        MsgBox strMsg, vbInformation, "AttachTable"
        `
        db.Close
        ws.Close
        Set td = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
    End Sub

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.

    Private Sub cmdFields_Click()
        `
        ` show all the field properties of a table field
        `
        On Error GoTo LocalErr
        `
        Dim ws As Workspace
        Dim db As Database
        Dim td As TableDef
        Dim fl As Field
        Dim pr As Property
        `
        Dim strDBName As String
        Dim strTDName As String
        Dim strFLName As String
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\NewDB.mdb"
        strTDName = "NewTable"
        strFLName = "NewField"
        `
        ` build new database & table
        cmdCreateTable_Click
        `
        ` now open ws and db and td
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        `
        ` open table and get a field
        Set td = db.TableDefs(strTDName)
        Set fl = td.Fields(strFLName)
        `
        ` show properties of the field
        strMsg = ""
        For Each pr In fl.Properties
            strMsg = strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type) & "}"
            strMsg = strMsg & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "Fields"
        `
        ` cleanup
        db.Close
        ws.Close
        Set pr = Nothing
        Set fl = Nothing
        Set td = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

    Private Sub cmdIndex_Click()
        `
        ` create a new index and display its properties
        `
        Dim ws As Workspace
        Dim db As Database
        Dim td As TableDef
        Dim ix As Index
        Dim fl As Field
        Dim pr As Property
        `
        Dim strDBName As String
        Dim strTDName As String
        Dim strFLName As String
        Dim strIXName As String
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\NewDB.mdb"
        strTDName = "NewTable"
        strFLName = "NewField"
        strIXName = "PKNewTable"
        `
        ` create db and table
        cmdCreateTable_Click
        `
        ` open ws, db and table
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        Set td = db.TableDefs(strTDName)
        `
        ` now create an index
        Set ix = td.CreateIndex(strIXName)
        Set fl = ix.CreateField(strFLName)
        ix.Required = True
        ix.Primary = True
        `
        ` add field to index's fields collection
        ix.Fields.Append fl
        `
        ` add index to table's index collection
        td.Indexes.Append ix
        `
        ` now show index properties
        strMsg = ""
        For Each pr In ix.Properties
            strMsg = strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type)
            strMsg = strMsg & "}"
            strMsg = strMsg & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "Index"
        `
        ` clean up
        db.Close
        ws.Close
        Set pr = Nothing
        Set fl = Nothing
        Set ix = Nothing
        Set td = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

    Private Sub cmdQueryDef_Click()
        `
        ` create a stored query
        `
        On Error Resume Next
        `
        Dim ws As Workspace
        Dim db As Database
        Dim qd As QueryDef
        Dim pr As Property
        `
        Dim strDBName As String
        Dim strQDName As String
        Dim strQDSQL As String
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\NewDB.mdb"
        strQDName = "qryNewQuery"
        strQDSQL = "SELECT * FROM NewTable WHERE NewField<>NULL"
        `
        ` create db & table
        cmdCreateTable_Click
        `
        ` open ws and db
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        `
        ` create a new query
        Set qd = db.CreateQueryDef(strQDName)
        qd.SQL = strQDSQL
        `
        ` show properties of the querydef
        strMsg = ""
        For Each pr In qd.Properties
            strMsg = strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type)
            strMsg = strMsg & "}" & vbCrLf
        Next
        `
        MsgBox strMsg, vbInformation, "QueryDef"
        `
        db.Close
        ws.Close
        Set pr = Nothing
        Set qd = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

    Private Sub cmdRunningQDs_Click()
        `
        ` running stored queries
        `
        On Error GoTo LocalErr
        `
        Dim ws As Workspace
        Dim db As Database
        Dim qd As QueryDef
        Dim rs As Recordset
        Dim pr As Property
        `
        Dim strDBName As String
        Dim strQDName As String
        Dim strQDSQLInsert As String
        Dim strQDSQLSelect As String
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\NewDB.mdb"
        strQDName = "qryNewQuery"
        strQDSQLInsert = "INSERT INTO NewTable VALUES(`Mike')"
        strQDSQLSelect = "SELECT * FROM NewTable"
        `
        `
        ` create db & table
        cmdCreateTable_Click
        `
        ` open ws & db
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        `
        ` create temp query and execute
        Set qd = db.CreateQueryDef("")
        qd.SQL = strQDSQLInsert
        qd.Execute
        `
        ` view query properties
        strMsg = ""
        For Each pr In qd.Properties
            strMsg = strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type)
            strMsg = strMsg & "}" & vbCrLf
        Next
        MsgBox strMsg, vbInformation, "TempQueryDef"
        `
        ` create stored query and get results
        Set qd = db.CreateQueryDef(strQDName)
        qd.SQL = strQDSQLSelect
        Set rs = qd.OpenRecordset(dbOpenDynaset)
        `
        ` view query properties
        strMsg = ""
        For Each pr In qd.Properties
            strMsg = strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type)
            strMsg = strMsg & "}" & vbCrLf
        Next
        MsgBox strMsg, vbInformation, "SavedQueryDef"
        `
        rs.Close
        db.Close
        ws.Close
        Set pr = Nothing
        Set rs = Nothing
        Set qd = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

    Private Sub cmdConnection_Click()
        `
        ` show use of ODBCDirect Connection object
        `
        Dim ws As Workspace
        Dim co As Connection
        `
        Dim strWSName As String
        Dim strCOName As String
        Dim strDSN As String
        Dim strDBQ As String
        Dim strCOConnect As String
        Dim strMsg As String
        `
        ` init vars
        strWSName = "wsODBCDirect"
        strCOName = "TDPConnection"
        strDSN = "DSN=MS Access 7.0 Database;"
        strDBQ = "DBQ=C:\TYSDBVB5\Source\Data\Books5.mdb"
        strCOConnect = "ODBC;" & strDSN & strDBQ
        `
        ` create ws for ODBCDirect
        Set ws = DBEngine.CreateWorkspace(strWSName, "admin", "", dbUseODBC)
        `
        ` open a connection
        Set co = ws.OpenConnection(strCOName, dbDriverNoPrompt, False, strCOConnect)
        `
        ` show properties of connection object
        ` connection objects *do not* have a properties collection!
        strMsg = strMsg & "Name = " & co.Name & vbCrLf
        strMsg = strMsg & "Connect = " & co.Connect & vbCrLf
        strMsg = strMsg & "Database = " & co.Database.Name & vbCrLf
        strMsg = strMsg & "QueryTimeOut = " & co.QueryTimeout & vbCrLf
        strMsg = strMsg & "RecordsAffected = " & co.RecordsAffected & vbCrLf
        strMsg = strMsg & "StillExecuting = " & co.StillExecuting & vbCrLf
        strMsg = strMsg & "Transactions = " & co.Transactions & vbCrLf
        strMsg = strMsg & "Updatable = " & co.Updatable & vbCrLf
        `
        MsgBox strMsg, vbInformation, "Connection"
        `
        ` clean up
        co.Close
        ws.Close
        Set co = Nothing
        Set ws = Nothing
        `
    End Sub

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 `BOOKS5.MDB` database used throughout this book. 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 book 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.

    Private Sub cmdRecordsets_Click()
        `
        ` demonstrate ODBCDirect and MS Jet Recordsets
        `
        On Error GoTo LocalErr
        `
        Dim wsDirect As Workspace
        Dim wsJet As Workspace
        Dim db As Database
        Dim co As Connection
        Dim pr As Property
        Dim rsDirect As Recordset
        Dim rsJet As Recordset
        `
        Dim strWSDName As String
        Dim strWSJName As String
        Dim strDBName As String
        Dim strCOName As String
        Dim strRSDName As String
        Dim strRSJName As String
        Dim strConnect As String
        Dim strMsg As String
        `
        ` init vars
        strWSDName = "wsDirect"
        strWSJName = "wsJet"
        strCOName = "coDirect"
        strConnect = "ODBC;DSN=MS Access 7.0 Database;DBQ=C:\ ÂTYSDBVB5\Source\Data\books5.mdb"
        strDBName = App.Path & "\..\..\Source\Data\books5.mdb"
        strRSDName = "SELECT * FROM Buyers"
        strRSJName = "SELECT * FROM Publishers"
        `
        ` establish ODBCDirect connection
        Set wsDirect = DBEngine.CreateWorkspace(strWSDName, "admin", "", dbUseODBC)
        Set co = wsDirect.OpenConnection(strCOName, dbDriverNoPrompt, False, ÂstrConnect)
        Set rsDirect = co.OpenRecordset(strRSDName, dbOpenForwardOnly)
        `
        ` establish MS Jet connection
        Set wsJet = DBEngine.CreateWorkspace(strWSJName, "admin", "")
        Set db = wsJet.OpenDatabase(strDBName)
        Set rsJet = db.OpenRecordset(strRSJName, dbOpenDynaset)
        `
        ` now show results
        strMsg = ""
        For Each pr In rsDirect.Properties
            strMsg = strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type)
            strMsg = strMsg & "}"
            strMsg = strMsg & vbCrLf
        Next
        MsgBox strMsg, vbInformation, "rsDirect"
        `
        strMsg = ""
        For Each pr In rsJet.Properties
            strMsg = strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type)
            strMsg = strMsg & "}"
            strMsg = strMsg & vbCrLf
            MsgBox strMsg
        Next
        MsgBox strMsg, vbInformation, "rsJet"
        `
        ` cleanup
        rsDirect.Close
        rsJet.Close
        db.Close
        co.Close
        wsDirect.Close
        wsJet.Close
        `
        Set pr = Nothing
        Set rsDirect = Nothing
        Set rsJet = Nothing
        Set db = Nothing
        Set co = Nothing
        Set wsDirect = Nothing
        Set wsJet = Nothing
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

    Private Sub cmdRelations_Click()
        `
        ` demonstrate relationship objects
        `
        On Error Resume Next
        `
        Dim ws As Workspace
        Dim db As Database
        Dim td As TableDef
        Dim fl As Field
        Dim ix As Index
        Dim rl As Relation
        Dim pr As Property
        `
        Dim strDBName As String
        Dim strTDLookUp As String
        Dim strTDMaster As String
        Dim strIXLookUp As String
        Dim strIXMaster As String
        Dim strRLName As String
        Dim strMsg As String
        `
        ` init vars
        strDBName = App.Path & "\RelDB.mdb"
        strTDLookUp = "ValidUnits"
        strTDMaster = "MasterTable"
        strIXLookUp = "PKUnits"
        strIXMaster = "PKMaster"
        strRLName = "relUnitMaster"
        `
        ` erase old db if it's there
        Kill strDBName
        `
        ` open ws and create db
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.CreateDatabase(strDBName, dbLangGeneral, dbVersion30)
        `
        ` now create the lookup list table & fields
        Set td = db.CreateTableDef(strTDLookUp)
        Set fl = td.CreateField("UnitID", dbText, 10)
        td.Fields.Append fl
        Set fl = td.CreateField("Description", dbText, 50)
        td.Fields.Append fl
        `
        ` now add table to database
        db.TableDefs.Append td
        `
        ` index the new table
        Set ix = td.CreateIndex(strIXLookUp)
        ix.Primary = True
        ix.Required = True
        Set fl = ix.CreateField("UnitID")
        ix.Fields.Append fl
        td.Indexes.Append ix
        `
        ` now create master record table
        Set td = db.CreateTableDef(strTDMaster)
        Set fl = td.CreateField("MasterID", dbText, 20)
        td.Fields.Append fl
        Set fl = td.CreateField("MasterUnitID", dbText, 10)
        td.Fields.Append fl
        `
        ` add index to the master table
        Set ix = td.CreateIndex(strIXMaster)
        ix.Primary = True
        ix.Required = True
        Set fl = ix.CreateField("MasterID")
        ix.Fields.Append fl
        td.Indexes.Append ix
        `
        ` now add defined table
        db.TableDefs.Append td
        `
        ` *now* do the relationship!
        Set rl = db.CreateRelation(strRLName)
        rl.Table = strTDLookUp ` table for lookups
        rl.ForeignTable = strTDMaster ` table to verify
        Set fl = rl.CreateField("UnitID")
        fl.ForeignName = "MasterUnitID"
        rl.Fields.Append fl
        rl.Attributes = dbRelationUpdateCascade
        db.Relations.Append rl
        `
        ` now show relation object
        strMsg = "Relation Properties:" & vbCrLf
        For Each pr In rl.Properties
            strMsg = vbTab & strMsg & pr.Name
            strMsg = strMsg & " = "
            strMsg = strMsg & pr.Value
            strMsg = strMsg & " {"
            strMsg = strMsg & ShowType(pr.Type)
            strMsg = strMsg & "}"
            strMsg = strMsg & vbCrLf
        Next
        `
        strMsg = strMsg & "Relation Fields:" & vbCrLf
        For Each fl In rl.Fields
            strMsg = vbTab & strMsg & fl.Name & vbCrLf
            strMsg = vbTab & strMsg & fl.ForeignName
        Next
        MsgBox strMsg, vbInformation, "Relation"
        `
        ` cleanup
        db.Close
        ws.Close
        `
        Set pr = Nothing
        Set fl = Nothing
        Set ix = Nothing
        Set td = Nothing
        Set db = Nothing
        Set ws = Nothing
        `
        Exit Sub
        `
    LocalErr:
        strMsg = strMsg & "<err>"
        Resume Next
        `
    End Sub

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.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.