Hi,

I would like to be able to extract the name (eg: db_client.mdb) of the MS Access database that my ODBC is currently pointing to.
The ODBC is known but I frequently change the DB it is pointing to. I use the DB name in my VB6 code a few times to make a DB reference so I want to make this step automatic instead of changing the code.
By the way, I'm already using a constant for the DB name.

Thanks,
Mo

Hi Mo,

The ODBC Installer DLL API exposes a function called SQLConfigDataSource that gives you total control over adding and removing, and modifying existing, user and system DSNs (ODBC Installer is a component of the Driver Manager). To modify the database attribute of an existing Access DSN, you set the DBQ option in the option string argument that the SQLConfigDataSource function takes.

I created a system DSN named AccessDSN and two Access databases located in a C:\Temp directory: DatabaseA.mdb and DatabaseB.mdb. AccessDSN defaults to DatabaseA.mdb.

I then created a VB6 project with 3 controls on a form: a combobox (cboSelectDatabase), a listbox (List1), and a command button (cmdFillList).

Startup: The Form_Load event populates the combobox with my two database names, which fires the cboSelectDatabase_Click event, which initializes the DSN’s DBQ option to DatabaseA.mdb.

User Input: Clicking cmdFillList initially populates List1 with data from DatabaseA.mdb. Selecting DatabaseB.mdb in cboSelectDatabase fires the combobox Click event which sets the DSN’s DBQ option to DatabaseB.mdb. Clicking cmdFillList now populates List1 with data from DatabaseB.mdb.

Place the following in the Form’s general declaration section:

~~~~StartCode~~~~

' Values for the SQLConfigDataSource functions hwndParent
' and fRequest argumets. vbAPINull is a NULL pointer.
Private Const ODBC_CONFIG_SYS_DSN = 5
Private Const vbAPINull As Long = 0

' SQLConfigDataSource is exposed by the ODBCCP32.dll API.
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
          (ByVal hwndParent As Long, ByVal fRequest As Long, _
          ByVal lpszDriver As String, ByVal lpszAttributes As String) _
          As Long

~~~~EndCode~~~~

Here are the event handlers:

~~~~StartCode~~~~

Private Sub Form_Load()

   ' I hard coded the database names to load the combobox
   ' and set the DBQ attribute for the SQLConfigDataSource
   ' function call, but you might want to maintain a db table
   ' of database names and path information for these purposes.

   cboSelectDatabase.AddItem "DatabaseA"
   cboSelectDatabase.AddItem "DatabaseB"
   cboSelectDatabase.Text = cboSelectDatabase.List(0)
   Call cboSelectDatabase_Click

End Sub

Private Sub cboSelectDatabase_Click()

    Dim strDriver As String
    Dim strOptions As String
    Dim intRet As Integer

    List1.Clear

    ' Set DSN Driver
    strDriver = "Microsoft Access Driver (*.mdb)"

    ' Set DSN options delimited by null
    strOptions = "DESCRIPTION=" & cboSelectDatabase.Text & Chr(0)
    strOptions = strOptions & "DSN=AccessDSN" & Chr(0)
    strOptions = strOptions & "DBQ=C:\Temp\" & cboSelectDatabase.Text & Chr(0)

    ' Call SQLConfigDataSource. The ODBC_CONFIG_SYS_DSN
    ' request modifies an existing system DSN.
    intRet = SQLConfigDataSource(vbAPINull, ODBC_CONFIG_SYS_DSN, strDriver, strOptions)

    ' Returns TRUE (1) if modification is successful.
    If intRet <> 0 Then
        MsgBox "DSN modified"
    Else
        MsgBox "DSN modification failed."
    End If

End Sub

Private Sub cmdFillList_Click()

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    ' Use system DSN as connection string argument
    cnn.Open "AccessDSN", "Admin"

    rst.Open "DataTable", cnn

    Do Until rst.EOF
        List1.AddItem rst!FieldB
        rst.MoveNext
    Loop

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

~~~~EndCode~~~~

