ADODB Recordset - BatchUpdate

[leannemurphy]

Hi guys,

I read that you should be able to loop through a client side recordset making changes as needed and then at the end do a batchupdate to send all changes at once to the server... however, I've never been able to get this to work as batchupdate only seems to update the current record and other changes are lost.

I use ADODB recordsets all the time to grab data, use it for whatever, and then close it when finished...

Anyone know why this isn't working for me?

Leanne

 

[Minty]

This will only work if your processing is wrapped in a transaction.
Generally, looping through a recordset is seen as inefficient compared to using an update query, which is intrinsically a batch operation, and can be rolled back easily if wrapped in a transaction.

 

[MajP]

Can you post example code where updatebatch method fails?

 

[arnelgp]

did you specify the Locktype as adLockBatchOptimistic?
when you do so, every time you execute .Update, your changes is saved to the Local Buffer.
then you execute UpdateBatch, to one time update the underlying table.

reference:
Updating and Persisting Data - ActiveX Data Objects (ADO) | Microsoft Learn

 

[leannemurphy]

This will only work if your processing is wrapped in a transaction.
Generally, looping through a recordset is seen as inefficient compared to using an update query, which is intrinsically a batch operation, and can be rolled back easily if wrapped in a transaction.

Hi @Minty,

Yes, if I can do an update query then I do one. But often I need information from the record in order to make a decision.

 

[Pat Hartman]

Yes, if I can do an update query then I do one. But often I need information from the record in order to make a decision.

Update queries allow joins to other queries????

 

[leannemurphy]

Update queries allow joins to other queries????

Yes I know that.

 

[Pat Hartman]

OK, but what was

But often I need information from the record in order to make a decision.

all about?

 

[leannemurphy]

I often prefer to look through some records before I make decisions about what I want to do next. I was asking why the batchupdate doesn't work for me when I use it. Sounds like that's because I need to wrap the code in a transaction. I'm an Access VBA developer, not SQL Server. I've done transactions before but a very long time ago.

 

[Minty]

Have a read here as a refresher on transactions :

How to use transactions in Microsoft Access VBA

How to use transactions in Microsoft Access VBA when executing multiple queries with VBA code.

codekabinett.com

 

[leannemurphy]

Thanks Minty. So if I were looping through an ADODB recordset and making changes to it, I could begin the transaction beforehand, make any changes, updatebatch and then Commit the transaction?

Also, on a side note, every time I reply to someone and it quotes them I am told I can't submit my reply due to it being spam like! If I delete their quoted reply then it doesn't!

 

[cheekybuddha]

Also, on a side note, every time I reply to someone and it quotes them I am told I can't submit my reply due to it being spam like! If I delete their quoted reply then it doesn't!

I think that is the site's new AI machine-learning algorithm passing judgement on the quality of people's replies!

(Really, I think you need a minimum number of replies yourself before you can start quoting people)