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

Posted on 2000-04-14
Medium Priority
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...
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.


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 150 total points
ID: 2722911
Sure, good luck with your project.

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

771 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