If you want to modify a user DSN instead of a system DSN, change the fRequest constant value from ODBC_CONFIG_SYS_DSN = 5 to ODBC_CONFIG_DSN = 2. There are also fRequest constants for adding user and system DSN’s (ODBC_ADD_DSN = 1 and ODBC_ADD_SYS_DSN = 4) and for removing user and system DSN’s (ODBC_REMOVE_DSN = 3 and ODBC_ADD_SYS_DSN = 6).

HTH,

Bob

Hi Mo,

If you wanted to just grab the DSN’s current database attribute value out of the registry, you can do that too. Since the DSN I was using in my first post is a System DSN, it is stored in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\AccessDS N. The DBQ attribute is a REG_SZ value of the AccessDSN key, and stores the full path and name of the database. You would need the following:

Place the following API function and constant declarations in a standard module…

~~~~StartCode~~~~

Public Const REG_SZ As Long = 1
Public Const REG_DWORD As Long = 4
Public Const HKEY_LOCAL_MACHINE = &H80000002
Public Const ERROR_NONE = 0
Public Const KEY_QUERY_VALUE = &H1

Declare Function RegCloseKey Lib "advapi32.dll" _
   (ByVal hKey As Long) As Long

Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
    "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
    ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _
    Long) As Long

Declare Function RegQueryValueExString Lib "advapi32.dll" Alias _
    "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
    String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
    As String, lpcbData As Long) As Long

Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias _
    "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
    String, ByVal lpReserved As Long, lpType As Long, lpData As _
    Long, lpcbData As Long) As Long

Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias _
    "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
    String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
    As Long, lpcbData As Long) As Long

~~~~EndCode~~~~

Here’s and command button event that requests the DSN’s current database name (DBQ value) …

~~~~StartCode~~~~

Private Sub Command1_Click()

    Dim strDSNDatabase As String
    strDSNDatabase = QueryValue("SOFTWARE\ODBC\ODBC.INI\AccessDSN", "DBQ")
    MsgBox strDSNDatabase

End Sub

~~~~EndCode~~~~

Here’s the QueryValue function called by the Click event…

~~~~StartCode~~~~

Public Function QueryValue(sKeyName As String, sValueName As String) As String
    Dim lRetVal As Long
    Dim hKey As Long
    Dim vValue As Variant

    lRetVal = RegOpenKeyEx(HKEY_LOCAL_MACHINE, sKeyName, 0, _
                           KEY_QUERY_VALUE, hKey)

    lRetVal = QueryValueEx(hKey, sValueName, vValue)

    RegCloseKey (hKey)

    QueryValue = vValue

End Function

~~~~EndCode~~~~

And here is a wrapper function that QueryValue calls…

~~~~StartCode~~~~

Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _
   String, vValue As Variant) As Long
       Dim cch As Long
       Dim lrc As Long
       Dim lType As Long
       Dim lValue As Long
       Dim sValue As String

       On Error GoTo QueryValueExError

       ' Determine the size and type of data to be read
       lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
       If lrc <> ERROR_NONE Then Error 5

       Select Case lType
           ' For strings
           Case REG_SZ:
               sValue = String(cch, 0)

   lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, _
   sValue, cch)
               If lrc = ERROR_NONE Then
                   vValue = Left$(sValue, cch - 1)
               Else
                   vValue = Empty
               End If
           ' For DWORDS
           Case REG_DWORD:
   lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, _
   lValue, cch)
               If lrc = ERROR_NONE Then vValue = lValue
           Case Else
               'all other data types not supported
               lrc = -1
       End Select

QueryValueExExit:
       QueryValueEx = lrc
       Exit Function

QueryValueExError:
       Resume QueryValueExExit
   End Function

~~~~EndCode~~~~

I've included the bare minimum of constant declarations to read a System DSN. If you wanted to read a User DSN, you would need to include the following constant:

Public Const HKEY_CURRENT_USER = &H80000001

And change your QueryValue function call to point to the right registry key.

HTH,

Bob