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
SQL Write Error
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
SQL Write Error
You do not have the required permissions to view the files attached to this post.
Last edited by HansV on 02 Mar 2014, 00:07, edited 1 time in total.
Reason: to crop white space from image
Reason: to crop white space from image
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Write Error
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: SQL Write Error
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
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
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Write Error
You could try to save the record before running the update query:
Code: Select all
If Me.Dirty Then Me.Dirty = False
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: SQL Write Error
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
Thanks,
Leesha
-
- 4StarLounger
- Posts: 495
- Joined: 13 Sep 2013, 07:56
Re: SQL Write Error
Sometimes, Access can have two instances running on the same machine which will give this error.Leesha wrote: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
Have a look in Task Manager and see.
I presume you have the setting to allow multi-user.
Regards, Ben
"Science is the belief in the ignorance of the experts."
- Richard Feynman
"Science is the belief in the ignorance of the experts."
- Richard Feynman
-
- 3StarLounger
- Posts: 253
- Joined: 19 Apr 2010, 09:01
- Location: Indiana, USA
Re: SQL Write Error
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
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
Thanks,
Mark
Mark
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Write Error
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 253
- Joined: 19 Apr 2010, 09:01
- Location: Indiana, USA
Re: SQL Write Error
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
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
Thanks,
Mark
Mark
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: SQL Write Error
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.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- 3StarLounger
- Posts: 253
- Joined: 19 Apr 2010, 09:01
- Location: Indiana, USA
Re: SQL Write Error
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
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
Thanks,
Mark
Mark