Sounds good. We'll start small by tackling the first macro only.
Unfortunately I don't understand how to clear a pivot cache or how
that helps me from getting Connection1, Connection2, Connection3,
etc. Right now the critical line of code is this:

PT.TableRange2.Clear

Prior to this line, I go to Data--Connections, and click to see
"where selected connections are used" Excel gives me the Sheet, Name,
and Location of the PivotTable. After this line of code, I recheck
the connection. Excel tells me "Connection(s) not used in this
workbook". Later in the code when I add the new cache, Excel just
increments the number on the Connection.

Terry

On Jan 10, 12:13*am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Best bet is to post the *smallest possible example* of non-working code, and
state exactly how it does work.
One issue at a time is optimal if it's anything other than a one-liner.

In your first macro you first clear all the pivot tables: why not also clear
the caches as well, before you add the new one ?
That way you won't end up with hundreds of connections.

Tim

"detrie" wrote in message

The code for the 'good' macro is below. *It works
because I am manually clearing the pivot table and re-creating it with
a fresh connection to the database, supplying the password
programmatically (the value of which is located in a VeryHidden
sheet). *I went to DatabaseConnections and sure enough, my file had
hundreds of connections (most of which have been orphaned).
Question: *is there a way to refresh the connection without
clearing and re-creating it?

----------------------------------------
Sub FetchDecks()
* *Application.ScreenUpdating = False
* *Application.Calculation = xlCalculationManual
* *Msg = "Begin Macro"

' * Delete PivotTable if it exists
* *Set WSD = Worksheets("Decks")
* *WSD.Visible = xlSheetVisible

* *For Each PT In WSD.PivotTables
* * * *PT.TableRange2.Clear
* *Next PT
* *Msg = "Cleared Pivot"
' * Create a Pivot Cache
* *Set PTcache = ActiveWorkbook.PivotCaches.Add
(SourceType:=xlExternal)

' * Path todatabasefile
* *DbFile = Range("DbFile")
* *PassWd = Range("PassWd")

' *ConnectionString
* *'ConString = "ODBC;DSN=MS AccessDatabase;DBQ=" & DbFile & ";PWD="
& PassWd & ";UID=admin"
* *ConString = "ODBC;DBQ=" & DbFile & ";DefaultDir=" & DbFile &
";Driver={Driver do Microsoft Access (*.mdb)};FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeou t=5;PWD=" & PassWd &
";SafeTransactions=0;Threads=3;UID=admin;UserCommi tSync=Yes;"

' * QueryString
* *QueryString = "SELECT *" & Chr(13) & "" & Chr(10) & _
* * * *"FROM `" & DbFile & "`.tblDecks tblDecks"
* *Msg = "Defined SQL Parameters"

* *With PTcache
* * * *.Connection= ConString
* * * *.CommandText = QueryString
* *End With
* *Msg = "Completed Query"

' * Create pivot table
<snip
* *Exit Sub
----------------------------------------