我不认为更新连接字符串是导致您问题的原因。在更新ODBC连接的CommandText属性时存在一个错误,会导致创建额外的连接。如果您暂时切换到OLEDB连接,更新CommandText属性,然后再切换回ODBC,它就不会创建新的连接。不要问我为什么……这对我有效。
我创建了一个模块,允许您更新CommandText和/或连接字符串。将此代码插入到新模块中:
Option Explicit
Sub UpdateWorkbookConnection(WorkbookConnectionObject As WorkbookConnection, Optional ByVal CommandText As String = "", Optional ByVal ConnectionString As String = "")
With WorkbookConnectionObject
If .Type = xlConnectionTypeODBC Then
If CommandText = "" Then CommandText = .ODBCConnection.CommandText
If ConnectionString = "" Then ConnectionString = .ODBCConnection.Connection
.ODBCConnection.Connection = Replace(.ODBCConnection.Connection, "ODBC;", "OLEDB;", 1, 1, vbTextCompare)
ElseIf .Type = xlConnectionTypeOLEDB Then
If CommandText = "" Then CommandText = .OLEDBConnection.CommandText
If ConnectionString = "" Then ConnectionString = .OLEDBConnection.Connection
Else
MsgBox "Invalid connection object sent to UpdateWorkbookConnection function!", vbCritical, "Update Error"
Exit Sub
End If
If StrComp(.OLEDBConnection.CommandText, CommandText, vbTextCompare) <> 0 Then
.OLEDBConnection.CommandText = CommandText
End If
If StrComp(.OLEDBConnection.Connection, ConnectionString, vbTextCompare) <> 0 Then
.OLEDBConnection.Connection = ConnectionString
End If
.Refresh
End With
End Sub
这个UpdateWorkbookConnection
子程序只适用于更新OLEDB或ODBC连接。该连接不一定需要链接到数据透视表。它还可以解决另一个问题,并允许您更新即使基于同一连接的多个数据透视表。
要启动更新,只需像这样使用连接对象和命令文本参数调用该函数:
UpdateWorkbookConnection ActiveWorkbook.Connections("Connection"), "exec sp_MyAwesomeProcedure", "ODBC