I keep getting write conflicts when I try to change data of the current record. (I know, I shouldn't )
Access is used as frontend to a postgres-database, which works fine, as long as I pass the values back to access and let vba-code do the changes. (see attached BtnCalkClick_vba.txt)
This is tiresome, if you have to change several fields, so I tried to change several fields in just one stored procedure without telling access about it . (see attached kalkAufpos_sql.txt)
I can even change fields in other records (line 'update "Auftrag" set "Start"...'), but not in the current record access is working on. (line 'update "AufPos" set "Kosten"...') - omitting this line: problem gone. (but no updated values either)
The form gets its data from an access-query which does _not_ include the fields I change.
The error occurs at the 'Me.Requery' line in the attached vba-code. Omitting this Requery is no solution - access complains when trying to close the form.
Is there a way to tell access to accept what I'm trying to do - i.e. clear its buffer or whatever?
I already found this information: http://dev.mysql.com/doc/refman/5.0/...ied-error.html which seems to be related to a similar error with mysql databases. The postgres table has a valid primary key (2 columns - could this be the problem?) and I already added a timestamp field to the table just to cure this problem. However, the fields involved are numeric(10,4) and numeric(10,2) - maybe use double? could this really cure the problem?
I've done some MySQL work (sorry no postgre - but I hear it is a good DB). After reading your post I started thinking of row locking as you said the Access Form is taking data from the your DB for this record, and maybe the reason it is giving you write conflicts as you already have the row locked by your form.
A solution maybe to try something like on a click event capture the columns needed into variables that are required for the update then change the record source of the form to nothing (thus dropping any row/record conflict) do the update, then return the the forms record source back to what it was before (and you have the variables to put it to the record you had before.
I hope this helps, let me know if I'm way off base as I was kind of confused reading your post
I've done some MySQL work (sorry no postgre - but I hear it is a good DB). After reading your post I started thinking of row locking as you said the Access Form is taking data from the your DB for this record, and maybe the reason it is giving you write conflicts as you already have the row locked by your form.
The magic word - you named it: locks
a simple:
Me.RecordLocks = false
just before calling the sp did the trick
- but no more value changes by vba can be made beyond this - what is not really a problem: just what I wanted.
Originally Posted by SQL_Sam
I hope this helps, let me know if I'm way off base as I was kind of confused reading your post
no confusion was intended - maybe access confused me so much to write confusing postings
)
Reply With Quote
Originally Posted by SQL_Sam
