If you are deploying Microsoft Access front ends with MySQL back end database there will be concurrency issues on forms with master – details format which cannot be remedied by adding a TIMESTAMP field. The most likely symptoms are:
- Auto deletion on encoded record
- Duplicate id errors sent by MySQL through ODBC
To remedy the issue you need to do two things on the form/sub form data entry upon initiating record edit.
- Manually assigned a auto key id
- Execute a SAVE Command
Here is the sample code snippet
If IsNull(IDmem) Then IDmem = Nz(DMax("id", "trnstitem"), 0) + 1 DoCmd.RunCommand acCmdSave
By the way, this is not 100% guarantee, sometimes if there are so many users encoding the same form at the same time you will still encounter the errors, that is why the above is just a remedy. Below is a sample form wherein there were 5 users encoding as fast as they can at the same time.
Remember that I only remedied the issue, the real solution is to create a SAVING pipe line method between the interface and the database but unfortunately Microsoft Access doesn’t allow that unless you provide a temporary table.