Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC Passwords saving in Excel queries | Excel Discussion (Misc queries) | |||
Data connections in Excel | Excel Discussion (Misc queries) | |||
Excel DB Queries: passwords and connections | Excel Programming | |||
How to Prevent Web Connections from Excel? | Excel Discussion (Misc queries) | |||
dynamic connections in excel? | Excel Worksheet Functions |