ODBC DSN setup

[funkyd77]

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...

[Aaron Young]

You could use the ODBC API's, ie.

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.

[Serge]

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.:

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:

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

This will create a SYstem DSN for Access database.

[JHausmann]

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.