Solved

Recordset is not updatable - how to avoid?

Posted on 2004-10-07
7
377 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
  • 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now