更改与 vba 的 odbc 连接的 commandText 时遇到问题

trouble changing commandText of odbc connection with vba

提问by CompanionCube

I have spent two days looking for a solution, this is driving me nuts...

我花了两天时间寻找解决方案，这让我发疯......

First let me explain what I'm doing. We're bringing in half a million records, with these records driving a dozen pivot tables. To keep the workbook manageable file size, I built the pivot table directly from the external data connection. It's an odbc connection that I configured manually.

首先让我解释一下我在做什么。我们带来了 50 万条记录，这些记录驱动了十几个数据透视表。为了保持工作簿文件大小可管理，我直接从外部数据连接构建了数据透视表。这是我手动配置的 odbc 连接。

It all works fine, I can hit "refresh all" in the workbook and all the pivot tables update automatically, wonderful.

一切正常，我可以在工作簿中点击“全部刷新”，所有数据透视表都会自动更新，太棒了。

But now I need to be able to restrict the entire recordset with manual start and end date... changing the date filters on the pivot tables is not ideal, because it is not just a single field affected by the end date, there are fields that need to be calculated prior to the pivoting, which have values that depend on formulas involving the end date.

但是现在我需要能够使用手动开始和结束日期来限制整个记录集......更改数据透视表上的日期过滤器并不理想，因为它不仅仅是受结束日期影响的单个字段，还有字段需要在旋转之前计算，其值取决于涉及结束日期的公式。

After a whole afternoon of crashing Excel repeatedly, I figured out the restriction that if your connection is directly to a pivot table, you can't use ? and the parameter dialog to point to a cell reference, the cell references are lost once you close the book.

经过一整个下午的反复崩溃 Excel 之后，我发现了一个限制，即如果您的连接是直接连接到数据透视表，则不能使用 ? 和指向单元格引用的参数对话框，一旦关闭书籍，单元格引用就会丢失。

So my next approach was to do this:

所以我的下一个方法是这样做：

Dim ReportStartDate, ReportEndDate

' Get parameters from Intro sheet
ReportStartDate = "'" & ActiveWorkbook.Worksheets("Intro").Range("$B").Value & "'"
ReportEndDate = "'" & ActiveWorkbook.Worksheets("Intro").Range("$B").Value & "'"

' There are 3 directpivot odbc connections/caches that need to be modified.
' In each query, the default report-end-date is specified by CURDATE().
' The default report-start-date is specified as '2010-01-01'
' Replace these defaults with the values retrieved above.

Dim cn As WorkbookConnection
Dim odbcCn As ODBCConnection
Dim originalsqltext, newsqltext

For Each cn In ThisWorkbook.Connections     ' loop through the connections
    If cn.Type = xlConnectionTypeODBC Then
        Set odbcCn = cn.ODBCConnection
        originalsqltext = odbcCn.CommandText
        If odbcCn.Parent = "Calls" Then
            newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
            newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
        ElseIf odbcCn.Parent = "Suboutcomes" Then
            newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
            newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
        ElseIf odbcCn.Parent = "QtyCallsPerDay1" Then
            newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
        Else
            newsqltext = originalsqltext
        End If
        odbcCn.CommandText = newsqltext
        odbcCn.Refresh
        odbcCn.CommandText = originalsqltext
    End If
    cn.Refresh ' refresh the other connection without modification
Next
Set cn = Nothing
Set odbcCn = Nothing

But it's throwing me an error when it gets to the odbcCn.CommandText = newsqltext Run-time error '1004: Application-defined or object-defined error. which is so not helpful...

但是当它到达 odbcCn.CommandText = newsqltext Run-time error '1004: Application-defined or object-defined error 时，它向我抛出了一个错误。这太没有帮助了...

I verified that newsqltext contained what I intended, it just won't assign back into CommandText.

我验证了 newsqltext 包含我想要的内容，它只是不会分配回 CommandText。

After another day of googling, and some simple macro-recording experiments, it looks like changing the CommandText requires syntax like

经过一天的谷歌搜索和一些简单的宏记录实验，看起来改变 CommandText 需要像这样的语法

