MySQL Forums
Forum List  »  Connector/ODBC

Creation of DSN does not work with SQLConfigDataSource
Posted by: Dominique Couot
Date: July 09, 2009 12:31PM

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

Options: ReplyQuote


Subject
Written By
Posted
Creation of DSN does not work with SQLConfigDataSource
July 09, 2009 12:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.