How to update ODBC connections in Excel 2010 PivotTables

[feipezi]

Hi,

I have a workbook full of PTs with data source of ODBC. Now I like to change the data source from one location to another in my local drive. I tried a few times and it wouldn't let me.

Here is what I did: at the ribbon, from Data, click Connectons; then Add; then Browse for more; then select the path I want; then Open the Access MDB file; then pick the table I want; then click OK; then back to Properties; then click Definition; the Connection String looks fine; but Command Text just shows the name of the table, no query;
change Command Type from Table to SQL, then nothing happens; trying to Refresh All but the PTs still look for the old connection, not the one just added.

I removed the old connection, then all the PTs are dead.

What's going on?

Thanks in advance.

 

[dhookom]

Why not change the existing connections to point to your local drive?

Duane
Hook'D on Access
MS Access MVP

 

[feipezi]

Yes, I did that first. But I was told the new location was not a valid path or the data cannot be found. In another word, Excel won't recognize the modified location.

Thanks.

 

[SkipVought]

Hi,

I never had much experience doing pivots on external data, since my data sources were often from various servers to be joined later in Excel via queries. So I simply queried the external data sources in Excel with appropriate parameters and performed subsequent SQL joins in Excel and/or PivotTables.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

I only connect to SQL servers and use code like:

Public Sub ChangeConnection()
    Dim strConnect As String
    Dim strConnectName As String
    Dim w As Worksheet
    Dim qt As QueryTable
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    [COLOR=#4E9A06]'GetConnectionString() is a function that returns the connection string[/color]
    strConnectName = "SAM"
    If Range("cboFactory") <> "" Then
        For Each cn In ThisWorkbook.Connections
            If cn.Name <> "Central" Then  'don't want to change the connection to the central database
                If cn.Type = xlConnectionTypeODBC Then
                    Set odbcCn = cn.ODBCConnection
                    odbcCn.SavePassword = True
                    odbcCn.Connection = GetConnectionString()
                ElseIf cn.Type = xlConnectionTypeOLEDB Then
                    Set oledbCn = cn.OLEDBConnection
                    oledbCn.SavePassword = True
                    oledbCn.Connection = GetConnectionString()
                End If
                cn.Refresh
                RefreshCharts  [COLOR=#4E9A06]'code that resets all of my pivot charts[/color]
            End If
        Next
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[feipezi]

Hi,

Thanks for the tip. But, can I have the function or sub "GetConnectionString()"?

Thanks in advance.

I took over the assignment from someone else. I wouldn't use ODBC if I started the project. The workbook has at least 10 PTs. If I redo the PTs and the charts, say with ADO or MS Query, it will take quite some time. It's really not worth the time.

Thanks again for replying.

 

[dhookom]

My GetConnectionString() returns the connection for SQL Server so it will not work for an Access MDB file. You can find connection string information here.

Duane
Hook'D on Access
MS Access MVP

 

[feipezi]

Thanks dhookom.

Btw, I found something on the web. It works in some way, i.e., it will be able to change qy.Connection but not qy.CommandText, which is true for the PT For Loop. So I'll have to manually make changes in CommandText window.

Any suggestions?

Thanks in advance.



Sub QueryChange()
Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim rng As Range

'Replace the following paths with the original path or server name
'where your database resided, and the new path or server name where
'your database now resides, data file name must be the same.

OldPath = "C:\CB_DATA"
NewPath = "C:\username\MM\CB"

For Each ws In ActiveWorkbook.Sheets
For Each qy In ws.QueryTables
qy.Connection = _
Application.Substitute(qy.Connection, _
OldPath, NewPath)
qy.CommandText = _
StringToArray(Application.Substitute(qy.CommandText, _
OldPath, NewPath))
qy.Refresh
Next qy

For Each pt In ws.PivotTables
pt.PivotCache.Connection = _
Application.Substitute(pt.PivotCache.Connection, _
OldPath, NewPath)
' On Error Resume Next
pt.PivotCache.CommandText = _
StringToArray(Application.Substitute(pt.PivotCache.CommandText, _
OldPath, NewPath))
Next pt
Next ws
End Sub

Function StringToArray(Query As String) As Variant

Const StrLen = 127
Dim NumElems As Integer
Dim Temp() As String

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

StringToArray = Temp

End Function

 

[dhookom]

I have used code to change the command text.

ThisWorkbook.Connections("FHF").OLEDBConnection.CommandText = GetSQL()

"FHF" is the name of the connection. GetSQL() is a function that returns a SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[feipezi]

You said GetSQL() is a function that returns a SQL statement. I believe you will have to have a path that leads to the data. I don't think you can use the statement above to change the path, can't you?

I tried to use a REPLACE() to replace the old path by the new one but not working on CommandText part.

Thanks for your input.

 

[dhookom]

My post was in reference to your statement "So I'll have to manually make changes in CommandText window." It had nothing to do with the path or connection string which I attempted to provide direction earlier.

Duane
Hook'D on Access
MS Access MVP