.CommandText = Array( _
"SELECT C1.CALLID, C1.TAKENON, C1A.TAKENAT, CAST(CONCAT(DATE_FORMAT(TAKENON,'%c/%e/%y'),' ',TIME_FORMAT(TAKENAT,'%H:" _
    , _
    "%i:%s')) AS CHAR) AS CallDateTime, YEAR(C1.TAKENON) AS Year, CEILING(MONTH(C1.TAKENON)/3) AS Quarter, MONTH(C1.TAKE" _
    , _

(I'm leaving off the rest because it's huge)... at first I thought that was my problem, since when I tried recording the macro initially, I was getting a "too many line continuations" error, so I shortened the query as much as possible, getting it down to 1428 chars before the substitution. After substitution, it ends up at 1448 chars... but how do I parse it into the array format the code wants? Or is there some better way to do this?

（我忽略了其余部分，因为它很大）......起初我认为这是我的问题，因为当我最初尝试录制宏时，我遇到了“太多的行延续”错误，所以我缩短了查询尽可能在替换之前将其减少到 1428 个字符。替换后，它以 1448 个字符结束......但是我如何将它解析为代码想要的数组格式？或者有没有更好的方法来做到这一点？

I really don't want to mangle my queries like this, just to be able to edit them with vba, and I feel like I'm just missing something for how to alter the CommandText...

我真的不想像这样修改我的查询，只是为了能够用 vba 编辑它们，而且我觉得我只是缺少一些关于如何更改 CommandText 的东西......

There were a couple of troubling things that my searching turned up, like this issue about not being able to change the CommandText on an odbc connection unless you changed it to an oledb first, then you could change the CommandText, then change the connection back to odbc... but that was prior to Excel 2010, which no longer uses these... http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html

我的搜索发现了一些令人不安的事情，比如这个问题是关于无法更改 odbc 连接上的 CommandText 除非您先将其更改为 oledb，然后您可以更改 CommandText，然后将连接更改回odbc...但那是在 Excel 2010 之前，不再使用这些... http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has- 1-rpt.html

The KnowledgeBase article linked to in there, http://support.microsoft.com/kb/816562, is even more alarming... I thought I was onto a solution when I saw the StringToArray function, but then I read further and saw

链接到那里的知识库文章http://support.microsoft.com/kb/816562更令人担忧......当我看到 StringToArray 函数时，我以为我找到了解决方案，但后来我进一步阅读并看到

Note The previous code may not work as you expect if you are using shared PivotCaches, an OLAP-based PivotTables, or a Multiple Consolidation Range-based PivotTables to connect to the database.

注意如果您使用共享数据透视缓存、基于 OLAP 的数据透视表或基于多合并范围的数据透视表连接到数据库，则前面的代码可能无法按预期工作。

and then

进而

If multiple PivotTables on a worksheet are derived from the same PivotTable, the subroutine does not work after it processes the first PivotTable. As of March 2003, there is no known workaround for this problem.

如果工作表上的多个数据透视表派生自同一个数据透视表，则子例程在处理第一个数据透视表后不起作用。截至 2003 年 3 月，此问题尚无已知的解决方法。

although it notes that the article only applies to Excel 2000 through 2003.

尽管它指出该文章仅适用于 Excel 2000 到 2003。

One other thing I tried, I thought maybe I could use ? parameters, and just set them with vba... but when I created a simple query with parameters, then recorded a macro while I pointed the parameters to new cell references, the macro contained only this: Sub PARAMEDIT5() ' ' PARAMEDIT5 Macro '

我尝试过的另一件事，我想也许我可以使用？参数，然后用 vba 设置它们...但是当我创建一个带参数的简单查询，然后在我将参数指向新的单元格引用时记录了一个宏，该宏只包含这个： Sub PARAMEDIT5() ' ' PARAMEDIT5 Macro '

'
    With ActiveWorkbook.Connections("PARAMEDIT").ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM Calls1 where TAKENON > ?" _
        )
        .CommandType = xlCmdSql
        .Connection = _
        "ODBC;DRIVER={MySQL ODBC 5.1 Driver};UID=xxxxxxx;PWD=xxxxxxxx;SERVER=xxxxxx;PORT=3306;BIG_PACKETS=1;"
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("PARAMEDIT")
        .Name = "PARAMEDIT"
        .Description = ""
    End With
    ActiveWorkbook.Connections("PARAMEDIT").Refresh
End Sub

I tried this with both the direct-into-pivot type of connection, and with a regular table connected to an external data source, which I know does support parameters.

我尝试使用直接转入枢轴类型的连接和连接到外部数据源的常规表，我知道它支持参数。

So... does anyone know what is the correct way to parameterize the query for a shared pivot cache odbc connection ?

