ODBC connection string can't connect- Thank for any help error 3151

[ellenr]

Windows 7, msaccess 2010 32bit. My db has been running several years and connecting to BE mysql with a mysql connector dsn. Now I need to deploy it to more users in distant locations, so I am trying to connect using vba connection string for a dsn-less connection. I cannot get it to connect and it is throwing a 3151 error. The techs at this particular host assure me they can see nothing wrong from the server end. I have another db that is able to connect dsn-lessly to a different mysql db on the same server, so I don't think it is a firewall problem. I don't even know where to start solving this--also my research hasn't enlightened me as to the 3151 error. Thanks for any help!

[Rawb]

Try reading through this link. Apparently ODBC connection errors can throw, essentially, multiple errors with the first one being just a generic "ODBC call failed". The link shows how you can work through the list of errors and get more specific information on your problem. It's written for Access 2000, but hopefully it'll work in 2010 too.

http://support2.microsoft.com/defaul...b;EN-US;209855

[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 code setting up the connection string and the line: "Set MyODBCdb = DBEngine(0).OpenDatabase("DBname", dbDriverNoPrompt, True, MyConnectionString)"

Code:
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.