CODEWITHSUNDEEP
mysqlms-accessms-access-2010
umesh motwani
umesh motwani

Reputation: 33

MS access error with mysql

I was working on an application that uses ms access 2010 and oracle. Now I am working on using my sql 5.7 instead of oracle.

But the code in ms access contains recordset.edit and then 2 set statements followed by recordset.update. recordset.edit recordset.Fields(22).value=1 recordset.update

This gives run time error 3197. You and another user are attempting to change the same data at same time.

I have tried to match all data types. But nothing seems to work. Still i get same error.

Appreciate your help. Thanks in advance

Upvotes: 0

Views: 333

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49009

This “other” user message is often misleading, and in fact another user has not updated the record.

Two things to check for:

If some sql update, or recordset code “may” update the current record you are working on, then force a disk write and thus no pending updates exist:

Eg:

If me.Dirty = True then me.Dirty = false.

YOUR code here such as above is now called/run

The 2nd common issue is null bit columns. These often confuse access, and thus you need to MAKE SURE at the SERVER level you have a default set for such columns (a value of 0 for sql server – not sure if same for MySQL). So this is a MUST check issue.

Next up:

Make sure the table in question has a PK, and you should add a row version (timestamp). This type of column is NOT to be confused with a date time, or a column to hold current time – it is a row version column. So add a timestamp column, make sure PK column exists, and make sure any bit (true/false) column in the table has a default. If existing bit columns have null values, then run update query to set them all false (0).

After you make the table changes (if required), then re-link all your access tables.

The above should cover 99% of the cases when you get that “other” user message when in fact you are sure it is not another user.

Upvotes: 1

Related Questions