We help IT Professionals succeed at work.
Get Started

Access 2016 linked to MySQL, ODBC call failed randomly

3,237 Views
Last Modified: 2016-09-06
Hello,

Some background: We are using Microsoft Access as a front end, (2000 32-bit, to 2010 32-bit, and now 2016 64-bit over the last several years) and MySQL 5.5 as a backend on a Fedora Linux system. We are using MySQL ODBC driver 5.2.7. Our FE is very complex, our users would frequently receive System Resource Exceeded errors which is why we converted to 64-bit (which did resolve the system resource errors). As of about two weeks ago, the migration to 2016 64-bit was completed. We have at most 15 users accessing the database at the same time. Everyone has their own "local" copy of the front end DB on their own system.

In early January (before 2016 64-bit), we started receiving random ODBC call failed errors throughout the day from our users. Using the DAO.Errors collection, we were able to get the exact  error message which is this:

Error #0  [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.28-log]Date overflow (Source: ODBC.Database)
Error #3146 ODBC--call failed. (Source: DAO.Database)

These errors happen on several different SELECT queries that access different tables. Some of them are extremely complex queries, but some aren't. We are using error checking code now that emails us the error when it occurs, and then tries to resume the same query again. Usually, the 2nd time it tries the same query it will work with no error. Sometimes it will go 3 or 4 tries and then it will work.

I believe the only thing these queries have in common is that they all use some sort of date filtering using the Now() function, example might be having a datetime field with criteria "> Now() + 5" for 5 days from now ... thinking maybe there was a strange issue there, we wrapped all of those criteria with CDate() calls so now they look like "> CDate(Now() + 5)" ... which did not make a difference.

The errors also seem to occur in bunches ... we might get a handful around the same time (not exactly the same time) from a couple different computers each morning, then go several hours with none, then in the afternoon a few more. The times of days are similar but not exactly the same.

I've tried using ODBC tracing to see what exactly is failing on the Access side, but this is difficult to do because we can't duplicate the error on demand, and ODBC tracing is extremely slow (10+ minutes for a single complex query which without tracing runs in 1-2 seconds).

I also tried running the MySQL general query log and I can see when the error occurs, but it only shows the "prepare" statement which has "?" for date values, the "execute" statement with the final values in the SELECT query does not make it into the log.

I did try upgrading the ODBC driver to 5.3.4 but it made no difference.

I also tried a new MySQL server running newest version of Fedora Linux and MariaDB ... no difference.

We've been trying to fix this since it started occurring and tried many different fix attempts with no success. Google also doesn't seem to turn up anything related to this error except for one result (googled "Date Overflow (#0)" ) in Spanish which, when translated, doesn't help.

Looking for any assistance or insight, thank you.
Comment
Watch Question
Director of IT
Commented:
This problem has been solved!
Unlock 2 Answers and 5 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE