How to return a recordset from a function

ado, excel, recordset, vba

Solution

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

Problem

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?

Original source