Issue with a cached connection from Access to SQL

Hello dear friends.
Recently I've tried to follow the instructions regarding "Cached connection", described in this article:
https://www.microsoft.com/en-us/microsoft-365/blog/2011/04/08/power-tip-improve-the-security-of-database-connections/
and indeed - the "miracle" did happen:
All my linked tables from Access to SQL Server now have a connection string which doesn't reveal anything about the username & password. Tables use the cached connection which has been prepared when the application was opened.

Now I have to include in this Access application some tables which reside in another database, on the same SQL Server. They won't use the cached connection.
1. Is there a way to create another cached connection for these new tables? without "harming" the first cached connection?
2. If these tables use a different connection string (so they won't use that cached connection) - is it going to "harm" the cached connection used for the old tables in any way?

Thank you very much!
NNOAM1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
1. You can create DSN-less connections (which is what this old blog is about) to one or more databases
2. They don't "see" each other.
NNOAM1Author Commented:
Yes, Gustav, from the beginning this was done DSN-less. This isn't a problem. I've been creating DSN-less connections for years.
But this time the idea of Cashed Connection was implemented for the first time. All tables connect to SQL via this cashed connection. What will happen when we want to have another cashed connection for the tables linked to the other SQL database?
Gustav BrockCIOCommented:
I guess that Access, of those connections created, will use that cached connection that matches the server address/hostname and the database name. Should be easy to check out.
Put Your Flow Data to Work

SolarWinds® Flow Tool Bundle combines three easy-to-download, easy-to-use flow analysis tools that can help you quickly distribute, test, and configure your flow traffic.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The other option (and a better one), is to use trusted connections to SQL.   That way, you avoid the whole username/password problem.

Jim.
NNOAM1Author Commented:
In this company, we're allowed to use SQL Server Authentication only.
John TsioumprisSoftware & Systems EngineerCommented:
Maybe its better to use DSN-less connections with UserName/Password and focus more on the user Permissions/Accessibility...if you tighten up which user has access to which table and work on this you could have all the tables without trying to trick the system...
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<In this company, we're allowed to use SQL Server Authentication only.>>

 That;'s too bad....is there a specific reason they do this?    Generally unless your working with multiple domains, your better off with trusted connections.   The problem with multiple domains is that you then need to setup trust relations between them, so most then rely on SQL server logiins.

 If your stuck with that, then I would do as John suggests and just stick the UN/pswd in the connection string.  

 Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NNOAM1Author Commented:
Yes, multiple domains. Thank you all for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.