[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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...
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

649 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