DbFile = Range("DbFile")
This range is a single cell with value, set by a different macro where
the user is prompted to find database.
Currently, its value is C:\Games\OCTGN\db\magic.mdb
This is a valid file on my computer.

Terry

On Jan 16, 10:41*pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Terry,

Just to let you know I didn't ignore your post after prompting you to
re-format it...

I've been trying on and off for a day or so to get your macro to work for
me - no luck so far.
How are you making yourdatabaseconnection: ie. what's the value of
"DBFile" ?

Tim

"detrie" wrote in message

...
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" *Excelgives me the Sheet, Name,
and Location of the PivotTable. *After this line of code, I recheck
theconnection. *Exceltells me "Connection(s) not used in this
workbook". *Later in the code when I add the new cache,Exceljust
increments the number on theConnection.

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 freshconnectionto thedatabase, supplying thepassword
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 theconnectionwithout
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
----------------------------------------