SQL Write Error

[Leesha]

Hi,
I am working in a DB that has tables linked to sql server. I have a form set to continuous When I do an update query I get a write error that I am about to save to a record that has been edited by another user. The save button is not enabled. Also, I have a timestamp field in the table which I thought prevented this. Is there any way around this?
Thanks,
Leesha

Write%20error.png

[HansV]

Does the table in SQL Server contain a bit field without a default value? This can cause the error - see You may receive write conflict errors when you update records of a linked SQL Server table in Access 2000 for a description and solution.

[Leesha]

Hi Hans,
I went in and checked and made sure that all defaults were set on bit fields. 1 wasn't but it is now. I refreshed the linked tables and still get the same error. This DB is in 2010 and is linked to sql 2005 tables.

Leesha

[HansV]

You could try to save the record before running the update query:

Code: Select all

    If Me.Dirty Then Me.Dirty = False

[Leesha]

Nope, that didn't work either. I'm just going to do a work around. This new piece of code will be handy on other projects so all is not lost!
Thanks,
Leesha

[BenCasey]

Hi,
I am working in a DB that has tables linked to sql server. I have a form set to continuous When I do an update query I get a write error that I am about to save to a record that has been edited by another user. The save button is not enabled. Also, I have a timestamp field in the table which I thought prevented this. Is there any way around this?
Thanks,
Leesha

Sometimes, Access can have two instances running on the same machine which will give this error.
Have a look in Task Manager and see.

I presume you have the setting to allow multi-user.

[santosm]

Hi All,
I have a SQL table that has a number of bit fields. I forgot to set a couple of those fields to a default of 0 as the value. Five records were created and then the user called to tell me they got a write error on one of the records. I quickly went to the table and corrected the problem, went into the front end and tried updating the table to make sure those fields were set to 0 but the 5 records remained un-editable in Access. I could update them with a query or in the SQL server manager software but when opening the table directly in Access, the records were still not able to be edited. Is this normal? I ended up just deleting the records and recreating them.

Thanks,
Mark

[HansV]

Strange - I would have expected the records to become editable in Access after you set the value to 0 in SQL Server. I cannot explain why that didn't work for you.

[santosm]

Hi Hans,
Yes, very strange. I could update the record only using a query or by the SQL manager. I also couldn't delete the record directly but could use a delete query to get rid of them.

Thanks,
Mark

[Wendell]

I presume these were linked tables - did you try deleting the links and then re-linking them? I've seen an occasional bizarre behavior in ODBC linked tables that could only be corrected by doing that.

[santosm]

Hi Wendell,
Yes, linked and I always re-link the tables after making changes as it never maps over the changes unless you do that. I read somewhere that it is that way for data integrity. Anyhow, the 5 records could not be directly edited in Access but I could write an update query to get the values set properly. Then I tried editing the records manually and still not luck. I finally just deleted them and created them new. Problem solved but not explained. I will look into the driver thing, I am not sure which one we are using.

Thanks,
Mark