ADP write conflict issue SQL
Hi Experts,
I have a query that is working perfectly fine in MDB (linked to SQL), however when I run it in ADP and try editing, it gives me the attached error.
following is the query.
I have a query that is working perfectly fine in MDB (linked to SQL), however when I run it in ADP and try editing, it gives me the attached error.
following is the query.
SELECT DocumentTypes.DocumentName, Query1.*
FROM DocumentTypes LEFT JOIN (SELECT ED.*
FROM EmployeesDocuments ED
WHERE (((ED.EmployeeID)=2))) as
Query1 ON DocumentTypes.ID = Query1.DocumentTypeID;
I hope it help yours.
Relink the tables in Access using the “Linked table manager” and the “Write Conflict” issue was solved.
Know more @ https://support.microsoft.com/en-us/kb/280730
Relink the tables in Access using the “Linked table manager” and the “Write Conflict” issue was solved.
Know more @ https://support.microsoft.com/en-us/kb/280730
ASKER
@Jason,
Nope, this is an ADP project where there is no linking, every time you start the ADP, Access refreshes all BE objects.
Thanks,
Ben
Nope, this is an ADP project where there is no linking, every time you start the ADP, Access refreshes all BE objects.
Thanks,
Ben
Are you sure no one else is accessing the database at same time as you?
Since ADP's have SQL Server backends, they do have some differences/special considerations.
The first thing that jumps to mind based on your posted image is your Yes/No field. In the SQL Server BE, make sure that you are not allowing NULLs in your bit fields (set the default to 0), You will definitely see Write Conflict errors in an Access UI otherwise.
An alternative solution is to add a field to the table with timestamp data type, or RowVersion datatype in more recent versions of SQL Server.
You do not need to modify your Access front end for any of these solutions - just the SQL back end.)
The first thing that jumps to mind based on your posted image is your Yes/No field. In the SQL Server BE, make sure that you are not allowing NULLs in your bit fields (set the default to 0), You will definitely see Write Conflict errors in an Access UI otherwise.
An alternative solution is to add a field to the table with timestamp data type, or RowVersion datatype in more recent versions of SQL Server.
You do not need to modify your Access front end for any of these solutions - just the SQL back end.)
ASKER
@Vitor,
Absolutely, the table is on test & only I have access to it.
@mbizup,
I tried both, the timestamp & the default value, including not allowing nulls and nothing helped.
FYI- I was looking at the SQL trace monitor to see what is the difference, and realized the following, when access is using linking tables it is smart enough to perform inserts in case of left join operations where ID is null on the other side..while ADP is trying to perform an update and does not find anything..
ADP:
MDB:
See attached.
Wondering if someone came across this issue and found a workaround?
Thanks,
Ben
Untitled.png
Absolutely, the table is on test & only I have access to it.
@mbizup,
I tried both, the timestamp & the default value, including not allowing nulls and nothing helped.
FYI- I was looking at the SQL trace monitor to see what is the difference, and realized the following, when access is using linking tables it is smart enough to perform inserts in case of left join operations where ID is null on the other side..while ADP is trying to perform an update and does not find anything..
ADP:
exec sp_executesql N'UPDATE "PlacementNP".."EmployeesDocuments" SET "EmployeeID"=@P1 WHERE "ID" IS NULL AND "DocumentTypeID" IS NULL AND "EmployeeID" IS NULL AND "DocumentDate" IS NULL AND "CheckedYN" IS NULL AND "DateEntered" IS NULL AND "Initial" IS NULL AND "Notes" IS NULL',N'@P1 int',75986MDB:
exec sp_executesql N'INSERT INTO "dbo"."EmployeesDocuments" ("DocumentTypeID","EmployeeID") VALUES (@P1,@P2)',N'@P1 int,@P2 int',4,75986See attached.
Wondering if someone came across this issue and found a workaround?
Thanks,
Ben
Untitled.png
Check that your INSERT has a process ID (7508) different from the UPDATE one (7648).
If you're working with recordsets an insert will trigger a change and that's why you might see the error.
If you're working with recordsets an insert will trigger a change and that's why you might see the error.
ASKER
@Vitor,
Of course the processID's are different, as they come from two app, one from an ADP and the other from MDB.
In both cases, I am working with the results of the query originally posted.
I believe there should be a workaround to this problem.
Thanks,
Ben
Of course the processID's are different, as they come from two app, one from an ADP and the other from MDB.
In both cases, I am working with the results of the query originally posted.
I believe there should be a workaround to this problem.
Thanks,
Ben
Back to your bit field... Did you update any nulls in existing records to 0 (false)?
Update yourtable set bitfield = 0 where bitfield Is NULL
For all bit fields....
Update yourtable set bitfield = 0 where bitfield Is NULL
For all bit fields....
ASKER
Hi,
Yes sir, there is only one bit field and I made sure to update. (otherwise it would not let me enforce the no null rule).
Thanks,
Ben
Yes sir, there is only one bit field and I made sure to update. (otherwise it would not let me enforce the no null rule).
Thanks,
Ben
Hi Ben,
the write conflict error occurs because Access checks the current record from the view/table in the local recordset against the same in the backend and if there is any difference it thinks that another user has made any change.
In case of an MDB you have linked tables which are internally converted to datatypes of JET (in ACCDB it is ACE). But there are problematic types like bit and float, where the conversion sometimes creates wrong values so in that case you can do what mbizup recommends regarding the bit field, the complete information can be found here which also covers the float datatype problem and why you need to add a timestamp column:
https://support.microsoft.com/en-us/kb/280730
But as you are using an ADP there is no need to do that, as you can see as MS recommended solution with the bit field problem in the article above: "Use a Microsoft Access project (*.adp) instead of an Access database (*.mdb)."
Although you don't need a timestamp column for ADPs I would nevertheless recommend to do that because SQL Server compares the records then automatically only using this column which makes it easier and faster for the row update check.
I am working with ADPs now every day since 2008 and have a lot bit fields, there is no problem using NULL here, it can be handled by the checkbox control with the Tristate setting, no problem. You also have no problem with float datatypes as there is no conversion of datatypes here, no JET/ACE in between, that's why I love ADPs.
But the Access database engine can do some things which SQL Server does not support, i.e. it can use an updatable query over some tables and if you want to insert a new row it is able to create the ID of the slave table first and then create the corresponding record in the master table and insert the foreign key at the right place. It is also able to handle the UPDATE command over several tables without any problem, that's a cool feature which I think only Access is able to do.
The "normal" database servers are not able to do that. If you have a view over two or more tables then UPDATE will only update one table at a time. If the user in the frontend knows what he does he can use such a view if he only change values of one table at the same time, save it and then change values of another table of this view - but mostly the user of course doesn't know anything about the underlying tables and even should not know that.
SQL Server can update such views if values in the slave table exist (best is: INNER JOIN). But if you use LEFT JOIN then you can only edit values in the slave table(s) if there is a corresponding record, it will not insert a fitting record in the slave table if there is no corresponding record like Access does with JET/ACE. The same of course with INSERT.
That's the reason why you see an INSERT command in the MDB version, I guess you have tried to insert a value in the slave table where there was no existing record which let Access insert a record in the slave table first.
ADP can't do that and does what it should do: Trying an UPDATE command on the complete view, which leads to a problem on SQL Server and possibly is the reason for the error: Check the SQL Server profiler log for error messages, normally you'll find errors here which the Access frontend sometimes misunderstand showing an error which has nothing to do with the reality.
You now have several possibilities to solve that problem:
I personally prefer the first way, separating the tables in separate forms and using not updatable list forms for overview only. Normally I create a list form and a detail form to edit a single record, in a detail form I can separate different tables in different subforms so that the user is not aware of this.
By the way: If you have any triggers on your tables you also need to check them, it can happen that the triggers are the reason for the write conflict problem if they change values in the tables which Access is not aware of (this would also change the timestamp column so the row is "updated by another user").
Cheers,
Christian
the write conflict error occurs because Access checks the current record from the view/table in the local recordset against the same in the backend and if there is any difference it thinks that another user has made any change.
In case of an MDB you have linked tables which are internally converted to datatypes of JET (in ACCDB it is ACE). But there are problematic types like bit and float, where the conversion sometimes creates wrong values so in that case you can do what mbizup recommends regarding the bit field, the complete information can be found here which also covers the float datatype problem and why you need to add a timestamp column:
https://support.microsoft.com/en-us/kb/280730
But as you are using an ADP there is no need to do that, as you can see as MS recommended solution with the bit field problem in the article above: "Use a Microsoft Access project (*.adp) instead of an Access database (*.mdb)."
Although you don't need a timestamp column for ADPs I would nevertheless recommend to do that because SQL Server compares the records then automatically only using this column which makes it easier and faster for the row update check.
I am working with ADPs now every day since 2008 and have a lot bit fields, there is no problem using NULL here, it can be handled by the checkbox control with the Tristate setting, no problem. You also have no problem with float datatypes as there is no conversion of datatypes here, no JET/ACE in between, that's why I love ADPs.
But the Access database engine can do some things which SQL Server does not support, i.e. it can use an updatable query over some tables and if you want to insert a new row it is able to create the ID of the slave table first and then create the corresponding record in the master table and insert the foreign key at the right place. It is also able to handle the UPDATE command over several tables without any problem, that's a cool feature which I think only Access is able to do.
The "normal" database servers are not able to do that. If you have a view over two or more tables then UPDATE will only update one table at a time. If the user in the frontend knows what he does he can use such a view if he only change values of one table at the same time, save it and then change values of another table of this view - but mostly the user of course doesn't know anything about the underlying tables and even should not know that.
SQL Server can update such views if values in the slave table exist (best is: INNER JOIN). But if you use LEFT JOIN then you can only edit values in the slave table(s) if there is a corresponding record, it will not insert a fitting record in the slave table if there is no corresponding record like Access does with JET/ACE. The same of course with INSERT.
That's the reason why you see an INSERT command in the MDB version, I guess you have tried to insert a value in the slave table where there was no existing record which let Access insert a record in the slave table first.
ADP can't do that and does what it should do: Trying an UPDATE command on the complete view, which leads to a problem on SQL Server and possibly is the reason for the error: Check the SQL Server profiler log for error messages, normally you'll find errors here which the Access frontend sometimes misunderstand showing an error which has nothing to do with the reality.
You now have several possibilities to solve that problem:
Instead of creating one view with all fields of all tables create a view for the master table and one for the slave table and then create a main form and subform in the ADP frontend to enter data in both tables. Here ist also would be no problem to i.e. add n document records to one master record, easier for the user and no issue with the UPDATE problem.
Create an INSTEAD OF trigger on the view itself - that's the only possibility SQL Server offers to update a view with more than one table with values in both tables. This solution is not easy, because the view need to be bound to a schema and that also stops you from changing the design of the underlying tables until you remove the binding. And writing the trigger is also not easy because a trigger always must be able to handle more than one record at the same time.
Create a stored procedure to handle the insertion of the slave record using a button in the frontend. That's similar to the first point, you would have the complete view as i.e. continous form but set all slave fields to be not editable in the form. If the button is clicked, open a popup form with the slave fields to edit/add a record using the stored procedure (or maybe using a second view)
I personally prefer the first way, separating the tables in separate forms and using not updatable list forms for overview only. Normally I create a list form and a detail form to edit a single record, in a detail form I can separate different tables in different subforms so that the user is not aware of this.
By the way: If you have any triggers on your tables you also need to check them, it can happen that the triggers are the reason for the write conflict problem if they change values in the tables which Access is not aware of (this would also change the timestamp column so the row is "updated by another user").
Cheers,
Christian
Are you sure no one else is accessing the database at same time as you?
@Vitor,
Absolutely, the table is on test & only I have access to it.
@Vitor,
Of course the processID's are different, as they come from two app, one from an ADP and the other from MDB.
So, you are contradicting yourself. There are two applications accessing the database and the table at the same time.
@Vitor,
Absolutely, the table is on test & only I have access to it.
@Vitor,
Of course the processID's are different, as they come from two app, one from an ADP and the other from MDB.
So, you are contradicting yourself. There are two applications accessing the database and the table at the same time.
@Vitor:
As far as I have understood Ben he only checked if the same thing works in MDB just for test purpose, it's not a real application. As I know from former questions from Ben he normally is working with ADPs only.
Cheers,
Christian
As far as I have understood Ben he only checked if the same thing works in MDB just for test purpose, it's not a real application. As I know from former questions from Ben he normally is working with ADPs only.
Cheers,
Christian
Yes but that doesn't avoid him to have more than one connection to the table, doesn't it?
And that's what the error is telling him. Someone changed the records while he was browsing it.
And that's what the error is telling him. Someone changed the records while he was browsing it.
Sure, but as you already quoted by yourself from Ben: "the table is on test & only I have access to it." - so that means to me he has the write conflict problem in a test environment where nobody else is working on originally in the single execution of one ADP frontend only. To create an MDB to see if it happens here also is only a further test (and of course has a different ID) but if you not start editing on one frontend then change to the other then start editing and save the same record and then switch back to the first one and try to save the same record this error cannot happen. Only in this case it would indeed be a normal write conflict problem. Simply looking at the records or changing different records at the same time does not end with a write conflict otherwise we would all not be able to create multiuser-frontends...
Ok, I might used wrong words on my question. By "no one else" I wanted to mean "no other process". What I wanted to check if there was concurrent process changing the table records.
ASKER
Hi Bit,
My question is a general one, how to accomplish a screen like the one attached (popup screen when clicking the button) to users at the time they open a new file.
If you take a look at the following posts, you will get an idea where I am coming from..
https://www.experts-exchange.com/questions/28893639/Design-strategy-question.html?anchorAnswerId=41363117#a41363117
I finally got it to work & was just waiting for the following peace
https://www.experts-exchange.com/questions/28897392/Help-Needed-in-design-of-MS-Access-form.html?anchorAnswerId=41369416#a41369416
When I realized its not working in ADP's..
So perhaps the simplest way to accomplish would be to have all fields in one single table (like my first option in first link above) ?
Let me know if you need more clarification regarding the request.
P.S. thanks for answering @Vitor on my behalf.
Thanks,
Ben
testDocTypes.mdb
My question is a general one, how to accomplish a screen like the one attached (popup screen when clicking the button) to users at the time they open a new file.
If you take a look at the following posts, you will get an idea where I am coming from..
https://www.experts-exchange.com/questions/28893639/Design-strategy-question.html?anchorAnswerId=41363117#a41363117
I finally got it to work & was just waiting for the following peace
https://www.experts-exchange.com/questions/28897392/Help-Needed-in-design-of-MS-Access-form.html?anchorAnswerId=41369416#a41369416
When I realized its not working in ADP's..
So perhaps the simplest way to accomplish would be to have all fields in one single table (like my first option in first link above) ?
Let me know if you need more clarification regarding the request.
P.S. thanks for answering @Vitor on my behalf.
Thanks,
Ben
testDocTypes.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Bit,
For ADP you're the expert! So if you only know of those work around I guess there aren't any..(which would mimic the Access MDB behavior).
Thanks,
Ben
For ADP you're the expert! So if you only know of those work around I guess there aren't any..(which would mimic the Access MDB behavior).
Thanks,
Ben
Hi Ben,
that are the solutions which are possible but that are of course not all possible solutions, only some. Although I work alot with ADPs that doesn't mean that there are other experts which have additional ideas..:-)
The MDB behaviour is what I described in the second solution above. That's a special service of Access, normally databases don't behave that way.
Here you can find my reposition solution which I use in my ADPs to go back to the current record (including the screen position) - so for the user there is no difference if Access does it automatically or if you do that in the background, requery and reposition: https://www.experts-exchange.com/articles/11515/Reposition-requery-with-positioning-to-the-recent-record.html
Cheers,
Christian
that are the solutions which are possible but that are of course not all possible solutions, only some. Although I work alot with ADPs that doesn't mean that there are other experts which have additional ideas..:-)
The MDB behaviour is what I described in the second solution above. That's a special service of Access, normally databases don't behave that way.
Here you can find my reposition solution which I use in my ADPs to go back to the current record (including the screen position) - so for the user there is no difference if Access does it automatically or if you do that in the background, requery and reposition: https://www.experts-exchange.com/articles/11515/Reposition-requery-with-positioning-to-the-recent-record.html
Cheers,
Christian
Hi mbizup,
yes, I love to work with ADPs as they offer in my eyes the only right way to work with a database server instead of the strange JET/ACE datatype conversion idea of ACCDB with all the following problems.
An ADP forces you to use a database in the right way and you get a complete other view how to use the backend server in the right way. I'm very sad about that MS has stopped supporting it with A2013, so A2010 is the last version I'll stuck on for a very long time and then maybe I will go on using .NET instead. Access without ADP leaves it as a toy, the last real professional feature of Access is gone now. I think we can wait until VBA is removed and replaced with silly macros then Access is finally dead.
I currently develop a database with several hundreds of tables for global use for a big company using A2010 ADPs so yes, I would say this is extensively...:-)
Cheers,
Christian
yes, I love to work with ADPs as they offer in my eyes the only right way to work with a database server instead of the strange JET/ACE datatype conversion idea of ACCDB with all the following problems.
An ADP forces you to use a database in the right way and you get a complete other view how to use the backend server in the right way. I'm very sad about that MS has stopped supporting it with A2013, so A2010 is the last version I'll stuck on for a very long time and then maybe I will go on using .NET instead. Access without ADP leaves it as a toy, the last real professional feature of Access is gone now. I think we can wait until VBA is removed and replaced with silly macros then Access is finally dead.
I currently develop a database with several hundreds of tables for global use for a big company using A2010 ADPs so yes, I would say this is extensively...:-)
Cheers,
Christian
ASKER
Hi Experts,
When you have a chance, please take a look at the following ADP issue..
https://www.experts-exchange.com/questions/28936511/ADP-issue-form-suddenly-started-giving-messages-while-adding-new-records.html
Thanks,
Ben
When you have a chance, please take a look at the following ADP issue..
https://www.experts-exchange.com/questions/28936511/ADP-issue-form-suddenly-started-giving-messages-while-adding-new-records.html
Thanks,
Ben
Hello All,
I am using .adp project for my MS access 2007. I am getting Write conflict error while trying to insert a new record in table. I have a trigger on a table where record is getting stored.
Could you please help me to resolve this issue?
I am using .adp project for my MS access 2007. I am getting Write conflict error while trying to insert a new record in table. I have a trigger on a table where record is getting stored.
Could you please help me to resolve this issue?
Hi,
please don't add a new request to an existing thread as comment because only the people in this thread will get informed about your issue. You need to create an own question to get answers.
Cheers,
Christian
please don't add a new request to an existing thread as comment because only the people in this thread will get informed about your issue. You need to create an own question to get answers.
Cheers,
Christian
ASKER
Hi Bit,
Has been a while already..good to hear from from you again:)
Has been a while already..good to hear from from you again:)
;-)
ASKER
testDocTypes.mdb