Connect to MySQL via ODBC (1 Viewer)

marlan

Registered User.
Local time
Today, 15:54
Joined
Jan 19, 2010
Messages
409
Hi All you experts!

I've developed an Acc2003 App that reads from a ODBC linked table.
This link was created fro DBwindow->Link table. It often opens a window asking for userNmae and Pwd - I wold liek to aviod this wen distributing the App. So I wrote tis code:
Code:
Public Sub linkOdbcTable(DataSource As String, UID As String, PWD As String, dbName As String, ParamArray Tables())
    Dim dbs As Database
    Dim tbl As Variant
    Dim rst As DAO.Recordset
    Dim linked As Boolean
    Dim ConnectionString As String
    
    linked = False
    
    ConnectionString = "ODBC;DSN=" & DataSource & ";" & "DATABASE=" & dbName
    If Len(UID) > 0 Then
        ConnectionString = ConnectionString & ";" & "UID=" & UID & ";PWD=" & PWD
    End If
    ConnectionString = ConnectionString & ";"
    
    For Each tbl In Tables
    
        delTables "ODBC" & tbl 'Deletes Table if exists
        'Create ODBC Connection:
        DoCmd.TransferDatabase acLink, "ODBC Databases", ConnectionString, acTable, tbl, "ODBC" & tbl, False, True  'LANGUAGE=us_english;
        If Not linked Then 'open a connection to speed the process:
            Set rst = CurrentDb.OpenRecordset("ODBC" & tbl)
            linked = True
        End If
    Next tbl
    rst.Close
End Sub
I get Errror 2507: (traslation:) you are using an uninstalled ODBC driver.
I have ODBC installed, I use it!

Any help please?
 

AccessVBANewbie

Registered User.
Local time
Today, 13:54
Joined
Jan 23, 2014
Messages
19
Hi

Have a look at this example:
Code:
ConnectString = "ODBC;DRIVER={MySQL ODBC 5.1 Driver}" _
    & ";SERVER=" & strServerIp _
    & ";DATABASE=" & strDatabaseName _
    & ";UID=" & strUserName _
    & ";PWD=" & strPassword _
    & ";OPTION=2"

and to use the connection as follow:

DoCmd.TransferDatabase acLink, "ODBC Database", ConnectString, acTable, tmpTableName, tmpTableName, False, True
 

marlan

Registered User.
Local time
Today, 15:54
Joined
Jan 19, 2010
Messages
409
Hi, and thanks for your replies!

I still get the same Errror 2507.
I checked: I have ODBC driver 5.1 this is the code I use now: ('DSN' is now 'SERVER', and I have spcified Driver type) :
Code:
    ConnectionString = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & DataSource & ";DATABASE=" & dbName
    If Len(UID) > 0 Then
        ConnectionString = ConnectionString & ";" & "UID=" & UID & ";PWD=" & PWD
    End If
    ConnectionString = ConnectionString & ";OPTION=3;"
But the App seems to not find the driver.
Could it be a reference I'm missing? Reference to what?

I would like to distribute this App, how do I distribute it with reliable ODBC?

Thanks in advance!
 
Last edited:

marlan

Registered User.
Local time
Today, 15:54
Joined
Jan 19, 2010
Messages
409
Hi again, i'm quite soure it is not a Connection string issue.

I establish an ODBC connection frome DB window -> RightClick Link table, using DSN file. (I'm running Acc2003). I now would like to Create a ODBC linked Table by code (DSNless?...), and get error #2507.
 

marlan

Registered User.
Local time
Today, 15:54
Joined
Jan 19, 2010
Messages
409
Thanks, it is great!
I run it once every time the App. is opened, no DSN window...

Thanks!
 

Users who are viewing this thread

Top Bottom