[AccessD] Changing DSN Connection strings

[David Emerson]

Hi Listers,

I have inherited a data base which has code which updates the connection
string of the tables between two different databases.  The user selects
which database to link to and the code updates the table's connect property:

tdf.Connect = strConnect_NEW

strConnect_NEW is in this format (with the actual password in place of
{Pwrd}):

ODBC:DSN= StockListSQL_Data_Archive;Description=
StockListSQL_Data_Archive;APP=Microsoft
Office;DATABASE=SL_DataSQL;User_Id=SQL_User;Password={Pwrd]

The only difference between the two connection strings in the name of the
Database

The Database could initially be connected to either of the SQL databases.
The first time the database connection is changed with this code:

                    tdf.Connect = strConnect_NEW
                    'Refresh the table link.
                    tdf.RefreshLink                     'Refresh the Link.

an SQL Server Login dialog box opens showing the Data Source, and Login ID,
and asking for the password.  Once the Password is entered then the user can
switch between the database tables without having to reenter the password.
However, if the Access program is closed and reopened then the first time
the database connection is changed the password is requested again.

Why is the Access program asking for the password when the password is
entered in the Connection string?  Is there some other connection that needs
the password saved in it?

When the DSN is set up for each user the "Connect to SQL Server to obtain
default settings for the additional configuration options" box is ticked and
the Login ID and Password is entered.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand