Rowset Does Not Support Scrolling Backward

Rowset does not support scrolling backward

adOpenDynamic is not declared in VBScript and therefore equals Empty, which gets converted to 0 when you assign the CursorType property.

0 is adOpenForwardOnly, and forward only does not support moving backwards, an ability the Find method wants.

You should replace adOpenDynamic with its literal value:

Recordset.CursorType = 2 'adOpenDynamic

To avoid this class of errors altogether, place Option Explicit as the first line of your script.

Recordset.Find Error: Rowset does not support scrolling backward with Stored Procedure

The problem lies not in the keys.CursorType statement, but in the keys.CursorLocation statement.

I replaced keys.CursorLocation = adUseServer with keys.CursorLocation = adUseClient, which immediately resolved the issue.

The working code has become

Private Sub maintablebox_Change()

Dim cnn As ADODB.Connection
Dim keys As ADODB.Recordset
Set cnn = New ADODB.Connection
connstring = "omitted"
cnn.Open connstring

Set keys = New ADODB.Recordset
keys.CursorLocation = adUseClient
query = "EXEC sp_fkeys @fktable_name = 'astAssets'"
keys.Open query, connstring, adOpenDynamic, adLockReadOnly

keys.Find "PKTABLE_NAME = 'astAssetTypes'"

Debug.Print keys.Fields("FKCOLUMN_NAME")
End Sub

I found the solution on this vbforums thread. I noticed the OP using a stored procedure just like I am, so the error code I was presented with may be due to that factor.

I have only limited knowledge of ADODB recordsets, so if someone would be willing to explain why setting the cursorlocation to adUseClient fixed my problem, please be my guest.

VBA rowset does not support fetching backward error with .cursertype adjusted

The problem is caused by using conn.Execute to fill the recordset. Setting the recordset's activeconnection to the ADODB.Connection and using the recordset's open method will fix the issue.

Function ConnectServer() As String()
   'Working SQL Server connection

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim strSqlQuery As String
    Dim iCols As Long

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=wait;" & _
                  "Initial Catalog=what;" & _
                  "User Id=foo;" & _
                  "Password=bar;"

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    conn.ConnectionString = sConnString
    conn.Open

    Set rs = New ADODB.Recordset

    With rs
        .ActiveConnection = conn
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = "SELECT * FROM b"
        .Open
    End With

    ' Check we have data.
    If Not rs.EOF Then

        rs.MoveLast
        Debug.Print rs.RecordCount

        'Read into array and cleanup...
    End If

End Function

You can use Recordset.GetRows() to fill the array. No need to dim it. GetRows Method (ADO)

How verify if a data exist in a recordset with an external variable? VBA

For DAO, try with:

filter2 = "[Nom] = '" & oLookFullName & "'"
rs.FindFirst filter2
If rs.NoMatch Then
    Debug.Print oLookFullName & " is not found."
End If

For ADO, try with:

filter2 = "[Nom] = '" & oLookFullName & "'"
rs.MoveFirst
rs.Find filter2
If rs.EOF Then
    Debug.Print oLookFullName & " is not found."
End If

recordset.movefirst Rowset position cannot be restarted

There is usually no reason to MoveFirst if you have not previously navigated the record set.

The overflow is unrelated to the database code and is caused by rs!ID not fitting in a VBA integer (16 bit) so make temp a Long instead (32 bit) and remove MoveFirst.

XML rowset and MsXML2

Given a table like:

SELECT TOP 5 * FROM [actor.txt]
----------------------------------------------------------------
|actor_id|first_name|last_name   |last_update         |
|       1|PENELOPE  |GUINESS     |2/15/2006 4:34:33 AM|
|       2|NICK      |WAHLBERG    |2/15/2006 4:34:33 AM|
|       3|ED        |CHASE       |2/15/2006 4:34:33 AM|
|       4|JENNIFER  |DAVIS       |2/15/2006 4:34:33 AM|
|       5|JOHNNY    |LOLLOBRIGIDA|2/15/2006 4:34:33 AM|
----------------------------------------------------------------

in an ADO (classic, tested with version 2.8) accessible database, you
can save the resultset to XML using

  oRS.Save sFSpec, adPersistXML

That gives you XML like:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly'>
        <s:AttributeType name='actor_id' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='first_name' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='string' dt:maxLength='45'/>
        </s:AttributeType>
        <s:AttributeType name='last_name' rs:number='3' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='string' dt:maxLength='45'/>
        </s:AttributeType>
        <s:AttributeType name='last_update' rs:number='4' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
            <s:datatype dt:type='dateTime' rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
    </s:ElementType>
