vba 重新加载工作簿时，如何修复“未找到数据库”的 ODBC 连接错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10542454/
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
How can I fix my "database not found" ODBC connection error when re-loading my workbook?
提问by Ben Strombeck
Using primarily the macro recorder, I created a VBA macro that sets up a Vendor list connection between Excel and a QuickBooks file.
我主要使用宏记录器，创建了一个 VBA 宏，用于在 Excel 和 QuickBooks 文件之间建立供应商列表连接。
Sub RefreshVendorList()
'
' RefreshVendorList Macro
'DatabaseName=3ae39a3bfa964f61a6f974654c1ddbe9;
Columns("C:E").Select
Selection.Delete Shift:=xlToLeft
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;Driver={QB SQL Anywhere};UID=Purchasing;;ServerName=QB_data_engine_21;AutoStop=NO;" _
), Array("Integrated=NO;Debug=NO;DisableMultiRowFetch=NO")), Destination:= _
Range("$C")).QueryTable
.CommandText = Array( _
"SELECT v_lst_vendor.name AS 'Vendor Name', v_lst_vendor_type.name AS 'Type', v_lst_vendor.is_hidden" & Chr(13) & "" & Chr(10) & "FROM QBReportAdminGroup.v_lst_vendor v_lst_vendor, QBReportAdminGroup.v_lst_vendor_type v_lst_vendo" _
, _
"r_type" & Chr(13) & "" & Chr(10) & "WHERE v_lst_vendor_type.id = v_lst_vendor.vendor_type_id AND ((v_lst_vendor.is_hidden=0) AND (v_lst_vendor_type.name='MBO'))" & Chr(13) & "" & Chr(10) & "ORDER BY v_lst_vendor.name, v_lst_vendor_type.name" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_PA_Vendor_List"
.Refresh BackgroundQuery:=False
End With
'delete the "ishidden" column
Columns("E:E").Delete
End Sub
I attached the code to a button so the user can update the vendor list. With the QuickBooks file open, this snippet works most of the time. This is what I would like to change, and there are two way I think it could be accomplished:
我将代码附加到按钮上，以便用户可以更新供应商列表。打开 QuickBooks 文件后，此代码段在大多数情况下都有效。这是我想要改变的，我认为有两种方法可以实现：
Option 1. Every time the button is pressed (and the ODBC connection is re-created), it asks the user for the password. I would like to pass the password instead of asking the user. Changing .SavePassword to True doesn't do it.
选项 1. 每次按下按钮（并重新创建 ODBC 连接）时，它都会要求用户输入密码。我想传递密码而不是询问用户。将 .SavePassword 更改为 True 不会这样做。
Option 2. I think there is a better way to set up the ODBC connection so it doesn't have to be "re-created" by a macro just to refresh it. As long as QuickBooks and this Excel file stay open, I am able to use the built-in refresh button in Excel (under Data -> Refresh All). However, if I close the Excel file and the QuickBooks file, re-open them and try pressing refresh, I get the following error, which is the reason I created a macro to re-create the ODBC connection:
选项 2。我认为有一种更好的方法来设置 ODBC 连接，因此不必为了刷新它而通过宏“重新创建”它。只要 QuickBooks 和此 Excel 文件保持打开状态，我就可以使用 Excel 中的内置刷新按钮（在“数据”->“全部刷新”下）。但是，如果我关闭 Excel 文件和 QuickBooks 文件，重新打开它们并尝试按刷新，则会出现以下错误，这就是我创建宏以重新创建 ODBC 连接的原因：
采纳答案by Paul Keister
You are using the custom reporting ODBC driver. This is provided by QuickBooks for reporting purposes only. You can only used this while the company file is open, and you must enter a password for the reporting user whenever you connect. That's the way Intuit designed it to work.
您正在使用自定义报告 ODBC 驱动程序。这由 QuickBooks 提供，仅用于报告目的。您只能在公司文件打开时使用此功能，并且每次连接时都必须为报告用户输入密码。这就是 Intuit 设计它的工作方式。
It would be possible to write VBA code to create and hold open the ODBC connection so that you didn't have to go through the log in process more than once per session. However, this is not just a snippet of code to copy; you'd have to have a global variable for the connection and manage it properly.
可以编写 VBA 代码来创建并保持打开 ODBC 连接，这样您就不必在每个会话中多次执行登录过程。然而，这不仅仅是要复制的代码片段；你必须有一个用于连接的全局变量并正确管理它。
A simpler solution would be to use the QODBC driverinstead of the custom report driver. I believe you get a copy of this bundled with QuickBooks Enterprise. I'm assuming you have the Enterprise edition, since you have custom reporting. QODBC takes some setup, but once it's set you should be able to avoid the login prompt and get a consistent connection to the database.
一个更简单的解决方案是使用QODBC 驱动程序而不是自定义报告驱动程序。我相信你会得到一份与 QuickBooks Enterprise 捆绑在一起的副本。我假设你有企业版，因为你有自定义报告。QODBC 需要一些设置，但是一旦设置，您应该能够避免登录提示并获得与数据库的一致连接。
