How to return a recordset from a function
Solution 1
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
Solution 2
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...
Eric Sexton
I am an innovative and resourceful business professional with over a decade of proven experience in creating systems and procedures to help businesses grow. My belief in life-long learning has led me to become an expert in a wide variety of subject areas, equipping me with the tools to help businesses to improve cashflow, increase profit margins, and maximize sustainability without reducing headcount or compromising on customer service. I work extremely well with colleagues, both locally and internationally. I am also a dynamic and engaging speaker, comfortable in front of an audience of any size. Top Five Strengths: Maximizer, Futuristic, Deliberative, Relator, Discipline Keirsey Temperament: Inspector Guardian (ISTJ)
Updated on March 31, 2020Comments
-
Eric Sexton about 4 years
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?