Exploring Multiple Active Record Sets with MS Access - Interleaving Data from Two Recordsets

(Page 4 of 4 )

The values from the recordsets can be interleaved as the following example shows. Of course the example is trivial, and contrived to display one row of data from one recordset followed by several related rows of data from another recordset -- in the nature of a join (see the last picture in this article). This is indeed useful if joins are not possible for some reason, or they are costly. Since Select, Bulk Insert, Fetch and a few other operations are possible, and as the recordsets are open, it will be possible to see the updated values without closing the connection.

Private Sub Form_Load()
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=HODENTEKMYSORIAN;" _
& "Database=Northwind;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open
Dim recordsaffected As Integer
Dim str1
Dim str2
str1 = "SELECT ProductID, ProductName from Products where
(ProductID)='17'"
str2 = "SELECT OrderID, ProductID, UnitPrice, Quantity FROM
[Order Details] WHERE (ProductID = '17')"
Set rst1 = con.Execute(str1, recordsaffected, adCmdText)
Set rst2 = con.Execute(str2, recordsaffected, adCmdText)
Dim strRst1
strRst1 = ""
rst1.MoveFirst
While Not rst1.EOF
If rst1.Fields.Item(0).Value = "17" Then
strRst1 = strRst1 & rst1.Fields.Item(0).Value & " | " 

& rst1.Fields.Item(1).Value & ";"
Else
strRst1 = strRst1 + ""
End If
rst1.MoveNext
Wend
MsgBox ("First RST is: " & strRst1)
Dim str3
str3 = ""
While Not rst2.EOF
str3 = str3 & ";" & rst2.Fields.Item(0).Value & " | " 

& rst2.Fields.Item(1).Value & " | " & rst2.Fields.Item(2).Value & ";"
rst2.MoveNext
Wend
MsgBox ("Second Rst is: " & str3)
Dim strln
strln = "**************************************"
Me.List0.RowSource = strRst1 & strln & vbCrLf & str3
con.Close
Set con = Nothing
End Sub

 

The result of displaying the form is shown in the next picture.

Summary

The tutorial has presented some of the basic features of connecting to SQL Server 2005 from MS Access using ADO. Some of the properties of the multiple record sets activated have been explored. Since the data is accessed by the fastest cursor combined with multiple cursors open at the same time (but not operating in parallel), it should be possible to improve the performance of data retrieval in the sense of fast access. Various limitations and other important considerations are outside the scope of this basic article and the reader is recommended to look in MSDN sites and dedicated blogs.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Exploring Multiple Active Record Sets with MS Access

· Multiple Active Record Sets is new in SQL 2005 server. Although presented as a MS...   >>> Post your comment now!