More Recordset Secrets | Ado Examples and Best Practices

Team-Fly

It's possible to pass the contents of several Recordsets to a client from the middle tier. For example, if you combine a series of table-lookup queries in a multiple ad hoc SELECT, or execute a stored procedure that returns several result sets, you can combine the Recordsets into a single Variant array and transport them back to the client thusly:

 EXE COM Server (running over DCOM): Function GetData() As Variant Dim rs(1 To 5) As ADODB.Recordset Dim v As Variant Dim i As Long, j As Long     For i = 1 To 5         Set rs(i) = New ADODB.Recordset         rs(i).CursorLocation = adUseClient         rs(i).Fields.Append "Now", adVarChar, 255         rs(i).Open         For j = 1 To 1000             rs(i).AddNew "Now", GetTickCount()         Next j         Debug.Print rs(i).RecordCount     Next i     v = Array(rs(1), rs(2), rs(3), rs(4), rs(5))     GetData = v End Function

The client-side code looks like this:

 Sub SimpleTest() Dim objGet As GetMultiRS Dim v As Variant Dim i As Long Dim rs As ADODB.Recordset Set objGet = New GetMultiRS     v = objGet.GetData     Set objGet = Nothing   ' This releases COM EXE server.     For i = 0 To 4         Set rs = v(i)         While Not rs.EOF             Debug.Print rs.Fields(0).Value             rs.MoveNext         Wend     Next i End Sub

Increasing Recordset Performance

You can greatly improve ADO's insertion performance by doing insertions using Arrays of variants instead of accessing the Field Value property Field-by-Field. We've seen major performance increases, as much as 400 percent, using this technique. Here is some code to demo the performance:

 Public Sub ADOFastInsert() Dim rs As ADODB.recordset Dim sngStartTimer As Single Dim i As Long, j As Long Dim arrRecords() As Variant Dim arrFieldList() As Variant Const RECORD_COUNT = 1000 Const FIELD_COUNT = 20      ' Build recordset.     Set rs = New ADODB.recordset     ReDim arrFieldList(0 To FIELD_COUNT − 1)     For i = 1 To FIELD_COUNT         rs.fields.Append "f" & i, adChar, 10         arrFieldList(i − 1) = "f" & i     Next i     rs.Open     ' Regular insert test.     sngStartTimer = GetTickcount()     With rs         For i = 1 To RECORD_COUNT             .AddNew             For j = 0 To FIELD_COUNT − 1                 .fields(j).Value = "xxxxxxxxxx"             Next j             .Update         Next i         Debug.Print "Elapsed time for regular insert is " & _ GetTickCount - sngStartTimer & " seconds."     End With     ' Array insert test.     sngStartTimer = GetTickCount     With rs         For i = 1 To RECORD_COUNT             ReDim arrRecords(0 To FIELD_COUNT − 1)             For j = 0 To FIELD_COUNT − 1                 arrRecords(j) = "xxxxxxxxxx"             Next j             .AddNew arrFieldList, arrRecords         Next i         Debug.Print "Elapsed time for array insert is " & GetTickCount - sngStartTimer & " seconds."     End With End Sub

Fine-Tuning the Filter Criteria

A user wrote in with a (fairly typical) problem. When using the Filter property, some criteria settings cause an ADO error 3001. This doesn't work:

 rsSchemaClone.Filter = "TABLE_NAME=" & Chr(34) & " 'Hello & World'$" & Chr(34)

but this does:

 rsSchemaClone.Filter = "TABLE_NAME=" & Chr(34) & "'Hello&World'$" & Chr(34)

It turns out that ADO's Filter property syntax parser gets kinda confused when there are loose single quotes. To get around this problem, surround the entire string with pound (#) signs.

 rsSchemaClone.Filter = "TABLE_NAME=#'Hello & World'$#"

Managing Errors from the Correct Source

When an error occurs, you might get a trappable error from Visual Basic, or ADO, or both. When working with an ASP, there are no error traps—at least not until Windows 2000 and ASP 3.0—so you'll have to test for errors yourself after each error-prone operations. I have seen cases where ASP gets an ADO error, but the VB description is empty. The following code tests for Visual Basic-generated trappable errors, as well as those errors generated by ADO and referenced in the Error object associated with the ADO Connection object.

 StrTmp = StrTmp & vbCrLf & "VB Error # " & Str(Err.Number)       StrTmp = StrTmp & vbCrLf & "   Generated by " & Err.Source       StrTmp = StrTmp & vbCrLf & "   Description " & Err.Description        ' Enumerate Errors collection and display properties of        ' each Error object. Set Errs1 = Conn1.Errors       For Each errLoop In Errs1         With errLoop             StrTmp = StrTmp & vbCrLf & "Error #" & i & ":"             StrTmp = StrTmp & vbCrLf & "   ADO Error  #" & .Number             StrTmp = StrTmp & vbCrLf & "   Description " & .Description             StrTmp = StrTmp & vbCrLf & "   Source      " & .Source             i = i + 1        End With Next

Working with the Schema Object

The ADO 2.0 exposes the Schema object to help you view and manipulate database schemas. The OpenSchema method returns self-descriptive information about the data source, such as what tables are in the data source, the columns in the tables, and the datatypes supported. However, it often makes more sense when working with SQL Server to simply query the sysobjects table to gather this information. Changes to the schema can be easily implemented in SQL Server using TSQL commands. On the other hand, not many providers are as flexible and powerful as SQL Server. This is where the Schema object comes into play.

The following example uses the OpenSchema method to display the name and type of each table in the Pubs database.

 Public Sub OpenSchemaX()    Dim cnn1 As ADODB.Connection    Dim rstSchema As ADODB.Recordset    Dim strCnn As String    Set cnn1 = New ADODB.Connection       strCnn = "Provider=sqloledb;Data Source=srv;Initial Catalog=Pubs; "    cnn1.Open strCnn ,"admin", "pw"    Set rstSchema = cnn1.OpenSchema(adSchemaTables)    Do Until rstSchema.EOF       Debug.Print "Table name: " & _          rstSchema!TABLE_NAME & vbCr & _          "Table type: " & rstSchema!TABLE_TYPE & vbCr       rstSchema.MoveNext    Loop    rstSchema.Close    cnn1.Close End Sub

The next example specifies a TABLE_TYPE query constraint in the OpenSchema method Criteria argument. As a result, only schema information for the Views specified in the Pubs database is returned. The example then displays the name and type of each table.

 Public Sub OpenSchemaX2()     Dim cnn2 As ADODB.Connection     Dim rstSchema As ADODB.Recordset     Dim strCnn As String     Set cnn2 = New ADODB.Connection         strCnn = "Provider=sqloledb;Data Source=srv;Initial Catalog=Pubs;"     cnn2.Open strCnn, "Admin", "pw"     Set rstSchema = cnn2.OpenSchema_     (adSchemaTables, Array(Empty, Empty, Empty, "VIEW"))         Do Until rstSchema.EOF           Debug.Print "Table name: " & _              rstSchema!TABLE_NAME & vbCr & _              "Table type: " & rstSchema!TABLE_TYPE & vbCr           rstSchema.MoveNext         Loop     rstSchema.Close     cnn2.Close End Sub

Team-Fly