Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Web Database Developer's Guide with Visual Basic 5

World Wide Web Database Developer's Guide with Visual Basic 5




-9-

Visual Basic 5 Data Access Features

Up to this point, you've covered an overview of database design and SQL, but very little information had to do specifically with Visual Basic. Well that's about to change. It's time to review the Visual Basic data access objects you will be using in an intranet and Internet environment.

The primary purpose of this chapter is to review the various Visual Basic data access objects used throughout this book. Code examples with lots of comments are provided to help refresh your Visual Basic data access skills. If you find you need more detail or do not understand the code examples, I suggest reading other Sams books that concentrate specifically on Visual Basic database development. If you're already an expert in Visual Basic data access objects, I suggest that you review this chapter. Several data access objects have been modified, such as the edition of RDO asynchronous events, and new objects have been added, such as ODBCDirect. This chapter also introduces a new and important data access object designed for Active Server Page Internet development--and maybe the future DAO and RDO replacement--called Active Data Objects.


TIP: If you are new to Visual Basic and want to learn more, check out Teach Yourself Visual Basic 5 in 21 Days by Sams Publishing or Visual Basic 5 Interactive Course by Waite Group Press.


An Overview of Visual Basic Data Access Features

When you discuss Visual Basic data access methods, it sounds like alphabet soup with acronyms such as DAO, RDO, ODBC, and ADO. Why are there so many different ways to access a database? The data access methods available to a Visual Basic programmer have evolved over time as Visual Basic has become a richer development environment for all types of database applications, from file-based database applications (such as dBASE and Access), to client/server database applications (such as Oracle, Sybase, Microsoft SQL Server, and Ingres), to the Internet. The following list shows the various data access methods available to a Visual Basic developer when developing intranet/Internet applications:

Let's take a brief look at each of these data access methods and then make a determination of the proper times to use each method.

Native Database APIs

Native database APIs (Application Programming Interfaces) are typically used in a client/server environment in which a developer creates an application using a proprietary set of native database APIs. An example of a native database API is VBSQL for Microsoft SQL Server. Native database APIs enable a developer to write applications that use the full functionality of the database with no middleware and provide a high level of performance between the application and the database server. Building applications using the native database API typically requires writing more code than object-based solutions require. One drawback of using native database APIs is that when a Visual Basic application is written using a native database API, the application can't be easily ported to another database because the database APIs are propriety. (For example, you could not easily port a Visual Basic Oracle native API application to a Visual Basic Microsoft SQL Server application.)

ODBC

ODBC stands for Open Database Connectivity. ODBC is an open standard that provides a common set of API calls to manipulate databases. Application developers can write applications that make ODBC calls and will work with many databases, instead of writing programs specifically for a particular database (which is the case with native database APIs). Some advantages to the ODBC API over native database APIs are as follows:

Applications built with ODBC can take advantage of the majority of the functionality of the database and are as fast as applications developed using native database drivers.


NOTE: You might find people who, upon hearing about ODBC, automatically think slow. I have run many tests on my own, attended numerous technical conferences, and have shown repeatedly that application processing speed using ODBC APIs versus native database APIs is about the same. I think ODBC got a bad name from poorly implemented version 1.0 drivers and Visual Basic applications using DAO and ODBC improperly.

ODBC applications can be ported easily by switching the ODBC driver instead of recoding the entire application. Using ODBC APIs requires a lot of coding, in some cases exceeding the amount of code required to write native database API applications. The architecture overview of an application accessing a remote database via ODBC is shown in Figure 9.1.

FIGURE 9.1. An application accessing a remote database via ODBC.

DAO

A DAO (data-access object) is probably the most familiar data access method available to Visual Basic programmers. DAO provides Visual Basic developers who have little or no SQL experience with a simple object model that can be used to create database applications. DAO was first introduced in Visual Basic 3 with Jet 1.1. Jet is the native database engine for the Microsoft Access database and is shared by Microsoft Visual Basic, Visual C++, Visual J++, and Access. DAO was enhanced to a 32-bit version, Jet 3.0, in Visual Basic 4 and has been further improved in Visual Basic 5 with Jet version 3.5. DAO consists of the Microsoft Jet engine and the data-access objects. The architecture overview of an application accessing an Access database and remote database via DAO is shown in Figure 9.2.

FIGURE 9.2. An application accessing an Access database and a remote database via DAO.

DAO can be used to create database applications with a lot less code than is required by either native database APIs or the ODBC APIs. The Jet engine is a very powerful feature-rich database engine. DAO can also be used against client/server databases using ODBC. However, accessing client/server databases via DAO adds overhead, less functionality, and, in many cases, poor performance when compared to the ODBC API or native database APIs. Microsoft addressed some of the performance problem in DAO 3.0. Later in this chapter, you learn how the performance problem has been addressed further in Visual Basic 5 with DAO and ODBCDirect.

RDO

To answer the cries for more performance and an object model designed specifically to handle ODBC client/server databases, Microsoft released RDO (Remote Data Objects) in the Visual Basic 4 Enterprise edition. RDO is a thin object model layered on top of the ODBC API designed to simplify and reduce the amount of code required to use the ODBC API. RDO enables you to use most of the functionality of the backend database, with higher performance than DAO and only a slight performance degradation compared to making direct ODBC API calls or using native database APIs. The architecture overview of an application accessing a remote database via RDO is shown in Figure 9.3.

FIGURE 9.3. An application accessing a remote database via RDO.

ODBCDirect

You bought Visual Basic 4 Enterprise edition and you were an experienced DAO programmer, but you needed to access remote ODBC databases as fast as possible, so you put in the extra work to learn a brand-new data-access object model--RDO. Welcome to Visual Basic 5 and another way to access client/server databases! This time it is that familiar object model DAO. Using ODBCDirect, you can now create applications that use the DAO object model, bypass the Jet engine, and access ODBC remote databases through RDO. That's right. ODBCDirect layers the DAO object model on top of RDO, as shown in Figure 9.4.

