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
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
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'$#"
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
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