Database Connection Password

[detrie]

I created an Excel file that queries an Access database that is
password protected. The
file has multiple pivot tables that have SQL connection to the
database.

When I try to refresh these pivot tables 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 unfortunate 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.

Is there an elegant way to put this password into the Connection?

Terry

 

[detrie]

Just a follow-up to anyone in future who has a similar question.
You can go into the properties of the database connection and save the
password.
In Excel 2007: Data Ribbon --> Connections Group --> Properties.
From that dialog box, click on Definitions and click Save Password.

In VBA:

Sub Initialize()
Dim CNN As WorkbookConnection
'...
'... (lots of code setting up the pivot tables)
'...
For Each CNN In ActiveWorkbook.Connections
CNN.ODBCConnection.SavePassword = True
Next CNN
End Sub