IPAddr

How to create an ODBC DSN in VB6

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 distribute 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, sDBQ As String, lngRet As Long, hKey As Long
      Dim regValue As String, 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 
          regValue = String$(1024, 0)	' Allocate Space AND Retrieve value of "DBQ" key 
          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 
          RegCloseKey hKey		' close the key
      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
    or
  MakeDSN "DSN Creation Test", "Microsoft Access Driver (*.mdb)", _
    App.Path & "\MyDatabase.mdb", ODBC_ADD_DSN
  

How To Programmatically Create a DSN for SQL Server with VB

This article demonstrates how to programmatically create a Data Source Name (DSN) for SQL Server using Visual Basic. The technique discussed in this article uses Windows Application Programming Interface (API) functions to create and manipulate entries in the Windows Registry.

DSNs are usually created through the ODBC Data Source Administrator window, which is accessible from the Windows Control Panel (or Administrator Tools in Windows 2000). Other techniques that provide access to ODBC-compliant databases include using RegisterDatabase (a Data Access Object (DAO) method), using the SQLConfigDataSource ODBC API function, or using a DSN-less connection string.

However, it is possible to establish a new DSN by manually creating and manipulating values in the Windows Registry. The following technique uses the RegCreateKey, RegSetValueEx, and RegCloseKey API functions to create a system DSN for a SQL Server database.

Step-by-Step Procedures

1. Open a new Visual Basic project. Form1 is created by default. Put a CommandButton on Form1 (Command1), and put the following code in the General Declarations section of the code for Form1: Option Explicit

    Private Const REG_SZ = 1    ' Constant for a string variable type.
    Private Const HKEY_LOCAL_MACHINE = &H80000002

    Private Declare Function RegCreateKey Lib "advapi32.dll" Alias "RegCreateKeyA" _
      (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
    Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" _
      (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, _
      ByVal dwType As Long, lpData As Any, ByVal cbData As Long) As Long
    Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long						
    

2. Place the following code in the click event of the Command1 button on Form1:
Change the values of the DataSourceName, DatabaseName, Description, DriverPath, LastUser, and Server variables as appropriate for your environment. Any of the drivers listed on the ODBC Drivers tab of the ODBC Data Source Administrator window can be used as part of the DriverPath variable. All of these drivers can be found in C:\Windows\System for Windows 95 or Windows 98 machines and C:\Winnt\System32 for Windows NT.


  Private Sub Command1_Click()

    Dim DataSourceName As String, DatabaseName As String, Description As String
    Dim DriverPath As String, DriverName As String, LastUser As String,  
    Dim Regional As String, Server As String, lResult As Long, hKeyHandle As Long

    ' Specify the DSN parameters.
    DataSourceName = "{the name of your new DSN}"
    DatabaseName = "{name of the database to be accessed by the new DSN}"
    Description = "{a description of the new DSN}"
    DriverPath = "{path to your SQL Server driver}"
    LastUser = "{default user ID of the new DSN}"
    Server = "{name of the server to be accessed by the new DSN}"
    DriverName = "SQL Server"

    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
      DataSourceName, hKeyHandle)	   ' Create the new DSN key.

    ' Set the values of the new DSN key.
    lResult = RegSetValueEx(hKeyHandle, "Database", 0&, _
      REG_SZ, ByVal DatabaseName, Len(DatabaseName))
    lResult = RegSetValueEx(hKeyHandle, "Description", 0&, _
      REG_SZ, ByVal Description, Len(Description))
    lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, _
       REG_SZ, ByVal DriverPath, Len(DriverPath))
    lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, _
      REG_SZ, ByVal LastUser, Len(LastUser))
    lResult = RegSetValueEx(hKeyHandle, "Server", 0&, _
      REG_SZ, ByVal Server, Len(Server))
    lResult = RegCloseKey(hKeyHandle)		' Close the new DSN key.
    ' Open ODBC Data Sources key to list the new DSN in the ODBC Manager
    ' Specify the new value, Close the key.
    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
    lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, _
      REG_SZ, ByVal DriverName, Len(DriverName))
    lResult = RegCloseKey(hKeyHandle)
  End Sub
  

3. Run the project and click on the Command1 command button. Then open up the ODBC Data Source Administrator from the Control Panel (or Administrator Tools in Windows 2000). Your new DSN will appear along with the other system DSNs that you have already created.