Setting up a Data Source Name(DSN) programmatically in VB/VBA

Normally in order to create an ODBC data source name (DSN) you have to, in NT4 anyway, do it via Start->Settings->ControlPanel->Data Sources(ODBC). Once here you click on the ADD button and fill in the values on-screen as required. However this is no use if you are sending a program to clients and want the DSN to be set up automatically. I had just this problem recently. I need to have a text Driver DSN set up on my clients machine but didn't want them to have to do it as this is potentially a very error prone task. The good news is that it can be done programmatically. In the example below a text driver USER DSN is going to be set up but the example can be tweaked to set up any other kind of DSN you might require. Here's the code.


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

Sub Main()

Dim intret As Long
Dim atts As String
Dim strdriver As String

strdriver = "Microsoft Text Driver (*.txt; *.csv)"
atts = "DSN=" & "Text Files"
intret = SQLConfigDataSource(0, 1, strdriver, atts)

If intret Then
Msgbox("DNS created sucessfully")
Else
MsgBox ("There was a problem setting up the DSN, please contact support")
End If

End Sub