FIGURE 9.4. An application accessing a remote database via ODBCDirect.

The advantage of ODBCDirect is that your application is immediately smaller because you don't load the Jet engine (which saves over 1MB), and you don't have to learn the RDO object model. Converting an existing Access database application to a remote ODBC database can now be accomplished with little or no code changes using ODBCDirect.

OLE DB and ADO

The data access methods discussed so far were created to handle client/server RDBMS and file-based database applications using Visual Basic. But with the increasing popularity of the Internet, expanding enterprise systems, and user demands to share and distribute data from many different data sources, a new object model designed for different data sources is required to handle various types of data such as RDBMS, spreadsheets, e-mail, groupware, and so on. Enter the OLE DB specification. OLE DB is a series of APIs based on COM that enables a C/C++ programmer to get at data from OLE DB providers. ADO (Active Data Objects) is a high-level object model that sits on top of OLE DB and gives Visual Basic developers a way to get at OLE DB databases. ADO does not ship with Visual Basic 5. It is shipped as part of the Internet Information Server Version 3.0. ADO will be the primary data access object used when creating Active Server Pages. OLE DB and ADO are discussed in detail in Chapter 21, "Active Server Pages, OLE DB, and Active Data Objects." Several of this book's sample applications make use of ADO. OLE DB and ADO are mentioned now to let you know where they currently fit into the intranet/Internet development environment, because ADO will be all the rage when developing Active Server Pages. Recent reports and speculation, published at the time this book was written, state that Microsoft will begin to work on consolidating the RDO and DAO interface into a single data-access object. Could this be ADO? Possibly....

Which Object Do I Use?

With the many different data access methods available to a Visual Basic Internet/intranet database developer, which methods should you use? I will quickly rule out using native database APIs and calling the ODBC API directly. Both methods provide great performance, and in the days of Visual Basic 3, 16-bit clients, and client/server databases, they were much needed technologies that saved many complex projects. Both methods, however, require far too much low-level coding, providing too many opportunities to make mistakes in an ActiveX component. Also, the lack of an object model and the increased amount of code required reduces the overall productivity of the programmer. Finally, the direction Microsoft is taking with intranet and Internet applications is clearly object-based, whereas these two programming APIs are not.

For developing intranet applications, you can choose between DAO, RDO, and ODBCDirect. If the database you are using is a Microsoft Access database, use DAO. If you are accessing a client/server database such as Sybase, Oracle, or Microsoft SQL Server, use RDO. If you are porting an existing Access database application to a remote ODBC database, use ODBCDirect. Another factor in data access selection will be the edition of Visual Basic you have purchased. RDO is available only in the Enterprise Edition of Visual Basic.

For developing Internet server-side applications, ActiveX data-aware components, or OLEISAPI components, the data access choices are the same as for an intranet with the additional ADO option. Active Data Objects controlled via Active Server Pages and VBScript will be the primary method for database access from within an Internet server-side script because the ADO object model is supported by Active Server Pages.

DAO Refresher

In this section, you review commonly used DAO objects, properties, and methods, along with sample code. DAO is the object interface, provided to the Visual Basic programmer, that sits on top of the Jet engine. DAO's native database, accessed via Jet and DAO, is the Microsoft Access database. You can also access ODBC or ISAM databases using DAO and Jet. Let's get started by taking a look at the DAO object model shown in Figure 9.5.

FIGURE 9.5. The DAO object model.

The dbEngine Object

Looking at Figure 9.5, you can see that the dbEngine object is at the top of the object hierarchy. dbEngine contains all the other DAO objects and is responsible for overall engine options. You can find out the version of DAO using the Version property. You can also perform Access database maintenance functions, such as a database repair and compact, using the RepairDatabase and CompactDatabase methods of the dbEngine object.

The Workspace Object

A Workspace object provides you with a way to manage transactions and security by providing a session object for your application. You can create multiple Workspace objects or use the default Workspace object automatically created for you when you use DAO--dbEngine.Workspaces(0), where Workspaces is the collection of Workspace objects. You can use Workspace objects to manage transactions or connections to multiple databases. When you begin a transaction on a Workspace, for example, the commit or rollback of the transaction affects all data modifications made within the Workspace.

The Database Object

An open database is a Database object that contains all the information about the open database, such as table definitions. The syntax for the OpenDatabase method is as follows:

Set db = workspace.OpenDatabase (name, options, read_only, connect)

In this syntax, workspace is optional (defaults to workspaces(0)), name is the name of the database you want to open, options are various options available when opening the database, read_only is set to true to open an Access database in read-only mode, and connect is used for an ODBC connection string when connecting to an ODBC client/server database. For example, the following code opens the Visual Basic sample Access database, biblio.mdb:

Dim dbBiblio as Database
Set dbBiblio = OpenDatabase("biblio.mdb")

When dealing with DAO, you use the Database object quite frequently, especially the following properties:

Important Database object methods are shown in Table 9.1.

Table 9.1. Important DAO Database Object Methods.

Method Description
Close Closes the open Database object.
CreateProperty Enables you to create a custom property.
CreateQueryDef Creates a new querydef object.
CreateTableDef Creates a new table.
Execute Executes a SQL string.
OpenRecordset Opens a new Recordset object.

The Recordset Object

The Recordset object is the workhorse of DAO. You will use them all the time! A Recordset object represents the records returned in the underlying query or in a table. The syntax to create a Recordset is as follows:

Set rs = database.OpenRecordset (RecordSorce [, Recordtype[,
ÂRecordOptions, [lockedits ]]])

