Excel DB Queries: passwords and connections
 

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
----------------------------------------

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
----------------------------------------

Excel DB Queries: passwords and connections
 

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
----------------------------------------