I have developed a front-end (FE) Access database on my Win11 PC, using latest version of Access. The back-end (BE) is running on a SQLServer hosted by a web hotel. The FE is connected to the BE using ODBC. This works without any error-messages.
I migrated a copy of the SQLServer BE db to a MySQL/MariaDb server hosted by the same web hotel. All queries/views used by the Access forms are stored in the Access FE only.
Aiming at using the same Access FE against the MySQL/MariaDB, I have ensured that the different tables' ‘RowVersion’ column have been set to 'timestamp', 'no nulls', default 'current_timestamp on update current_timestamp'. Columns for ‘false/true’ values are set with 'tinyint', 'not null' and default '0'. Columns for ‘double/single’ values have been set to decimal.
I open the FE, being the only user, having re-connected using the latest ODBC driver for MariaDB. No errors! I proceed to open a continueous form based on one table only, and I get an 'write conflict' followed by an Access 3197-error message. The same error-massage appears if I try to run any procedure updating data in the in the BE. Opening the same table directly in Access 'table view' and edit, no conflict. I open the table with an SQL query in MySQL Workbench; no conflict.
I carried out the same exercise, but now migrating the db to a MySQL server on my PC ('localhost'). The same 'write-conflict'-message appears.
I take it that the record locking strategies are different in MySQL/MariaDb and SQLServer. My question is, what may I do to remedy my problems with respect to using MariaDb/MySQL as server?
Gerhard