Write Conflict

Question

Write Conflict

barry.nielson

SSC Eights!

Points: 912
More actions
November 4, 2018 at 9:43 pm

#376303

Hi all,
So this issue is driving me bat guano insane.
I have MS Access linked to SQL tables.
1. There is no form or any other connection being made to the table. I simply have the table open.
2. I can create a record and at that time of creation, I can add data into any field.
3. Once I leave the record and come back into it, any further editing creates a write conflict error.
4. Based on some research, I have added a timestamp field to the table.
5. I have sacrificed 12 innocent kittens to the gods of Microsoft.
There seems to be no adequate answer to this issue. Can anybody offer a reasonable solution?
Ps. Am something of a noob to the SQL world.
Cheers

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

barry.nielson - Sunday, November 4, 2018 9:43 PM
Hi all,
So this issue is driving me bat guano insane.
I have MS Access linked to SQL tables.
1. There is no form or any other connection being made to the table. I simply have the table open.
2. I can create a record and at that time of creation, I can add data into any field.
3. Once I leave the record and come back into it, any further editing creates a write conflict error.
4. Based on some research, I have added a timestamp field to the table.
5. I have sacrificed 12 innocent kittens to the gods of Microsoft.
There seems to be no adequate answer to this issue. Can anybody offer a reasonable solution?
Ps. Am something of a noob to the SQL world.
Cheers

Does the table have a unique primary key?
😎

mark.humphreys SSC Eights! Points: 867 More actions · Answer 2

What are the fields in the table? Is there a bit (Yes/No field for Access) field?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

barry.nielson - Sunday, November 4, 2018 9:43 PM
Hi all,
So this issue is driving me bat guano insane.
I have MS Access linked to SQL tables.
1. There is no form or any other connection being made to the table. I simply have the table open.
2. I can create a record and at that time of creation, I can add data into any field.
3. Once I leave the record and come back into it, any further editing creates a write conflict error.
4. Based on some research, I have added a timestamp field to the table.
5. I have sacrificed 12 innocent kittens to the gods of Microsoft.
There seems to be no adequate answer to this issue. Can anybody offer a reasonable solution?
Ps. Am something of a noob to the SQL world.
Cheers

I don't know much about access but it sounds like there's a transaction that's been left open by Access. I don't know where "implicit transactions" may be set by Access. Connection string?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bgalway Ten Centuries Points: 1158 More actions · Answer 4

I used to see this a lot and never figured it out (altho timestamp did help). Somewhere along the way (having upgraded to SQL 2012 and Access 2013) the problem went away.

barry.nielson SSC Eights! Points: 912 More actions · Answer 5

Thanks to all those responded.

I solved the issue by altering all the bits fields to TinyInt fields. This them solved the write conflict issue, but created an issue with using checkboxes, since TinyInt fields are not happy with the recording of -1 values (a particularly silly way Access resolves the difference between true or False) - I had to swap out checkboxes with comboboxes instead. Also, it turn out I didn't need a timestamp after all. It's not a perfect solution, but it works.

Thanks again to all responders.

Cheers

mark.humphreys SSC Eights! Points: 867 More actions · Answer 6

You can use BIT fields and then set the default value to 0 in SQL. This gets around the issue I have experienced in the past.

Thanks

barry.nielson SSC Eights! Points: 912 More actions · Answer 7

Hi Mark. that's Interesting. Thanks for the tip. I'll look into it. For example can i create the table with the 0 value as the default?
Thanks again.
Cheers

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

barry.nielson - Wednesday, November 7, 2018 2:16 PM
Hi Mark. that's Interesting. Thanks for the tip. I'll look into it. For example can i create the table with the 0 value as the default?
Thanks again.
Cheers

In SQL Server, yes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)