How to remedy Microsoft Access to MySQL concurrency issues?

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:

  1. Auto deletion on encoded record
  2. 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.

  1. Manually assigned a auto key id
  2. 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.


3 thoughts on “How to remedy Microsoft Access to MySQL concurrency issues?

  1. Some clarification here Gleen, you mean you did not use the mysql autoincrement field as your primary key? in my experience ms access front end is quite happy to handle concurrency issues in the above scenario.Problem with mysql odbc is that the login and password is visible in the registry, of course you can enforce OS level security if you wish.

    Here’s what I did after also experiencing some strange error messages with mysql backend,
    turning the following on in ODBC connection:

    return matched rows instead of affected rows
    allow big result sets
    treat BIGINT columns as INT columns

    I also create boolean columns as as tinyint’s with length of 1 instead of bit. Also they’re required.

    – new and verified Y/N field can be replace by TINYINT 4 allow NULL (NAVICAT auto Converted), values remain -1=True and 0=false , tested in access 2003

  2. Thanks for the input ron. We do not change any ODBC settings as much as possible and I think that might be the solution to the problem. And you are right with the TinyInt, we are using that also.

    As much as I love using MySQL we have to dumped it since it was bought by Oracle and we don’t know future to it. Currently we reverted back to MS SQL this due also to the fact that MySQL has limited support on Entity Framework.

  3. Yes Gleen, I’m contended with SQL SERVER EXPRESS 2008 R2 10 gb. I will try the new SQL SERVER EXPRESS 2012 express. Salamat bai!

    “The only way the human situation will
    improve is for each individual to take charge of his
    or her life”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s