Excel DB Queries: passwords and connections
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 your database connection: 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" 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
----------------------------------------
|