How to return a recordset from a function
- by Scott
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
Set recordset = 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
' These seem to be equivalent.
'Set recordset = command.Execute(recordsAffected)
recordset.Open command.Execute(recordsAffected)
Set Execute = recordset
recordset.ActiveConnection = Nothing
recordset.Close
Set command = Nothing
Call Disconnect
End If
Set recordset = Nothing
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
End If
rs.ActiveConnection = Nothing
Set rs = Nothing
End Function
What am I doing wrong?