In this syntax, RecordSource is the query string or table name to create the recordset. RecordOptions allows you to set options such as creating pass-through queries to client/server databases. lockedits enables programmers to specify various locking options. What about the Recordtype option? The Recordtype option enables you to pick the type of recordset you want. Let's take a closer look at the possible values for the Recordtype option. dbOpenTable The value of dbOpenTable creates a table type recordset. The table type recordset can be used only when working with an Access database and creates a recordset with all of the rows and columns in the table. You can add, update, or delete records in a table recordset. dbOpenDynaset dbOpenDynaset creates a dynaset recordset. A dynaset recordset is a dynamic recordset created by a query that can be used to add, update, or delete records in the underlying table or tables that make up the query. A keyset of all the records that make up the dynaset is generated when a dynaset is created. Records added or changed through the dynaset's AddNew and Edit methods will be reflected in the dynaset. Records added or deleted by other users after the dynaset's keyset has been created will not appear as members of the dynaset. dbOpenSnapShot dbOpenSnapshot creates a snapshot recordset. A snapshot is a static copy (picture) of the query at the time the snapshot was created. You cannot use a snapshot to add, update, or delete records in the table or tables in the query. When a snapshot is created, all the records (data) that make up the recordset are copied to the client machine and stored in memory or on the disk in the Windows temporary directory. So avoid creating snapshots that return a lot of records! dbOpenForwardOnly dbOpenForwardOnly is exactly the same as a snapshot recordset, except no cursor is created to manage the recordset, and you can walk only forward through the recordset. Performance is improved for single pass recordsets by not adding the overhead of creating a cursor or allowing backward scrolling. Remember that you can't add, update, or delete records when using a forward-only DAO recordset.

Recordset Navigation

Navigating through a recordset is quite simple. The following methods can be used to move through the recordset:

To help determine whether you are at the end of the recordset or the start of the recordset, use the recordset properties BOF and EOF. The BOF and EOF properties return Boolean values (TRUE/FALSE), based on the current position of the recordset. BOF is TRUE if the current recordset is before the first record, and it is FALSE if it is on or past the first record. EOF is TRUE if the recordset is past the last record in the recordset, and it is FALSE if it is on or before the last record in the recordset. If both BOF and EOF are set to TRUE and the recordset RecordCount property is zero, no records are contained in the recordset. The code sample in Listing 9.1 shows an example of opening a recordset and walking through the recordset.

Listing 9.1. An example of navigating through a DAO recordset.

Private Sub Command1_Click()

`The following sample code demonstrates how to open and navigate through
`a recordset. This sample is based off of the sample
`Access database that ships with Visual Basic
`Biblio.mdb.

`Declare Variables
Dim dbBiblio As Database      `DAO Database Object
Dim recAuthors As Recordset   `DAO Recordset Object
Dim strSQL As String

`Set up generic error handler to trap any errors
On Error GoTo DB_Error

`Open the database
Set dbBiblio = OpenDatabase("Biblio.mdb")

`Set up the SQL string for the recordset
strSQL = "Select Author,Title from Authors a, [Title Author] b, Titles c " _
        & "Where a.Au_ID = b.Au_ID And b.ISBN = c.ISBN"

`Open a SnapShot Recordset
Set recAuthors = dbBiblio.OpenRecordset(strSQL, dbOpenSnapshot)

`Check for an empty Recordset
If Not recAuthors.EOF And Not recAuthors.BOF And recAuthors.RecordCount > 0 Then
    `The recordset is not empty
    `Navigate through the recordset
    With recAuthors
        .MoveLast   `Move to the last record using the MoveLast method
        .MoveFirst  `Move to the first record using the MoveFirst method
        While Not .BOF And Not .EOF  `Walk through each 
                                     `record until we reach the end
            `Walk through the cursor set
            `displaying each column using
            `the debug object
            Debug.Print !Author     `Use the column name
            Debug.Print !Title
            .MoveNext           `Move to the next record

        Wend  `End While Loop
    End With
End If

Exit_Sample:
`Cleanup and Exit!
`Make sure the recordset and database were created
`then close them and release the objects.
If Not (recAuthors Is Nothing) Then
    recAuthors.Close
    Set recAuthors = Nothing
End If

If Not (dbBiblio Is Nothing) Then
    dbBiblio.Close
    Set dbBiblio = Nothing
End If

Exit Sub   `Exit the routine

`Generic Error Handler
DB_Error:
    `Display the error message and then exit
    MsgBox Err.Description
    Resume Exit_Sample
End Sub

Editing a Recordset

If you have created a recordset that can be modified (which would apply to everything but a snapshot or forward-only), you can use the AddNew method of the recordset to add a new record. The AddNew method has the following syntax:

Recordset.AddNew

When you have invoked the AddNew method, you have created a new placeholder in the recordset for the new row. You then populate each column in the recordset with the data and invoke the Update method to add the record. The following code snippet adds a new record to the Authors table in the biblio.mdb database:

With recAuthor
   .AddNew          `Time to add a new record
   !Au_ID = 200     `Set the author id
   !Author = "Spenik, Mark" `The author name
   ![Year Born] = 1962     `The year born
   .Update   `Add the new record to the Authors table
End With

To modify an existing record, use the Edit method followed by the Update method, as shown in the following code snippet:

With recAuthor
   .Edit          `Modify the existing record
   !Author = "Spenik, Mark A." `Change the author name - add the middle initial
   .Update   `Update the record
End With

To delete the current record in the recordset, use the Delete method, which has the following syntax:

Recordset.Delete

TIP: For maximum performance when using DAO and updating or deleting a large number of records, wrap the update and delete statements around a transaction and use SQL statements instead of walking through the records in the recordset using the Edit, Update, and Delete methods discussed earlier. The time savings is enormous and will increase as the number of records modified increases.

QueryDefs

