I tried that but got an Invalid Argument error.
-----Original Message-----
From: AccessD <accessd-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com> On
Behalf Of David Emerson
Sent: Wednesday, 8 December 2021 1:42 pm
To: 'Paul Wolstenholme' <Paul.W at industrialcontrol.co.nz>
Cc: 'AccessD' <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Changing DSN Connection strings
Thanks Paul,
Should that be just before
tdf.RefreshLink 'Refresh the Link.
Regards
David
From: Paul Wolstenholme <Paul.W at industrialcontrol.co.nz>
Sent: Wednesday, 8 December 2021 1:28 pm
To: newsgrps at dalyn.co.nz
Cc: AccessD <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Changing DSN Connection strings
David
You need to add this VBA line:
tdf.Attributes = tdf.Attributes Or dbAttachSavePWD
That corresponds to the linked table manager's pop-up that warns about
saving the password with the linked table in the Access database file
because it isn't safe. Some would suggest you should unlink all tables on
closing the database and relink them on opening (presumably they also assume
the front end is never shared).
Paul Wolstenholme
On Wed, 8 Dec 2021 at 13:10, David Emerson <newsgrps at dalyn.co.nz
<mailto:newsgrps at dalyn.co.nz> > wrote:
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