Can't update rows in a view if it includes a left-outer-joined bitcolumn.

[Kipp Woodard]

I have a SQL Server 2008 view linked to Access 2007.

I cannot make updates to the rows if the view includes a bit data-type
column from a table that is left-outer-joined. In this case, if I
change any values in a row of the view, I get the message "the record
has been changed by another user...." with options to copy the data to
the clipboard or drop the changes.

If I exclude this column from the view then the view is updateable. I
can get this column into the view without having this issue by making
it a calculation, like [1 * <column-name>].

What's going on with this?

 

[Sylvain Lafontaine]

First, this newsgroup is about ADP and has nothing to do with MDB/ACCDB's
ODBC Linked Tables or Views; so you should consider the possibility of
reposting this question in a more appropriate newsgroup where you'll have
more chances of finding knowledgeable peoples about this particular problem.

Second, bit fields are a known source of problems with Access - both ADP and
MDB/ACCDB - particularly when they are nullable and without any default
value. Make sure that this bit field is not nullable and has a default
value of either 0 or 1. If you make any change, don't forget to refresh the
ODBC Link after that.

Make sure also that you have all the latest service packs (and hotfixes?)
installed for your version of Access 2007.

If nothing of this can work, then consider the possibility of changing the
bit field to a small integer but then, the value True will be stored as -1
instead of 1.

Also, if you are working with a single form - instead of a continuous form -
you could replace the bound control with an unbound control and make the
necessary change to the underlying recordset when entering/leaving the
control/record but this is not a pretty solution.

Does this problem happen only when using a Left Outer Join or if it's also
happen with an ordinary Inner Join?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Kipp Woodard]

Thanks Sylvain.

I just did some more investigation and found that it is only in the
outer join that the issue occurs.

More specifically, it only occurs on the rows of the view when there
is no match in the outer joined table.
In other words, some rows are updateable, some are not.

I have a work-around, but posted this hoping to learn more about the
issue.

Kipp

First, this newsgroup is about ADP and has nothing to do with MDB/ACCDB's
ODBC Linked Tables or Views; so you should consider the possibility of
reposting this question in a more appropriate newsgroup where you'll have
more chances of finding knowledgeable peoples about this particular problem.

Second, bit fields are a known source of problems with Access - both ADP and
MDB/ACCDB - particularly when they are nullable and without any default
value.  Make sure that this bit field is not nullable and has a default
value of either 0 or 1.  If you make any change, don't forget to refresh the
ODBC Link after that.

Make sure also that you have all the latest service packs (and hotfixes?)
installed for your version of Access 2007.

If nothing of this can work, then consider the possibility of changing the
bit field to a small integer but then, the value True will be stored as -1
instead of 1.

Also, if you are working with a single form - instead of a continuous form -
you could replace the bound control with an unbound control and make the
necessary change to the underlying recordset when entering/leaving the
control/record but this is not a pretty solution.

Does this problem happen only when using a Left Outer Join or if it's also
happen with an ordinary Inner Join?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

I have a SQL Server 2008 view linked to Access 2007.

I cannot make updates to the rows if the view includes a bit data-type
column from a table that is left-outer-joined.  In this case, if I
change any values in a row of the view, I get the message "the record
has been changed by another user...." with options to copy the data to
the clipboard or drop the changes.

If I exclude this column from the view then the view is updateable.  I
can get this column into the view without having this issue by making
it a calculation, like [1 * <column-name>].

What's going on with this?- Hide quoted text -

- Show quoted text -