Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-24-2014, 02:51 AM   #1
marlan
Newly Registered User
 
Join Date: Jan 2010
Location: Jerusalm metropolitan
Posts: 400
Thanks: 15
Thanked 35 Times in 34 Posts
marlan is on a distinguished road
Connect to MySQL via ODBC

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?

marlan is offline   Reply With Quote
Old 01-24-2014, 02:54 AM   #2
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,066 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Connect to MySQL via ODBC

See if the Connection String might be a problem : http://www.connectionstrings.com/mysql/
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 01-24-2014, 03:26 AM   #3
AccessVBANewbie
Newly Registered User
 
Join Date: Jan 2014
Posts: 19
Thanks: 4
Thanked 0 Times in 0 Posts
AccessVBANewbie is on a distinguished road
Re: Connect to MySQL via ODBC

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

AccessVBANewbie is offline   Reply With Quote
Old 01-25-2014, 04:14 PM   #4
marlan
Newly Registered User
 
Join Date: Jan 2010
Location: Jerusalm metropolitan
Posts: 400
Thanks: 15
Thanked 35 Times in 34 Posts
marlan is on a distinguished road
Re: Connect to MySQL via ODBC

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 by marlan; 01-26-2014 at 03:20 AM.
marlan is offline   Reply With Quote
Old 01-26-2014, 10:09 AM   #5
marlan
Newly Registered User
 
Join Date: Jan 2010
Location: Jerusalm metropolitan
Posts: 400
Thanks: 15
Thanked 35 Times in 34 Posts
marlan is on a distinguished road
Re: Connect to MySQL via ODBC

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 is offline   Reply With Quote
Old 01-26-2014, 10:18 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,064
Thanks: 79
Thanked 2,003 Times in 1,951 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Connect to MySQL via ODBC

See if this helps. Doug is the man.
http://www.accessmvp.com/djsteele/DSNLessLinks.html
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
marlan (01-29-2014)
Old 01-30-2014, 12:53 AM   #7
marlan
Newly Registered User
 
Join Date: Jan 2010
Location: Jerusalm metropolitan
Posts: 400
Thanks: 15
Thanked 35 Times in 34 Posts
marlan is on a distinguished road
Re: Connect to MySQL via ODBC

Thanks, it is great!
I run it once every time the App. is opened, no DSN window...

Thanks!


marlan is offline   Reply With Quote
Reply

Tags
dsn , dsnless , mysql , odbc

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Connect To Remove MySQL Server abbaddon223 Modules & VBA 1 07-18-2013 08:35 PM
Question How to connect Ms Access (FE) to MySQL (BE) budzprog General 2 08-07-2011 05:34 PM
Access Connect To Remote MySQL Db CharlesWh Modules & VBA 2 05-12-2010 01:50 PM
How to connect VB to MySQL Arvin Forms 8 02-22-2009 11:18 AM
How to connect VB to MySQL Arvin Modules & VBA 9 02-22-2009 10:48 AM




All times are GMT -8. The time now is 03:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World