Creation of DSN does not work with SQLConfigDataSource
I'm modifying an existing MS Access application by externalizing the tables to a MySQL 5 server. When creating the DSN manually though MS ODBC Admin I have no problem and all is perfect. However, the application is installed at various sites for various customers and setting up manually DSN is not an option.
I wrote (inspired by a lot of posts) a connection script in VBA to create and remove DSN as needed, using SQLConfigDataSource. The creation works when using MSSQL (sic), but not when using the MySQL ODBC driver. The removal process works fine.
What am I missing?
Script is as follow:
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 ODBC_ADD_SYS_DSN = 4 ' Add System DSN
Private Const ODBC_CONFIG_SYS_DSN = 5 'Configure (edit) data source
Private Const ODBC_REMOVE_SYS_DSN = 6 ' Remove System DSN
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
Private Sub CreateDSN()
#If Win32 Then
Dim intRet As Long
#Else
Dim intRet As Integer
#End If
Dim strDriver As String
Dim strAttributes As String
strDriver = ""
strAttributes = ""
'Set the driver to SQL Server because it is most common.
strDriver = "MySQL ODBC 5.1 Driver"
'strDriver = "SQL Server"
'Set the attributes delimited by null.
'See driver documentation for a complete
'list of supported attributes.
strAttributes = strAttributes & "DSN=mydsn" & Chr$(0)
strAttributes = strAttributes & "DESCRIPTION=mydescription" & Chr$(0)
strAttributes = strAttributes & "SERVER=myserver" & Chr$(0)
strAttributes = strAttributes & "PORT=3306" & Chr$(0)
strAttributes = strAttributes & "USER=myuser" & Chr$(0)
strAttributes = strAttributes & "PASSWORD=mypassword" & Chr$(0)
strAttributes = strAttributes & "DATABASE=mydatabase" & Chr$(0)
strAttributes = strAttributes & Chr$(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN, strDriver, strAttributes)
If intRet Then
MsgBox "DSN Created"
Else
MsgBox "Create Failed"
End If
End Sub
Private Sub RemoveDSN()
#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 = "MySQL ODBC 5.1 Driver"
'Set the attributes delimited by null.
'See driver documentation for a complete list of attributes.
strAttributes = "DSN=mydsn" '& Chr$(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_SYS_DSN, strDriver, strAttributes)
If intRet Then
MsgBox "DSN Deleted"
Else
MsgBox "Delete Failed"
End If
End Sub
The intRet variable all return False and the DSN is not created....
Software in use:
Winxp SP3
Access 2000/2003/2007
Mysql 5.0
ODBC Mysql 5.1.6 (beta)
Thanks for the help,
Dom