Can the contents of a DSN file be retrieved from variables embedded in an Access ACCDB?

I have at long last decided to migrate some old Access "projects" (.ADP) to Access 2016 ACCDB applications.
I am retaining my SQL Server back-end database and (with a little help from EE) have made the connections to the SQL-Server Tables, Views and Stored Procedures via a DSN file.

When I used the the Access "projects" (.ADP) method, there was a "CurrentProject" variable that contained the various parameters of the Access-SQLServer connection. This allowed me to present on my Access users' screens a few pieces of data relating to their current database connection.

That "CurrentProject" variable still compiles in Access 2016, but does not seem to behave exactly as it did in the ADP applications.
Is there a "simpler" embedded object in Access ACCDB that holds the contents of the DSN file? My DSN file appears to be a simple "text" file that can be opened with Notepad. Do I just need to do my own interrogation of the DSN text content?

Many thanks.
colinasadAsked:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You just put it in the connect string, which is the tabledefs.connect property.

The way ODBC is setup in Access is that it first looks there.  Then if a DSN is referenced, looks there.    Between the two, it expects to have all the values required to connect to the data source.   If something is missing, you'd get prompted for it.

So you can have it all in the connect, all in the DSN, or a mix of the two.

This article here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

 talks about putting everything in the connect property (because it's easy to distribute an app this way) and has code to help move DSN elements into the connect property.

Jim.
0

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
colinasadAuthor Commented:
Thanks for the prompt reply, Jim.
The suggested link gives me more meaty, low-level properties to investigate.
I guess, as ever, I was looking for a "simple" method handed to me on a plate!
Everything I need is in there; I just need to tease it out.
Many thanks.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The biggest thing with SQL is security.   SERVER you always need for example, but depending on the security setup in SQL, it's either using a trusted connect or a username / password pair.

A good source for connection string examples BTW is:

https://www.connectionstrings.com/

at it's simplest, it would be this if your using ODBC (linked tables):

https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/


 Fastest way to find out what you need for ODBC is to use Access itself to create a linked table without using a DSN.   You can then look at the connect property in the debug window:

?  CurrentDB().TableDefs("myTableNameHere").Connect

 There are also the native client drivers, which covers later versions of SQL.  If you were using OLEDB/ADO, then you can use something like this:

https://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/

Jim.
0
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
Microsoft Access

From novice to tech pro — start learning today.