How to return a recordset from a function

Question

I'm building a data access layer in Excel VBA and having trouble returning a recordset. The Execute() function in my class is definitely retrieving a row from the database, but doesn't seem to be returning anything.

The following function is contained in a class called DataAccessLayer. The class contains functions Connect and Disconnect which handle opening and closing the connection.

Public Function Execute(ByVal sqlQuery As String) As ADODB.recordset
    Dim rs As ADODB.recordset
    Set rs = New ADODB.recordset
    Dim recordsAffected As Long

    ' Make sure we're connected to the database.
    If Connect Then
        Set command = New ADODB.command

        With command
            .ActiveConnection = connection
            .CommandText = sqlQuery
            .CommandType = adCmdText
        End With

        'Set rs = command.Execute(recordsAffected)
        'Set Execute = command.Execute(recordsAffected)
        rs.Open command.Execute(recordsAffected)
        rs.ActiveConnection = Nothing
        Set Execute = rs
        Set command = Nothing
        Call Disconnect
    End If
End Function

Here's a public function that I'm using in cell A1 of my spreadsheet for testing.

Public Function Scott_Test()
    Dim Database As New DataAccessLayer
    'Dim rs As ADODB.recordset
    'Set rs = CreateObject("ADODB.Recordset")
    Set rs = New ADODB.recordset

    Set rs = Database.Execute("SELECT item_desc_1 FROM imitmidx_sql WHERE item_no = '11001'")
    'rs.Open Database.Execute("SELECT item_desc_1 FROM imitmidx_sql WHERE item_no = '11001'")
    'rs.Open

    ' This never displays.
    MsgBox rs.EOF

    If Not rs.EOF Then
        ' This is displaying #VALUE! in cell A1.
        Scott_Test = rs!item_desc_1
        rs.Close
    End If

    rs.ActiveConnection = Nothing
    Set rs = Nothing
End Function

What am I doing wrong?

Answer 1

Set Execute = recordset

creates a pointer to recordset, which you close on exiting the function.
Thats's why it can't contain anything.

I am also relectant on your variable names which are identical to possibe reserved words (recordset). I generally use rs or rsIn or rsWhateverYouWant...

Answer 2

As mentioned by Patrick, the recordset is a pointer. The Caller 'Scott_Test' should call recordset.Close instead.

The Execute method CANNOT call recordset.Close, however I believe it is OK to leave the recordset.ActiveConnection = Nothing

Answer 3

The problem was with setting the ActiveConnection = Nothing. The following code works:

Public Function Execute(ByVal sqlQuery As String) As ADODB.recordset
    Dim rs As ADODB.recordset
    Set rs = New ADODB.recordset
    Dim recordsAffected As Long

    ' Make sure we are connected to the database.
    If Connect Then
        Set command = New ADODB.command

        With command
            .ActiveConnection = connection
            .CommandText = sqlQuery
            .CommandType = adCmdText
        End With

        rs.Open command.Execute(recordsAffected)

        Set Execute = rs
        Set command = Nothing
        Call Disconnect
    End If
End Function