Solved

Recordset is not updatable - how to avoid?

Posted on 2004-10-07
7
384 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

729 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