A DAO QueryDef object is a stored query. The query can be a select statement consisting of one or more tables, or it can be an action query that adds, updates, or deletes records from one or more tables. QueryDefs offer a performance gain because a QueryDef is a precompiled SQL statement. Precompiled means that when a QueryDef is executed, the Jet engine does not have to compile the SQL statement. QueryDefs can accept parameters, and you can create temporary QueryDef objects (which are not saved to disk ) at runtime. To create a QueryDef, you can use Microsoft Access or the Visual Basic Add-In Visual Data Manager, shown in Figure 9.6, and you can also create them using code.

FIGURE 9.6. The Visual Data Manager Query Builder dialog box.

To run an action QueryDef, use the Execute method of the QueryDef object. To create a recordset, use the OpenRecordset method of the QueryDef. The code shown in Listing 9.2 is similar to the code in Listing 9.1, except the SQL statement is first saved as a temporary QueryDef object and is then used to create a recordset. After navigating through the recordset, the SQL statement is saved as a permanent QueryDef.

Listing 9.2. An example of creating and using QueryDefs.

Private Sub Command1_Click()
`The following sample code demonstrates how to create
`and use a QueryDef. This sample is based off of the sample
`Access database that ships with Visual Basic
`Biblio.mdb.

`Declare Variables
Dim dbBiblio As Database           `DAO datbase object
Dim recAuthors As Recordset        `DAO recordset object
Dim strSQL As String
Dim qdfAuthors As QueryDef         `DAO QueryDef object

`Set up a generic error handler to trap any errors
On Error GoTo DB_Error

`Open the database
Set dbBiblio = OpenDatabase("Biblio.mdb", False)

`Set up the SQL string for the recordset
strSQL = "Select Author,Title from Authors a, [Title Author] b, Titles c " _
        & "Where a.Au_ID = b.Au_ID And b.ISBN = c.ISBN"

`Create a temporary QueryDef using the CreateQueryDef method of
`the database object. Using "" instead of a name creates a temporary
`QueryDef.
Set qdfAuthors = dbBiblio.CreateQueryDef("", strSQL)

`Open a SnapShot Recordset using the querydef object OpenRecordset method.
Set recAuthors = qdfAuthors.OpenRecordset(dbOpenSnapshot)

`Check for an empty Recordset
If Not recAuthors.EOF And Not recAuthors.BOF And recAuthors.RecordCount > 0 Then
    `The recordset is not empty
    With recAuthors
        .MoveLast   `Move to the last record
        .MoveFirst  `Move to the first record
        While Not .BOF And Not .EOF
            `Walk through the cursor set
            `displaying each column using
            `the debug object
            Debug.Print !Author     `Use the column name
            `Debug.Print !Title
            .MoveNext           `Move to the next record

        Wend  `End While Loop

    End With
End If

`Create a permanent QueryDef named qryAuthorTitle
Set qdfAuthors = dbBiblio.CreateQueryDef("qryAuthorTitle", strSQL)

Exit_Sample:
`Cleanup and Exit!
`Make sure the recordset and database were created
`then close them and release the objects.
If Not (recAuthors Is Nothing) Then
    recAuthors.Close
    Set recAuthors = Nothing
End If

If Not (dbBiblio Is Nothing) Then
    dbBiblio.Close
    Set dbBiblio = Nothing
End If

Exit Sub   `Exit the routine

`Generic Error Handler
DB_Error:
    `Display the error message and then exit
    MsgBox Err.Description
    Resume Exit_Sample
End Sub

The Data Control

One advantage of using DAO is that you can create applications using the data control in a client/server or intranet environment. The data control enables you to create applications that can add, update, delete, and search for records by setting a few properties--and with little or no code. The data control enables you to bind columns in a recordset to other Visual Basic ActiveX controls such as the textbox control or the label control. The data control can be found on the Visual Basic toolbox, and it is shown in Figure 9.7.

FIGURE 9.7. The data control on the Visual Basic toolbox.

Further easing the creation of database applications is the Visual Basic add-in, the Data Form Wizard. You can use the Data Form Wizard to create fully functional forms similar to the one shown in Figure 9.8.

FIGURE 9.8. A form generated by the Data Form Wizard.

If you have not used the data control, review the Visual Basic documentation for more information and examples.

Using Attached Tables

Earlier I stated that ODBC was thought by many to be slow. Part of the performance problems had to do with early ODBC drivers. I think another part of the story is Visual Basic 3, DAO, and the data control using remote databases (client/server). When Visual Basic 3 first shipped, developers immediately started using DAO and the data control against remote databases such as Oracle, Sybase, and Microsoft SQL Server. As the number of data controls on the form increased and resultsets were returned to the form, it quickly became apparent that performance was an issue. The problem consisted of several factors--in some cases, poor performing ODBC drivers; the overhead of the Jet engine; and, at the time, an unpublished feature of the data control using remote data sources. It turned out that when using a data control to a remote database such as SQL Server, before the SQL statement was executed, Jet queried the backend database about the columns in the table, data types, and indexes. This caused a great deal of unknown overhead! Microsoft remedied the situation by publishing and documenting the feature in Visual Basic 4.

The proper way to use the data control or DAO with remote databases is to use Access attached tables. Attached tables store locally all the information about the remote database table required by Jet. Jet no longer has to query the remote data source about the table; therefore, overall performance is increased. I recommend using RDO or ODBCDirect when accessing a remote database. However, for those of you who are still in a 16-bit environment or who don't have RDO or ODBCDirect options available to you, Listing 9.3 shows an example of attaching remote ODBC tables to a Microsoft Access database. The example first deletes any attached tables found and then attaches tables whose names are in a local Access table called AttachedTables.

Listing 9.3. An example of attached tables.

Dim dbODBC As Database        `DAO database object
Dim tdTables As TableDef      `DAO table object
Dim rsAttach As Recordset     `DAO recordset object

    `Set up a generic error handler
    On Error GoTo ODBC_Error
    `Log on to the remote database. By only specifying ODBC in the connection
    `string parameter of the OpenDatabase method, you will be prompted
     `for a valid ODBC DSN,User Name and password.
    Set dbODBC = DBEngine.Workspaces(0).OpenDatabase("", 0, False, "ODBC;")
   `Check to make sure we are connected and an object created.
   If Not (dbODBC Is Nothing) Then

        `This section of code checks attributes of the
        `tableDefs in the access database and removes
        `all the attached tables found.
        `
        For Each tdTables In dbODBC.TableDefs
            `Check for attached table
            If (tdTables.Attributes And dbAttachedODBC) <> 0 Then
                `Remove the attached table
                 dbODBC.TableDefs.Delete tdTables.Name
            End If
        Next tdTables
        `Open a recordset on a local Access database table that
        `contains the name of the remote table you wish to attach.
        `The local Access table is called AttachedTables and
        `contains a single column - TableName.
        Set rsAttach = _
 dbODBC.OpenRecordset("Select TableName from AttachedTables;", dbOpenSnapshot)
        Do Until rsAttach.EOF    ` Do until the end of the recordset is reached.
            `Attach the table - Use the remote table name as the attached
            `table name as well.
            Set tdTables = dbODBC.CreateTableDef(rsAttach(0), 0, rsAttach(0), _
                                                     dbODBC.Connect)
            tdTables.Attributes = dbAttachSavePWD
            `Save the Table object
            dbODBC.TableDefs.Append tdTables
            `Release Object
            Set tdTables = Nothing
            `Get the next record
            rsAttach.MoveNext    ` Move to next record.
        Loop

    End If
