具有外部数据源动态查询功能的数据透视表

Question

我的问题不是专门寻找解决方案的想法，而是更多地检查我自己的解决方案，因为自从我在Excel VBA中使用adodb以来已经有一段时间(至少两次主要修订)了。

所以情况是这样的。我正在创建一个Excel报告的用户谁想要访问大块的数据库视图，太大了，不适合内存。用户需要能够访问所有视图，因此硬编码过滤器是不可能的。除非我缺少一个更优雅的解决方案，否则他们需要的是能够以生成的SQL语句的形式动态控制连接上的过滤器，该SQL语句将数据馈送到数据透视缓存中，然后告诉数据透视表刷新。

我创建了一个"刷新"按钮，它会弹出一个包含用户通常需要的所有过滤器的表单。用户输入筛选值，我解析并验证它们，然后根据这些值形成合适的SQL语句。当我这样做时，我遇到了一个问题:

'this has to be dynamic becuase I'm distributing to multiple users, using different testing/production databases on different workstations.
mypivottable.PivotCache.Connection = "ODBC;" & driver & myserver & myuser & trusted & app & workstationid & databse
'this also has to be dynamic becuase of the sql filters
mypivottable.PivotCache.CommandText = sql

所以我按下按钮，它工作!设置完这些属性后，数据透视表会自动刷新，因为它知道缓存后面有新的数据，并在新的条件下获取查询结果。

问题是，当我查看外部数据源清单时，每次我更改这些属性时，它都会创建一个新的连接，并且不会在自己之后进行清理。使用100次后，有100个连接，其中99个未使用。如果我删除所有的连接，那么它将不可逆转地破坏数据透视表。

有更好的方法吗?是否有一种程序化的方式来清理未使用的连接?

Answer 1

如果只需要更新SQL语句，则不必重置连接。我实际上有一个类似的问题，这就是我如何解决它:

Sub test()
' add logic...
    ' then change sql code
    With ThisWorkbook.Connections("your connection name").OLEDBConnection
            .CommandText = Array("select * from mytable where id = 1") ' adjust accordingly
            .Refresh
    End With
End Sub

您真正需要做的就是获取当前连接，然后更新CommandText属性。顺便说一下，我不确定为什么我需要使用Array()，但没有它我有问题。

我实际上写了一篇关于使用SQL创建数据透视表的博文(链接在这里)。也许我应该再创建一个关于创建动态内容的:)