Exploring Multiple Active Record Sets with MS Access - Executing SQL Queries on the Open Connection

(Page 3 of 4 )

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.

Populating ListBoxes with the records

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

Next: Interleaving Data from Two Recordsets >>
 

More Microsoft Access Articles
More By Jayaram Krishnaswamy

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!