How to return a recordset from a function

Question

I am creating a data access layer in Excel VBA and cannot return a recordset. The Execute () function in my class definitely retrieves a row from the database, but does not seem to return anything.

The following function is contained in the DataAccessLayer class. The class contains Connect and Disconnect functions that handle opening and closing a 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 use in cell A1 of my table 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

The problem was setting 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

Answer 2

Set Execute = recordset

, .
.

, ( ). rs rsIn rsWhateverYouWant...

Answer 3

, . Caller 'Scott_Test' . .

Execute ., , , recordset.ActiveConnection = Nothing