MySQL Forums
Forum List  »  Microsoft Access

Re: MS Access front end - MySQL back end
Posted by: Daniel Hofer
Date: May 10, 2012 04:44PM

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.

Options: ReplyQuote

Written By
Re: MS Access front end - MySQL back end
May 10, 2012 04:44PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.