Re: [MSAccessProfessionals] Connection to SQL with saved User and Password

I meant after you've created a DSN and you're linking tables with it:

Paul

------ Original Message ------

From "Doyce Winberry" <doyce.winberry@xpo.com>

To "MSAccessProfessionals@groups.io" <MSAccessProfessionals@groups.io>

Date 6/2/2022 8:40:33 AM

Subject Re: [MSAccessProfessionals] Connection to SQL with saved User and Password

Paul,

 

I've setup new DSN and don't see a checkbox to save password. Not sure what procedure you are referring to when you say link tables manually.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPOLogistics

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Paul Baldy
Sent: Thursday, June 2, 2022 8:50 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Connection to SQL with saved User and Password

 

If you are linking the tables manually, there should be a checkbox in that process that allows you to save the password. Paul ------ Original Message ------ From "Doyce Winberry" <doyce.winberry@xpo.com> To MSAccessProfessionals@groups.io

ZjQcmQRYFpfptBannerStart

This Message Is From an External Sender This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd

If you are linking the tables manually, there should be a checkbox in that process that allows you to save the password.

 

Paul

 

------ Original Message ------

From "Doyce Winberry" <doyce.winberry@xpo.com>

To MSAccessProfessionals@groups.io

Date 6/2/2022 5:48:38 AM

Subject [MSAccessProfessionals] Connection to SQL with saved User and Password

 

Hello everyone. I'm using Access 2016 and have a DB that connects to some SQL server tables. Lately my corporate DB team has said we are not Sarbanes Oxley compliant because individual users have write rights to the SQL tables so they propose that we use a "service account" to connect to the SQL tables instead of individual accounts. When they remove the write rights from my individual accounts the DB breaks because it needs to write to the SQL tables. I'm using an ODBC connection setup as a system DSN to connect to the SQL tables. When I change the DSN to use the service account they want me to use, it does not remember the password. I'm not supposed to share the password with the users. So how can I connect to the SQL tables with the service account and password and have the DB "remember" the password. I've found examples of connection strings with userID and passwords but don't know where to put them in the DB. 
Thanks in advance.
Doyce Winberry