Recordset not updateable in ADP - tried everything

[Uiop8000]

Im using Access XP/2002 and SQL server 2000 no SPs on either.
I have the following:

tblService
PK: serviceID
servorg --- 1 to M ----- tblOrganisation.orgID
servsite ----------- 1 to M ----- tblSite.siteID
servname

tblOrganisation
PK: orgID
orgname

tblSite
PK: siteID
sitename
siteorg --- 1 to M ----- tblOrganisation.orgID

I have VIEW1:

select servname, orgname, sitename
from tblservice
join tblorganisation on servorg = orgid
join tblsite on servsite = siteid

In query analyser I can use VIEW1 to update fields in tblService

When the view is bound to a form in access, or when the view is opened
in access and I try to update ONLY a field from tblService using the
view i get the dreaded "recordset not updateable" error.

PKs are set. Unqiue table is set. WITH_METADATA doesn't help.
None of the criteria in access help "Updating data in an access
project" are voilated. Recordset type is "updateable snapshot" and
allowedits etc. = yes
I am logged on as SA with full access to all db objects.

Me.recordset.cursorlocation = adUseClient
Me.recordset.cursortype = adOpenStatic
Me.recordset.connectionstring =
Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data
Source=HOMEPC;User ID=sa;Initial Catalog=db1SQL;Data
Provider=SQLOLEDB.1

Putting the sql text of the view into the Form's rowsource doesn't
work.

If I change the view to

select servname, orgname
from tblservice
join tblorganisation on servorg = orgid
join tblsite on servsite = siteid

or

select servname, sitename
from tblservice
join tblorganisation on servorg = orgid
join tblsite on servsite = siteid

it works but I need both fields.

Help!! Any help would be very much appreciated, I have searched the
msg boards
with no luck.
Thanks in advance

[Matthew Hansel]

I think I may be able to help.

This is what I had to do for the SAME issue. I created an ID field
that was of data type uniqueidentifier. The default value for that
field was newid() and it was set as the ROWGUID. I then ran a query
from query analyzer along the lines of this:

update masterschools
set masterschools.id = newid()
where masterschools.id is null

I then created the index for that table based on the ID field (making
it a unique index).

Good luck and I hope this helps.

--Matt

On Tue, 03 Sep 2002 14:48:20 -0700, John <jo...@serbin.com> wrote:

>Hi Matthew,
>
>I use the uniqueidentifier data type and set the
>isRowGuid to true for the primary key. You'll
>notice the default value automatically becomes
>newid() which generates a new GUID when
>a column is inserted with no value being passed
>to the column, which is the whole point. There
>may be a lag when adding this column to a large
>table as each row needs to run the newid()
>function. HTH.
>
>Cheers,
>John

[John]

Mathew has the right idea. To make it generic, a
view is updateable only if primary key for every
involved table is part of the view. Otherwise SQL
Server has no way of identifying the proper record
in the underlying tables.

Cheers,
John

[Uiop8000]

Hi Matthew and John, thanks for your replies

Just after posting I actually managed to get it work. To simplify my
post I left out a few details which actually make the difference. The
actual view is

SELECT dbo.tblService.*, dbo.tblSites.[Chargeable Parking],
dbo.tblOrganisations.Name, dbo.tblSites.SiteNum,
dbo.tblSites.SiteName
FROM dbo.tblService
INNER JOIN dbo.tblSites ON dbo.tblService.SiteID =
dbo.tblSites.SiteID
INNER JOIN dbo.tblOrganisations ON
dbo.tblService.OrganisationID = dbo.tblOrganisations.OrganisationID

And each of the tables (tblSites, tblService, tblOrganisations) had a
timestamp field in them. tblSites had a unique index on SiteNum.

When I removed the timestamp field from tblSites, the view became
updateable! Strangely removing the timestamp field from
tblOrganisations instead of tblSites didn't make the view updateable.

I tried what you said John, and with the timestamp field in tblSite, I
could also make the view updateable if I included both PKs from
tblSite & tblOrganisation in the list of selected columns. Since
SiteNum had a unique index, it sufficed to only add tblOrganisation's
PK to make the view updateable irrespective of whether I had the
timestamp field or not.

Thanks again for your time
regards
uiop

[jib...@usac.unr.edu]

Many years later, but just ran into this myself. An Access .adp project had an updateable view in SQL server that worked just fine in both SQL Server and Access. Adding a rowVersion/timestamp to two of the 5 tables in the view (1 of which was the "Unique Table" in Access) caused us to start getting "This recordset is not updateable". Removing the timestamp fixed the application. I did not get a chance to test whether it would be updateable if I included more of the primary keys from the other tables.

Ironically, this happened while trying to move away from Access. Our new data layer will use timestamps for concurrency, but a few people were still accessing this one datasheet for quick filtering / edits of a large set of data.

On a traditional (mdb/accdb) Access model, I was able to get around this by giving the linked table (linked view) a primary key within Access ( http://stackoverflow.com/questions/9602687/vba-code-to-add-linked-table-with-primary-key )and including the timestamp field in the view.

Hope this helps someone who might be using Access views with timestamp fields.