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 functionsPrivate Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _    hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, _    ByVal lpszAttributes As String) As LongPrivate Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As LongPrivate 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 LongPrivate 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 LongConst REG_SZ = 1Const KEY_ALL_ACCESS = &H2003FConst HKEY_CURRENT_USER = &H80000001Public Const ODBC_ADD_DSN = 1      ' Add data sourcePublic Const ODBC_REMOVE_DSN = 3   ' Delete data sourceSub 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, "SoftwareODBCODBC.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 IfEnd 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