Binaryworld - Create and delete DSN at runtime ... [ VB -> ODBC ]

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:

Click here to copy the following block

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 the code snippet which demonstrate how to add/remove DSN for SQL Server. You can easily use the same code for MS Access DSN or any other ODBC Driver. Only thing you need to change is Driver name and Attributes.

Note : For security reason you can use TRUSTED_CONNECTION=true but can not specify password in attribute string for SQL Server ODBC while calling SQLConfigDataSourceAPI. You can pass username and password when actually connecting to SQL Server DSN as shown below.

Click here to copy the following block

Function TestDSNConnection(DSNName) As Boolean
  Dim cn
  Dim rs
  Set cn = CreateObject("ADODB.Connection")
  cn.Open ("DSN=" & DSNName & ";UID=sa;PWD=xxxxxx")

  MsgBox "Connection Test Ok"

  cn.Close

  TestDSNConnection = True

  Exit Function
errHandler:
  MsgBox "TestDSNConnection : " & Err.Description, vbCritical
End Function

Step-By-Step Example

- Create a standard exe project
- Add 2 command button controls on form1
- Add the folloing code in form1 and run the project

Click here to copy the following block

Option Explicit

'Constant Declaration
Private Const ODBC_ADD_DSN = 1               ' Add data source
Private Const ODBC_CONFIG_DSN = 2             ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3             ' Remove data source
Private Const vbAPINull As Long = 0            ' NULL Pointer

'Function Declare
#If Win32 Then

  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
#Else
  Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _
      (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
      lpszDriver As String, ByVal lpszAttributes As String) As Integer
#End If

'//Add DSN
Private Sub Command1_Click()
  #If Win32 Then
    Dim intRet As Long
  #Else
    Dim intRet As Integer
  #End If
  Dim strDriver As String
  Dim strAttributes As String

  'Set the driver to SQL Server because it is most common.
  strDriver = "SQL Server"

  'Set the attributes delimited by null.
  'See driver documentation for a complete
  'list of supported attributes.

  'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc_d_4x4k.asp
  'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsetting_options_programmatically_for_the_access_driver.asp
  'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetmicrosoft_desktop_database_drivers.asp
  'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcodbc_drivers_overview.asp

  strAttributes = "SERVER=(local)" & Chr$(0)
  strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0)
  strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)
  strAttributes = strAttributes & "DATABASE=pubs" & Chr$(0)
  strAttributes = strAttributes & "Trusted_Connection=true" & Chr$(0)

  '//For microsoft Access
  'strDriver = "Microsoft Access Driver (*.mdb)" & Chr(0)
  'strAttributes = "DSN=" & DSNName & Chr(0) & "Uid=Admin" & Chr(0) & "pwd=" & Chr(0) & "DBQ=" & DataBasesName & Chr(0) & "Description=" & Description & Chr(0)

  'To show dialog, use Form1.Hwnd instead of vbAPINull.
  intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
      strDriver, strAttributes)
  If intRet Then
    MsgBox "DSN Created"
    Call TestDSNConnection("DSN_TEMP")
  Else
    MsgBox "Create Failed"
  End If

End Sub

Function TestDSNConnection(DSNName) As Boolean
  Dim cn
  Dim rs
  Set cn = CreateObject("ADODB.Connection")
  cn.Open ("DSN=" & DSNName)

  MsgBox "Connection Test Ok"

  cn.Close

  TestDSNConnection = True

  Exit Function
errHandler:
  MsgBox "TestDSNConnection : " & Err.Description, vbCritical
End Function

'//Remove DSN
Private Sub Command2_Click()
  #If Win32 Then
    Dim intRet As Long
  #Else
    Dim intRet As Integer
  #End If

  Dim strDriver As String
  Dim strAttributes As String

  'Set the driver to SQL Server because most common.

  strDriver = "SQL Server"
  'Set the attributes delimited by null.
  'See driver documentation for a complete list of attributes.
  strAttributes = "DSN=DSN_TEMP" & Chr$(0)

  'To show dialog, use Form1.Hwnd instead of vbAPINull.
  intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, _
      strDriver, strAttributes)
  If intRet Then
    MsgBox "DSN Deleted"
  Else
    MsgBox "Delete Failed"
  End If
End Sub

Private Sub Form_Load()
  Command1.Caption = "Add DSN"
  Command2.Caption = "Remove DSN"
End Sub