ODBC Connection Lost after PC iddle.

Hi,

Access is a desktop database, was never an Internet database. That means, it is heavily dependent on a permanent network connection to the backend database. Only in newer Access versions there shall be an automatic reconnection possible (have never tried that, so I cannot say if it is working).
Maybe you've changed something in the energy settings of your computer? (Especially if it is a Laptop.)
The network card can also be switched off in energy settings, also the harddisk so maybe it's the reason for loosing connection.

Cheers,

Christian

Don't believe this is on the Access side.   It's probably Hostgator that is dropping the connection.   Most servers try to conserve resources and will only let a connection sit idle for so long.

Try adding a hidden form with a OnTimer event that polls a table in the DB every xx minutes.   See if that prevents the connection from getting dropped.

Jim.

what does the connection link or link string for accessing the Access database look like from the MySQL side?

Hi bbao,

Access is the frontend here so it would be strange if MySQL would have a connection to the Access frontend... ;)

Cheers,

Christian

Why would that be strange, Christian?

One of the nice things about Access is that there are drivers to connect it to almost any BE database.

Hi!

One potential solution is to adjust the MySQL server's wait_timeout setting to a higher value. The wait_timeout variable determines how many seconds an inactive connection should be kept open before it is closed by the server. By default, this value is set to 28800 seconds (8 hours), but it can be adjusted by modifying the MySQL configuration file.

To do this, follow these steps:

1.Connect to the MySQL server using a MySQL client application, such as MySQL Workbench or phpMyAdmin.

2. Execute the following command to check the current value of wait_timeout:

SHOW VARIABLES LIKE 'wait_timeout';

Select allOpen in new window

3. If the current value of wait_timeout is lower than the amount of time it takes for your MS-Access application to become idle, you can increase it by modifying the MySQL configuration file. On most Linux-based systems, this file is located at /etc/mysql/mysql.conf.d/mysqld.cnf.

4. Open the configuration file in a text editor, and look for the [mysqld] section. Add the following line to this section:

wait_timeout = 600

Select allOpen in new window

This will set the wait_timeout value to 600 seconds (10 minutes). You can adjust this value to a higher value, depending on your requirements.

5. Save the configuration file and restart the MySQL server.

Once you have adjusted the wait_timeout value, you should no longer experience issues with lost connectivity due to idle connections.

Hi Dale,

maybe you misunderstood: It's good and normal to access MySQL with Access as frontend, no problem.
But not the other way round, that MySQL would connect to the Access frontend, that would be strange (and complicate in case of more than one user where usually each user has his own frontend).
A database server usually does not connect itself to a frontend, except for import/export solutions, but that would be a datafile only, not an Access frontend.

Cheers,

Christian

Try trapping the error in VBA code, then re-initialise the connection.