Mysql – MS Access / MySQL: unlock locked Records

ms accessMySQLodbc

I have a Microsoft Access database that is connected to a MySQL server via ODBC and is accessed by multiple users.

Over time many records are locked and editing is not possible anymore.

Is there a way to bulk unlock all records?

The Error Message:

This record has been changed by another user since you started editing
it. If you save the record, you will overwrite the changes the other
user made. Copying the changed to the clipboard will let you look at
the values the other user entered, and then paste your changes back in
if you decide to make changes.

Best Answer

I finally could fix my problem by adding a field named TIMESTAMP of type timestamp and default value CURRENT_TIMESTAMP and attribute = on update CURRENT_TIMESTAMP. Then i updated all records and set TIMESTAMP=NOW().

For now i did not get any error again.

Related Solutions

Ms-access – Calculating Open Days field and stopping with status change

Counting days without explicitly storing the start date would require some kind of scheduled task to count them. This is not trivial to implement and not recommended.

I would suggest storing the date when opened and then doing a date diff between the start date and the current date to work out the number of days (including the appropriate rounding). I would suggest that it might be a good idea to also store the closing date.

Edit:

When the item is closed you have two options to work out/read the days opened in the future:

You can store both the start date and the close date and do a datediff between them when you need to know the days opened.
You can store the days open explicitly when you mark the item as closed.

I would recommend 1 as it is more concise.

Edit2:

To fully explain point 1. Basically you would use the datediff function and compare the start date to the current date, while specifying days (d) as the required unit.

In an SQL select query this would look something like:

select 
    datediff('d',table.StartDate,date()) as OpenDays,
    table.Status
from table

The date() function returns the current date.

Microsoft Access Help on DateDiff

MySQL – How to Link a Table with Binary Key into MS Access

I solved this by creating a updateable VIEW to the table. The view displays every column but the primary key LNEr is converted to a string with the HEX() function:

CREATE VIEW v_ereignisse AS SELECT HEX(LNEr) AS LNEr, Datum, StatNr, ...

In MS Access instead of creating a link to the table I created a link to the view. Because the view has no primary key, the "table link manager" asks for selecting one, this (of course) is the string version of LNEr.

Best Answer

Related Solutions

Ms-access – Calculating Open Days field and stopping with status change

MySQL – How to Link a Table with Binary Key into MS Access

Related Question