Solved

Recordset is not updatable - how to avoid?

Posted on 2004-10-07
7
383 Views
Last Modified: 2008-02-01
Hello all,

I have a strange problem here that appeared at no obvious reason. I have an SQL Server 7 called SERVER, a database DB1 with a table Table1 and a view to this table View1. When I try to edit the values in this table - either using Enterprise Manager or through Access's ODBC call - I receive a "Recordset is not updatable" (in Access) and "Transaction cannot start while in firehose mode" in EM.

I've made some research (http://support.microsoft.com/?id=286199), and it seems the problem is that both apps are using so-called Firehose cursors - i.e., they don't download the whole recordset, but only a part of it shown to the user. When I scroll the table all the way down in EM, it then allows me to edit values OK; however, in Access it doesn't work. The table remains read-only whatever I do...

Here is the code I use to get the data in Access:

Set qdf = dbs.CreateQueryDef(qdfname)
  qdf.Connect = "ODBC;DSN=DB1;SERVER=SERVER;UID=sa;DATABASE=DB1;Trusted_Connection=Yes"
 'qdf.ODBCTimeout = timeout
 qdf.sql = sql
 qdf.ReturnsRecords = True
 Set rst = qdf.OpenRecordset(dbOpenSnapshot)
   If rst.RecordCount > 0 Then
    rst.MoveLast
    rst.MoveFirst
    Forms![DOC]![Form1].Form.RecordSource = rst.name

The form Form1 has the following as RecordSource:

SELECT dbo_View1.Field1, dbo_View1.Field2, dbo_View1.Field3 FROM dbo_View1 ORDER BY dbo_View1.Field1;

I'm using Microsoft SQL Server ODBC Driver Version 03.80.0194.

The questions are the following:

1) Why could this error appear? We're using these Access interfaces for about a year, and everything was OK...
2) Can I disable these firehose cursors or anyhow alleviate the problem without modifying the Access code? We have tons of forms and scripts, I'll die rewriting them all. :(
3) If anything else is impossible - what modifications should I do in Access code to avoid using firehose cursor?

TIA for any help-hints-links-whatever...

(x-posted to SQL channel: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21158091.html)
0
Comment
Question by:Yaroslav_Buzko
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 7

Accepted Solution

by:
Jonathan Kelly earned 500 total points
ID: 12248005
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

change to  

rst = qdf.OpenRecordset(dbOpendynaset)

read write


does the underlying table have a unique index and does access know what it is ?

have you tried adding a WHERE clause to try and limit the number of rows returned ?

is the TSQL u have listed above the TSQL being fed into your code by your variable sql ?

have you tried just linking the view and then scrolling the table ?

how many rows are in the table ?
0
 
LVL 1

Author Comment

by:Yaroslav_Buzko
ID: 12248414
1) What do you mean by 'read write', sorry? I tried changing Snapshot to Dynaset, no luck.

2) No, I don't think Access is aware of a unique index, at least it isn't explicitly stated anywhere. The table itself does have an index, Field1.

3) Sorry, I missed this: in fact there is a simple WHERE clause, WHERE Field3 = '02'.

4) The TSQL is in the RecordSource property of the form Form1.

5) Yes, even if I link the table, it's still not updatable.

6) About 200 - not much.
0
 
LVL 1

Author Comment

by:Yaroslav_Buzko
ID: 12248806
I fixed it. The problem really was that Access lost the UNIQUE INDEX data on that linked table. I re-created the linked table and it worked. Thank you, Datrias.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 12248916
great
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 12248933
may I be so bold as to enquire why you graded the answer as  'B' ?
0
 
LVL 1

Author Comment

by:Yaroslav_Buzko
ID: 12249008
I used the FAQ as a reference. I assume that if you just told me "Go update unique index", that would be an A; as soon as you provided an array of possible issues that I had to research myself - that should be B.

However, I'm new here, so if  I undervalued your comment - please excuse me, cause I'm unfamiliar with the system yet.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 12249122
ummm - you have a valid point.
thanks
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question