Resolving The Problem

Client Access V4R5 and Earlier

At Client Access V4R5 and earlier releases, the only cursor type that was returned by an SQL statement was the dynamic cursor type. Although Microsoft ActiveX Data Objects (ADO) specify a default cursor type of forward only, the provider at this release level did not support a forward only cursor. This meant that any SQL recordset was opened with a dynamic cursor, and was fully scrollable. This was not the behavior defined by the ADO specification, but a direct result of the cursor type limitation of the IBMDA400 OLE DB provider.

Client Access V5R1 and Later

With Client Access V5R1, two new cursor types were introduced for the Client Access OLE DB provider for SQL recordsets. The two cursors introduced were dynamic and forward only. Introducing the forward only cursor allowed Client Access to return the default cursor type called for by the ADO specification. This means that any recordset that is created without requesting a dynamic or static cursor will not be scrollable. This includes recordsets produced by the execute method of the command and connection objects. According to the Microsoft documentation for the execute method of a connection:

The returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type.

Applications that were developed to the limitation of the OLE DB provider and expect a scrollable result set default must be changed. Any methods that require a scrollable result will fail with a message similar to one of the following:

Rowset position cannot be restarted.
Operation is not allowed in this context.
Rowset does not support scrolling backward.

The following is an example of Visual Basic code that can be used to produce a scrollable result set.

Dim cn As New ADODB.Connection
'Some connection code.

Dim sql As String
sql = "SELECT * FROM QIWS.QCUSTCDT"

Dim rs As New ADODB.Recordset
rs.Open sql, cn, adOpenDynamic

This change can be found documented in the Client Access V5R1 README file.