Write Conflict

Question

My form (frmMainEmp) has an unbound text box (actually a combo box called MgrNew) that through VBA should update the changed value of frmMainEmp.MgrNew to tblWholeEmp.Mgr.  For some reason I was not getting the table to update due to a Write Conflict so I posted my code attempt on this site and was able to get the table to accept the change via the OnChange Event Procedure.  (Thx John Spencer / Roger Carlson) But after the code runs (and I confirm the update at tblWholeEmp.Mgr)  I am not able to tab out of the form's field (frmMainEmp.MgrNew) with out seeing an error menu claiming that a write conflict is occuring.  When I click the "Drop Changes" button all is fine.  The frmMainEmp.MgrNew shows me the correct result and I can now tab over without any errors.  I just can't understand why there is a Write Conflict when the code works.

Some info to help solve the problem:

Using Access 2003 as a FE with PostgreSQL as BE

Combo box Row Source: SELECT DISTINCT tblemployee.MgrName FROM tblemployee;

Answer 1

Hi Brian,

As Nathan suggested, you should avoid Null values.

That is the other thing, I do when working with MS Access as FE and mysql as BE. 

I never have null values, each field on my tables have a default Value.

Event if it is a Text I use "None" or "N/A".

Goog luck!!!

---

I hope this helps, Bre-x just a regular user, not an expert

Answer 2

Hi Brian,

Bre-x is correct.  This problem can usually be fixed by adding a timestamp field to your SQL table (or in your case PostgreSQL) and then after making this change, relink the tables within your Access database.  The problem tends to happen when one of the following is true:

Access is creating Null bit fields, which causes a type mismatch.

or

The linked table is using fields defined as floating point data type (i.e. Real). This data type allows for storing "floating point" number data. When the edited record is passed to SQL Server, if both engines see the data in these fields as having the identical value, there is no problem. However, because of the "rounding" algorithms used by JET and SQL Server, the actual value compared may be different. Since JET has to review each field and see if there are any concurrency issues, when JET compares the number it sees (in the "Real" data type field) to that value stored in SQL Server, if it does not match exactly the record is assumed to have been changed and a Write Conflict occurs.

This problem is outlined in KB 280730 and can also be found by doing a quick BING search with keywords Access Write Conflict J

280730 You may receive write conflict errors when you update records of a linked SQL Server table in Access 2000

http://support.microsoft.com/default.aspx?scid=kb;EN-US;280730

Best Regards,

Nathan Ost

Microsoft Online Community Support

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 3

Hi Brian,

IN SQL Server, you would add a field that actually has a data type of timestamp.  This is different then an actual date field.

Best Regards,

Nathan Ost

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 4

Thank you Bre-X and Nathan.  From the advice in this thread as well as what I have read in corresponding web links it appears that adding a time stamp field in my table should eliminate this write conflict.  However after reviewing my PostgreSQL table's field I see that I already have a poplated date field that has a time stamp associated with it (timestamp without time zone).  Is there something that I need to add/modify to this?

Let me think out loud here: my table has 6 sample records - some I can update directly in the linked table while others I cannot.  Of those that I cannot, I have tried unsuccesfully (hence the term "cannot") to add data to some of the empty fields in hope of pinpointing the cause. But as it was stated once the record is created it is not possible to add data.  FYI, all of the boolean fields are populated from the defaults=0.  I have number and text fields that are not populated.  Not sure if this means anything though.  I can provide more data but not sure at this time what is pertinent and what is not.  Any advice is appreciated.

Brian

Answer 5

There is a similar issue with mysql. It is the way the ODBC connect to your postgreSQL. The way you fix it is to add to each table a timestamp field

Google it!!

mysql ms access write conflict on your internet explorer

:)

---

I hope this helps, Bre-x just a regular user, not an expert