odbc mysql won't connect - help!

[ellenr]

I am at my wit's end! Several years ago I set up an Access DB with mysql backend--works great for a dozen users. Now I have set up a DB for a different group, and since there will be several users, I uploaded the files to a different mysql db. Connections work perfectly on my test computer. Went today to install it on another user's laptop and cannot get it to connect. That computer had odbc connections to the original DB and is still able to open and use it. I thought connecting the new program would be as simple as it was on my test computer. I opened data sources (ODBC) in Control panel, clicked on System DSN tab and entered the the credentials for the location of the new db. Clicked Test and got a "Successful connection" back. And yes, I have even rebooted. The development computer is Windows 7, Access 2010 and the other computer is Windows 7 running Access 2010 Runtime. I did try setting up Administrator and loaded the connection credentials there, but it didn't help.

Any ideas would be most appreciated! :banghead:

 

[DavidAtWork]

have you considered using DSN-less connection strings. This would negate the need to set up ODBC on local machines (much less maintenance), also easy to switch the FE app from DEV to QA to PROD simply by changing the server/database names

David

 

[ellenr]

How do I do that? Sounds like a wonderful solution.

Ok, I have now searched and found lots of code and the following connection string: "Driver={MySQL ODBC 3.51 Driver}; Server=servername; Database=dbname; UID=username; PWD=password; Option=3"

I need some serious hand-holding here! I can fill in the blanks but don't know where to put it. And, do I need other code along with it? Thank you.

 

[jdraw]

Doug Steele is the guru on DSNless connections.
see http://www.accessmvp.com/djsteele/DSNLessLinks.html

Here's more Access/MySQL connector

 

[ellenr]

OMG! It worked! BIG thank you to both DavidAtWork and jdraw! This will be so easy to distribute.

 

[DavidAtWork]

you're welcome

 

[ellenr]

argh! With your help I had no problem connecting and deploying a db using dns-less connection string for mysql db. I am currently attempting to do the same for another db which has been running several years without a problem, by setting up odbc connector on each computer. Now I need to deploy it to more users, most of whom live at a distance, so changing it to dns-less connections seems imperative. I cannot get it to connect. I have triple-checked the spelling, etc., with no success. It tells me "ODBC--connection to 'xxx' failed.

MyConnectionString = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
    "Server=xx.xxx.xxx.xxx;Database=" & "DBname" & ";" & _
    "User=" & "DBusername" & ";" & _
    "Password=" & "DBpassword" & ";" & _
    "Option=3;"

Set MyODBCdb = DBEngine(0).OpenDatabase("DBname", dbDriverNoPrompt, True, MyConnectionString)

I have no idea where to look for errors or how to trouble shoot. Any help would be appreciated.

 

[John.Woody]

Could it be a firewall blocking the connection, or router config port forwarding to the server?

 

[ellenr]

John, I don't think so. If I go back and set up the dns it works fine. Also, my other db that uses dns-less connection works just fine. They use different hosts, but the one that works was set up originally at the host that I can't get to work now.

Edit: I just turned off my firewall and tried it--no go, so that isn't the problem.

 

[ellenr]

I have searched and researched and haven't made any progress--I am getting anxious, as my timetable will soon expire. I have tried both the supplied server name and the ip address for the server. I can successfully use either when I set up the dsn. When I single step through the code in my previous post, it steps all the way beyond the line:

Set MyODBCdb = DBEngine(0).OpenDatabase("DBname", dbDriverNoPrompt, True, MyConnectionString)

It doesn't error until the first time it touches a linked table reference several steps later. I can set up the dsn and open the db leaving the line above intact and it doesn't seem to mind. I wouldn't be so flummoxed if I weren't able to connect via regular dsn, or if I didn't have another db connecting (to a different host) as a dns-less connection. I smugly thought I knew what I was doing! Please help!

I have rebooted, repaired and compacted, imported all into a fresh db--no help. If I set up the dns and open the db, it works--while it is open, I can delete that dns setup, and the db is still happy. I guess it is ok once the connection is made.

 

[John.Woody]

Maybe you could dump your MySQL db in to a sql file and load in on to the server that works with the other db, make the appropriate location changes in your code and see if it works. It will rule in or out a server related issue.

 

[ellenr]

Still no luck. To recap: I can set up a Mysql datasource thru Data Source Administrator and the odbc connection works. I cannot make the connection via vba connection string. The error number is 3151. I have been on the phone with the techs at the host. They can telnet and establish a connection but I cannot. Mine apparently connects then immediately tells me that the connection is lost. I have tried to telnet on two computers, one of which is new and running Windows 8. I don't think it is router or firewall on my home connection to the internet since another db can connect via similar vba connection string, albeit to a different host.

I hope some of you with better brains and more experience will chime in on this. Thank you

 

[ellenr]

I have finally solved my problem. I had to delete all odbc-linked tables from the db. Then, I had to place the following code in the opening vba following the line: "Set MyODBCdb = DBEngine(0).OpenDatabase("DBname", dbDriverNoPrompt, True, MyConnectionString)"

Dim tdfCurrent As Variant  'do the following for any new table

Set tdfCurrent = CurrentDb.CreateTableDef("Docs")
tdfCurrent.Connect = MyConnectionString
tdfCurrent.SourceTableName = "Docs"
CurrentDb.TableDefs.Append tdfCurrent

The last four lines had to be completed for each one of the tables to be linked. After the code runs once and the table links appear in the table list, this code must be removed.

I hope this helps someone else struggling with the same problem.