Complete ODBC-String with all defined attributes for a specific DSN

Posted on 2000-04-14
Last Modified: 2012-05-04
I am trying to get an complete ODBC-String with all defined attributes for a specific DSN name I created with the odbc admin tool. My Intention is to establish a connection to an oracle or informix database within ms access by code like this

  strDSN = FunctionOrAPICallIamSeekingFor("My_DSN_Name")
  Call DBEngine.OpenDatabase("", dbDriverPrompt, False, "ODBC;" & strDSN & ";UID=SuperBrain;PWD=MegaSecret")

Executing this code at applications start up prevents any driver prompt on opening a odbc table because ms access seems to make a internal clone of my (!) established connection for its own further use. For flexibility reasons I only want to store the DSN name instead of all the attributes in the code.

Does anyone know how to get all attributes for a stored DSN preferably with VB?
Question by:baechlek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1

Expert Comment

ID: 2716112
in your registry, using regedit.  You will see the DSN parameters stored there for the User DSNs you have defined on your system.  Similarly, you can get system dsn parameters as well via reading the registry.

Author Comment

ID: 2716259
In HKEY_CURRENT_USER\SOftware\ODBC\ODBC.INI there are only standard definitions on my maschine. At HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MyDSN I found driver specific values e. g. "Driver"="C:\\orant\\BIN\\SQORA32.DLL"UserID"="" .... They are not usefull for connect strings because for example UserID has to be mapped to "UID" or "USER" and the mapping depends on the driver. If our support exchanges a driver I have to adapt my software and thats what I wanted to prevent by storing only the name of the DSN.

nevertheless, thanks for your comment

Expert Comment

ID: 2716499
IS it possible you can add both UID and USER to the connection string and it will ignore one?  Or does it bomb?

I guess the other way is to define the username and password in the ODBC DSN config...
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).


Author Comment

ID: 2716648
I am afraid we cannot garantie that all drivers which will be installed in the future will ignore the wrong attribute for the user in the connect string. Your second hint is pracmatic but we have an very fussy internal audit department who seeks for stored passwords in linked tables and ODBC DSN configurations.

Thanks for taking the trouble.

Expert Comment

ID: 2717088
The ADO manual say you can do this:

    ' Open a connection using a DSN and individual
    ' arguments instead of a connection string.
    Set cnn4 = New ADODB.Connection
    cnn4.Open "Pubs", "sa", "pwd"
The above looks like VB syntax, but this may work:

Set cn=Server.CreateObject("ADODB.Connection")
cn.Open strDSN, "SuperBrain", "MegaSecret"
LVL 10

Expert Comment

ID: 2717190
Hello baechlek,

If you change option dbDriverPrompt to dbDriverNoPrompt users are not prompted by connect dialog if all required connect information is given in connect parameters. Why do you need other parameters from ODBC settings? If you don't define them, default values are used.

Btw, if you like to change default settings, you can use API function to modify ODBC registry settings. Here's an example code which adds/modifies foxpro DSN (System DSN page):

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
    (ByVal hwndParent As Long, _
    ByVal fRequest As Integer, _
    ByVal lpszDriver As String, _
    ByVal lpszAttributes As String) As Long
Public Sub AddFoxProSystemDsn()
Dim LngResult As Long

LngResult = SQLConfigDataSource(0, _
   4, _
   "Microsoft FoxPro driver (*.dbf)", _
   "DSN=FoxProTest" & Chr(0) & _
   "DefaultDir=g:\exchange" & Chr(0) & _
   "Description=Fox Pro database description" & Chr(0) & Chr(0))

End Sub

Hope this helps,
LVL 10

Expert Comment

ID: 2717202
Note, you can easily modify the function to suit your needs. After driver name, the rest parameters are setting keys and their values (change them depending the database driver you're using). You could also build a function with API that enums registry values but that's quite complicated task...



Author Comment

ID: 2722057
Hi folks,

I found out a solution for my problem:

Sub EstablishMSAInternalConnection(strDSN As String, strUserID As String, strPassword As String)
' Establish an connection to a data source identified by strDSN for user strUserID with Password strPassword
' to force ms access to make its internal connection to the specified source to prevent driver prompts on initial
' access on linked tables of that source. Also the user is not able to change parameters. All neccessary attributes
' for the connection (which can be driver dependent) are taken from the stored dsn, so there will be no driver
' prompt for unspecified attributes if there is a complete dsn.

Dim wrkODBC As DAO.Workspace
Dim con As DAO.Connection
Dim strConnect As String
  ' find out the complete connect string by establishing a connection only by
  ' the use of dsn, userid and password (all attributes are driver independent!)
  Set wrkODBC = CreateWorkspace(strDSN, strUserID, strPassword, dbUseODBC)
  Set con = wrkODBC.OpenConnection(strDSN, dbDriverNoPrompt)
  ' save the completed connect string
  strConnect = con.Connect
  ' close connection an workspace because they are not needed any more
  ' open database for ms-access
  Call DBEngine.OpenDatabase("", dbDriverNoPrompt, False, strConnect)
End Sub

As I read dittrichs code I thought may ado complets the connect string after opening a connection. This was true. So I tried to find an equivalent in the odbc world. Here it is!

Thank you all for your comments!

Dittrich, is it okay for you to receive 30 points for your hint?

Accepted Solution

drittich earned 50 total points
ID: 2722911
Sure, good luck with your project.

Featured Post

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question