Dynamic DSN using VB6 for SQL server

Question

I want to create Dynamic DSN in my VB6 Application. I have tried the following code in my MODULE. using the following Microsoft link- http://support.microsoft.com/kb/171146.

My code is here-

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 Function PrepareDSN(strServerName As String, strDBName As String, strDSN As String, strDBUser As String, strDBUserPassword As String) As Boolean
On Error GoTo error_hdl
Dim boolError As Boolean
Dim strDSNString As String
PrepareDSN = False

strDSNString = Space(MAX_BUFFER_SIZE)
strDSNString = ""
strDSNString = strDSNString & "DSN=" & strDSN & Chr(0)
strDSNString = strDSNString & "DESCRIPTION=" & "DSN Created Dynamically On " & CStr(Now) & Chr(0)
strDSNString = strDSNString & "Server=" & strServerName & Chr(0)
strDSNString = strDSNString & "DATABASE=" & strDBName & Chr(0)
strDSNString = strDSNString & Chr(0)



 If Not CBool(SQLConfigDataSource(0, _
                        ODBC_ADD_DSN, _
                        ODBCDriverDescription, _
                        strDSNString)) Then
    boolError = True
    MsgBox ("Error in PrepareDSN::SQLConfigDataSource")


  End If

If boolError Then
    Exit Function
End If
PrepareDSN = True
Exit Function

error_hdl:
    MsgBox "PrepareDSN_ErrHandler::" & err.Description
End Function

Here my function "SQLConfigDataSource" always returning false. Please suggest.

Purely out of interest, is there a need to create a DSN, or can you actually get away with a DSNless connection? — Paul, Jul 12 '13 at 09:02
yes Westie, i also tried to implement the same but its already existing 900 reports where they were using the DSN to design the report, i think if i go for DSN-less then i have to remove all fields in the report and bind it again, is it so? or my thinking is wrong? — Teju MB, Jul 15 '13 at 01:30

score 2 · Accepted Answer · answered Jul 12 '13 at 07:41

Two things are missing.

1) You did not define the request type ODBC_ADD_DSN 2) Database driver is not set

Here is the modified code, which I used successfully to connect to my database.

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 Const ODBC_ADD_DSN = 1 'Define request type
'!!!!!!!!!!
Public Function PrepareDSN(strServerName As String, strDBName As String, strDSN As String, strDBUser As String, strDBUserPassword As String) As Boolean
On Error GoTo error_hdl
Dim boolError As Boolean
Dim strDSNString As String
PrepareDSN = False

strDSNString = Space(MAX_BUFFER_SIZE)
strDSNString = ""
strDSNString = strDSNString & "DSN=" & strDSN & Chr(0)
strDSNString = strDSNString & "DESCRIPTION=" & "DSN Created Dynamically On " & CStr(Now) & Chr(0)
strDSNString = strDSNString & "Server=" & strServerName & Chr(0)
strDSNString = strDSNString & "DATABASE=" & strDBName & Chr(0)
strDSNString = strDSNString & Chr(0)


'!!!!!!!!!!
Const Driver As String = "SQl Server" 'Set driver descr
'!!!!!!!!!!
 If Not CBool(SQLConfigDataSource(0, _
                         ODBC_ADD_DSN, _
                        Driver, _
                        strDSNString)) Then
    boolError = True
    MsgBox ("Error in PrepareDSN::SQLConfigDataSource")


  End If

If boolError Then
    Exit Function
End If
PrepareDSN = True
Exit Function

error_hdl:
    MsgBox "PrepareDSN_ErrHandler::" & Err.Description
End Function

@ Teju MB You should add an `Option Explicit` statement to the top of you module in order to force variable declaration. You have used `ODBC_ADD_DSN` but it is not declared anywhere. `Option Explicit` would have alerted you to that. — jac, Jul 12 '13 at 17:39
+1, Thanks a lot Luke and Jac, I just want to clarify whether 1.will it check before creating as the DSN exists or not if exists then will it override? 2. It has created USER DSN, what about System DSN? — Teju MB, Jul 15 '13 at 02:17

Dynamic DSN using VB6 for SQL server

1 Answers1