ODBC_Exit:
    If Not (rsAttach Is Nothing) Then
        `Close the attached table reference
        rsAttach.Close
        Set rsAttach = Nothing
    End If
    `Close the ODBC database
    If Not (dbODBC Is Nothing) Then
        `Close the ODBC connection
        dbODBC.Close
        Set dbODBC = Nothing
    End If
    Exit Sub

`
` Error Handler
`
ODBC_Error:
    MsgBox Err.Description
    Resume ODBC_Exit
End Sub

RDO Refresher

This section is a review of commonly used RDO objects, properties, and methods. Even if you are familiar with RDO, read on. RDO has changed quite a bit!

RDO is the thin object interface over the ODBC APIs. RDO is designed to handle remote ODBC databases, including stored procedure calls, multiple resultsets, and asynchronous operations. The RDO object model is shown in Figure 9.9.

FIGURE 9.9. The RDO object model.

The rdoEngine Object

The rdoEngine is the top object in the RDO object hierarchy, shown in Figure 9.9. An application can have only a single instance of the rdoEngine, which contains all the other RDO objects and is responsible for overall engine options. The rdoEngine is created the first time you access an RDO object such as the default login timeout property (which is the number of seconds to wait before timing out during a login request). rdoEngine's equivalent DAO object is the dbEngine. Use the rdoEngine to set options such as the default cursor type library, used by resultsets. Speaking of default cursor types, the rdoEngine property rdoDefaultCursorDriver enables you to set the cursor library used by the ODBC manager. The available options are listed in Table 9.2.

Table 9.2. The rdoDefaultCursorDriver Property Settings.

Visual Basic Constant Cursor Description
rdUseIfNeeded Default property--can be server side or client side
rdUseODBC Uses the ODBC cursor library
rdUseServer Uses server-side cursor library
RdUseClientBatch Uses RDO batch cursor library
rdUseNone Does not create a cursor set


For more detail on the various cursor options, look through the Visual Basic Help files and documentation.

If you are executing stored procedures that use multiple Select statements or are executing multiple Select statements in one resultset, you must use the ODBC cursor library. For the best performance with a read-only forward-scrolling cursor, use the option rdUseNone, which eliminates the overhead required to create and manage a cursor.


NOTE: Not all remote ODBC databases support server-side cursors. For example, Microsoft SQL Server does, but Oracle 7.3 does not. If you want to use server-side cursors, check with your specific database manufacturer or related documentation for server-side cursor support.

The rdoEngine object also contains the InfoMessage event. The InfoMessage event fires when informational messages are sent back from a remote data source.

The rdoEnvironment Object

The rdoEnvironment object can be used to control transactions across multiple connections. The rdoEnvironment object contains methods for transaction management, such as BeginTrans, CommitTrans, and RollBackTrans. The rdoEnvironment object is equivalent to the DAO Workspace object. You automatically start with rdoEnvironments(0) when the RDO engine is created.

Transaction management events such as BeginTrans, CommitTrans, and RollBackTrans are also part of the rdoEnvironment.

The rdoConnection Object

The rdoConnection object represents an open or unopened connection to a remote ODBC database. Use the rdoConnection object to log on to the ODBC database. The equivalent DAO object is the Database object.

rdoConnection objects can now be free-standing objects and include events such as QueryComplete, Connect, and Disconnect, to name just a few. Let's look at a few code examples using the rdoConnection object. The following code snippet connects to SQL Server using the OpenConnection method of the rdEnvironment object:

Dim envSQLServer As rdoEnvironment
Dim cnnDBA As rdoConnection
Dim strConn As String
    strConn = "UID=sa;Database=pubs;DSN=local SQL Server;"
    Set En = rdoEnvironments(0)
    `               syntax for OpenConnection is
    `                   Data Source Name, ODBC prompt,
    `                   readonly, connection string, options
    Set cnnDBA = En.OpenConnection("", rdDriverPrompt, False, strSQL)

With Visual Basic 5, you can create a standalone rdoConnection. When you use a standalone connection object, the object does not automatically appear in the rdoCollections object, but it must be added. The following code snippet shows an example using a standalone rdoConnection object to make a connection to a remote ODBC database:

`Set some properties of the connection object
    `and then use the EstablishConnection method
    `to connect to the remote database.
    With cnnDBA
        .LoginTimeout = 10 `Set to 10 seconds
        .Connect = "UID=sa;DATABASE=pubs;" & _
                    "DSN=local SQL Server;"
        .EstablishConnection (rdDriverNoPrompt)
    End With
    `Add the connection to the collection
    rdoEnvironments(0).rdoConnections.Add cnnDBA

