ADO/Stored Procedure Data Retrieval

Unfortunately, the majority of example code for ADO/stored procedure data retrieval, fails to yield workable code when employed in real world applications. Problems arise when applications require MoveFirst and MoveLast relocation within the recordset. Although this may appear a basic requirement, such movement is not supported in the most examples, such as the following:

   With cmm
      Set .ActiveConnection = cnConnection
      .CommandText = "usp_phonebook"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("strName", _
        adVarChar, adParamInput, 10)
      .Parameters("@strName").Value = "Smith"
      Set rst = .Execute
   End With

The problem is due to opening a recordset based on a recordset created with default cursor/lock properties. The solution is to set these properties before binding the Recordset object to a Command or Connection object. First set up the Recordset for offline navigation as follows:

   With rst
       .CursorType = adOpenStatic
       .CursorLocation = adUseClient
       .LockType = adLockBatchOptimistic
       .ActiveConnection = Nothing
   End With

Then, with the Command object, define the ActiveConnection property, assign the stored procedure and its parameters and open the recordset object based on the Command object, preserving the non default recordset cursor/lock properties as follows:

   With cmm
       .Prepared = True
       .ActiveConnection = cnConnection
       .CommandType = adCmdStoredProc
       .CommandText = "usp_phonebook"
       .Parameters("@strName").Value = "Smith"
   End With
   rst.Open cmm

This will result in a recordset capable of MoveNext, MovePrevious, MoveFirst and MoveLast.