Software

Ask something
Question Locked

ADODB recordset update refused

0 helpful
5 replies

by terry.walters

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have created a union query in string 'SQLstr' to select data by individual name and then provide column totals. This is sorted by person's name (column 0), and to ensure the 'totals' row is at the bottom, I use name 'zz' on the union query. This is then put into a recordset called RS like this;

Set DB = CurrentProject.Connection
Set RS = New ADODB.Recordset
RS.Open SQLstr, DB, adOpenDynamic, adLockOptimistic.

I then want to tidy up the 'zz' name before sending the data to a MS Word table for output. When I use the following loop it finds the record but the fails with 'Run time error, cannot update - database or object is read only'.

Do While Not RS.EOF
If RS.Fields(0) = "zz" Then
RS.Fields(0) = " "
RS.Update
End If
RS.MoveNext
Loop

Any suggestions as to why it fails, or how to update the 'totals' row in this dataset?

5 replies

by jck

because your 'zz' is based on an aggregate command function result of a COUNT() or SUM() and, in kind, that is not an updatable field related to a table but a function call which is read only?

If i understand your explanation correctly, I believe that is the issue. But, I was not clear on the entirety of your situation.

Hope that helps.

by terry.walters

Thanks for your input.

The second part of the union query starts

SELECT 'zz', 'Totals', ' ', Count(Tbldata.Reference) AS Volume, Sum(Tbldata.Completed) AS Checks, Format([Checks]/[Volume],'0.0%') AS Pct ..."

So most fields are agregated, but the initial few are constants. As far as I understood (and this is where the problem started) the output from the query was saved in the the RS dataset as a set of individual records, and therefore each field in any record should be editable. I have since read that the RS dataset only holds pointers to the records identified, and not a copy of the records, so I don't know how or where the final totals row is held, and if it is only a virtual record then this may explain why it can't be changed.

Clear as mud, eh!

by jck

the RS holds the pointers to the various data you load into it.

If I'm correct: when you use RS.Update, you are telling the recordset to update all of its columns to their respective items, not just those containing column-referencing data.

Any aggregate function result will have the .Update try to update its value, for which there is no pointer to a column to update and would fail.

And actually, I believe that RS does hold a copy in memory and changes are not committed to the DB until you execute Update. However, I could be wrong. Been a while.

And yes, DBA stuff is always so crystal clear...lmao.

Good luck with that though. I feel your pain. Been there, done that, got the t-shirt. :)

by tony hopkinson

tidying up.

Presumably you have an order by after the union
and 'zz' is just to make sure the totals row is at the bottom

Use an inner query

Select * From (Select something from somewhere order by column1) firstpart
Union
SELECT '', 'Totals', ' ', Count(Tbldata.Reference) AS Volume, Sum(Tbldata.Completed) AS Checks, Format([Checks]/[Volume],'0.0%') AS Pct

firstpart is just a dummyname for the query in parentheses.

e.g.

Select * From (
Select ID as [Description], Count() as [Number] From AllNames Group By ID, Order By 2) firstPart
Union
Select 'Totals',Count() From AllNames

PS RS.Update writes from the recordset to the db, to do that it needs to be able to write an update statements that uniquely identifies each record to change, which it cannot ever do with your query. Most times use of union the recordset just gets marked as non updatable because it would be too difficult to figure out those rare occasions that it might be possible.

HtHs

This discussion is closed to new replies.