Can't update MySql Database

[GegeMArtinelli]

Hello,



From a MS/Access application (developped in VBA) I'm reading an external database under MySQL. Of course, I have already defined the ODBC bridge to do this.

The application starts correctly, I'm able to read all my tables rows from MySQL, but as soon as I want to update any row, I get a message box from MS/ACCESS saying (translated from French) :

"This record was modified by another user since you started modifying it.
If you record these changes you will replace those done by the other user.

If you wish display them, copy your changes ... etc"

I'm in test mode locally, so there is no other user. So I'm wondering why I get this message. Initially I thought it was be due to a lack of authorizations so I granted my user-id with all privileges for this data base. But unfortunately nothing better.

By reading other fora, I have noticed that a few people had the same problem, but no-one tells whether they resolve it.

Thanks in advance for any help or suggestions

Gege

PS : I hope my English is not to bad to be understood...

[lfpm062010]

I don't know what is causing the problem. I know there is one issue that will cause not be able to insert/update/change your records.

Does the table in MySQL database has a primary key? If not, define one and relink the table and see that will resolve your issue.

[GegeMArtinelli]

I don't know what is causing the problem. I know there is one issue that will cause not be able to insert/update/change your records.

Does the table in MySQL database has a primary key? If not, define one and relink the table and see that will resolve your issue.

Yes all my tables have primary keys.
Thanks anyway for answering

Gégé

[lfpm062010]

Don't know if this will help. This is how they fix the issue similar to your.

A solution how to Fix it: i added a Field named "TIMESTAMP" of type timestamp. Then i updated all records with TIMESTAMP=NOW();

[ItsMe]

...PS : I hope my English is not to bad to be understood......

Your English translation is working just fine. Here
PS : I hope my English is not too difficult to understand...

The error you are getting looks like an Access error message. Are you working from one form and opening another form to edit a similar, filtered, recordset?

Use
if me.dirty = true then
me.dirty = false
end if

Just before opening the second form and also in the second form before closing.

If you still get the error, reference the first form from the second form's module before causing edits to the recordset.

if Forms!FormName.dirty = true then
Forms!FormName.dirty = false
end if

[GegeMArtinelli]

Your English translation is working just fine. Here
PS : I hope my English is not too difficult to understand...

The error you are getting looks like an Access error message. Are you working from one form and opening another form to edit a similar, filtered, recordset?

Use
if me.dirty = true then
me.dirty = false
end if

Just before opening the second form and also in the second form before closing.

If you still get the error, reference the first form from the second form's module before causing edits to the recordset.

if Forms!FormName.dirty = true then
Forms!FormName.dirty = false
end if

Thanks a lot ItsMe for answering and correcting my English. However, I forgot to tell something which might be important :

Originally, this application worked with a MS/Access data base. For many reasons I can't explain here, it was decided that all databases have to be migrated to MySQL, and all future applications be developed in Java (I guess it's for portability purposes). To avoid to re-write already existing applications, we are trying to use the ODBC MySQL driver bridge.

So the application I'm talking about was already split in 2 parts (the forms and the Access database). This worked very well .
The problem I have described previously happens when I replace the Access Database by the MySQL database. As I also told, I have no problems to read, but only to update records (therefore I suppose ODBC works OK). The VBA code was not written by me, so I'm just discovering the logic.

Do you think the the changes, you are suggesting, will work if I include them in the VBA code since it used to work with the MS/Access base, and that the problem occurs now when I exit the first form. Is there a sort of "COMMIT" command In MS/ACCESS ?

Thanks again you your help,

Gégé

[ItsMe]

It may feel like there is not any difference from the previously working code but, from what I read here, I suspect the save record VBA code is the solution.

It would only take five minutes to place a few lines of code behind the two forms. Maybe remove the close button in the second form's properties and replace it with a Command Button so you can force a save.

If it helps, here is the same code in a single line.
If me.dirty then me.dirty = false
or
if Forms!FormName.dirty then Forms!FormName.dirty = false

I am not an expert but, I am having a hard time seeing how the ODBC driver or MySQL is throwing the warning. It would only take a few minutes to prove me wrong (just be sure to place the code in many places, before opening, before editing and from the second form, after editing from the second form).

[GegeMArtinelli]

I tried to add the instructions you suggested. Unfortunately there was not anything better.

However I should admit, that I have very few knowledges in VBA and maybe I put this instruction at a bad place.

As I told I did not create this application. There are more than 20 forms (with many boxes in each) and I have many difficulties to understand how such things work.
I think I'm going to give up, and start to redevelop all in Java, because I guess it will take me less that 5 days to rewrite everything.
Thanks again for trying help me. Have a nice day

Gégé