在 Excel-VBA 中访问 SQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1120674/
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
Accessing SQL Database in Excel-VBA
提问by Saobi
I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007):
我正在从 MSDN 复制一个 VBA 代码片段，它向我展示了如何将 SQL 查询的结果抓取到 Excel 表（Excel 2007）中：
Sub GetDataFromADO()
'Declare variables'
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
'Open Connection'
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"
objMyConn.Open
'Set and Excecute SQL Command'
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = "select * from myTable"
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
'Open Recordset'
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd
'Copy Data to Excel'
ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
End Sub
I have already added Microsoft ActiveX Data Objects 2.1 Library under as a reference. And this database is accessible.
我已经在下面添加了 Microsoft ActiveX Data Objects 2.1 Library 作为参考。而且这个数据库是可以访问的。
Now, when I run this subroutine, it has an error:
现在，当我运行这个子程序时，它有一个错误：
Run-time error 3704: Operation is not allowed when object is closed.
运行时错误 3704：对象关闭时不允许操作。
On the statement:
在声明中：
ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
Any idea why?
知道为什么吗？
Thanks.
谢谢。
回答by David Walker
I've added the Initial Catalog to your connection string. I've also abandonded the ADODB.Command syntax in favor of simply creating my own SQL statement and open the recordset on that variable.
我已将初始目录添加到您的连接字符串中。我还放弃了 ADODB.Command 语法，转而简单地创建我自己的 SQL 语句并打开该变量的记录集。
Hope this helps.
希望这可以帮助。
Sub GetDataFromADO()
'Declare variables'
Set objMyConn = New ADODB.Connection
Set objMyRecordset = New ADODB.Recordset
Dim strSQL As String
'Open Connection'
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
objMyConn.Open
'Set and Excecute SQL Command'
strSQL = "select * from myTable"
'Open Recordset'
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open strSQL
'Copy Data to Excel'
ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
End Sub
回答by onedaywhen
Suggested changes:
建议更改：
- Do not invoke the Command object's Execute method;
- Set the Recordset object's Source property to be your Command object;
- Invoke the Recordset object's Open method with no parameters;
- Remove the parentheses from around the Recordset object in the call to
CopyFromRecordset; - Actually declare your variables :)
- 不要调用 Command 对象的 Execute 方法；
- 将 Recordset 对象的 Source 属性设置为您的 Command 对象；
- 不带参数调用 Recordset 对象的 Open 方法；
- 在调用中删除 Recordset 对象周围的括号
CopyFromRecordset； - 实际上声明你的变量:)
Revised code:
修改后的代码：
Sub GetDataFromADO()
'Declare variables'
Dim objMyConn As ADODB.Connection
Dim objMyCmd As ADODB.Command
Dim objMyRecordset As ADODB.Recordset
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
'Open Connection'
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"
objMyConn.Open
'Set and Excecute SQL Command'
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = "select * from mytable"
objMyCmd.CommandType = adCmdText
'Open Recordset'
Set objMyRecordset.Source = objMyCmd
objMyRecordset.Open
'Copy Data to Excel'
ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset
End Sub
回答by Gary McGill
I'm sitting at a computer with none of the relevant bits of software, but from memory that code looks wrong. You're executing the command but discarding the RecordSetthat objMyCommand.Executereturns.
我坐在一台没有任何相关软件位的计算机前，但从记忆中该代码看起来是错误的。你执行命令，而是丢弃RecordSet该objMyCommand.Execute回报。
I'd do:
我会做：
Set objMyRecordset = objMyCommand.Execute
...and then lose the "open recordset" part.
...然后丢失“打开记录集”部分。
回答by shahkalpesh
Is that a proper connection string?
Where is the SQL Server instance located?
这是一个正确的连接字符串吗？
SQL Server 实例位于何处？
You will need to verify that you are able to conenct to SQL Server using the connection string, you specified above.
您将需要验证您是否能够使用上面指定的连接字符串连接到 SQL Server。
EDIT: Look at the State property of the recordset to see if it is Open?
Also, change the CursorLocation property to adUseClient before opening the recordset.
编辑：查看记录集的 State 属性以查看它是否为 Open？
此外，在打开记录集之前将 CursorLocation 属性更改为 adUseClient。
回答by eric.christensen
Add set nocount onto the beginning of the stored proc (if you're on SQL Server). I just solved this problem in my own work and it was caused by intermediate results, such as "1203 Rows Affected", being loaded into the RecordsetI was trying to use.
添加set nocount on到存储过程的开头（如果您使用的是 SQL Server）。我刚刚在自己的工作中解决了这个问题，它是由中间结果引起的，例如"1203 Rows Affected"被加载到Recordset我试图使用的。
回答by Jock
@firedrawndagger: to list field names/column headers iterate through the recordset Fields collection and insert the name:
@firedrawndagger：要列出字段名称/列标题，请遍历记录集 Fields 集合并插入名称：
Dim myRS as ADODB.Recordset
Dim fld as Field
Dim strFieldName as String
For Each fld in myRS.Fields
Activesheet.Selection = fld.Name
[Some code that moves to next column]
Next
