Excel DB Queries: passwords and connections
 

My apologies in advance for the long story, but it's been quite a
learning experience for me where every "solved" issue seemed to lead
to more questions.

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.Refr esh

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;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
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").ODBCConnect ion
.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;PageTimeou t=5;PWD=" & PassWd &
";SafeTransactions=0;Threads=3;UID=admin;UserCommi tSy" _
), 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