List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:November 17 2004 1:27am
Subject:Re: Access front end problem
View as plain text  
Titus wrote:

>
> We recently migrated a medioum sized genetic laboratory database
> from Access to MySQl.  Of course, for the time being, we are stuck
> with our Access front end, using Access linked tables to our MySQL
> backend.  Everything seems to be 'reasonalbly' okay after a month.
>
> However, we occasionally get a message from the front end that
> "This record has been changed by another user since you started
> editing it.  etc. etc."
>
> This occurs in a "Write Conflict" message box from Access.  We
> aren't having much luck running this problem down.  Can anyone
> shed some light on it and save us some time?
>
> Thanks for even -considering- this a MySQL problem.
>
> titus sends

Are you using MyISAM tables?
I've found that if we have a number of users ( 5 or more ) connected to 
the same table, we start to get these errors.

 From memory, MyISAM tables use *table* level locking. The idea here is 
that table-level locking is faster ( and simpler ) than row-level 
locking. When a user running MS Access goes to edit / add a record, they 
record a *record* lock. However MySQL locks the whole *table* and 
reports back to Access that it has a *record* lock. Now another user 
goes to edit / add a record, and you get your error message ( original 
post ).

Solution: Use InnoDB tables, which give row-level locking.

If it's any consolation, we get the same errors with SQL Server ( which 
is kinda strange considering SQL Server is supposed to do row-level 
locking ).

What I do is try MyISAM tables to start with, and as more people start 
to use the table ( and get the above error ), I convert them to InnoDB 
tables, and don't get any more problems.

Dan

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au

Thread
Access front end problemTitus16 Nov
  • Re: Access front end problemDan Nelson17 Nov
  • Re: Access front end problemDaniel Kasak17 Nov