I have a SQL Server view that I am trying to use in an Access form and get a write conflict message whenever I attempt to update one of the linked tables. I think the problem is that I use two separate outer joins to the same table:
View is structured as follows:
Select * from CodeMapping as CM
outer join to Codes as SC on
CM.SourceCode = SC.Code
outer join to Codes as TC on
CM.TargetCode = TC.Code
The keys for these three tables are integer columns called row_key, but the Code values are alternate(natural) keys, but these have different values on each table. There are no integer values on CodeMapping to allow a join to Codes, which is why I use the Code column.
In many cases, I only get one of the two joins, so I have values from one table from the form but the other fields are empty. As an example, SC has a row_key of 4111 and TC has a null row_key as it did not have a matching row in the TC outer join.
When I change the field SC.flag1 on the form
Access generates the update statement something like
update Codes
set flag1 = 'YES'
where row_key = 4111 and row_key is null
This appears to be because Access has determined that the underlying table for the flag1 column is Codes, but when it tries to determine the key values for the Codes table, it ends up with the key from the tables referenced as SC and TC in the view even though its trying to update the SC version.
How do I get around this?
I've tried numerous things like adding timestamp columns to the tables. This didn't help. I also divided the view into two views -
View1 joins CodeMapping to Codes as SC
View2 joins CodeMapping to Codes as TC
If I use these individually on a form, no problems. If I combine the views:
View3 joins View1 to View2
I'm back where I started from with the write conflict.
Anyone encounter this before? Any idea how to get around the error?
View is structured as follows:
Select * from CodeMapping as CM
outer join to Codes as SC on
CM.SourceCode = SC.Code
outer join to Codes as TC on
CM.TargetCode = TC.Code
The keys for these three tables are integer columns called row_key, but the Code values are alternate(natural) keys, but these have different values on each table. There are no integer values on CodeMapping to allow a join to Codes, which is why I use the Code column.
In many cases, I only get one of the two joins, so I have values from one table from the form but the other fields are empty. As an example, SC has a row_key of 4111 and TC has a null row_key as it did not have a matching row in the TC outer join.
When I change the field SC.flag1 on the form
Access generates the update statement something like
update Codes
set flag1 = 'YES'
where row_key = 4111 and row_key is null
This appears to be because Access has determined that the underlying table for the flag1 column is Codes, but when it tries to determine the key values for the Codes table, it ends up with the key from the tables referenced as SC and TC in the view even though its trying to update the SC version.
How do I get around this?
I've tried numerous things like adding timestamp columns to the tables. This didn't help. I also divided the view into two views -
View1 joins CodeMapping to Codes as SC
View2 joins CodeMapping to Codes as TC
If I use these individually on a form, no problems. If I combine the views:
View3 joins View1 to View2
I'm back where I started from with the write conflict.
Anyone encounter this before? Any idea how to get around the error?