</s:Schema>
<rs:data>
    <z:row actor_id='1' first_name='PENELOPE' last_name='GUINESS' last_update='2006-02-15T04:34:33'/>
    <z:row actor_id='2' first_name='NICK' last_name='WAHLBERG' last_update='2006-02-15T04:34:33'/>
    <z:row actor_id='3' first_name='ED' last_name='CHASE' last_update='2006-02-15T04:34:33'/>
    <z:row actor_id='4' first_name='JENNIFER' last_name='DAVIS' last_update='2006-02-15T04:34:33'/>
    <z:row actor_id='5' first_name='JOHNNY' last_name='LOLLOBRIGIDA' last_update='2006-02-15T04:34:33'/>
</rs:data>
</xml>

To read that data, start with (local, console) code like:

  Dim oFS    : Set oFS   = CreateObject( "Scripting.FileSystemObject" )
  Dim sDDir  : sDDir     = oFS.GetAbsolutePathName( "..\Data" )
  Dim sFSpec : sFSpec    = oFS.GetAbsolutePathName( "..\Data\actor.xml" )
  Dim oXDoc  : Set oXDoc = CreateObject("msxml2.domdocument")
  Dim sXPath, ndFnd, ndlFnd, attrX, nIdx
  oXDoc.async             = False
  oXDoc.validateOnParse   = False
  oXDoc.resolveExternals  = False
  oXDoc.setProperty "SelectionLanguage", "XPath"
  oXDoc.setProperty "SelectionNamespaces", Join( Array( _
      "xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'" _
    , "xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'" _
    , "xmlns:rs='urn:schemas-microsoft-com:rowset'" _
    , "xmlns:z='#RowsetSchema'" _
 ), " ")
  If oXDoc.load(sFSpec) Then
     WScript.Echo sFSpec, "looks good."
     For Each sXPath In Array( _
              "/xml" _
            , "/xml/s:Schema" _
            , "/xml/rs:data" _
            , "/xml/rs:data/z:row[@actor_id=""2""]" _
         )
         WScript.Stdout.Write "|" & sXPath & "| => "
         Set ndFnd = oXDoc.selectSingleNode( sXPath )
         If ndFnd Is Nothing Then
            WScript.Stdout.WriteLine "not found"
         Else
            WScript.Stdout.WriteLine "found a(n) " & ndFnd.tagName
         End If
     Next
     WScript.Echo "-----------------------"

'<rs:data>
'   <z:row actor_id='1' first_name='PENELOPE' last_name='GUINESS' last_update='2006-02-15T04:34:33'/>
'       ...
'</rs:data>
     sXPath = "/xml/rs:data/z:row[@actor_id=""3""]"
     Set ndFnd = oXDoc.selectSingleNode( sXPath )
     If ndFnd Is Nothing Then
        WScript.Echo "|", sXPath, "| not found"
     Else
        For Each attrX In ndFnd.Attributes
            WScript.Echo attrX.Name, attrX.Value
        Next
     End If
     WScript.Echo "-----------------------"

     sXPath = "/xml/rs:data/z:row"
     Set ndlFnd = oXDoc.selectNodes( sXPath )
     If ndlFnd Is Nothing Then
        WScript.Echo "ndlFnd Is Nothing"
     Else
        If 0 = ndlFnd.Length Then
           WScript.Echo "ndlFnd is empty"
        Else
           For Each ndFnd In ndlFnd
               WScript.Echo TypeName(ndFnd)
               For Each attrX In ndFnd.Attributes
                   WScript.Echo "", attrX.Name, attrX.Value
               Next
           Next
        End If
     End If
  Else
     WScript.Echo "Bingo!"
     WScript.Echo oXDoc.parseError.reason
  End If

The important steps:

For developing/testing avoid the extra complexity of using a server;
if cscript readxml.vbs succeeds, it's easy to 'port' the working code
to .asp (and deal with server specific problems separately)
Create an msxml2.domdocument
Configure it; especially copy the namespaces from the xml tag to the
SelectionNamespaces
Load the file, check for errors
Specify your XPath expressions (carefully!, try to 'translate' XML from your sample to XPATH)
Check the results of .selectSingleNode() and .selectNodes()
Access the attributes holding the data

Rowset Does Not Support Scrolling Backward