473,322 Members | 1,504 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,322 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 13055
"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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
1
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.