Home » Tip Bank » XML » XML with Visual Basic and VB.NET

Language: VB5,VB6
Expertise: Intermediate

Nov 6, 1999

If you're developing a VB database application, you're probably using a DSN (data source name) because it makes the access to your database file easier. Of course, when you distribuite your application, you must create the DSN.

There are some installation programs that offers the possibility to create the DSN during the setup process, but unfortunately the Setup Wizard distributed with VB5 or earlier versions, or the Package and Deployment Wizard that comes with VB6 doesn't offer this option. Therefore you must create the DSN manually.

This doesn't have to be hard, though, and can be done programmatically, for example the first time you run your app. The SQLConfigDataSource ODBC API function is what you need, in that it allows you to create, modify or delete a DSN:

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

The first argument is the parent window's handle, and can be null (0& in VB). The second argument specifies the action to be performed: 1 to add, 2 to config and 3 to delete a DSN. The third argument is a string that specifies the driver to use: for example, to create a DSN from an Access database you must pass "Microsoft Access Driver (*.mdb)". The last parameter is a string that contains many information about the DSN, such as the DSN name, the source database file, the user name and the password (if any).

Here is function that makes easier the process to create or delete a DSN:

' Registry API functions
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
Private Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long
Private Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" _
    (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, _
    ByVal samDesired As Long, ByRef phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32" Alias _
    "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
    ByVal lpReserved As Long, ByRef lpType As Long, ByVal lpData As String, _
    ByRef lpcbData As Long) As Long

Const REG_SZ = 1
Const KEY_ALL_ACCESS = &H2003F
Const HKEY_CURRENT_USER = &H80000001

Public Const ODBC_ADD_DSN = 1      ' Add data source
Public Const ODBC_REMOVE_DSN = 3   ' Delete data source

Sub MakeDSN(ByVal sDSN As String, ByVal sDriver As String, _
    ByVal sDBFile As String, ByVal lAction As Long)

    Dim sAttributes As String
    Dim sDBQ As String
    Dim lngRet As Long
    
    Dim hKey As Long
    Dim regValue As String
    Dim valueType As Long
 
    ' query the Registry to check whether the DSN is already installed
    ' open the key
    If RegOpenKeyEx(HKEY_CURRENT_USER, "Software\ODBC\ODBC.INI\" & sDSN, 0, _
        KEY_ALL_ACCESS, hKey) = 0 Then
        ' zero means no error => Retrieve value of "DBQ" key
        regValue = String$(1024, 0)                             
               ' Allocate Variable Space
        If RegQueryValueEx(hKey, "DBQ", 0, valueType, regValue, _
            Len(regValue)) = 0 Then
            ' zero means OK, so we can retrieve the value
            If valueType = REG_SZ Then
                sDBQ = Left$(regValue, InStr(regValue, vbNullChar) - 1)
            End If
        End If
        ' close the key
        RegCloseKey hKey
    End If
    
    ' Perform the action only if we're adding a DSN that doesn't exist
    ' or removing and existing DSN
    If (sDBQ = "" And lAction = ODBC_ADD_DSN) Or (sDBQ <> "" And lAction = _
        ODBC_REMOVE_DSN) Then
        
        ' check that the file actually exists
        If Len(Dir$(sDBFile)) = 0 Then
            MsgBox "Database file doesn't exist!", vbOKOnly + vbCritical
            Exit Sub
        End If
        sAttributes = "DSN=" & sDSN & vbNullChar & "DBQ=" & sDBFile & vbNullChar
        lngRet = SQLConfigDataSource(0&, lAction, sDriver, sAttributes)
    End If
End Sub

Notice that there is a call to the GetKeyValue function. You can add this routine if you install the VB Template Manager Add-in. This add-in comes with VB6, but VB5 owners can download it from Microsoft web site. After you install the add-in, just issue the Tools | Add Code Snippet menu command, and select the Registry Access item in the dialog that appears.

Here's an example to create a DSN, called "DSN Creation Test", that points to a Access database:

sDriver = "Microsoft Access Driver (*.mdb)"
sName = "DSN Creation Test"
sFile = App.Path & "\MyDatabase.mdb"
MakeDSN sName, sDriver, sFile, ODBC_ADD_DSN