Exploring Multiple Active Record Sets with MS Access

Introduction

SQL Native Client, or SQLNC for short, is a data access technology that made its debut with SQL 2005 and ADO.NET. SQLNC is only available when you are working with SQL 2005. It is not bundled with MDAC Stack, a technology which has been popular for many years in various versions.

SQLNC combines both OLEDB and ODBC features, that is, it has both SQLOLEDB as well as SQL ODBC drivers in one API. It brings with it several new features in SQL 2005 such as Multiple Active Record Sets (MARS), support for XML data type, mirroring, and so on. The rationale for the use of this driver is to work with or migrate to SQL 2005 on the one hand, and escape the wrath of 'dlls' from the MDAC stack on the other hand. It offers the possibility to return multiple recordsets from the same connection instead of the one active query per one connection that was possible with OleDB.

In this tutorial we will look at accessing multiple recordsets from a single connection to SQL Server 2005 in Microsoft Access. It will be shown how you may be able make a single connection using this driver and look at three different tables from the same connection. We will also explore the properties of the recordsets so created.

In particular we will connect to the Northwind database on the SQL 2005 server. The Northwind database did not come with the out-of-the-box installation of SQL 2005, but it was moved from a SQL 2000 server which is described in an earlier tutorial. We will be looking at three of the tables from this database, and the design of the tables as they will be referenced in this tutorial is shown in the next three pictures. These are obtained by expanding the columns folder of the related tables in the Northwind database.

Orders Table

Products Table

Order Details Table

Connecting to SQL 2005 using ADO

Create a blank MS Access application. Since it is a blank application there are as yet no objects in this database. Create a new form from design and save it with the name Martian. To the load event of the form add the code shown here. The recordsets are assigned by the connection's execute method, and are available for reviewing.

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
Set rst1 = con.Execute("SELECT OrderDate, OrderID FROM Orders where
OrderDate>'5/5/1998'", recordsaffected, adCmdText)
Set rst2 = con.Execute("SELECT ProductID, ProductName, UnitPrice
FROM Products", recordsaffected, adCmdText)
Set rst3 = con.Execute("SELECT UnitPrice, OrderID From [Order Details]",
recordsaffected, adCmdText)
Set rst1 = con.Execute("SELECT OrderDate,OrderID FROM Orders where
OrderDate>'5/5/1998'", recordsaffected, adCmdText)
'------Place code from next section to populate a list box-----
'--------------------------------------------------------------
MsgBox ("Number of Columns in the rst1 recordset is = " &
rst1.Fields.Count)
MsgBox ("Number of Columns in the rst2 recordset is = " &
rst2.Fields.Count)
MsgBox ("Number of Columns in the rst3 recordset is = " &
rst3.Fields.Count)
con.Close
Set con = Nothing End Sub

The SQLNC connection string has several components to it. The provider now is called SQLNCI, short for SQL Native client, the new API alluded to in the introduction. The server is the SQL 2005 server (the instance name is Mysorian) on the machine Hodentek. The database is Northwind as described earlier. The security is Windows integrated and the DataType Compatibility is set to 80; this is necessary so that the ADO data types map correctly to the new data types of the SQLNCI. Of course if you want to access Multiple Active Recordsets (MARS), you want this argument (MARS Connection) to be assigned a value true.

After providing this string you may open the connection, and this connection will support opening multiple record sets at the same time. The results from these can be used independently, or in any other fashion within the context of a single batch.

With the connection in hand you may get access to records from any of the tables as shown here. The syntax shown here comes from the drop-down help as shown here for rst4's Execute() method. Four connections, rst1, rst2, rst3, and rst4 are open at the same time.

The rest of the code just displays the number of columns in the recordsets in four message boxes, the first of which is shown in the next picture.

Properties of the opened recordsets

The opened recordsets return the following values for some of the properties. The CursorType property returns a value of 0 which means it is Open forward only, and the LockType is 1 which means it is read-only. The CursorLocation property with a value of 2 means the queries are managed by the database server or the OleDB provider. Calling the sort method on the recordset returns a message that the provider does not provide any interface for sorting or filtering.

All recordsets are opened at the same time as shown in the next picture for this snippet. Recordset's state property is 'open' for a value 1.

MsgBox ("rst1's state is: " & rst1.State & vbCrLf _
& "rst2's state is: " & rst2.State & vbCrLf _
& "rst3's state is: " & rst3.State)

Recordsets can be stuffed into a string which can be used as the RowSource of list boxes and combo boxes. However, the RowSourceType property should be set to Value List. For example, the following code populates a list box on the form as shown in the next picture. Similar code for the other recordsets can be used to populate the list boxes or combo boxes.

Dim strRst1
strRst1 = ""
While Not rst1.EOF
strRst1 = strRst1 & rst1.Fields.Item(0).Value & " | " &
rst1.Fields.Item(1).Value & ";"
rst1.MoveNext
Wend
Me.List2.RowSource = strRst1

Interleaving Data from Two Recordsets

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.

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.