473,320 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

"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 13054
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Michael Stevens | last post by:
Probably the wrong wording but since I'm not a scripter I won't claim to know what I'm talking about. I got this script from www.htmlgoodies.com <script language="JavaScript"> <!--...
3
by: maadhuu | last post by:
well,i am curious to know what would be the output of the following: delete this; basically , comment on this .
1
by: PeteCresswell | last post by:
I'm working on a new report in an MS Access DB. The first anomaly was a message "This action will reset the current code in break mode." when the report was run. Seems TB something about my...
0
by: Colleyville Alan | last post by:
My app is giving me this error. Run-time error 3211: The database engine could not lock table 'Sorted_Template' because it is already in use by another person or process. When I run the app...
1
by: Shapper | last post by:
Hello, I am accessing a value in a XML value: news.Load(Server.MapPath("xml/ news.rss")) newslabel.Text = CType(news.SelectSingleNode("rss version=&quot;2.0 &quot;/channel/title").InnerText, String) ...
6
by: ZRexRider | last post by:
I've been reading the newsgroups and most of the solutions for this message are simple and obvious but unfortunately don't solve my problem. I have an MS-Access 2002 ADP application that...
2
by: Jeff Gardner | last post by:
Greetings: I've a script written for paging through a given recordset with page links, etc. I want to be able to limit the number of page numbers displayed as a large query may result in 100 or...
17
by: radio1 | last post by:
Configuration: Access 2002 and SQL Server 2000 using a .ADP Project. I would VERY MUCH appreciate anyone's input into this problem I'm having. I have a form in Access that does not permit...
2
by: Eric Layman | last post by:
Hi, I have the following script below. I intend to have a dynamic array of non fixed size set rsseats = conn.execute("select * from seats") Dim arr(1) response.write ubound(arr) do while...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.