那么...有谁知道参数化共享数据透视缓存 odbc 连接的查询的正确方法是什么？

UPDATE: I tried this:

更新：我试过这个：

Dim cn, originalCn, newCn As WorkbookConnection
Dim odbcCn As ODBCConnection
Dim originalsqltext, newsqltext
Dim connStr As String

For Each cn In ThisWorkbook.Connections     ' loop through the connections
    If cn.Type = xlConnectionTypeODBC Then
        Set odbcCn = cn.ODBCConnection
        originalsqltext = odbcCn.CommandText
        Set originalCn = cn
        connStr = odbcCn.Connection
        Select Case odbcCn.Parent
            Case "Calls", "Suboutcomes"
                newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
                newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
            Case "QtyCallsPerDay1"
                newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
            Case Else
                newsqltext = originalsqltext
        End Select
        Set newCn = ActiveWorkbook.Connections.Add(odbcCn.Parent & "New", "WhoCares", connStr, newsqltext)
        Set cn = newCn
        cn.Refresh
        Set cn = originalCn
        newCn.Delete
    Else
        cn.Refresh ' refresh any other connections without modification
    End If
Next

Set cn = Nothing
Set odbcCn = Nothing
Set newCn = Nothing
Set originalCn = Nothing

And while it seems to do what I want in terms of getting the commandtext to what I want, cn.Refresh does nothing when I step through. If it was refreshing, but my pivots weren't updating, I could see where maybe they're looking for Calls1 and at the moment the refresh happens, it's named to Calls1New, but the connection is just not doing anything (the query usually takes a few minutes to complete). Or maybe I can't assign it to the existing connection with the same name? After I set cn = newCn, they both look exactly the same, with same name.

虽然在将命令文本设置为我想要的内容方面似乎可以做我想做的事情，但当我逐步完成时 cn.Refresh 什么也不做。如果它令人耳目一新，但我的枢轴没有更新，我可以看到他们可能正在寻找 Calls1 的位置，并且在刷新发生的那一刻，它被命名为 Calls1New，但连接没有做任何事情（查询通常需要几分钟即可完成）。或者我无法将它分配给同名的现有连接？在我设置 cn = newCn 后，它们看起来完全一样，名称相同。

I'll poke around some more, but if anybody else has done something like this, I'd appreciate more help. Thanks much for what you've given so far!

我会再四处看看，但如果其他人做过这样的事情，我会很感激更多的帮助。非常感谢您到目前为止所做的一切！

EDIT: so I'm back to the original

编辑：所以我回到原来的

odbcCn.CommandText = newsqltext
cn.Refresh
odbcCn.CommandText = originalsqltext

I also tried odbcCn.CommandText = StringToArray(newsqltext) cn.Refresh odbcCn.CommandText = StringToArray(originalsqltext) that I found at http://support.microsoft.com/kb/816562. neither one worked.

我还尝试了我在http://support.microsoft.com/kb/816562找到的 odbcCn.CommandText = StringToArray(newsqltext) cn.Refresh odbcCn.CommandText = StringToArray(originalsqltext) 。没有一个工作。

I'll post the originalsqltext and newsql text, as they are right before the error. Note the the originalsqltext works fine if I manually paste it into the dialog box for the query, as does newsqltext

我将发布 originalsqltext 和 newsql 文本，因为它们就在错误之前。请注意，如果我手动将 originalsqltext 粘贴到查询对话框中，则它可以正常工作，newsqltext 也是如此

**removed earlier edit, due to new information **

**删除了之前的编辑，因为有新信息**

