Disconnected Recordsets

Disconnected recordsets can be a powerful way to do operations on the client computer once you have retrieved the results of a statement or procedure. Basically, the order of operations is to instantiate a connection with the AS/400, (or other data source), execute a statement or stored procedure that returns a recordset, read until the end of the recordset and then set the recordsets connection property to nothing. Setting the connection property to nothing causes the recordset to "disconnect" from the data source. You are now free to modify the recordset, re-sort the recordset or diddle it in any way you see fit. Any changes will be discarded when you no longer reference the recordset.

Example List Indexes Macro

The following Macro is from my Index Listing Spreadsheet which you can download from this site. The Macro uses a disconnected recordset in order to display the column text of each field used in a logical file. The reason for choosing a disconnected recordset in this macro is purely a performance decision. Lets take a look at how you would write the code if you did not have a disconnected recordset.

Open a connection to the AS/400
Prepare a statement to retrieve column text for a field from the SYSCOLUMNS table
Prepare a statement to retrieve the indexes over a physical file
for each index returned by statement 3
Bind the column name to statement 2
Execute statement 2
retrieve column text
next
Now, note that for each column the code needs to bind the returned column name into the statement at step 2 and then execute the statement and retrieve the columns text. This is very inefficent in two ways,

Columns that appear in multiple indexes will have their data fetched several times
Each fetch of column information requires several packet trips from client to server
Now, you could write a caching algorithm so that you save each column text returned and then before getting column text for a field you check the cache to see if your have already retrieved it. This would be a fine solution, as it would minimize the number of times you retrieved information for the same column. However, because you are fetching column data a single column at a time, the transfer of that data is not optimized so you still have many more packets flying back and forth than you need.

The Beauty of a Disconnected Recordset

See, with the disconnected recordset we can achieve two goals

We know that we are listing indexes for table <X>, so we can optimize the transfer of all column descriptions of table <X> to the client.
We can use the built-in search and sorting of the recordset to retrieve the column text for each column in an index, thereby saving us from having to write caching or array search logic.

Here is the VB Code

Following is the code that makes up the disconnected recordset macro for listing indexes.

Public Sub GetIndexInfo()
Dim con1 As New ADODB.Connection
'make it fast
Application.ScreenUpdating = False
Worksheets("DataEntry").Activate
Range("A1").Activate
Library = UCase(Range("C4").Value)
Table = UCase(Range("C5").Value)
DSN = Range("C6").Value
UID = Range("C7").Value
PWD = Range("C8").Value
DSNSTR = "DSN=" & DSN & ";UID=" & UID & ";PWD=" & PWD
con1.Open DSNSTR
ArgArray = Array(Empty, Library, Table, Empty)

'In the call below, we are opening the columns resultset

Set cs = con1.OpenSchema(adSchemaColumns, ArgArray)
cs.MoveLast
 

'Next we are disconnecting the recordset

cs.ActiveConnection = Nothing

'Move the recordset back to the beginning
cs.MoveFirst

'The next few lines get the indexes for the physical file
ArgArray = Array(Empty, Library, Empty, Empty, Table)
Set rs = con1.OpenSchema(adSchemaIndexes, ArgArray)

'At this point RS is instantiated with a recordset of indexes
'associated to the physical file
'The code that follows sets up the display in the worksheet

Worksheets("TableIndexes").Activate
Cells.ClearContents
Range("A1").Activate
Range("A1").Value = "Available Indexes"
Range("A1").Font.Size = 12
Range("A1").Font.Bold = True
Range("A1", "E1").MergeCells = True
Range("A2").Value = Library & "/" & Table
Range("A2").Font.Size = 12
Range("A2").Font.Bold = True
Range("A2", "E2").MergeCells = True
Range("A4").Activate
R = 0
ActiveCell.Offset(R, 0).Font.Size = 10
ActiveCell.Offset(R, 0).Font.Bold = True
ActiveCell.Offset(R, 0).Font.Underline = True
ActiveCell.Offset(R, 0).Value = "Index Name"
ActiveCell.Offset(R, 1).Font.Size = 10
ActiveCell.Offset(R, 1).Font.Bold = True
ActiveCell.Offset(R, 1).Font.Underline = True
ActiveCell.Offset(R, 1).Value = "Unique?"
ActiveCell.Offset(R, 2).Font.Size = 10
ActiveCell.Offset(R, 2).Font.Bold = True
ActiveCell.Offset(R, 2).Font.Underline = True
ActiveCell.Offset(R, 2).Value = "SortSeq"
ActiveCell.Offset(R, 3).Font.Size = 10
ActiveCell.Offset(R, 3).Font.Bold = True
ActiveCell.Offset(R, 3).Font.Underline = True
ActiveCell.Offset(R, 3).Value = "ColName"
ActiveCell.Offset(R, 4).Font.Size = 10
ActiveCell.Offset(R, 4).Font.Bold = True
ActiveCell.Offset(R, 4).Font.Underline = True
ActiveCell.Offset(R, 4).Value = "Description"
R = R + 1
c = 0
ixname = ""

'For each record in the index recordset
While Not rs.EOF

'the following code looks to see if the ixname is the same
If rs.Fields(5).Value <> ixname Then
If c > 0 Then
'if not the first index then add a blank line
R = R + 1
End If
ActiveCell.Offset(R, 0).Font.Size = 10
ActiveCell.Offset(R, 0).Font.Bold = True
ActiveCell.Offset(R, 0).Value = rs.Fields(5).Value
ActiveCell.Offset(R, 1).Value = rs.Fields(7).Value
ixname = rs.Fields(5).Value
c = c + 1
End If

'print whether the fied is ascending or descending
If rs.Fields(20).Value = 1 Then
SS = "Asc"
Else
SS = "Desc"
End If
ActiveCell.Offset(R, 2).Value = SS

'get the index field name
FldName = rs.Fields(17).Value
ActiveCell.Offset(R, 3).Value = FldName

'set the filter property of disconnected recordset cs
'so that we can find the column text for the field
'if not eof, (we found the field), then print the text

cs.Filter = "COLUMN_NAME = '" & FldName & "'"
If Not cs.EOF Then
ActiveCell.Offset(R, 4).Value = cs.Fields(27).Value
End If
R = R + 1
 

'Go to the next record in the index list and loop

rs.MoveNext
Wend

'set the print area of the worksheet to only the data retrieved
'this helps make nice with the printer

Worksheets("TableIndexes").PageSetup.PrintArea = "A1:E" & R + 4
Application.ScreenUpdating = True
Range("A1").Activate
End Sub