Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Recordset is not updatable - how to avoid?

Posted on 2004-10-07
7
Medium Priority
?
388 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 1500 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

609 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