note - I found a thread Excel VBA: Update Pivot Sourcedatathat seems like a similar problem - because I have tested trying to assign odbcCn.CommandText = originalsqltext (which hasn't been altered in any way) and it fails also. However, this thread is from 2009, so it is most likely not using excel 2010, because I tried to write

注意 - 我发现了一个线程Excel VBA: Update Pivot Sourcedata这似乎是一个类似的问题 - 因为我已经测试过尝试分配 odbcCn.CommandText = originalsqltext （它没有以任何方式改变）并且它也失败了。然而，这个线程是从 2009 年开始的，所以很可能没有使用 excel 2010，因为我试图写

For Each pvtC In ThisWorkbook.PivotCaches
    name = pvtC.WorkbookConnection.name
    originalsqltext = pvtC.CommandText
    pvtC.CommandText = originalsqltext
Next

and it also fails right at pvtC.CommandText = originalsqltext

它也在 pvtC.CommandText = originalsqltext 处失败

UPDATE:I'm certain now that this has nothing to do with the query itself, but rather the condition of having multiple pivot tables pointed to the same pivotcache. I created a new external datasource with the simple query

更新：我现在确定这与查询本身无关，而是多个数据透视表指向同一个数据透视缓存的条件。我用简单的查询创建了一个新的外部数据源

SELECT * FROM clientdashboard1.Calls1 WHERE TAKENON BETWEEN '2010-01-01' AND CURDATE()

as its query. I named the connection AlphaTest, and created a pivot table from it, then copied that pivot table to another sheet and used different fields. I modified my code to run through this first:

作为它的查询。我将连接命名为 AlphaTest，并从中创建了一个数据透视表，然后将该数据透视表复制到另一个工作表并使用不同的字段。我修改了我的代码以首先运行：

For Each pvtC In ThisWorkbook.PivotCaches
    name = pvtC.WorkbookConnection.name
    If name = "AlphaTest" Then
        originalsqltext = pvtC.CommandText
        pvtC.CommandText = originalsqltext
    End If
Next

It failed at exactly the same point, pvtC.CommandText = originalsqltext

它在完全相同的点失败， pvtC.CommandText = originalsqltext

then I removed the second pivot table, and stepped through again, and it worked.

然后我移除了第二个数据透视表，并再次通过，它起作用了。

Then just for kicks, I plopped my original huge query in, and stepped through again. It worked. However, it uncovered another wrinkle... changing CommandText via code causes it to refresh. So my original plan, of making substitutions, doing a refresh, and then setting back to the original, is not going to work, as the tables will refresh again at the second assignment (if it worked, that is).

然后只是为了踢球，我把我原来的大问题放进去，然后再次跨过去。有效。然而，它发现了另一个问题……通过代码更改 CommandText 会导致它刷新。因此，我最初的计划，即进行替换，进行刷新，然后重新设置为原始计划，是行不通的，因为表格将在第二次分配时再次刷新（如果有效的话）。

UPDATEThis just gets better and better. I though about making dummy copies of my pivot caches, with just 1 single record perhaps, point each pivot table to its own cache, then have vba alter the query of the "real" one, then loop through and point each pivot table to that one. Do the reporting tasks (copy out sheets, break links, save-as, close is how we usually do it). Then back in the original book, point all the pivot tables to their respective dummy caches. Well, you know that AlphaTest datasource that I had? I thought that pvtC.CommandText = originalsqltext actually changed the query and caused AlphaTest to refresh... oh no my friends. It created a new connection, called Connection, which is a copy of AlphaTest. Ok Fine. How can I use this ? ..... I have some ideas I'm going to try, but if anyone else has dealt with this, please, please.... I'm going to try something like the approach Tim suggested... it's just that I won't be able to directly alter the pivotcache that the tables are connected to, I'll have to have them hooked to a default shared connection, have an editable connection (with no pivot tables connected), edit that one with my parameters, point each pivot table to it, do the reporting tasks, then point the pivot tables back to the default shared connection... If this works I'll be very happy.

更新这只会变得越来越好。我想制作我的数据透视缓存的虚拟副本，也许只有 1 个单条记录，将每个数据透视表指向它自己的缓存，然后让 vba 更改“真实”缓存的查询，然后循环遍历并将每个数据透视表指向那个一。执行报告任务（复制工作表、断开链接、另存为、关闭是我们通常的做法）。然后回到原书中，将所有数据透视表指向它们各自的虚拟缓存。好吧，您知道我拥有的 AlphaTest 数据源吗？我认为 pvtC.CommandText = originalsqltext 实际上改变了查询并导致 AlphaTest 刷新......哦，不，我的朋友们。它创建了一个名为 Connection 的新连接，它是 AlphaTest 的副本。好的。我怎样才能使用它？.....我有一些想法我会尝试，但如果其他人已经处理过这个问题，请

回答by Brian Pressler

After doing a lot of research online... I'm finding that this is a bug when updating the CommandTextproperty of an ODBC connection. If you temporarily switch to an OLEDB connection, update your CommandTextproperty and then switch back to ODBC it does not create the new connection. Don't ask me why... this just works for me.

在网上做了大量研究之后……我发现这是更新CommandTextODBC 连接属性时的一个错误。如果您临时切换到 OLEDB 连接，请更新您的CommandText属性，然后切换回 ODBC，它不会创建新连接。不要问我为什么......这对我有用。

Create a new module and insert the following code:

创建一个新模块并插入以下代码：

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

This UpdateWorkbookConnectionsubroutine only works on updating OLEDB or ODBC connections. The connection does not necessarily have to be linked to a pivot table. It also fixes another problem and allows you to update the connection even if there are multiple pivot tables based on the same connection.

此UpdateWorkbookConnection子例程仅适用于更新 OLEDB 或 ODBC 连接。连接不一定必须链接到数据透视表。它还解决了另一个问题，即使存在多个基于同一连接的数据透视表，也允许您更新连接。

To initiate the update just call the function with the connection object and command text parameters like this:

要启动更新，只需使用连接对象和命令文本参数调用函数，如下所示：

UpdateWorkbookConnection ActiveWorkbook.Connections("Connection"), "exec sp_MyAwesomeProcedure"

You can optionally update the connection string as well.

您也可以选择更新连接字符串。

I never use the ?parameters so i can't really tell if this will fix your issues with that, but I suspect it will. I always just use string concatenation to insert the parameters directly into the CommandText string.

我从不使用这些?参数，所以我无法确定这是否会解决您的问题，但我怀疑它会。我总是只使用字符串连接将参数直接插入到 CommandText 字符串中。

回答by user2799311

I use ODBC connections all the time. I first establish my connection manually on a worksheet. I make sure the "Enable Background Refresh" is turned off. I do this stuff all day long. Here's some simple code to refresh the connection (Dim your variables as necessary):

我一直使用 ODBC 连接。我首先在工作表上手动建立连接。我确保“启用后台刷新”已关闭。我整天都在做这些事情。下面是一些简单的代码来刷新连接（根据需要调暗变量）：

ActiveWorkbook.Connections("ExampleConnection").Refresh

To change the command text:

要更改命令文本：

ActiveWorkbook.Connections("ExampleConnection").ODBCConnection.CommandText = _
"SELECT FILE1.FIELD1 AS ""Name1"", FILE1.FIELD2 as ""Name2""" & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
"FROM SERVER.LIBRARY.FILE1 FILE1" & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
"WHERE FILE1.FIELD1 = 'FILTER'"
ActiveWorkbook.Connections("ExampleConnection").Refresh

To change the command text with a run-time variable:

要使用运行时变量更改命令文本：

DIM str AS STRING

str = "VARIABLE"

ActiveWorkbook.Connections("ExampleConnection").ODBCConnection.CommandText = _
"SELECT FILE1.FIELD1 AS ""Name1"", FILE1.FIELD2 as ""Name2""" & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
"FROM SERVER.LIBRARY.FILE1 FILE1" & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
"WHERE FILE1.FIELD1 = '" & str & "'"
ActiveWorkbook.Connections("ExampleConnection").Refresh

And to refresh multiple pivots, as I mentioned above, make sure your ODBC connection "Enable Background Refresh" checkbox is unchecked and you can refresh pivots all day long:

并且要刷新多个支点，正如我上面提到的，请确保您的 ODBC 连接“启用后台刷新”复选框未选中，并且您可以整天刷新支点：

ActiveWorkbook.Connections("ExampleConnection").Refresh
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
Sheet1.PivotTables("PivotTable2").PivotCache.Refresh
Sheet2.PivotTables("PivotTable1").PivotCache.Refresh
Sheet2.PivotTables("PivotTable2").PivotCache.Refresh

An option, either for a dynamic WHERE clause or to get around the "Too many lines" error:

一个选项，用于动态 WHERE 子句或解决“行太多”错误：

DIM s AS STRING
DIM f AS STRING
DIM w AS STRING
DIM r AS RANGE
Dim str AS STRING

set r = Sheet1.Range("A1")
str = r.Value
s = "SELECT FILE1.FIELD1 as ""Name1"", FILE1.FIELD2 as ""Name2"""
f = "FROM SERVER.LIBRARY.FILE1 FILE1"

If r.Value = "" Then
   w = ""
Else
   w = "WHERE FILE1.FIELD1 = '" & str & "'"
End If

ActiveWorkbook.Connections("ExampleConnection").ODBCConnection.CommandText = _
s & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
f & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
w
ActiveWorkbook.Connections("ExampleConnection").Refresh

回答by Luckychel

it's works 100%

它 100% 有效

WorkbookConnection wc = book.Connections.Add("SQL-STRING", "", response.ConnectionString, response.SqlString, XlCmdType.xlCmdSql);
pivot = worksheet.PivotTables(1);
pivot.ChangeConnection(wc);

回答by Gerwin

Good day,

再会，

the problem obviously occurs when more than one pivot table is accessing the connection.

当多个数据透视表访问连接时，问题显然会发生。

Following workaround does the trick:

以下解决方法可以解决问题：

MANUALLY (Only one time required)

手动（只需要一次）

1.) Export manually a suitable ODBC-connection as file to any location. (Double click on a connection and find the button to the bottom at "Definition")

