在 Excel-VBA 中访问 SQL 数据库 [英] Accessing SQL Database in Excel-VBA

问题描述

我正在从 MSDN 复制 VBA 代码片段，该代码片段向我展示了如何将 SQL 查询的结果抓取到 Excel 表 (Excel 2007) 中:

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):

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

我已经在下面添加了 Microsoft ActiveX Data Objects 2.1 Library 作为参考.而且这个数据库是可以访问的.

I have already added Microsoft ActiveX Data Objects 2.1 Library under as a reference. And this database is accessible.

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

Now, when I run this subroutine, it has an error:

运行时错误 3704:对象关闭时不允许操作.

Run-time error 3704: Operation is not allowed when object is closed.

关于声明:

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

知道为什么吗?

谢谢.

推荐答案

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

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.

希望这会有所帮助.

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

这篇关于在 Excel-VBA 中访问 SQL 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！