如何使Excel中的VBA中的ADODB.Connection持久化？ [英] How do I make an ADODB.Connection Persistent in VBA in Excel?

问题描述

我有一个Excel电子表格，需要显示我们的SQL数据库中的数据。

我将缓慢查询的结果存储在临时表中，并希望成为能够重复访问这些结果，而无需重新运行缓慢的查询。

我在VBA中使用ADODB.Connection连接到我们的SQL数据库并检索信息。我想打开一次连接，只要用户使用电子表格，就可以跨各种宏使用该会话。我找不到任何方式使连接持久，所以一旦我退出打开它的宏，我总是关闭，我丢失了我创建的任何临时表。

解决方案

当宏完成并因此关闭时，您的连接超出范围。您不希望无限期地连接到SQL Server，因为这可能会严重影响其他用户/服务的数据库的性能。

数据有多大你正在存储在临时表中？如果不是太大，你可以在本地持有。为了防止在宏完成时这些数据也超出范围，您需要将其存储在一个模块级变量中，而不是在宏中。是的，您可以将您的连接保存在此处，但我强烈地 建议您不要。

I have an Excel spreadsheet that needs to display data from our SQL database.

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.

解决方案

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.

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 could store your connection here too but I'd strongly recommend you don't.

这篇关于如何使Excel中的VBA中的ADODB.Connection持久化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！