vba 如何从函数返回记录集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2463212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
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.
我正在 Excel VBA 中构建数据访问层，但在返回记录集时遇到问题。我的类中的 Execute() 函数肯定是从数据库中检索一行，但似乎没有返回任何内容。
The following function is contained in a class called DataAccessLayer. The class contains functions Connect and Disconnect which handle opening and closing the connection.
以下函数包含在名为 DataAccessLayer 的类中。该类包含处理打开和关闭连接的 Connect 和 Disconnect 函数。
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.
这是我在电子表格的单元格 A1 中用于测试的公共函数。
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?
我究竟做错了什么？
回答by Scott
The problem was with setting the ActiveConnection = Nothing. The following code works:
问题在于设置 ActiveConnection = Nothing。以下代码有效：
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回答by Patrick Honorez
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...
我也对与可能的保留字（记录集）相同的变量名感到厌恶。我通常使用 rs 或 rsIn 或 rsWhateverYouWant...
回答by user52212
As mentioned by Patrick, the recordset is a pointer. The Caller 'Scott_Test' should call recordset.Close instead.
正如帕特里克所说，记录集是一个指针。调用方“Scott_Test”应该改为调用recordset.Close。
The Execute method CANNOT call recordset.Close, however I believe it is OK to leave the recordset.ActiveConnection = Nothing
Execute 方法不能调用 recordset.Close，但是我相信离开 recordset.ActiveConnection = Nothing 是可以的