Other commonly used methods of the rdoConnection object are the OpenResultset method to create a resultset and the Execute method to execute a SQL statement or stored procedure that does not return any rows.

The resultset Object

The resultset object consists of the returned rows of a query or executed stored procedure. The DAO equivalent object is the recordset object; however, the resultset object supports many powerful features not found in a DAO recordset, such as the capability to support multiple result sets or asynchronous processing capabilities. A resultset object can be created using the OpenResultset method of the rdoConnection object and the rdoQuery object (discussed later). The syntax to create a resultset using the rdoConnection object is as follows:

Set rs =rdoConnection.OpenResultset (SQL_Name [,
                 Cursortype [, LockFlag, [ResultOptions ]]])

In this syntax, SQL_Name is the query string or RDO object name used to create the recordset. ResultOptions enables you to set options such as asynchronous queries. The LockFlag enables programmers to specify various locking. The CursorType option enables you to pick the type of cursor you want for your resultset. Let's take a closer look at the possible values for the CursorType option. rdOpenForwardOnly rdOpenForwardOnly allows only forward scrolling and does not create a cursor keyset. Only a single row is retrieved at a time. You can add, delete, or update records using the forward-only resultset.


NOTE: This is a quick reminder that in DAO, you can't make modifications to a forward-only recordset. Also, in DAO, the records being returned in the query are referred to as a recordset. Records (rows) being returned in RDO are referred to as a resultset.

rdOpenKeyset rdOpenKeyset is equivalent to the DAO dynaset. A keyset of all the records that make up the cursor is generated on the client or the server. The cursor generated is updatable so that you can add, delete, or modify records. On some ODBC sources, records added or changed through the resultset's AddNew and Edit methods will be reflected in the resultset. On other ODBC sources, records added using the AddNew and Edit methods will not appear as a member of the resultset until the resultset is refreshed. rdOpenDynamic rdOpenDynamic creates a cursor that is truly dynamic. The dynamic resultset contains an accurate resultset of all the current records, including those being added, updated, or deleted. The drawback to a dynamic resultset is that it has the highest cursor management overhead of all the cursor types (which equates to less than optimal performance). rdOpenStatic A static cursor is similar to the DAO snapshot, except that you can edit records in the resultset. A static cursor is a copy (picture) of the query data at the time the cursor was created. The cursor can reside on the client or the server. Changes made through the AddNew, Update, and Delete methods of the resultset object will appear as part of the resultset. Changes made using the execute method or made by other users will not appear until the resultset is refreshed.

Resultset Navigation

The following methods can be used to move through the resultset:

You can also use the AbsolutePosition property to move to an absolute row position in the cursor.

To help determine whether you are at the end or start of the resultset, use the BOF and EOF properties of the resultset object. The BOF and EOF properties return Boolean values (TRUE/FALSE), based on the current position of the resultset. If BOF or EOF is TRUE, there is no current row, and any attempt to Move through the resultset will result in an error. When a resultset is open containing one or more rows, both BOF and EOF are FALSE. If both BOF and EOF are set to TRUE and the recordset RowCount property is zero, no records are contained in the resultset. The code sample in Listing 9.4 shows an example of opening and navigating through a resultset.

Listing 9.4. An example of navigating through an RDO resultset.

Private Sub Command1_Click()

`The following sample code demonstrates how to open and navigate through
`an RDO resultset. This sample is based off of the sample
`database that ships with Microsoft SQL Server pubs.


`Declare Variables
Dim envApp As rdoEnvironment
Dim conPubs As rdoConnection
Dim recAuthors As rdoResultset
Dim strSQL As String
Dim strMsg As String
Dim strConn As String
Dim errReport As rdoError


`Set up generic error handler to trap any errors
On Error GoTo DB_Error

`Set up the connection string. This string will vary from
`SQL Server to SQL Server.
`
strConn = "UID=sa;PWD=;Database=pubs;DSN=local SQL Server;"

`Obtain a reference to the default RDO Environment
Set envApp = rdoEnvironments(0)

`Set default cursor type to use server side cursors
rdoEngine.rdoDefaultCursorDriver = rdUseServer

`Open a connection to SQL Server
Set conPubs = envApp.OpenConnection("", rdDriverNoPrompt, False, strConn)

`Set up the SQL string for the resultset
strSQL = "Select au_id, au_lname, state from authors"


`Open a static cursor
Set recAuthors = conPubs.OpenResultset(strSQL, rdOpenKeyset)

`Check for an empty Resultset
If Not recAuthors.EOF And Not recAuthors.BOF And recAuthors.RowCount <> 0 Then
    `The recordset is not empty
    With recAuthors
        .MoveLast   `Move to the last record
        .MoveFirst  `Move to the first record
        While Not .BOF And Not .EOF
            `Walk through the cursor set
            `displaying each column using
            `the debug object
            Debug.Print !au_id & " " & !au_lname & " " & !state  `Use the 
                                                            `column name
            .MoveNext           `Move to the next row
        Wend  `End While Loop
        `Use the Absolute property to change the row position
        .AbsolutePosition = .RowCount - 3
        Debug.Print !state  `Display the value of the State column
    End With
End If
Exit_Sample:
`Cleanup and Exit!
`Make sure the resultset and connection were created
`then close them and release the objects.
If Not (recAuthors Is Nothing) Then
    recAuthors.Close
    Set recAuthors = Nothing
End If

If Not (conPubs Is Nothing) Then
    conPubs.Close
    Set conPubs = Nothing
End If
Exit Sub   `Exit the routine

