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=SERVE
'qdf.ODBCTimeout = timeout
qdf.sql = sql
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset(dbOpenSn
If rst.RecordCount > 0 Then
e = 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