William R. Vaughn, Microsoft Visual Studio, Enterprise Product Manager
October 1997
No consultant worth his or her salt will tell you to fix something that isn't broken. This article is not about "fixing" existing Remote Data Objects (RDO) applications that are doing the job they were designed to do. It is about knowing what benefits can be achieved by using ActiveX™ Data Objects (ADO) when it makes sense. ADO adds a number of new choices when you need to access data. Specifically, ADO is designed to better address the special needs of Internet developers who need to access special types of data—in addition to traditional relational sources—and in the process, permit creation of faster, smaller, and more efficient applications. With the release of version 1.5, ADO is well on its way to becoming the de facto standard for data access with the Visual Basic® development system and the rest of the Visual Studio™ development system languages, including Visual C++®, Visual J++™, Visual Basic Scripting Edition, Visual InterDev™, and Microsoft Office applications.
Question: Are developers going have to re-write their RDO code now that ADO is here? The answer to this questions is no—not unless the ADO's expanded features offer real solutions to problems that RDO can't solve.
This article discusses why Microsoft® has chosen to create a new all-encompassing data access interface and how your applications can take advantage of its new features. Does ADO replace RDO? No, ADO is implemented alongside of RDO and DAO as both of these established programming interfaces are still supported. Does ADO 1.5 functionality replace RDO 2.0? No, today ADO does not implement everything that RDO supports. However, ADO 2.0 will support a superset of RDO's functionality.
This information is presented from the point of view of an RDO developer. After a brief outline of how we got to this point, we walk through a number of typical scenarios in RDO and show how to implement the same functionality in ADO. This article by no means covers all of ADO's functionality. For example, ADO implements a number of new features that make it far better suited for code run remotely on an IIS server, thus making development of Microsoft Transaction Server or Visual Basic Scripting Edition components easier. However, that's the meat of another article.
Over the last six years or so, Microsoft has been driven by customer demand to release an ever-expanding series of data access interfaces, beginning with the DB-Library interface for Visual Basic ("VBSQL") and ending with the most recent interface to OLE DB—ADO. Each of these interfaces addressed broadening customer requirements. As Visual Basic applications grew more sophisticated and data-centric, so did the data access interfaces provided:
Microsoft ActiveX Data Objects (ADO) enable your client applications to access and manipulate data in a database server and other data stores through any OLE DB provider. The primary benefits of ADO are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and web-based applications, including the following:
Note: While ADO supports these features, all of the underlying providers and drivers called by ADO may not. Check the topic Using OLE DB Providers with ADO as well as the documentation for the underlying providers and drivers to determine what functionality they support. Unless otherwise noted in the language reference topics, all of the ADO 1.5 Objects, Methods, and Properties described in this document are available when used with the Microsoft OLE DB Provider for ODBC and Microsoft SQL Server version 6.5.
ADO's Remote Data Service (RDS) allows data remoting. This means you can move data from a server to a client application or web page, manipulate the data on the client, and return updates to the server in a single round trip. Previously released as Advanced Data Connector 1.0, RDS has been combined with the ADO programming model to simplify client-side data remoting. For more information, see the Remote Data Service documentation.
As part of the Microsoft Data Access Components (MDAC), ADO and RDS are automatically installed and registered by a host product, such as Microsoft Internet Information Server. The host product setup program may require that you specifically request the Microsoft Data Access Components in order to have ADO and RDS installed. In any case, you can find ADO and MDAC on the web at http://www.microsoft.com/data.
ADO is a dual-interface COM type library. The filename is msado15.dll. The program ID (ProgID) is "ADODB." In two- and three-tier database applications, ADO code that is to execute on the client uses the ProgID "ADOR" instead.
To use ADO with Microsoft Visual Basic or Microsoft Office, you also need to establish a reference to the ADO type library. Select References from the Project menu, check the box for "Microsoft ActiveX Data Objects 1.5 Library," and then click "OK." ADO objects, methods, and properties will then be accessible through the Visual Basic for Applications (VBA) Object Browser and the IDE Editor.
To use ADO with Microsoft Visual J++, you also need to create a set of class files using the "Java Type Library Wizard". Select the Java Type Library Wizard from the Tools menu, check the box for "Microsoft ActiveX Data Objects 1.5 Library," and then click "OK." The wizard will then create the necessary Java class files for ADO.
ADO "flattens" the rather complex RDO (or DAO) object model to facilitate understanding and use of the object model. Shown below is a diagram showing the ADO interfaces and how they relate to one another:
Although ADO objects are creatable outside the scope of a hierarchy, the objects exist within hierarchical relationships, as shown in the ADO Object Model. Notice that this model seems considerably simpler than RDO's, so don't expect a 1:1 correspondence with ADO objects, properties, and methods. There will be lots of documentation and helpful hints on how to map the DAO and RDO model to ADO.
There are seven objects in the ADO object model. In some ways these objects are similar to RDO objects but often combine functionality into a flatter model.
These additional properties appear as Property objects in that Recordset object's Properties collection. Dynamic properties can be referenced only through the collection, using the MyObject.Properties(0) or MyObject.Properties("Name") syntax. You will find that each data provider might implement one or more special properties to deal with special provider-specific operations.
Generally, the ADO object model is flatter (has fewer objects) but more properties, methods, and method arguments. For example, there are no equivalents to the rdoEngine or rdoEnvironment objects—these expose the ODBC driver manager and hEnv interfaces. The InfoMessage event supplied by the rdoEngine object is not supported in ADO at this time (nor are any of the events). You also cannot create ODBC data sources from ADO—despite the fact that your interface might be through the ODBC OLE DB service provider—but this is coming in a later version. As we work through the rest of the ADO interface, we will find other dissimilarities. Some of these differences are covered by new approaches to the problem (and are addressed), some were left behind for one good reason or another, and some of which are yet to be implemented.
With the introduction of ADO, developers can create applications that perform all of the fundamental data-access operations that all client/server and multi-tier applications require. ADO 2.0 promises to implement a superset of RDO's and DAO's functionality. No, that does not mean that the code you have been writing in RDO and DAO will automatically morph over to equivalent ADO code when the time comes to convert. However, it does mean the problems you have solved with these older object models in the past will be solvable with ADO—and more. ADO implements a number of new data access strategies never before available to address new problems.
When deciding to migrate to ADO, you have to decide if ADO's additional capabilities are enough to justify converting existing software. Another consideration is how your existing developers can leverage their existing RDO development skills. Although RDO will be available indefinitely, it is always a good idea to consider ADO for applications currently under design.
Because ADO 2.x is expected to exceed RDO's functionality, ADO work done now can be leveraged even more easily when ADO 2.0 is released. However, if you must use the features available in RDO 2.0 today, be sure to study this article for code that might not be easy to convert or for programming paradigms that are most like ADO's. This will make the eventual transition easier.
To get an in-depth understanding of the issues, see "Choosing the Right VB5 Data Access Interface" in the Experienced User area of http://www.microsoft.com/vbasic/. This paper concludes that if ADO will address your current needs, use it. Otherwise, for new designs, use RDO for client/server systems and DAO for ISAM or small departmental systems. There are other factors to consider when choosing a data access interface, so this simplistic statement should not be taken without considering the other factors discussed in the aforementioned paper.
The following sections discuss how to solve some basic data access problems using RDO. This section is followed by a parallel discussion on how to perform the same operations with ADO. Each of these "problems" addresses a SQL Server database—a variation of the Biblio database installed with Visual Basic 5.0.
Getting a connection open requires a connect string and a decision or two about how ODBC is to create the connection. Note that a connection is not required by RDO to create an rdoQuery object, but is required to initially create an rdoResultset object.
Dim cn As New rdoConnection
Dim cnB As New rdoConnectionConst ConnectString = "uid=billva;pwd=bv;" _
driver={SQL Server};server=sequel2;" _
database=biblio;dsn=''"
This connect string accesses a specific SQL server and permits ODBC to open a "DSN-less" connection. Note that this is a typical ODBC connect string with all of the standard arguments.
Private Sub Form_Load()
With cn
cn.Connect = ConnectString
cn.LoginTimeout = 10
cn.CursorDriver = rdUseNone
cn.EstablishConnection rdDriverNoPrompt
End With
Establish a connection specifying the type of cursor driver and login timeout. By default, RDO uses rdUseIfNeeded, which invokes server-side cursors on SQL Server—we override this in our example. We also choose to generate an error if the user-id and password do not match. The second connection performs the client-batch updates. Notice that the connect string is used in the first operation.
With cnB
cnB.Connect = ConnectString
cnB.CursorDriver = rdUseClientBatch
cnB.EstablishConnection
End With
End Sub
Handle any errors that occur when the connection is opened. This event fires when the connection operation is completed so we can test to see if it worked and enable any buttons that rely on an open connection.
Private Sub cn_Connect(ByVal ErrorOccurred As Boolean)
If ErrorOccurred Then
MsgBox "Could not open connection", vbCritical
Else
RunOKFrame.Enabled = True
End If
End Sub
To return a basic result set based on an SQL statement, in this case we execute a restricted query and pass the result set to a control that simply dumps the result to the MSFlexGrid. Building a result set requires use of an open connection.
Private Sub RunButton_Click()
Dim rs As rdoResultset
Set rs = cn.OpenResultset("select * from titles where title like '%h'")
rdoGrid1.ShowData rs
rs.Close
End SubThis routine is the ShowData method of a custom ActiveX control used to display data from a result set in an MSFlexGrid. The code sets the grid up based on the rdoColumns property titles and initializes the grid making it ready for the data. Note the use of the OrdinalPosition property to index the result set rdoColumns property. There are two sets of code to extract data from the rdoResultset—one that uses GetClipString, and another that uses the GetRows method to contrast typical approaches to this problem. Note that ADO 1.5 does not initially support the GetClipString method—its support in ADO 2.0 is under consideration.
Public Function ShowData(Resultset As rdoResultset) As Variant
Dim cl As rdoColumn
Static GridSetup As Boolean
Dim MaxL As Integer
Dim rsl As rdoResultset
Dim Rows As Variant
On Error GoTo ShowDataEH
Set rsl = Resultset
If GridSetup Then
Else
FGrid1.Rows = 51
FGrid1.Cols = rsl.rdoColumns.Count
FGrid1.Row = 0
For Each cl In rsl.rdoColumns
FGrid1.Col = cl.OrdinalPosition - 1
FGrid1 = cl.Name
If rsl.rdoColumns(cl.OrdinalPosition - 1).ChunkRequired Then
MaxL = 1
Else
MaxL = rsl.rdoColumns(cl.OrdinalPosition - 1).Size + 4
End If
If MaxL > 20 Then MaxL = 20
FGrid1.ColWidth(FGrid1.Col) = TextWidth(String(MaxL, "n"))
Next cl
GridSetup = True
End If
FGrid1.Rows = 1 'Clear Grid of data (except titles)
FGrid1.Rows = 51
FGrid1.Row = 1
FGrid1.Col = 0
FGrid1.RowSel = FGrid1.Rows - 1
FGrid1.ColSel = FGrid1.Cols - 1
FGrid1.Clip = rsl.GetClipString(50, , , "-")
' With FGrid1
' Rows = rsl.GetRows(FGrid1.Rows)
' For i = 1 To UBound(Rows, 2) ' Loop through resultset returned
' .Row = i
' For j = 0 To UBound(Rows, 1) - 1
' .Col = j
' .Text = "" & Rows(j, i)
' Next j
' Next i
' .Row = 1
' End With
ExitShowData:
FGrid1.RowSel = 1
FGrid1.ColSel = 0
Exit Function
ShowDataEH:
Select Case Err
Case 40022:
FGrid1.Clear
Resume ExitShowData
Case 13
FGrid1.Text = "< >"
Resume Next
Case Else
MsgBox "Could not display data: " & Err & vbCrLf & Error$
Resume ' ExitShowData
End Select
End Function
The following discussion walks through code used to perform the same basic operations using ADO. Initially we create a set of stand-alone ADO objects referenced off of the ADODB object. These objects are fleshed out later in the code when we set specific properties to open connections and execute result sets.
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cnB As New ADODB.Connection
Dim Qy As New ADODB.Command
The next line of code creates a connect string—just like RDO's. In both cases we are using ODBC's "DSN-less" connection strategy to save time and increase performance. It seems the documentation calls for a somewhat different Connect string that references an ADO "Provider" instead of a "Driver" and does not call for the "dsn=" syntax, but the ODBC connect string used in RDO seems to work fine.
Const ConnectString = "uid=billva;pwd=bv;driver={SQLServer}; _ server=sequel2;database=biblio;dsn=''"
These next declarations set up the variables used throughout the example. Note creation of a variant array to hold the result set.
Dim sql As String
Dim rc As Integer
Dim i As Integer
Dim Changes As Integer
Dim bms() As Variant
Next, we open the ADO connection to our database in the Form_Load event. Note that this code is very similar to RDO except that the constants are prefixed with "ad" instead of "rd". While this is the case in this example, many ADO constants are not the same as their RDO equivalents so you cannot just change the first two letters and expect them to work.
Note that we have to use the ADO Properties collection to deal with the desired prompt behavior—but ADO defaults to "no prompt" which makes more sense anyway, so you should not have to specify the prompting behavior. In RDO, we could simply set the behavior using the OpenConnection argument. In ADO we have to set the Properties ("Prompt") property—an example of this is shown in the code.
Keep in mind that the code in your RDO Connect event is handled differently—probably in an in-line ADO error handler, as ADO does not (yet) support events. This means that you will have to work with situations where the "informational" messages sent back by SQL Server will be discarded and that asynchronous open connection routines will have to be coded using synchronous techniques.
Private Sub Form_Load()
With cn
' Establish DSN-less connection
.ConnectionString = ConnectString
.ConnectionTimeout = 10
.CursorLocation = adUseNone
' .Properties("Prompt") = adPromptNever ' This is not required in ADO
.Open
End With
With cnB
.ConnectionString = ConnectString
.CursorLocation = adUseClientBatch
.Open
End With
End Sub
Once the connection is open, we can proceed to running a query. This code is very similar to the code we just executed with RDO. However, in this case we use the new ADO Open method that takes the SQL query and the ADO Connection object as arguments—instead of using the rdoConnection object's .OpenResultset object. Yes, you can also use the ADO Connection object's Execute method—just as you could in RDO as long as it didn't return a rowset.
No you won't be able to run this query asynchronously in ADO—not until version 2.0. Yes, you can choose to process the query's resultset asynchronously. That is, ADO will tell the cursor driver to complete population of the result set in the background by setting the "IDBAsynchStatus" property to True. No, ADO will not return control any sooner when this option is set as with RDO asynchronous operations, but the cursor will be populated (in time) without your code having to move to the end of the result set.
Private Sub RunButton_Click()
Dim rs As New ADODB.Recordset
rs.Open "select * from titles where title like '%h'", cn
ADOGrid1.ShowData rs
rs.Close
End Sub
The following code implements the same ShowData method of a custom ActiveX control adapted from the RDO control. In this case, since we can't use GetClipString, we use GetRows instead. Since we then have to parse the resulting Variant array ourselves, the routine is noticeably slower. If you have problems getting the GetRows function to work, you will have to convert to a more conservative (read slower) approach that loops through the rows. However, this technique should be avoided as it is far less efficient than either using GetRows or binding directly to a control.
Note how the OrdinalPosition property can no longer be used as an index on the Fields collection to pull out the column titles (as it could in RDO). To deal with this, substitute a new integer counter to address the column being worked on. We also had to use the DefinedSize and ActualSize properties to find the TEXT and IMAGE datatype fields that would not fit in a column. These new properties make it easier to determine the details of specific field values. I also added additional code to deal with BLOB types when we worked through the data columns.
Public Function ShowData(Resultset As Recordset) As Variant
Dim cl As Field
Static GridSetup As Boolean
Dim MaxL As Integer
Dim Op As Integer
Dim rsl As Recordset
Dim rows As Variant
On Error GoTo ShowDataEH
Set rsl = Resultset
If GridSetup Then
Else
FGrid1.rows = 51
FGrid1.Cols = rsl.Fields.Count
FGrid1.Row = 0
Op = 0
For Each cl In rsl.Fields
FGrid1.Col = Op
FGrid1 = cl.Name
If rsl.Fields(Op).DefinedSize > 255 Then
MaxL = 1
Else
MaxL = rsl.Fields(Op).ActualSize + 4
End If
If MaxL > 20 Then MaxL = 20
FGrid1.ColWidth(FGrid1.Col) = TextWidth(String(MaxL, "n"))
Op = Op + 1
Next cl
GridSetup = True
End If
FGrid1.rows = 1
FGrid1.rows = 51
ReDim rows(FGrid1.Cols, FGrid1.rows)
With FGrid1
rows = rsl.GetRows(adGetRowsRest)
''
'' The following code is used instead of GetRows which did not work
''
''
' i = 1
' Do Until rsl.EOF Or i >= FGrid1.rows
' For j = 0 To rsl.Fields.Count - 1
' If rsl(j).Type = adLongVarBinary _
' Or rsl(j).Type = adLongVarChar Then
' rows(j, i) = "<binary>"
' Else
' rows(j, i) = rsl(j)
' End If
' Next j
' i = i + 1
' rsl.MoveNext
' Loop
'
' Take the array filled above and pass it to the grid
'
For i = 1 To UBound(rows, 2) ' Loop through resultset returned
.Row = i
For j = 0 To UBound(rows, 1)
.Col = j
.Text = "" & rows(j, i)
Next j
Next i
.Row = 1
End With
ExitShowData:
FGrid1.RowSel = 1
FGrid1.ColSel = 0
Exit Function
ShowDataEH:
Select Case Err
Case 40022, 3021:
FGrid1.Clear
Resume ExitShowData
Case 13, Is < 0
rows(j, i) = "< >"
Resume 'Next
Case Else
MsgBox "Could not display data: " & Err & vbCrLf & Error$
Resume ' ExitShowData
End Select
End Function
The next challenge is to access data sources that are more complex than simple select queries. In this next series of exercises, we will execute a parameter-driven stored procedure (without the benefit of the UserConnection designer) and execute an optimistic batch update operation.
One of the first of our more challenging tasks is to execute a fairly ordinary SELECT statement that requires parameters. This is done in two phases:
The code shown below implements this technique. The first time this subroutine is called, RDO is asked to create a new RDO query object. Since the object is appended to the rdoConnection object's rdoQueries collection, we can re-reference it each time the subroutine is called. Each subsequent time the subroutine is called, the Refresh method is used to re-execute the query. This technique builds a temporary stored procedure (under the covers) that is referenced by the Requery method. This temporary SP is dropped when the connection is closed.
Private Sub ParmQueryButton_Click()
Dim Qy As New rdoQuery
Dim rs As rdoResultset
Static FirstTime As Boolean
If cn.rdoQueries.Count = 0 Then
FirstTime = True
sql = "select * from authors where year_born = ?"
Set Qy = cn.CreateQuery("Pq", sql)
End If
Qy(0) = QueryParam.Text
If FirstTime Then
Set rs = Qy.OpenResultset()
FirstTime = False
Else
rs.Requery
End If
rdoGrid1.ShowData rs
rs.Close
End Sub
Since many, many client/server applications depend heavily on stored procedures (SP), any data access interface worth its salt must be able to execute them quickly and efficiently. Stored procedures pose a number of complex issues to the developer. In some cases, stored procedures require management of OUTPUT and return status values and other, more conventional arguments. In addition, an SP can return several complex result sets including PRINT or RAISERROR statement return values. Question: does it make sense to make your SPs this complex? Well, sometimes, but not always. It might be easier to keep SPs simpler and more modular and use Visual Basic application code to tie them together.
The code samples below show how RDO approaches these problems. To start with we execute a simple parameter-based SP and show the results in our grid. We do make an accommodation or two to the subsequent ADO design, and those are noted. We will be using the same connections established in the earlier examples. We will not be comparing use of the UserConnection designer with ADO, as it is simply not supported at this time. Ideally, this is the most efficient way to execute a parameter-driven SP.
Note that our code requires us to include a correct ODBC "Call" statement. Again, this is not necessary in the UserConnection designer, but is essential here in the RDO code-based approach. We do use the stand-alone rdoQuery object and assign the already open Connection to it. This object can then be used in subsequent calls to handle a parameter query—that's the next set of code.
Note that the code does not attempt to reference the return status argument. This value is not available until the result set is fully populated—only then does SQL Server return this value.
Private Sub RunSPButton_Click()
Dim Qy As New rdoQuery
Dim rs As rdoResultset
sql = "{? = Call AuthorByYearBorn (?,?)}"
Set Qy.ActiveConnection = cn
Qy.sql = sql
Qy.rdoParameters(0).Direction = rdParamReturnValue
Qy(1) = "1947"
Qy(2) = "1948"
Set rs = Qy.OpenResultset()
rdoGrid1.ShowData rs
ShowRows = rs.RowCount
rs.Close
End Sub
The next challenge is to execute a query that returns more than one result set. It is not unusual for a stored procedure to return more than a single set of rows or result set that contains results from an action query—as a matter of fact, it's quite common. Your code must deal with each of the result sets individually unless you want to toss out the entire product of your query. This is fairly simple in RDO—you simply use the MoreResults method to step through the result sets one at a time. Each call to MoreResults closes the current result set and moves to the next (if there is one).
Private Sub MultipleRSButton_Click()
sql = "Select * from Authors Where year_born is not null; " _
& "Select * from Authors where year_born is null"
Set rs = cn.OpenResultset(sql)
rdoGrid1.ShowData rs
i = MsgBox("Ready for next results?", vbYesNoCancel)
If i = vbYes Then
If rs.MoreResults Then
rdoGrid1.ShowData rs
End If
End If
End Sub
In case your application needs to manipulate tables directly, or simply perform a maintenance operation (like SQL Server's DBCC functions), you can use the Execute method to run the query directly. In this case we don't need ODBC (or SQL Server) to create a temporary SP to run the query as we won't be doing this again. Of course, if this is a regular operation, creating an SP to do it would make sense. Note that we can pick up the rows affected by this query by using the RowsAffected property.
Private Sub ExecuteButton_Click()
sql = "Begin Transaction " _
& " Update Authors " _
& " set Year_Born = 1900 where year_born is null" _
& " rollback transaction"
Screen.MousePointer = vbHourglass
cn.Execute sql, rdExecDirect
ShowRows = cn.RowsAffected
Screen.MousePointer = vbDefault
End Sub
Our next challenge is to execute a query that can be used to drive a subsequent "optimistic batch update" operation. In this case, we fetch a result set using the ClientBatch cursor library and save the bookmarks for each row fetched. When the user chooses a row in the grid (where the rows are displayed), we ask the user to provide a new value and post that value to the result set. The trick is that these changes are not made against the data until we finally decide to run the BatchUpdate method.
Private Sub BatchOpsButton_Click()
Dim rs As rdoResultset
sql = "Select * from Authors where year_born is null"
cnB.QueryTimeout = 45
Set rs = cnB.OpenResultset(sql, rdOpenKeyset, rdConcurBatch)
rs.MoveLast: rs.MoveFirst
ReDim bms(rs.RowCount + 1) As Variant
Do Until rs.EOF
bms(i) = rs.Bookmark
i = i + 1
rs.MoveNext
Loop
rs.MoveFirst
rdoGrid1.ShowData rs
End Sub
Perform the update operation based on user input:
Private Sub rdoGrid1_Click()
Dim rs As rdoResultset
Dim NewValue As Integer
NewValue = InputBox("Enter new age -- 1900 to 1997", "Author Age", "1960")
rs.Bookmark = bms(rdoGrid1.Row)
rs.Edit
rs!Year_Born = NewValue
rs.Update
Changes = Changes + 1
i = MsgBox("Commit all " & Changes & " changes?", vbYesNoCancel)
Select Case i
Case vbYes
rs.BatchUpdate
Changes = 0
Case vbNo
Exit Sub
Case vbCancel
Changes = 0
i = MsgBox("Cancel just this change (Yes) or all " & Changes & " made so far (No)?", _
vbYesNo)
If i = vbYes Then
rs.CancelBatch (True)
Else
rs.CancelBatch
End If
End Select
End Sub
Using the preceding code as a base, the next task is to demonstrate how to perform each of these fundamental operations on ADO. In some cases, as in the first example below, the change is fairly significant. Yes, I expect that some of the code might not be needed, as the properties being set would work with their default settings. However, I include it as it can help performance in some cases—by avoiding unneeded calls to the DBMS for more information.
This code is designed to execute a fairly simple table-access query that accepts a marked parameter. Again, we use the "?" character to indicate where to place the parameter. In this case, however, we do not create an rdoQuery object that is kept in a collection off of the rdoConnection object—we use a stand-alone ADO Command object created (and scoped) earlier. The first time through we set up the Command properties, and each time thereafter, we simply execute the command after having changed the parameter.
ADO gives you a lot of flexibility here—more, in some cases than RDO. Because ADO is told everything it needs to know about a query, ADO will not have to execute informational queries against the DBMS to get missing information. This means queries run faster the first time and every time and that they run the way you want them to.
Note that we don't have to build the ADO Parameters collection in code—it is created for us as it is when we use RDO. However, it is possible to do so and doing so can improve performance—but make your application more complex to code. Just make sure that the Command is associated with an open connection so ADO can query the service provider (and the server) for the parameters' description.
To run the query and create the result set, we use the Execute method against the Command object.
Private Sub ParmQueryButton_Click()
Static FirstTime As Boolean
If Cmd.CommandText = "" Then
Cmd.ActiveConnection = cn
FirstTime = False
With Cmd
.CommandText = "select * from authors where year_born = ?"
.CommandType = adCmdText
.CommandTimeout = 15
End With
'
' The following section of code is not required,
' but can make execution faster. It eliminates the need
' for ADO to fetch the parameter metrics from the server.
'
With Parm
.Type = adInteger
.Size = 4
.Direction = adParamInput
.Value = QueryParam.Text
Cmd.Parameters.Append Parm
End With
End If
Cmd.Parameters(0).Value = QueryParam.Text
Set rs = Cmd.Execute()
ADOGrid1.ShowData rs
rs.Close
End Sub
ADO has a lot of flexibility when it comes to executing stored procedures. This also means that it will require more code. As with the previous example, you will still have to build up your own ADODB Parameters collection. Since the UserConnection designer is not online yet for ADO (that's coming), this has to be done carefully. In this case we are executing a simple two-argument SP, "AuthorByYearBorn," that returns a small result set.
Private Sub RunSPButton_Click()
Dim Qy As New ADODB.Command
Dim Parm As New ADODB.Parameter
Dim Parm2 As New ADODB.Parameter
Set Qy.ActiveConnection = cn
Qy.CommandType = adCmdStoredProc
Qy.CommandText = "AuthorByYearBorn"
Parm.Type = adInteger
Parm.Name = "YearHigh"
Parm.Size = 4
Parm.Direction = adParamInput
Parm.Value = 1947
Qy.Parameters.Append Parm
Set Parm2 = New ADODB.Parameter
Parm2.Type = adInteger
Parm2.Name = "YearLow"
Parm2.Size = 4
Parm2.Direction = adParamInput
Parm2.Value = 1948
Qy.Parameters.Append Parm2
Set rs = Qy.Execute(ShowRows)
ADOGrid1.ShowData rs
End Sub
Our next challenge is the basic multiple-result set SP. As we said before this is not that uncommon, so you should be expected to deal with this type of issue quite frequently. ADO's approach is very unlike the RDO approach. ADO uses the NextRecordset method, which lets you assign the next record set in the batch to an ADO Recordset object. In ADO 1.5, the previous Recordset is automatically closed, but subsequent versions are expected to leave them open until specifically closed. Since you might not know how many result sets will be generated by your query, you must now test Recordset State property to see if it is still open—you no longer have a Boolean return value to test as in RDO.
This new paradigm for handling multiple result sets is certainly different than what an RDO developer is used to working with. Yes, the same Recordset object can be reused or another can be created to handle each result set. But in the latter case, you will have to manually close the Recordset object when you are done with it to prevent memory leaks—at least when ADO is upgraded to keep these objects open once you move to the next result set.
Private Sub MultipleRSButton_Click()
Dim rs2 As New ADODB.Recordset
sql = "Select * from Authors Where year_born is not null; " _
& "Select * from Authors where year_born is null"
rs.Open sql, cn
Do
i = MsgBox("Ready for results?", vbYesNoCancel)
If i = vbYes Then
ADOGrid1.ShowData rs
Set rs = rs.NextRecordset
End If
Loop Until rs.State = adStateClosed
End Sub
When you need to execute an action query, you can take advantage of the Execute method in ADO to do the job. In this case, we have to set a few more properties than we do in RDO. However, these can improve data access performance so ADO does not have to poll the server to determine what to do, or how to handle the query. Note that the new output argument for the Execute method returns the number of rows affected. Generally, you don't see Visual Basic using arguments passed back to the application—just arguments passed to the object interface.
Private Sub ExecuteButton_Click()
Dim Qy As New ADODB.Command
Dim Rows As Long
sql = "Begin Transaction " _
& " Update Authors " _
& " set Year_Born = 1900 where year_born is null" _
& " rollback transaction"
Qy.ActiveConnection = cn
Qy.ActiveConnection = cn
Qy.CommandText = sql
Qy.CommandType = adCmdText
Qy.Execute Rows
MsgBox Rows & " rows would have been affected", vbInformation
End Sub
Our last ADO example implements a batch operation. I was able to code this pretty much as I had for RDO and found the ADO coverage of this functionality very comprehensive. Note that the routine used to change the chosen row in the R/W result set did not require starting an "Edit" session. Simply changing the contents of a Field and using the Update method was enough to make the changes to the DB.
Private Sub BatchOpsButton_Click()
sql = "Select * from Authors where year_born is null"
rs.Open sql, cnB, adOpenKeyset, adLockBatchOptimistic
rs.MoveLast: rs.MoveFirst
ReDim bms(rs.RecordCount + 1) As Variant
Do Until rs.EOF
bms(i) = rs.Bookmark
i = i + 1
rs.MoveNext
Loop
rs.MoveFirst
ADOGrid1.ShowData rs
rs.Close
End Sub
Private Sub ADOGrid1_Click()
Dim NewValue As Integer
NewValue = InputBox("Enter new age -- 1900 to 1997", "Author Age", "1960")
rs.Bookmark = bms(ADOGrid1.Row)
rs!Year_Born = NewValue
rs.Update
Changes = Changes + 1
i = MsgBox("Commit all " & Changes & " changes?", vbYesNoCancel)
Select Case i
Case vbYes
rs.BatchUpdate
Changes = 0
Case vbNo
Exit Sub
Case vbCancel
Changes = 0
i = MsgBox("Cancel just this change (Yes) or all " & Changes & " made so far (No)?", _
vbYesNo)
If i = vbYes Then
rs.CancelBatch (True)
Else
rs.CancelBatch
End If
End Select
End Sub
All in all, an RDO developer will notice a number of new ways to deal with data when using ADO. Most of the standard techniques are covered by ADO functionality Version 1.5, and only in a few cases were we forced to choose alternative paths to get our problems solved. When ADO 2.0 arrives with its richer coverage of RDO and DAO functionality, we should see fewer instances (if any at all) that require radical changes in our development approach.