469,275 Members | 1,649 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

"This recordset is not updatable" - why not?

I have a very basic query that Access 2000 will not allow to be
updatable. I've gone through all the situations in the help file
where data cannot be updated, and I don't see that this query belongs
to any of those. I have checked all permissions, locks, etc.. - can't
find anything out of the ordinary that would explain it. Deleted the
DistinctRow - no change. The SQL version is below - anyone notice the
problem or have any suggestions? I'm sure it's something
simple/stupid, but, geez... I've already spent too long on this.
Thanks!
Martin Lacoste

(what it does - find where [incipit] field differs when [ID] field is
the same in both tables)

SELECT DISTINCTROW [Index]![Incipit], MASTER.Incipit, MASTER.FullText
FROM [Index] INNER JOIN MASTER ON [Index].ID = MASTER.ID
WHERE ([Index]![Incipit]<>[MASTER]![Incipit])
ORDER BY [Index].Folio, [Index].Sequence;
Nov 13 '05 #1
7 12791
"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...
I have a very basic query that Access 2000 will not allow to be
updatable. I've gone through all the situations in the help file
where data cannot be updated, and I don't see that this query belongs
to any of those. I have checked all permissions, locks, etc.. - can't
find anything out of the ordinary that would explain it. Deleted the
DistinctRow - no change. The SQL version is below - anyone notice the
problem or have any suggestions? I'm sure it's something
simple/stupid, but, geez... I've already spent too long on this.
Thanks!
Martin Lacoste

(what it does - find where [incipit] field differs when [ID] field is
the same in both tables)

SELECT DISTINCTROW [Index]![Incipit], MASTER.Incipit, MASTER.FullText
FROM [Index] INNER JOIN MASTER ON [Index].ID = MASTER.ID
WHERE ([Index]![Incipit]<>[MASTER]![Incipit])
ORDER BY [Index].Folio, [Index].Sequence;


The problem is caused by the DISTINCTROW predicate. You will need to write
your query some other way in order to make this recordset updateable,
(shouldn't use DISTINCTROW anyway IMO)
Nov 13 '05 #2

"John Winterbottom" <as******@hotmail.com> wrote in message
news:2k************@uni-berlin.de...

The problem is caused by the DISTINCTROW predicate. You will need to write
your query some other way in order to make this recordset updateable,
(shouldn't use DISTINCTROW anyway IMO)

I thought there was a KB article on this - here it is

http://support.microsoft.com/default...b;en-us;207761
Nov 13 '05 #3
Thanks for your replies, John. I had tried removing the DistinctRow -
no change :-( The Unique Values Property is set to 'no', also. Any
further thoughts? I thought this was a very simple query.

Thanks
Martin Lacoste

"John Winterbottom" <as******@hotmail.com> wrote in message news:<2k************@uni-berlin.de>...
"John Winterbottom" <as******@hotmail.com> wrote in message
news:2k************@uni-berlin.de...

The problem is caused by the DISTINCTROW predicate. You will need to write
your query some other way in order to make this recordset updateable,
(shouldn't use DISTINCTROW anyway IMO)

I thought there was a KB article on this - here it is

http://support.microsoft.com/default...b;en-us;207761

Nov 13 '05 #4
ma*******@rogers.com (Martin Lacoste) wrote in
news:d4**************************@posting.google.c om:
Thanks for your replies, John. I had tried removing the
DistinctRow - no change :-( The Unique Values Property is set
to 'no', also. Any further thoughts? I thought this was a
very simple query.

Thanks
Martin Lacoste


Some other things to check:
The Recordset Type property is snapshot.
there is a left join in one of the source queries.
one of the source tables is a linked .dbf or spreadsheet

The first is guaranteed to make the query uneditable. the other two
are sometimes fixable by setting the recordset type to inconsistent
updates.

Bob Quintal
Nov 13 '05 #5
Thanks for your reply, Bob. Recordset Type is dynaset. No left
joins. No dbfs or spreadsheet links. Sigh... The search
continues...

Thanks
Martin Lacoste

Bob Quintal <bq******@generation.net> wrote in message news:<71******************************@news.terane ws.com>...
ma*******@rogers.com (Martin Lacoste) wrote in
news:d4**************************@posting.google.c om:
Thanks for your replies, John. I had tried removing the
DistinctRow - no change :-( The Unique Values Property is set
to 'no', also. Any further thoughts? I thought this was a
very simple query.

Thanks
Martin Lacoste


Some other things to check:
The Recordset Type property is snapshot.
there is a left join in one of the source queries.
one of the source tables is a linked .dbf or spreadsheet

The first is guaranteed to make the query uneditable. the other two
are sometimes fixable by setting the recordset type to inconsistent
updates.

Bob Quintal

Nov 13 '05 #6
Primary Keys?
Nov 13 '05 #7
dc****@aol.comSPNOAM (DCM Fan) wrote in message news:<20***************************@mb-m07.aol.com>...
Primary Keys?


Yup - both tables have them - have tried making them a part of the
data returned, have taken them out. This shouldn't be this
complicated, should it?

Thanks again for your help!
Martin Lacoste
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Michael Stevens | last post: by
3 posts views Thread by maadhuu | last post: by
1 post views Thread by Shapper | last post: by
2 posts views Thread by Jeff Gardner | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.