CODEWITHSUNDEEP
vbaoracle-databasems-accessadodb
LYGT
LYGT

Reputation: 21

Cant add fields to ADO recordset - this operation is not allowed in this context

I need to add fields to an existing ADO recordset from an oracle server. This is my code I tried, but I get the error

Operation not allowed in this context

With ADORec
Set .ActiveConnection = ADOConn
.Source = SQL
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With
ADORec.AddNew
ADORec.Fields.Append "calcField", adInteger, , adFldUpdatable

SQL gets a list of IDs.

How can I achieve an extra column in my recordset where I can run a calculation within vba?

Or: What would be your approach on this problem in the first place?

Upvotes: 2

Views: 978

Answers (1)

Erik A
Erik A

Reputation: 32642

You can very rarely (= pretty much never) add fields to ADODB recordsets.

An ADODB recordset is, by default, bound to the table it came from, and making changes to it should make changes to that table. That's also clearly not your intent. While you can sever the connection or copy over the design and data to an unlinked recordset, that tends to lead to trouble.

Also, .AddNew refers to adding a new record, not a new field.

If you want to display calculated results in a form, do the calculation in the control source. Split forms allow for that.

Just add a control to contain the result of your caclulation, and set the control source equal to =MyFunctionToDoCalculation(InpArguments)

As a general rule, though, do the calculations in SQL if you can, both for performance and reliability reasons.

If you want to perform multi-row calculations (e.g. a moving average), comment and I'll share a substantially more complicated approach that also has more side-effects (such as errors when sorting/filtering/refreshing).

Upvotes: 1

Related Questions