ADO RS.UpdateBatch - how do I get it to work? (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:24
Joined
Oct 29, 2018
Messages
13,760
So the code I'm using is more or less lifted from that article. I believe the recordset is connected initially in order to populate it, then it's disconnected. Then it's updated via the code she has on the form. Then the recordset is re-connected to the table. Then she runs UpdateBatch to write the changes to the table. Through testing, I can see that my subform successfully modifies the data in the recordset. The reconnection also appears to work. But UpdateBatch doesn't do what it ought to do.
Hi. I looked over the referenced 2008 article on TechRepublic and tried to download the sample database but couldn't. It would have been nice to see it in action. However, I tried to duplicate the functionality in your database, not using the exact same technique shown in the article, but in the way of how I understood the approach being described in the article. Unfortunately, it didn't work as I had hoped - that's why seeing a sample database would be nice. If anyone can download the sample database, please share it here. In the meantime, I have attached my attempt in case someone can see what I was missing to make it work. Open the form "frmDemoSingle." Cheers!
 

Attachments

  • TestingDisconnectedRS.zip
    52.2 KB · Views: 21

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:24
Joined
May 21, 2018
Messages
4,155
The issue appears to be with binding the recordset to the subform. If you take your code, disconnect, do some updates in code, reconnect, then the update batch works fine. All the examples use code to do the updates. Is there an example of doing form updates? Is it possible that an ADO recordset bound to a form does not cache the updates?

If you cannot find a solution maybe the workaround is to clone the disconnected recordset and set that to the subform. Then write all changes to the disconnected as well.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:24
Joined
Oct 29, 2018
Messages
13,760
Hi. I looked over the referenced 2008 article on TechRepublic and tried to download the sample database but couldn't. It would have been nice to see it in action. However, I tried to duplicate the functionality in your database, not using the exact same technique shown in the article, but in the way of how I understood the approach being described in the article. Unfortunately, it didn't work as I had hoped - that's why seeing a sample database would be nice. If anyone can download the sample database, please share it here. In the meantime, I have attached my attempt in case someone can see what I was missing to make it work. Open the form "frmDemoSingle." Cheers!
Hey. I've made some good progress updating the table en masse, but I had to quit for the day. I'll keep working on it tomorrow. Have a good weekend!
 

arnelgp

error reading drive A:
Local time
Today, 12:24
Joined
May 7, 2009
Messages
11,183
you may test your Disconnected recordset.
 

Attachments

  • TestingDisconnectedRS.zip
    50.6 KB · Views: 23

Bennet

New member
Local time
Today, 04:24
Joined
May 23, 2020
Messages
9
I'm blown away by the helpfulness of the responses here. Thanks so much for all the thoughts and assistance, and my sincere thanks to arnelgp for providing a working solution.

If I've understood your solution correctly, MajP had it right. Binding the recordset to the subform somehow breaks the potential to later use UpdateBatch. A second, cloned recordset resolves the issue.

Excellent work and thanks again. Wow...

ba1f8cf364a77d984f05741ae662126f.jpg
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:24
Joined
Oct 29, 2018
Messages
13,760
I'm blown away by the helpfulness of the responses here. Thanks so much for all the thoughts and assistance, and my sincere thanks to arnelgp for providing a working solution.

If I've understood your solution correctly, MajP had it right. Binding the recordset to the subform somehow breaks the potential to later use UpdateBatch. A second, cloned recordset resolves the issue.

Excellent work and thanks again. Wow...

View attachment 82418
Hi. I eventually came to the same conclusion in that using a single recordset bound to a table is problematic in Access. I thought of using a temp table, which is basically the same as using a second recordset. Instead, the solution I came up with was to use an unbound form, which is based on the TechRepublic article. This approach works with only one recordset because it is not bound to a form. However, after seeing both solutions (duplicate recordset and unbound form), I can't say I can think of a situation where I would use this.

Unfortunately, I tested both Arnel's solution (using a duplicate/separate recordset) and my own solution (using unbound form), and I am sorry to say both of them suffer from multi-user issues. In both demo, if a second user makes and commits any changes to the table while the first user is still using a disconnected recordset, the first user will get an error when they try to commit their changes to the table.

Make sure you do a thorough testing if you're planning to use this approach in your real project and let us know how you solved that problem. Cheers!
 

Users who are viewing this thread

Top Bottom