When I linked to my exported MySQL table using MS Access as the front end and connect using MySQL ODBC 8.0 ANSI Driver (32 bit) I found problems with the time format. Using Access tables my date format would look like 5/25/11 9:10:09 AM but the same record in the linked SQL table looks like '2011-05-25 09:10:09'. When trying to add a record that has time on it I get an error message. The Message box from Access says "ODBC call failed [MySQL][ODBC 8.0(a) driver][mysqld-8.0.28]Date overflow (#0). Similarly, when I try to delete a record I get the same error message. I can't put the time into the Access field in the same format as the SQL field as that creates an error. So it seems that the Access format has to be the same as the host computer format. Let me know if you have any suggestions how I can make this work.
I couldn't find that specific error in the documentation, or anything on datetime overflows, but here's the section on date/time data types:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
There's a link in there about "date literals" that might help explain why you're getting that error.
For ODBC:
https://dev.mysql.com/doc/connector-odbc/en/
Please note that SQLTeam.com is a Microsoft SQL Server focused site, this may not be the best forum for help.
Based on my (ancient) MS Access experience, you might need to attach some VBA code to your form inputs, instead of binding them directly to the data source/table columns, to convert or otherwise reformat the date & time entered in the form. You would, in essence, construct an INSERT or UPDATE statement as a string and then execute that statement to pass to the MySQL instance. Or, if the INSERT/UPDATE can be encapsulated in a MySQL stored procedure, you would construct the procedure call as a string and pass the parameters to it. I haven't done this in MySQL but have done it a lot with SQL Server linked tables and stored procedures behind MS Access forms.
I can't understand why you'd get that error message when trying to delete a row. It makes me think there's some issue in the ODBC driver, or in the way MS Access is binding to the data source. If you have a way to profile or trace the statements being sent from Access to MySQL, that might illuminate where the error lies.
Again, from my ancient Access experiences with ODBC drivers, whenever I had annoying problems like this I'd switch to another driver. If that's an option for you it's worth considering.
Thanks, I will check out your suggestions. I did come across a thread that mentions trying a different ODBC driver as well but that was back around version 5.0
Unless your MySQL instance is v5.x, I don't think a v5 ODBC driver will work.
Also check out LibreOffice, they have an Access-like module called Base that can also do data input. It's free and open-source and more directly designed to work with MySQL.
Thanks for the info. I have 50,000 lines of code in my current Access database so I would prefer not to move to a different platform but worth looking into. I agree, not sure of ODBC 5.o will work either. Probably will try just to see what will happen but don't really like to rely on older software due to bugs and security concerns.
I have decided that my best workaround is to convert all of the time fields to text format and then reformat the string to DateTime format in my forms and queries. This seems to fix my ODBC issues