We help IT Professionals succeed at work.

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

colinasad
colinasad asked
on
95 Views
Last Modified: 2017-03-30
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.
Comment
Watch Question

President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions