Re: MS Access front end - MySQL back end

[Daniel Hofer]

I'm having trouble with write conflicts with the following.

I have 3 tables [with time-stamp fields set in the InnoDB tables with a Default of CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP].The tables are projects, jobs, lineitems related 1-many-many and are shown with a projects form containing a jobs sub-form which contains a lineitems sub-form.

When the quantity on the lineitem is changed, I recalc the lineitem subtotal which works fine posting the data to MySQL5.5 when you leave the field in Access 2010.

I also want to update the new sum of the 'many' lineitems into the job value field and update the new sum of the 'many' jobs into the project value field and refresh the form.

I use vba to recalc the lineitems extended total. I've then tried to use calculations setting the value field of the parent to a textbox SUM(value) field of the child for jobs from lineitems and projects from jobs and then refresh but get write conflicts along the way.

Next I tried, after posting the single quantity field change to MySQL OK, to update lineitem extended total and the parent fields new values using stored procedures but still run into write conflicts.

I've tried the AfterUpdate event to successfully change all fields in MySQL and show the refreshed values in the Access Form, but cannot stop the Write Conflict from Access about another user changing the record. Using the BeforeUpdate event, stored procedure, and Cancel = True also gives error msgs.

I need to figure this out since I want to do as much work in MySQL with stored procedures as practical. I'll re-post when I get this working.