1.) 将合适的 ODBC 连接作为文件手动导出到任何位置。（双击连接并在“定义”底部找到按钮）

IN VBA:

在 VBA 中：

2.) Add the connection to the workbook you exported in step 1:

2.) 将连接添加到您在步骤 1 中导出的工作簿：

myWorkbook.Connections.AddFromFile "\myPath\myODBCConnection.odc"

myWorkbook.Connections.AddFromFile "\myPath\myODBCConnection.odc"

3.) Edit the command text of the added connection

3.) 编辑添加的连接的命令文本

myWorkbook.Connections("nameOfAddedConnectionName").ODBCConnection.CommandText = "SELECT * FROM whatever"

myWorkbook.Connections("nameOfAddedConnectionName").ODBCConnection.CommandText = "SELECT * FROM 无论"

4.) Delete the connection which is accessed by your pivot tables.

4.) 删除数据透视表访问的连接。

myWorkbook.Connections("oldPivotConnectionName").Delete

myWorkbook.Connections("oldPivotConnectionName").删除

5.) Rename the added connection to the name of the old connection

5.) 将添加的连接重命名为旧连接的名称

myWorkbook.Connections("nameOfAddedConnectionName").Name = "oldPivotConnectionName"

myWorkbook.Connections("nameOfAddedConnectionName").Name = "oldPivotConnectionName"

