Keep losing ODBC connection to mySql

[latex88]

I am excited now that I have moved my database to a new hosting company that allows remote connection to my mySql database. I was able to link the tables via ODBC connection. However, I can only view the data for a short period of time before I get that "ODBC --called failed" error message.

I can exit the database and reopen it immediately and the connection is there. What can I do to keep the connection?

 

[Banana]

When you get that "ODBC -- call failed", what do you get when you read all error messages contained in the DAO.Errors collection?

Look at this if you're unfamiliar with DAO.Errors and how to use it.

 

[latex88]

When you get that "ODBC -- call failed", what do you get when you read all error messages contained in the DAO.Errors collection?

Look at this if you're unfamiliar with DAO.Errors and how to use it.

Below is the error message I received.

ODBC Error
2006
[MySQL][ODBC 5.1 Driver][mysqld-5.1.52]MySQL server has gone away
ODBC Error
3146
ODBC--call failed.

 

[Banana]

Okay, I thought that was the case - I helped a poster with similar problem. See if this helps you also.

 

[latex88]

I just chatted with my hosting company. The tech told me that because I am on a shared server, my wait_timeout=3600, so it sounds like it's being timed out.

I can use Navicat, an application that can query the database. I don't quite understand why that connection stays on much much longer than MS Access.

 

[Banana]

Possibly only because Navicat sets the timeout to a different setting. If you look at the link I cited above and note the last post, we basically do the same thing, setting the timeout to a bigger value.

 

[latex88]

OK. It took awhile to read the entire thread, but basically he suggests the following

Create a pass through query with the following properties:

Connect property:
ODBC;DRIVER={MySQL ODBC 5.1 Driver};SERVER=xxx.xxx.xxx.xxx;DATABASE=YourDatabaseName;OPTION=35;INITSTMT=SET @@wait_timeout=28800;UID=YourUserName;PWD=YourPassword

SQL property:
SELECT @@wait_timeout;

Where I don't understand is where to apply these settings. Is that coded in the module or these are the settings in the ODBC Data Source Administrator? If it's the former, how to code exactly, just copy and paste to a module? If it's the latter, where are the settings at? I don't see the wait_timeout, OPTION, or INITSTMT parameters.

Sorry if I sound ignorant.

 

[Banana]

Those settings go into a passthrough query you create in Access and that query should be the very first thing you do when you open Access. The settings like OPTION and INITSTMT are MySQL-specific parameters that you write in the connection string.

So basically, in Access, you create a new blank query, set it as "Passthrough Query" then fill in the Connect Str property and use the SQL.

Just in case you want to know why we use passthrough query, maybe this will provide the background information: Link - notice especially figure 5 where it show you to put the connect property.

Let me know if this helps.

 

[latex88]

Those settings go into a passthrough query you create in Access and that query should be the very first thing you do when you open Access. The settings like OPTION and INITSTMT are MySQL-specific parameters that you write in the connection string.

So basically, in Access, you create a new blank query, set it as "Passthrough Query" then fill in the Connect Str property and use the SQL.

Just in case you want to know why we use passthrough query, maybe this will provide the background information: Link - notice especially figure 5 where it show you to put the connect property.

Let me know if this helps.

Again, sorry for sounding ignorant, but when you refer to a query, you do mean query, not a form? If it's a query, how do you ensure that it's the first thing it runs when you open Access?

The VBA referenced in the link you provided, I essentially copy and paste to a module and change the setting parameters?

If you can guide me with a "Connection for Dummies" version, I would greatly appreciate it.

 

[Banana]

Yes. You'd execute it at the startup by either using a unbound form designated as the Startup Form OR called via AutoExec macro.

Maybe if you download the sample I made for another poster will help?
Link to the sample.


Oh, BTW, you may notice I use DSN-less connection string, too. You could still use DSNs but I only like DSN-less because that means no need to visit each user's computer to put in the DSN.

 

[latex88]

This is way beyond me. I tried to follow but without success. I noticed that as long as I am updating the database, I can stay connected. I know this is probably not a good practice, but what if I constantly query the database to keep the connection alive? Is there a way I can automatically run a query every so often?

 

[Banana]

I don't think that's good idea, either. I'm sure your hosting plan most likely has a bandwidth cap so if you exceeded X bytes in a given month, you may be then blocked or charged extra. Querying that frequently just to keep the connection alive will make that happen and you don't want it to happen. You should only query when you actually need to query.


What part of the process are you having difficulty following?