`Generic Error Handler
DB_Error:
    `Walk through the rdoErrors collection to
    `construct the error message string to display
    For Each errReport In rdoErrors
        strMsg = strMsg & errReport.Description & vbCrLf
    Next errReport
    `Display the error message and then exit
    MsgBox strMsg
    Resume Exit_Sample
End Sub

In the code sample shown in Listing 9.4, the generic error routine uses the RDO collections object rdoErrors to display all the error messages returned from the ODBC data source.

Editing a Resultset

If you have created a resultset that can be modified, you can use the AddNew method of the resultset, which has the following syntax:

Resultset.AddNew

When you have invoked the AddNew method, you have created a new placeholder in the resultset for the new row. You then populate each column in the resultset with the data and invoke the Update method to add the record. To modify an existing record, use the Edit method followed by the Update method. To delete the current record in the resultset, use the Delete method, which has the following syntax:

Resultset.Delete

When dealing with remote ODBC data sources, you can also make changes to the database using the Execute method with SQL statements or stored procedures.

Query Object

In RDO version 2.0, the rdoPreparedStatement objects are gone. (They are still supported for backward-compatibility, but they should no longer be used.) In their place is the new rdoQuery object. The rdoQuery object is similar to the DAO QueryDef object in that it functions as a set of compiled SQL statements. The rdoQuery object is designed to handle stored procedures with multiple input and output parameters, multiple resultsets, and SQL statements that use parameters. Parameters are managed via an rdoParameter collection associated with the rdoQuery object. An rdoQuery object can be created as a standalone object (like the rdoConnection object), or it can be created using the CreateQuery method of the rdoConnection object.

Let's look at an example that uses a rdoQuery object and the rdoConnection object QueryComplete event, which is fired when an asynchronous query completes. You might remember that in RDO version 1.0, your program had to poll to determine when an asynchronous query had completed. Now, with RDO 2.0, the asynchronous query process is event driven. Listing 9.5 shows an example of creating an rdoQuery object, executing an asynchronous query, and using the QueryComplete event to notify the user. The code to handle the asynchronous query is located in the procedure conPubs_QueryComplete in Listing 9.5. The example calls a SQL Server stored procedure that delays for 10 seconds. The stored procedure code is as follows:

create procedure usp_delay
as
/*
** Delay for 10 seconds and then return 0
*/
Waitfor Delay '00:00:10'
return 0

Listing 9.5. Executing an asynchronous query.

`Declare a standalone connection object.
`WithEvents allows us to code the query complete event for the
`object.
`
Private WithEvents conPubs As rdoConnection
Dim recAsync As rdoResultset
Dim qryStoredProc As rdoQuery

Private Sub Command1_Click()

`The following sample code demonstrates how to create
`an rdoQuery object and use asynchronous query events.
`
`Declare Variables
Dim strSQL As String
Dim strMsg As String
Dim strConn As String
Dim errReport As rdoError


`Set up generic error handler to trap any errors
On Error GoTo DB_Error

`Set up the connection string. This string will vary from
`SQL Server to SQL Server.
`
strConn = "UID=sa;PWD=;Database=pubs;DSN=local SQL Server;"

`Set up the query string
strSQL = "exec usp_delay"

rdoEngine.rdoDefaultCursorDriver = rdUseOdbc

Set conPubs = New rdoConnection
`Open a connection to SQL Server
With conPubs
    .QueryTimeout = 20  `Set to 20 seconds
    .Connect = strConn
    .EstablishConnection
    `Create a query object
    Set qryStoredProc = .CreateQuery("Delayed Query", strSQL)
End With
`Open the recordset asynchronously
Set recAsync = qryStoredProc.OpenResultset(rdOpenKeyset, _
                               rdConcurReadOnly, rdAsyncEnable)
Debug.Print "Waiting on query! But I'm not blocked!"
Exit_Sample:
Exit Sub   `Exit the routine

`Generic Error Handler
DB_Error:
    `Walk through the rdoErrors collection to
    `construct the error message string to display
    For Each errReport In rdoErrors
        strMsg = strMsg & errReport.Description & vbCrLf
    Next errReport
    `Display the error message and then exit
    MsgBox strMsg
    Resume Exit_Sample

End Sub


Private Sub conPubs_QueryComplete(ByVal Query As RDO.rdoQuery, _
                                    ByVal ErrorOccurred As Boolean)
Dim strMsg As String

    strMsg = "Query: " & Query.Name & " completed."
    MsgBox strMsg

End Sub

RDO Data Control and the UserConnection ActiveX Designer

Like DAO, RDO has a data control that enables you to create applications that can add, update, delete, and search for records by setting a few properties and using little or no code. One new and exciting tool added in Visual Basic 5 is the ActiveX designer UserConnection. You can use the UserConnection designer to create rdoConnections and rdoQuery objects at design time. The designer provides an easy property page interface to set connection properties. You can also use the designer to create Query objects based on stored procedures or SQL statements. Microsoft Query integrates into the UserConnection designer so that you can create queries using drag and drop. An example of a UserConnection designer with several Query objects is shown in Figure 9.10.

FIGURE 9.10. A UserConnection ActiveX designer.

After you have used the designer to create a connection and associated queries, you can easily connect to the remote database and execute the queries as if they were methods of the Connection object. The following code snippet shows an example using a UserConnection designer called UserConnection1 to connect to a remote database, execute a stored procedure, and open a resultset:

Dim tstOne As New UserConnection1 `UserConnection Active X Designer
Dim myQuery As rdoQuery     `Rdo Query Object
Dim rsTest As rdoResultset
Dim myRet As String

