Ok, so I've managed to get the links to update and have a VBA command button
that successfuly updates the links after the user browses to the new location
and selects the new database. Solve, right?

Partly true. I discovered last night that when the database is on another
drive that it would seem that the path of the file is placed into the SQL
code of the query itself making it impossible for my code as it is now to
update the .connection. All the user's My Documents folders have been set to
point to their network drive, so as you can see this stands to pose a big
problem. I don't know if this was a setting on my copy of excel at home that
is different at work, but I don't want to take that chance. I'd rather have
it working 100%. I'm running Office 2k3 both at home and at work and this
needs to be backwards compatible to at at least Office XP.

My code as it currently stand is:

Option Explicit

Public Sub ChangePivotTableSourceQuery()

' Call the routine ChangePivotTableSource using a queried set of parameter
values.


' The original code used Find/Replace to change the .connection by having
the user browse
' the old location. However with the way the this file and the database are
' going to be distributed the old locaton will have never existed on that
machine,
' so I can only directly input the new path.

'Dim OldFilePath As String
Dim NewFilePath As String


'Calls the function to open the browse window
NewFilePath = Application.GetOpenFilename(FileFilter:="All Files
(*.*),*.*", Title:="Select New Database Source")
If NewFilePath = "False" Then Exit Sub


'Calls the subs to change the .connection properties
ChangePivotTableSource NewFilePath
ChangeQueryTableSource NewFilePath

End Sub

Public Sub ChangePivotTableSource(ByVal NewFilePath As String)


' Change a data source for all PivotTables in the workbook.
'
' Syntax
'
' ChangePivotTableSource(OldFilePath, NewFilePath)
'
' OldFilePath - Full path to the old source file.
'
' NewFilePath - Full path to the new source file.

Dim Pivot As PivotCache
Dim pConnection As String
Dim pConnectionOld As String
Dim Count As Long
Dim NewDBSource As String, NewPath As String


'Getting the filename and path from the getopenfilename function
NewDBSource = Left(NewFilePath, InStrRev(NewFilePath, ".") - 1)
NewPath = Left(NewDBSource, InStrRev(NewDBSource, "\") - 1)


'The code to change the pivot .connection
For Each Pivot In ThisWorkbook.PivotCaches
pConnectionOld = Pivot.Connection
pConnection = "ODBC;DSN=MS Access Database;DBQ=" & NewDBSource & _
";DefaultDir=" & NewPath & ";DriverId=25;FIL=MS Access;" & _
"MaxBufferSize=2048;PageTimeout=5;UID=admin;"
Count = Count + 1
Pivot.Connection = pConnection
Next
MsgBox Count & " PivotTables changed."


'Debugging to display the old .connection and the new one
budget.txtDebug = pConnectionOld
budget.txtDebug2 = pConnection

End Sub

Public Sub ChangeQueryTableSource(ByVal NewFilePath As String)


' Change a data source for all PivotTables in the workbook.
'
' Syntax
'
' ChangePivotTableSource(OldFilePath, NewFilePath)
'
' OldFilePath - Full path to the old source file.
'
' NewFilePath - Full path to the new source file.

Dim qConnection As String
Dim qConnectionOld As String
Dim Count As Long
Dim NewDBSource As String, NewPath As String
Dim ws As Worksheet
Dim Query As QueryTable



NewDBSource = Left(NewFilePath, InStrRev(NewFilePath, ".") - 1)
NewPath = Left(NewDBSource, InStrRev(NewDBSource, "\") - 1)


'The actual code that changes the .connection
For Each ws In ThisWorkbook.Worksheets
For Each Query In ws.QueryTables
qConnectionOld = Query.Connection
qConnection = "ODBC;DSN=MS Access Database;DBQ=" & NewDBSource & _
";DefaultDir=" & NewPath & ";DriverId=25;FIL=MS Access;" & _
"MaxBufferSize=2048;PageTimeout=5;UID=admin;"
Count = Count + 1
Query.Connection = qConnection
Next
Next
MsgBox Count & " Queries changed."


'Debugging to display the old path and the new path
budget.txtDebug3 = qConnectionOld
budget.txtDebug4 = qConnection

End Sub



"A.Gates" wrote:

Hello all,Option Explicit

Public Sub ChangePivotTableSourceQuery()

' Call the routine ChangePivotTableSource using a queried set of parameter
values.


' The original code used Find/Replace to change the .connection by having
the user browse
' the old location. However with the way the this file and the database are
' going to be distributed the old locaton will have never existed on that
machine,
' so I can only directly input the new path.

'Dim OldFilePath As String
Dim NewFilePath As String


'Calls the function to open the browse window
NewFilePath = Application.GetOpenFilename(FileFilter:="All Files
(*.*),*.*", Title:="Select New Database Source")
If NewFilePath = "False" Then Exit Sub


'Calls the subs to change the .connection properties
ChangePivotTableSource NewFilePath
ChangeQueryTableSource NewFilePath

End Sub

Public Sub ChangePivotTableSource(ByVal NewFilePath As String)


' Change a data source for all PivotTables in the workbook.
'
' Syntax
'
' ChangePivotTableSource(OldFilePath, NewFilePath)
'
' OldFilePath - Full path to the old source file.
'
' NewFilePath - Full path to the new source file.

Dim Pivot As PivotCache
Dim pConnection As String
Dim pConnectionOld As String
Dim Count As Long
Dim NewDBSource As String, NewPath As String


'Getting the filename and path from the getopenfilename function
NewDBSource = Left(NewFilePath, InStrRev(NewFilePath, ".") - 1)
NewPath = Left(NewDBSource, InStrRev(NewDBSource, "\") - 1)


'The code to change the pivot .connection
For Each Pivot In ThisWorkbook.PivotCaches
pConnectionOld = Pivot.Connection
pConnection = "ODBC;DSN=MS Access Database;DBQ=" & NewDBSource & _
";DefaultDir=" & NewPath & ";DriverId=25;FIL=MS Access;" & _
"MaxBufferSize=2048;PageTimeout=5;UID=admin;"
Count = Count + 1
Pivot.Connection = pConnection
Next
MsgBox Count & " PivotTables changed."


'Debugging to display the old .connection and the new one
budget.txtDebug = pConnectionOld
budget.txtDebug2 = pConnection

End Sub

Public Sub ChangeQueryTableSource(ByVal NewFilePath As String)


' Change a data source for all PivotTables in the workbook.
'
' Syntax
'
' ChangePivotTableSource(OldFilePath, NewFilePath)
'
' OldFilePath - Full path to the old source file.
'
' NewFilePath - Full path to the new source file.

Dim qConnection As String
Dim qConnectionOld As String
Dim Count As Long
Dim NewDBSource As String, NewPath As String
Dim ws As Worksheet
Dim Query As QueryTable



NewDBSource = Left(NewFilePath, InStrRev(NewFilePath, ".") - 1)
NewPath = Left(NewDBSource, InStrRev(NewDBSource, "\") - 1)


'The actual code that changes the .connection
For Each ws In ThisWorkbook.Worksheets
For Each Query In ws.QueryTables
qConnectionOld = Query.Connection
qConnection = "ODBC;DSN=MS Access Database;DBQ=" & NewDBSource & _
";DefaultDir=" & NewPath & ";DriverId=25;FIL=MS Access;" & _
"MaxBufferSize=2048;PageTimeout=5;UID=admin;"
Count = Count + 1
Query.Connection = qConnection
Next
Next
MsgBox Count & " Queries changed."


'Debugging to display the old path and the new path
budget.txtDebug3 = qConnectionOld
budget.txtDebug4 = qConnection

End Sub



I have an excel pivot table that I'm populating using a query to an Access
DB. The database and excel file will be distributed to the various contract
managers through-out the company. This will invariably mean that the
database that they have will be located in different locations and could end
up being renamed. I'm looking for some VBA code that I could insert into a
command button located on the same sheet as the pivot table so that they
could click on the button and the browse window would open and they could
navigate to the database, click ok and it would save the location of the
database.

Currently when the database isn't found it comes up with the prompt that the
database isn't found and do you want to browse to find it. This is all well
and good, but the end user's I'm coding for would freak out at the mere sight
of this dialogue and I'm trying to minimize the chance for human error. I'd
found something similar to what I'm trying to do as far as the browse window
that opens when the macro is run from the following site:
http://www.tushar-mehta.com/excel/ne..._database.html

Unfortunately the code didn't work in my spreadsheet and at this point after
having done days of research my brain is so fried, I can't even try to adapt
this to my needs.

I'll be extremely grateful for any help given.
~Andrew Gates
MAA