[AccessD] Changing DSN Connection strings

Paul Wolstenholme Paul.W at industrialcontrol.co.nz
Tue Dec 7 18:28:08 CST 2021


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> 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
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list