如何在 Excel 中的 VBA 中使 ADODB.Connection 持久化？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6642548/
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 do I make an ADODB.Connection Persistent in VBA in Excel?
提问by Matt Victory
I have an Excel spreadsheet that needs to display data from our SQL database.
我有一个 Excel 电子表格，需要显示来自我们 SQL 数据库的数据。
I am storing the results of a slow query in a temporary table and want to be able to access those results repeatedly without having to rerun the slow query.
我将慢查询的结果存储在临时表中，并希望能够重复访问这些结果而不必重新运行慢查询。
I am using an ADODB.Connection in VBA to connect to our SQL database and retrieve information. I want to open a connection once and use that session across various macros for as long as the user is using the spreadsheet. I can't find any way to make the connection persistent, so it always closes once I exit the macro that opened it and I lose any temporary tables that I created.
我在 VBA 中使用 ADODB.Connection 连接到我们的 SQL 数据库并检索信息。只要用户使用电子表格，我想打开一次连接并在各种宏中使用该会话。我找不到使连接持久的任何方法，因此一旦我退出打开它的宏，它就会始终关闭，并且丢失了我创建的所有临时表。
采纳答案by Darren Lewis
Your connection is going out of scope when the macro completes and therefore closing. You don't want to be holding connections open indefinitely against the SQL Server as this can seriously affect performance of the database for other users/services.
当宏完成并因此关闭时，您的连接将超出范围。您不希望无限期地保持对 SQL Server 的连接打开，因为这会严重影响其他用户/服务的数据库性能。
How big is the data you're storing in the temp table? If it's not too big you could hold it locally. To prevent this data from also going out of scope when the macro completes you'll need to store it in a module level variable rather than within the macro. Yes you couldstore your connection here too but I'd stronglyrecommend you don't.
您存储在临时表中的数据有多大？如果它不是太大，你可以在本地保存它。为了防止这些数据在宏完成时也超出范围，您需要将其存储在模块级变量中，而不是在宏中。是的，您也可以在这里存储您的连接，但我强烈建议您不要。
回答by Kevin Pope
Daz Lewis is entirely correct that you shouldn't be keeping database connections open indefinitely. However, if this is a local database and you're the only one using it, then you should be able to keep it open as long as you please.
Daz Lewis 是完全正确的，您不应该无限期地保持数据库连接打开。但是，如果这是一个本地数据库并且您是唯一使用它的数据库，那么您应该可以随时保持打开状态。
Add this as the top line of the module holding your code:
将此添加为保存代码的模块的第一行：
Global dbConnPublic As ADODB.Connection
In the "ThisWorkbook" object:
在“ThisWorkbook”对象中：
Private Sub Workbook_Open()
Set dbConnPublic = openDBConn() 'Or whatever your DB connection function is called'
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
dbConnPublic.Close
End Sub
This will open the db connection on opening the workbook and will close it on closing the workbook. Keep in mind you may want to check to make sure the connection is still open whenever you need to use it as it could get closed by the server if it's open for too long.
这将在打开工作簿时打开数据库连接，并在关闭工作簿时关闭它。请记住，您可能需要检查以确保连接在您需要使用时仍处于打开状态，因为如果连接时间过长，它可能会被服务器关闭。
