ADO Recordset Losing Its Data When Passed To Several Functions In Sequence (Access VBA)

Question

I have a function that returns an ADO recordset, this in turn is used to feed subsequent functions. The first time it's passed to one of these functions, it runs fine; but in the next function, the recordset is at EOF. I assumed that my recordset was being passed By Ref, so tried using MoveFirst: this gives me an error because the cursor type is defaulted as forward only.

I tried the following:

• Set it the parameter as By Val; this had no effect.
• Created a copy of the recordset and used the original in the first function and the copy in the second; still the same problem (second is saying EOF, which implies that it's empty rather than at EOF; I can't imagine that the two recordsets are connected in some way, but perhaps).
• Changed my DB code to use a different cursor type (adOpenStatic): This would actually be more correct for my application, but it gave very weird results. Namely, in a query that worked fine before, after the change, the first field was not being returned! This is kind of important, so I couldn't continue down this route.

Any ideas?

---

Main DB query function: GetData. Takes SQL and list of varchar parameters (if any) and returns recordset. It references dbOpen, which simply opens the connection with the supplied credentials.

Private Function GetData(ODBC As DBConnection, ByVal QuerySQL As String, Optional Parameters As Collection) As ADODB.Recordset
    Dim DB As ADODB.Connection
    Dim Query As ADODB.Command
    Dim Parameter As ADODB.Parameter

    Set DB = New ADODB.Connection

    If dbOpen(DB, ODBC) Then
        Set Query = New ADODB.Command
        Query.ActiveConnection = DB
        Query.CommandText = QuerySQL

        If Not Parameters Is Nothing Then
            For Each param In Parameters
                Set Parameter = Query.CreateParameter(, adVarChar, adParamInput, Len(param), param)
                Query.Parameters.Append Parameter
            Next
            Set Parameter = Nothing
        End If

        Set GetData = Query.Execute

        ' Uncommenting this seems to reset the data in GetData...
        ' A bit of a memory leak, but we assume VBA will take care of it :P
        'dbClose DB
    Else
        MsgBox "Cannot connect to the database.", vbExclamation
        Set GetData = Nothing
    End If
End Function

I then do something like this:

Dim myRecords as ADODB.Recordset

' For the sake of argument, we assume this returns a nontrivial recordset
Set myRecords = GetData(someDSN, someSQL, someParameters)

Debug.Print myRecords.EOF ' Returns False

ID = writeHeader(myRecords)

Debug.Print myRecords.EOF ' Returns True

writeData ID, myRecords   ' Breaks because at EOF

The writeHeader function will go through each record in the recordset, using MoveNext. However, if I pass myRecords by value or as a copy, then we still get the EOF problem. If I add MoveFirst at the end of the record iteration code in writeHeader, it will complain that I can't move to the beginning of such a recordset. If I change my GetData function so that Set GetData = Query.Execute is changed to:

Set GetData = New ADODB.Recordset

GetData.CursorType = adOpenStatic
GetData.Open Query

Then, when a query in my code is run -- which hasn't changed -- the first field is not a part of the recordset (!?) Say, for example, myRecords!ID is the first field: if this is referenced, I'll get a warning that said item isn't a member of the records (and it's not an enumerated member, either; so myRecords.Fields(1) just returns the next field in the query).

Answer 1

With a useful tip from @TimWilliams, I solved this by changing my GetData function to the following:

Private Function GetData(ODBC As DBConnection, ByVal QuerySQL As String, Optional Parameters As Collection) As ADODB.Recordset
    Dim DB As ADODB.Connection
    Dim Query As ADODB.Command
    Dim Parameter As ADODB.Parameter
    Dim Output As ADODB.Recordset

    Set DB = New ADODB.Connection

    If dbOpen(DB, ODBC) Then
        Set Query = New ADODB.Command
        Query.ActiveConnection = DB
        Query.CommandText = QuerySQL

        If Not Parameters Is Nothing Then
            For Each param In Parameters
                Set Parameter = Query.CreateParameter(, adVarChar, adParamInput, Len(param), param)
                Query.Parameters.Append Parameter
            Next
            Set Parameter = Nothing
        End If

        Set Output = New ADODB.Recordset
        Output.CursorType = adOpenStatic
        Output.CursorLocation = adUseClient
        Output.Open Query
    Else
        MsgBox "Cannot connect to the database.", vbExclamation
        Set Output = Nothing
    End If

    Set GetData = Output
End Function

Then, before cursoring through the recordset, I use MoveFirst.