Write Conflict

[FixItPete]

I have read and read and read... I'm at the end of my rope...

I recently learned how to use ODBC to edit a MySQL database via MS Access.
I'm very new to this part of Access. So forgive me if I ask questions that seem obvious to others.

Everytime I try to change a field I get the "Write Conflict" error.

Let me know if you need more information.

Thanks a bunch,

Pete

P.S.

A slightly different way to look at this?

Say I go into the table: products and I try to delete an item I get this:

"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time"

I have no idea what this means since there are no other users.

Pete

(Access 2000 9.0)

 

[Pat Hartman]

You are conflicting with yourself. You are using a bound form and you are attempting to change that same record with DAO or ADO.

 

[The_Doc_Man]

Pat would be your best authority, but I'll take a guess and say that the lock settings on the remote database or the lock settings on your ODBC query are set wrong. You want to minimize your locks and use optimistic locking. Look into the parameters on the query or form you use to verify that all lock settings are set to their minimum.

THIS IS A GUESS. Something to try, but I'm not sure of it. So if it don't work, all I can say is "sorry."

 

[FixItPete]

Ok,

Looks like I am in a little over my head on this one... but I'm not going to quit yet...

Pat:

I am not using any form. I am trying to modify a table, via the table.

I don't know what DAO or ADO mean.

The_Doc_Man:

Where can I find the lock settings you're talking about? On my ODBC Data Source Administrator Interface? I don't see anything there about "optimistic locking" -- but that doesn't mean they aren't there... I'm very new at all this...
Where exactly should I be looking? On Access or in the OBDC DSA?

Thanks for all the wonderful help,

Pete

 

[FixItPete]

All fixed...

I ended up adding a time/date stamp field to the MySql db, and that did it.

I have NO idea why... but it works.

Pete

 

[Pat Hartman]

Updates from Access to an ODBC database are asynchronous. That means that the record is not locked in the db when it is sent to Access. The two processes are disconnected. When Access sends back an update, the RDBMS needs to be able to tell if the record has been changed in the db by someone else in the interim. Apparently MySQL has trouble with this that Oracle, SQL server, and DB2 do not. The date/time field gives MySQL a point of reference. It compares the field value from the stored record to the copy you are sending for update. If the values are the same, no one has changed the record between the time MySQL sent it to you and the time you sent it back with changes. Even if it took you three days to send back the update. The date/time stamp is updated when the record is saved.