访问SQL数据库在Excel的VBA

问题描述：

我复制从MSDN的VBA代码片段，显示我如何抓住从一个SQL查询到Excel工作表的结果（Excel 2007中）：访问SQL数据库在Excel的VBA

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

我已经加入微软ActiveX Data Objects 2.1 Library作为参考。这个数据库是可以访问的。

现在，当我运行这个子程序，它有一个错误：

运行时错误3704：当对象被关闭的操作是不允许的。

的声明：

ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

任何想法，为什么？

谢谢。

帮助！我有同样的错误，但下面的解决方案没有帮助：http://stackoverflow.com/questions/1682717/vba-adodb-run-time-error-3704 – Steven 2009-11-05 23:17:56

答

我已将初始目录添加到连接字符串。我也放弃了ADODB.Command语法，只是简单地创建自己的SQL语句并打开该变量的记录集。

希望这会有所帮助。

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

它为我工作，谢谢，记得添加对Microsoft ActiveX Data Objects 2.1的引用，我添加了2.6版本。你也不需要在最后关闭连接吗？像objMyRecordset.Close？ – 2015-01-15 14:18:41

答

这是一个正确的连接字符串？
SQL Server实例位于何处？

您将需要验证您是否能够使用上面指定的连接字符串与SQL Server相关联。

编辑：看看记录集的状态属性，看看它是否打开？
另外，在打开记录集之前，将CursorLocation属性更改为adUseClient。

答

我坐在电脑上，没有任何相关的软件，但从内存中看，代码看起来不对。您正在执行该命令，但丢弃objMyCommand.Execute返回的RecordSet。

我会怎么做：

Set objMyRecordset = objMyCommand.Execute

...然后失去了 “开放式记录” 的一部分。

答

更改建议：

不要调用Command对象的Execute方法;
将Recordset对象的Source属性设置为您的Command对象;
调用不带参数的Recordset对象的Open方法;
在调用CopyFromRecordset;时删除Recordset对象周围的括号;
其实声明变量:)

修改后的代码：

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

+1用于删除括号。不必要的括号与对象引用相结合似乎会导致奇怪的VBA错误 – barrowc 2009-07-13 23:14:11

答

添加set nocount on到存储过程的开始（如果你的SQL服务器上）。我刚刚在自己的工作中解决了这个问题，它是由中间结果引起的，如"1203 Rows Affected"，正在加载到我试图使用的Recordset中。

答

@firedrawndagger：列出字段名称/列表头遍历记录字段集合并插入名称：

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

访问SQL数据库在Excel的VBA

相关推荐