6.) Refresh the connection

6.) 刷新连接

myWorkbook.Connections("oldPivotConnectionName").Refresh

myWorkbook.Connections("oldPivotConnectionName").刷新

That's it! :-)

就是这样！:-)

回答by Joost

This is not really an answer to your question, but I wanted to make these suggestions anyway.

这并不是您问题的真正答案，但无论如何我还是想提出这些建议。

For Each cn In ThisWorkbook.Connections     ' loop through the connections
    If cn.Type = xlConnectionTypeODBC Then
        Set odbcCn = cn.ODBCConnection
        originalsqltext = odbcCn.CommandText
        Select Case odbcCn.Parent
            Case "Calls", "Suboutcomes"
                newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
                newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
            Case "QtyCallsPerDay1"
                newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
            Case Else
                newsqltext = originalsqltext
        End Select
        odbcCn.CommandText = newsqltext
        odbcCn.Refresh
        odbcCn.CommandText = originalsqltext
    Else ''' this used to be End If
        cn.Refresh ' refresh the other connection without modification
    End If
Next

Notice 2 things: I used Select Case (but that's just for nicer code in my opinion, it doesn't change anything to the functionality). 2nd, are you sure the cn.Refresh shouldn't be in an Else-block? Maybe I'm misinterpreting the code, but it looks like the connection will refresh after you reassign the original SQL to it.

请注意 2 件事：我使用了 Select Case（但在我看来这只是为了更好的代码，它不会改变任何功能）。第二，你确定 cn.Refresh 不应该在 Else 块中吗？也许我误解了代码，但在您将原始 SQL 重新分配给它后，连接似乎会刷新。

As for the too many line continuations, you can trick VBA like this:

至于too many line continuations，你可以像这样欺骗 VBA：

somevar = "line 1" & _
          "line 2" & _
          .....
          "line 55"
somevar = somevar & _
          "line 56" & _
          "line 57"

This way, you aren't technically limited to a fixed number of & _.

这样，您在技术上就不受固定数量的& _.

Now, to your real question: 1004: Application-defined or object-defined errormeans there is something in the SQL string which can't be properly interpreted by the provider. You say it looks perfectly fine -- would it be possible to post the contents of newsqltext? Because indeed, it doesn't look like you've made a coding error.

现在，对于您真正的问题：1004: Application-defined or object-defined error意味着 SQL 字符串中有一些内容无法被提供者正确解释。你说它看起来很好 - 可以发布内容newsqltext吗？因为确实，看起来您并没有犯编码错误。