执行从Excel到SQL Server的ADO连接，但不返回任何记录

Question

我有一个电子表格，我想从中向SQL server数据库传递SQL脚本，以检索记录并运行存储过程。

这是我的代码：

Sub ApendPickListData()
Dim SqlConn As New ADODB.Connection
Dim listID As Integer
Dim lists As New ADODB.Recordset
Dim SQLstr As String
SqlConn.ConnectionString = "Provider = 'SQLOLEDB';Server=MyServerSQLEXPRESS;Database=MyDatabase;Uid=Username;PWD=Password;"
SqlConn.Open

'The following execution of a stored procedure works
SqlConn.Execute "Exec spListsInsertNew @Type = 'Picking', @Date ='" & Date & "'"

SQLstr = "SELECT ItemList.ItemNumber from ItemList"
'This method doesn't work

With lists
.ActiveConnection = SqlConn
.Source = SQLstr
.Open
Debug.Print .RecordCount
'prints -1 in the immediate window - no records
End With
'Neither does this method
Set lists = SqlConn.Execute(SQLstr)
Debug.Print lists.RecordCount
'prints -1 in the immediate window - no records    
SqlConn.Close
End Sub

我觉得我错过了一些显而易见的东西。我搜索了这个网站和其他网站，找到了这个代码应该工作的例子。我已经在SSMS中测试了select语句，它按预期工作。

任何帮助都将不胜感激！

Answer 1

有效的代码来自Mark Balhoff的评论。这是：

Sub ApendPickListData()
Dim SqlConn As New ADODB.Connection
Dim listID As Integer
Dim lists As New ADODB.Recordset
Dim SQLstr As String
SqlConn.ConnectionString = "Provider = 'SQLOLEDB';Server=MyserverSQLEXPRESS;Database=MyDB;Uid=Username;PWD=Password;"
SqlConn.Open

SQLstr = "select dbo.ItemList.ItemNumber from dbo.ItemList"
With lists
.ActiveConnection = SqlConn
.Source = SQLstr
.CursorLocation = adUseClient 'This was the key!
.Open
Debug.Print .RecordCount
End With
SqlConn.close
End Sub