X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Mysql Backend Collection Of Tips And Notes.    
post Jan 31 2018, 04:47 PM

Posts: 64
Joined: 18-January 16

Hello everyone.

Considering that information on Access use with a MySQL backend is a little scarce and disperse, I decided to collect some information here based on my experience and from others, hoping people can put their two cents on here too.

If this transgredes forum policy, let me now.
Go to the top of the page
post Jan 31 2018, 05:00 PM

Posts: 64
Joined: 18-January 16

First the "This record has been changed by another user since you started editing it..." problem that has been eloquently exposed before:

This occurs in all bound Access forms (at least in our company) with SQL tables in the back.

Open a form
Klick in a field (e.g. a textbox)
Change something (e.g. remove the last letter of a name)
Add the letter you just removed - in other words - undo the change manually
And force Access to save the "change" e.g. by exiting the form or moving to another record.

The well known write conflict warning appears.
"This record has been changed by another user since you started editing it..."
Which is simply not true. It was changed by me alone and not by another user and I didn't even CHANGE anything. I started changing but before it was written to the backend I undid the change.

This can best be observed with checkboxes. Simply clicking on a checkbox TWICE will make this warning appear.

While this sounds rather harmless it get's worse in combination with this.
If you trigger a requery of the form or if you try to set the Dirty-property of the form to false then an error shows up claiming that the property (e.g. dirty) of the form is not available. This causes the whole frontend to crash if you didn' catch the error.

So the error shows up if you undid a change like I descibed above and then set Me.dirty = false or try to requery the form.
This combination happens quite often at least with our employees. Maybe they accidantially checked a check box or started writing something in a textbox and then changed their mind and deleted it again. The datatype doesn't really matter...

So here some background informations.
2. The table contains neither bit nor floating point field types

This can be reproduced with a simple table.
Two columns (VarChar(20), TIMESTAMP)
But as I already said - this "works" with any table.

As suggested in the MySQL ODBC connector documentation, it is standard routine to check the "Return matching rows instead of affected rows" checkbox when configuring the ODBC connection. PostgreSQL ODBC FAQ shows more light into the problem and some other observations apply as well.

That said, the "another user..." message can still happen. As of MySQL 5.7, using the ON_UPDATE_CURRENT_TIMESTAMP option on datetime rows will send this error if you modify a record and revisit it to modify it again some seconds later, even at table level. I guess, the change puts Access out of sync with the modified record, so I had to disable that feature (Navicat sometimes add this property automatically when using it to import Access tables to MySQL).
Go to the top of the page
post Jan 31 2018, 05:06 PM

Posts: 64
Joined: 18-January 16

Since there's no boolean field type in MySQL, two options are available: set the field to TINYINT(1) or BIT(1) type.

Although tinyint can be handled, it has some problems: Access sets it to -1 on True and apparently some Web Development frameworks need to handle it as 1. The other problem is that Not(Field) returns 2 when the existing value is -1.

Other than working out the code to handle the tinyint type specifically (checkboxes are a pain, though), setting the field type to bit(1) solves both the -1 / 1 problem and the NOT operator problem, with a caveat: the field MUST be set to NOT NULL since Access can't handle triple state (Null boolean) fields and a newly created record with a null bit field won't be updatable.
Go to the top of the page
post Feb 14 2018, 01:43 PM

Posts: 64
Joined: 18-January 16

Sometimes an ODBC error happens and a message of "Date overflow" is displayed and a record fails to update.

A quick search led to the following explanation:

"For your information the fix approach was that in C or C++ it is possible to read or write DATE type using SQL_TIMESTAMP_STRUCT. This struct can hold both date and time. The error (Date overflow) was generated when with operations that are supposed to be DATE-only this struct got non-zero values for time. That is the canonical approach as ODBC API requires, however, it causes inconveniences sometimes when for instance the app did not bother to initialize the whole structure with 0 values because it knows it will only need the DATE part but the random values for TIME fraction could cause the errors despite of being truncated. A new option was introduced to continue with the query execution rather then return error. The server will ignore the TIME part and the result is the same as if there were zeroes."

As of February 2018, the version 5.3.10 of the MySQL ODBC driver includes a "Disable date overflow error". Marking this checkbox will stop this error from appearing and so far allows records to be updated.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    18th February 2018 - 03:49 AM