ADODB.Connection 未定义

发布于 2024-08-11 05:56:32 字数 1052 浏览 4 评论 0原文

在我完成上述工作后，我从例程中复制了所有全局变量/常量，其中包括

    Const CS As String = "Driver={SQL Server};" _
                       & "Server=****;" _
                       & "Database=****;" _
                       & "UID=****;" _
                       & "PWD=****"
    Dim DB_Conn As ADODB.Connection
    Dim Command As ADODB.Command
    Dim DB_Status As String

into a similar module in another spreadsheet. I also copied into the same module

Sub Connect_To_Lockbox()
    If DB_Status <> "Open" Then
        Set DB_Conn = New Connection
        DB_Conn.ConnectionString = CS
        DB_Conn.Open  ' problem!
        DB_Status = "Open"
    End If
End Sub

I added the same reference (ADO 2.8)

第一个电子表格仍然有效； DB_Conn.Open 的第二个弹出“运行时错误 '-214767259 (80004005)': [Microsoft][ODBC Driver Manager] 未找到数据源名称且未指定默认驱动程序” 删除两者上的引用、保存文件、重新打开、重新添加引用都没有帮助。一个仍然有效，而另一个出现错误。

？！？

原文

Reference Excel VBA to SQL Server without SSIS

After I got the above working, I copied all the global variables/constants from the routine, which included

    Const CS As String = "Driver={SQL Server};" _
                       & "Server=****;" _
                       & "Database=****;" _
                       & "UID=****;" _
                       & "PWD=****"
    Dim DB_Conn As ADODB.Connection
    Dim Command As ADODB.Command
    Dim DB_Status As String

into a similar module in another spreadsheet. I also copied into the same module

Sub Connect_To_Lockbox()
    If DB_Status <> "Open" Then
        Set DB_Conn = New Connection
        DB_Conn.ConnectionString = CS
        DB_Conn.Open  ' problem!
        DB_Status = "Open"
    End If
End Sub

I added the same reference (ADO 2.8)

The first spreadsheet still works; the seccond at DB_Conn.Open pops up "Run-time error '-214767259 (80004005)': [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
Removing the references on both, saving files, re-opening, re-adding the references doesn't help. The one still works and the other gets the error.

?!?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

薯片软お妹 2024-08-18 05:56:32

我观察到相同的错误消息，就我而言，没有任何变化。我想知道我的 odbc 驱动程序是否需要重新安装（根据我在网上阅读的内容）。无论如何，重新启动 excel 就成功了。有时解决方案要简单得多。 :-)

回复收藏 0 原文

谈场末日恋爱 2024-08-18 05:56:32

当错误弹出时，检查“本地”窗口以查看 CS 保存的内容。查看>当地人之窗

回复收藏 0 原文

等待我真够勒 2024-08-18 05:56:32

问题：编译器找不到您的常量。

解决方案：由于常量位于单独的模块中，您需要将其设置为 Public，以便其他代码能够看到它。

证明：
为了证明这个理论，您可以执行以下操作：
打开一个新的 Excel 电子表格
转到 VBA 设计器并添加新模块
在此模块中放置：

Const TestString As String = "Test String"

然后将以下代码添加到 ThisWorkbook：

Public Sub TestString()
   MsgBox (TestString)
End Sub

添加此代码后返回到工作簿并添加一个按钮，选择“TestString”作为单击时要运行的宏。

单击该按钮，将出现一个空白消息框。
返回VBA设计器并将Module1中的const更改为Public
单击电子表格上的按钮，您现在应该在消息框中看到“测试字符串”。

Problem: Your constant isn't found by the compiler.

Solution: With the constant being located in a separate module, you'll need to set it as Public for the other code to see it.

Proof:
In order to prove this theory you can do the following:
Open a new Excel spreadsheet
Go to the VBA designer and add a new module
In this module put:

Const TestString As String = "Test String"

Then add the following code to ThisWorkbook:

Public Sub TestString()
   MsgBox (TestString)
End Sub

After adding this return to the workbook and add a button, selecting "TestString" as the macro to run when clicked.

Click the button and a blank message box will appear.
Go back to the VBA designer and change the const in Module1 to Public
Click the button on the spreadsheet and you should now see "Test String" in the message box.

回复收藏 0 原文