Record Locked errors when updating to same value

[Phil Smith]

I have never seen this behavior before. Access continues to amaze me.

I just purchased a new laptop, installed Access 2003 SP2. I am running
an update query on a mysql database via ODBC.

Let us say I am doing a single table update query, updating 1 single
field in every record that matches my criteria, to the value 4. 100
records match my criteria, and 20 records happen to already have the
value 4 in the specified field. (this field is not part of the
selection criteria.) When I run the query, I get the typical laundry
list of records counts that will not update for various reasons. In
this case 20 records will not update because they are locked records.

Guess which 20 records? Yep, those that already HAVE the value 4.
These records are NOT locked by any other application accessing MySQL
database. The end result is the same, all these records are now set to
4, but I have to deal with a bogus error message, which will hide the
one time I really do have locked records for legitiamte reasons.

Any ideas?
Thanx

Phil

 

[Michel Walsh]

Hi,

At first glance sounds that the ODBC driver is the problem. Is it
up-to-date?

Also, is your application has a recordset, or a filtered form, already
opened on that table? that recordset may have decided to lock those records
or you may have open the (filtered) form with the option to lock the records
of the table? There are so many possible settings you have used... does the
problem exist in a brand new Access application where only that ODBC linked
table would exit and the update made through a query (no form, no nothing,
the bare minimum) exhibit the same behavior? if not, then that tends to
prove that a setting, or a mix of settings, you used created the 'problem',
in the 'real' application, since the simplistic one would work.


Vanderghast, Access MVP

 

[Phil Smith]

Nope, brainddead simple directly run query. No forms parameters, nothing.