Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

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

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?
0
baechlek
Asked:
baechlek
  • 3
  • 3
  • 2
  • +1
1 Solution
 
cymbolicCommented:
Check out HKEY_CURRENT_USER\SOftware\ODBC\ODBC.INI
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.
0
 
baechlekAuthor Commented:
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
Kai
0
 
drittichCommented:
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...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
baechlekAuthor Commented:
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.
0
 
drittichCommented:
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"
0
 
paaskyCommented:
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,
Paasky
0
 
paaskyCommented:
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...

Paasky

0
 
baechlekAuthor Commented:
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
  con.Close
  wrkODBC.Close
 
  ' 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?
0
 
drittichCommented:
Sure, good luck with your project.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now