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