ODBC DSN setup

Hi, i have an app that accesses a database using a odbc dsn that i set up manually <from control panel> as a system dsn.

When i distribute my application, how can i setup the required dsns on the pcs being installed to automatically. I don't want to have to go around to every computer and manually set up the DSNs <esp since some of them are in other countries, tho i could go a holiday>

If anyone has dealt with this, or done this before then i'd love to hear about it

Thanks...

You could use the ODBC API's, ie.

Code:

---

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwndParent As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
Private Const ODBC_ADD_DSN = 1          ' Add a new data source.
Private Const ODBC_CONFIG_DSN = 2      ' Configure (edit) existing data source.
Private Const ODBC_REMOVE_DSN = 3      ' Remove existing data source.
Private Const ODBC_ADD_SYS_DSN = 4      ' Add data source
Private Const ODBC_CONFIG_SYS_DSN = 5  ' Configure (edit) data source
Private Const ODBC_REMOVE_SYS_DSN = 6  ' Remove data source

Private Sub Command1_Click()
    On Error Resume Next
    If SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, _
    "Microsoft Visual FoxPro Driver", _
    "DSN=" & "New DSN Name" & Chr(0) & _
    "DESCRIPTION=" & "New DSN Description" & Chr(0) & _
    "SOURCETYPE=DBC" & Chr(0) & _
    "SOURCEDB=" & "Path and Filename of DBC" & Chr(0) & _
    "BACKGROUNDFETCH=YES" & Chr(0) & _
    "NULL=YES" & Chr(0) & _
    "DELETED=YES" & Chr(0) & _
    "EXCLUSIVE=NO" & Chr(0) & Chr(0)) Then
        MsgBox "DSN Created"
    Else
        MsgBox "Create Failed"
    End If
End Sub

---

Obvously I used this to create aVFP DSN, but you can easily adapt it to whatever DSN type you require.

I don't think Package Deployment Wizard has that feature. [/b]InstallShield[/b] and Wise both do. But any way, what you can do is to write a code to register DSN programmatically. Here's the routine I wrote a while back. Add a module to your project and copy this code.:

Code:

---

Const ODBC_ADD_SYS_DSN = 4      'Add System data source
Const ODBC_ADD_DSN = 1      'Add User data source
Const ODBC_CONFIG_SYS_DSN = 5    'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6    'Remove data source

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

Public Enum enumDSN_Type
    eUserDSN = ODBC_ADD_DSN
    eSystemDSN = ODBC_ADD_SYS_DSN
End Enum
Public Function CreateDSN(pDSNName As String, pDBPath As String, pDSNType As enumDSN_Type) As Boolean
    Dim lngRetVal
    Dim strDriver As String
    Dim strAttributes As String
   
    CreateDSN = False
   
    strDriver = "Microsoft Access Driver (*.mdb)" & Chr(0)
    strAttributes = "DSN=" & pDSNName & Chr(0)
    strAttributes = Attributes & "Uid=Admin" & Chr(0) & "pwd=" & Chr(0)
    strAttributes = Attributes & "DBQ=" & pDBPath & Chr(0)
    lngRetVal = SQLConfigDataSource(0, pDSNType, strDriver, strAttributes)
   
    'If the Return Value 1 then
    'DataSource is created succesefully
    'otherwise it has falied
    If lngRetVal = 1 Then
        CreateDSN = True
    End If
End Function

---

You can call this routine like this:

Code:

---

Private Sub Command1_Click()
    Call CreateDSN("MyDSN", "C:\MyDB.mdb", eSystemDSN)
End Sub

---

This will create a SYstem DSN for Access database.

If you're using VB6, you can call the CreateDSN function by adding a reference to the ODBC Driver & Data Source Name Functions. You can also use the function GetDataSourceList to check to see if the DSN you want is present.