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

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.

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

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

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.

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"

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

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

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?