Excel DB Queries: passwords and connections

[detrie]

Before I start I have to ask a few questions about this newsgroup
1) The text below was originally posted on Dec 14. It is quite long,
and the scope of the questions may have been too narrow, but I was
really hoping *someone* would have responded. Could someone provide
feedback on the most effective way to pose complex questions?
2) A few days ago, I could no longer find this message on the
newsgroup. Was it removed by some admin? If so, would that admin
please contact me and tell me why.

***

I am running Excel 2007 on a Vista computer. I have an Excel file
that connects to an Access database that is password protected. The
file has multiple macros that query different parts of the database.
Until a few months ago, all worked great. Question #1: Was there some
update to Office 2007 / Excel 2007 that changed the way Excel
communicates with ODBC Data Sources?

I have one macro in particular that is troublesome. When the code
gets to this line:

Worksheets("Decks").PivotTables(1).PivotCache.Refresh

I get a dialog box asking me to provide authorization to the data
source (Login name is supplied, password is not). If I were the only
user I would consider this an unfortuante inconvenience, but I created
this Excel file to allow other users to access data in specific tables
and not have access to the whole database. Excel asking for a
password pretty much kills the purpose of the file. I've done some
troubleshooting and testing, and have found out the following:

I have another query macro that works just fine, and after I run it,
the line of code above doesn't have any complaints about a
PivotCache.Refresh. 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 Database Connections and sure enough, my file had
hundreds of connections (most of which I'm sure have been orphaned).
This lead to Solution A (Call FetchDecks in the AutoOpen routine).
Question #2: 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 to database file
DbFile = Range("DbFile")
PassWd = Range("PassWd")

' Connection String
'ConString = "ODBC;DSN=MS Access Database;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;PageTimeout=5;PWD=" & PassWd &
";SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=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
Set PT = PTcache.CreatePivotTable( _
TableDestination:=WSD.Range("A1"), _
TableName:="DeckListPivot")
Msg = "Created Pivot"

' Add fields
With PT
.PivotFields("DECKTITLE").Orientation = xlRowField
.PivotFields("DECKTYPEID").Orientation = xlDataField
End With
WSD.Visible = xlSheetVeryHidden
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub
----------------------------------------

I dug deeper and changed the properties of the active connections: 1)
renamed them to something more descriptive than Connection315, and 2)
checked the 'Save Password' box. Solution B works quite well, but
there's still one problem. I wrote a macro to establish the link for
first time users (or to re-establish link should the source file move
accidentally), but it creates new Connections. Now I have to remove
the old named Connections and rename the new (and have Excel save the
password). I'm having great difficulty writing the code to do what I
just did manually. I tried the macro recorder, but it generated some
very cumbersome code spanning multiple lines. I was inclined to leave
the code as is (since it does work) except that I needed to substitute
the database file with the variable DbFile. All worked well, except
for the one instance where Excel did a word-wrap in the middle of file
name. For some reason I can't change this without the code failing.
Question #3: Can someone please help me simplify this code?

Sub Def_CardList()
DbFile = Range("DbFile")
PassWd = Range("PassWd")

With ActiveWorkbook.Connections("CardList").ODBCConnection
.BackgroundQuery = False
.CommandText = Array( _
"SELECT tblDecks.DeckTitle, tblCards.CardTitle,
tblCards.CastingCost, tlkpSpellTypes.SpellTypeName, trelDecks_Cards."
_
, _
"Sideboard, trelDecks_Cards.Quantity" & Chr(13) & "" & Chr(10)
& "FROM `" & DbFile & "`.tblCards tblCards, `C:\Games\OCTGN\db\magic"
_
, _
".mdb`.tblDecks tblDecks, `" & DbFile & "`.tlkpSpellTypes
tlkpSpellTypes, `" & DbFile & "`" _
, _
".trelDecks_Cards trelDecks_Cards" & Chr(13) & "" & Chr(10) &
"WHERE tblCards.CardID = trelDecks_Cards.CardID AND
tblCards.SpellTypeID = tlkpSpe" _
, "llTypes.SpellTypeID AND tblDecks.DeckID =
trelDecks_Cards.DeckID")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DBQ=" & DbFile & ";DefaultDir=" & DbFile & ";Driver=
{Driver do Microsoft Access (*.mdb)};DriverId=25;" _
), Array( _
"FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;PWD=" & PassWd &
";SafeTransactions=0;Threads=3;UID=admin;UserCommitSy" _
), Array("nc=Yes;"))
.RefreshOnFileOpen = False
.SavePassword = True
.SourceConnectionFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("CardList")
.Name = "CardList"
.Description = "List all Cards"
End With
End Sub

 

[Tim Williams]

This is a pretty high-traffic group, so it wouldn't surprise me if your
original post is no longer visible: messages aren't kept forever.
It's very unlikely to have been removed by someone with admin rights.

On this group if you don't get an answer in a couple of days you might
consider re-wording and posting again.
Your original post it really too long: lots of back and forth with different
methods you've tried, etc.

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]

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

 

[Tim Williams]

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

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

 

[detrie]

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