Solved

Recordset is not updatable - how to avoid?

Posted on 2004-10-07
7
380 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

791 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