Lost connection to MySQL 2013

[FuzMic]

Hi guys

Recently i start using ODBC 5.2a Driver to connect to an remote Unix server. The Access front end appears to work perfectly with the MySQL database.
However whenever the front end is left on itself for sometime, a fresh query from the FE is not possible because of the connection is lost, error 2013. The only way i know is to restart the front end, then the query works. This is without fail.

Questions
1 Generally can a robust front end be built to work extensively with a remote MySql?
2 Why is there always a lost connection? How can this be fixed. It is annoying to end users if the Access front end need to restart everything the connection is lost.
3 For best practice, what settings should be used in the ODBC setup eg Enable AutoReconnect, etc.

Appreciate advice from those who are ahead.

 

[Rx_]

Not going to be able to help you with a MySQL question.

Lets assume you are using Linked tables?
Are you manually linking the tables then pushing the copy of access to the users?

Or, are you writing the vba code to create the ODBC connection string?
I have personally had better luck with creating the connection string with VBA. You can probably appreciate trapping an error that at least refreshes or re-creates the link.

This forum has some great examples of how to create the ODBC Linked table. Then there is my 'average' example too. LOL

I will try to help if there isn't a MySQL expert to assist you.

 

[mdlueck]

What type(s) of connection(s) does your Access FE use?

I noticed that Windows 7 has a more severe power save than Windows XP, such that ADO.Connection objects die while in the sleep mode. I had to convert all ADO code to use brand new ADO.Connection objects for each query.

I have not noticed an issue of Linked tables being unable to reconnect after Windows 7 power save. Though, I do not test much on Windows 7 with my development FE database which has the Linked table objects. For production I remove those Linked table objects and remove the residual data from the FE DB:

VBA to Cleanup A2007 DB Extra Objects
http://www.access-programmers.co.uk/forums/showthread.php?t=226466

Now, this code will wipe out saved QueryDef objects, as I always code creation of those through DAO.QueryDef objects.

 

[FuzMic]

Sorry to both of you for being a bit late replying. Much appreciate the effort.

The FE form is permanently linked to a MySQL table via the ODBC Data Source (Rx_ yr question). By configuring the MySQL connector / ODBC Data Source to "Enable auto reconnect", opening the form is perfect, except when the form is left just a minute or two, dialog appears "ODBC -- Call failed".

This dialog event is what i am trying to avoid. I have been tinkering with the configuration but they are too many parameters and i have not found any documentation to fix it.

Once this event occur, all i have to do is reopen the form (md.. yr point), then there is no problem. I use Access 02 or 03.

Cheers!!

 

[mdlueck]

The FE form is permanently linked to a MySQL table via the ODBC Data Source (Rx_ yr question). By configuring the MySQL connector / ODBC Data Source to "Enable auto reconnect", opening the form is perfect, except when the form is left just a minute or two, dialog appears "ODBC -- Call failed".

I would say that here lies your problem. In your design, you rely on maintaining an active connection to the SQL BE DB the entire time your FE application is open. This is not a good design. When dealing with SQL BE DB's: "Get in, exchange data, get out and DISCONNECT!"

Example:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

 

[FuzMic]

Michael, Many Thanks from Mike.

After scanning your suggested post, it seems you are saying that the correct way to use permanently linked table in FE (via ODBC Data source) is to rely on some sort of query to get the data and store it in a temp table at the FE and use that instead of using directly from the BE table. Did i get you right or i am off the mark.

By the way if I use a ADO.recordset from a permanently linked table will the recordset disappear if the link is disconnected?

 

[mdlueck]

By the way if I use a ADO.recordset from a permanently linked table will the recordset disappear if the link is disconnected?

I think you best steer clear of Linked Table objects for production use. I suspect the server will get annoyed with the hanging connection and drop it eventually.

The trouble is not with the ADO object, rather the Linked Table object the ADO object points to.

 

[FuzMic]

You are right, server just drop me off when i hang on too long.