`Establish the connection to the remote data source
tstOne.EstablishConnection
`Execute a stored procedure
myRet = tstOne.qryRollFinance
`Open a resultset
Set rsTest = tstOne.OpenResultset("Query4")

The UserConnection ActiveX designer cuts down on the lines of code you are required to write and makes remote database development a graphical experience.

ODBCDirect

As mentioned earlier, ODBCDirect allows you to continue to use the familiar DAO interface without using the Jet engine. Jet is replaced by RDO. The DAO objects are mapped to their equivalent RDO counterparts. Although not all RDO functionality is available, important features such as asynchronous queries and multiple resultsets are supported. ODBCDirect also supports the RDO dynamic resultset using the dbOpenDynamic option. Use ODBCDirect when you have an existing application using an Access database, and you want to move the application to a remote ODBC database such as Microsoft SQL Server or Oracle. If you are an experienced DAO programmer, you will like the performance benefit provided by ODBCDirect when using remote ODBC databases. However, with the changes to RDO and the addition of the UserConnection ActiveX designer, it's time to start learning RDO.

Using ODBCDirect requires creating an ODBCDirect workspace. This is accomplished using the CreateWorkspace method of the DAO dbEngine using the constant dbUseODBC, as shown in the following code snippet:

Dim wksODBC as Workspace
set wksODB = DBEngine.CreateWorkspace("ODBCDirectWKS","sa","book",dbUseODBC)

After you create an ODBCDirect workspace, the next step is to connect to the ODBC database. You can use the OpenDatabase method or the Connection object. The Connection object provides RDO features such as asynchronous connections and queries and the capability to create QueryDef objects on the remote database. You lose the additional RDO functionality when using the OpenDatabase method to connect to a remote ODBC database. When you have connected to the remote database, everything else is DAO!

To give you an example of porting existing DAO code to ODBCDirect, I have taken the DAO code used in Listing 9.1 and converted it to ODBCDirect. Instead of using the authors table from the biblio.mdb database, the following example uses the authors table found in the SQL Server database pubs. The ODBCDirect example is shown in Listing 9.6. Notice that the only major difference between Listing 9.1 and Listing 9.6 is the creation of the ODBC workspace (which is accomplished in the example by setting a DbEngine option instead of using the constant dbUseODBC in the CreateWorkspace method shown earlier) and the connection string option used in the OpenDatabase method.

Listing 9.6. Navigating through a recordset using ODBC Direct.

Private Sub Command1_Click()
`The following sample code demonstrates how to navigate through
`a recordset using ODBC Direct. This sample is based off of the sample
`database, Pubs that ships with Microsoft SQL Server.

`Declare Variables
Dim dbBiblio As Database
Dim wksODBC As Workspace
Dim recAuthors As Recordset
Dim strSQL As String
Dim strConn As String

`Set up generic error handler to trap any errors
On Error GoTo DB_Error

`Set the DAO engine default parameters
`to ODBC direct so that when a workspace is created
`it will use ODBC Direct
With DBEngine
    .DefaultType = dbUseODBC
    .DefaultUser = "sa"
    .DefaultPassword = ""
End With

`Create the ODBC Direct WorkSpace
Set wksODBC = DBEngine.Workspaces(0)

`The next step is to connect to the remote database.
`You can use the connection object to get RDO capabilities
`or for no code changes the database object. This sample uses the database
`object.
strConn = "ODBC;UID=sa;PWD=;Database=pubs;DSN=local SQL Server;"

`Open the database - which connects us to SQL Server
`
Set dbBiblio = OpenDatabase("Pubs", dbDriverNoPrompt, False, strConn)

`Set up the SQL string for the recordset
strSQL = "select * from authors"

`Open a SnapShot Recordset
Set recAuthors = dbBiblio.OpenRecordset(strSQL, dbOpenSnapshot)

`Check for an empty Recordset
If Not recAuthors.EOF And Not recAuthors.BOF And recAuthors.RecordCount > 0 Then
    `The recordset is not empty
    With recAuthors
        .MoveLast   `Move to the last record
        .MoveFirst  `Move to the first record
        While Not .BOF And Not .EOF
            `Walk through the cursor set
            `displaying each column using
            `the debug object
            Debug.Print !au_lname & " " & !state
            .MoveNext           `Move to the next record
        Wend  `End While Loop
    End With
End If
Exit_Sample:
`Cleanup and Exit!
`Make sure the recordset and database were created
`then close them and release the objects.
If Not (recAuthors Is Nothing) Then
    recAuthors.Close
    Set recAuthors = Nothing
End If

If Not (dbBiblio Is Nothing) Then
    dbBiblio.Close
    Set dbBiblio = Nothing
End If
Exit Sub   `Exit the routine

`Generic Error Handler
DB_Error:
    `Display the error message and then exit
    MsgBox Err.Description
    Resume Exit_Sample

End Sub

Summary

This chapter provided an overview of the many different methods and object models available to a Visual Basic Internet/intranet database developer. A quick overview and review of the important database objects and terms was provided, as well as sample code. The remaining chapters in this book use the various data access objects described in this chapter to build Internet/intranet applications and ActiveX components.

This chapter also provided some insight and suggestions on when to use each database object. Calling the ODBC API directly or using the native database drivers was ruled out because there are more flexible object models available that are easier to use and provide equivalent performance. Use DAO when dealing with an Access database. When working with remote ODBC databases, use RDO except in cases when you are porting existing DAO code to a remote ODBC database; in that case, use ODBCDirect.

This chapter also provided a very basic overview of the future of Microsoft data access technologies, OLE DB, and the Active Data Objects (ADO). You learn about these topics in great detail in Chapter 21. ADO is the data-access object of choice when writing server-side Visual Basic scripts (Active Server Pages). At the time this book was being written, Microsoft had begun to talk about eventually merging the DAO and RDO interface into a single data-access object interface. Microsoft has not committed to whether this single interface is ADO. So keep watching the Microsoft Website for the latest data-access object model to emerge; and in the meantime, continue to learn and use the data-access object models that are being shipped today with Visual Basic 5 and the Internet Information Server--DAO, RDO, ODBC Direct, and ADO.