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

Bennet

New member
Local time
Today, 18:14
Joined
May 23, 2020
Messages
13
Hi there.
First time poster here. Just wondering if anyone can offer me some assistance.

I am grabbing data from a local table, placing it into an ADO recordset, disconnecting the recordset, then applying the recordset to a subform.

I then manually edit the data in the subform (let's say I change some text in one of the fields), and now I want to apply my changes back to the original table using the "UpdateBatch" command - but nothing happens. No error is thrown up, but the source table data isn't modified.

(I realise that there is no need to do any of this to edit data in a local table, but once I have this working, the local table will be sitting on a network (accdb) back end.)

The following code is on my main form:

Code:
Option Compare Database
Option Explicit
Dim MyDisconnectedRS As ADODB.Recordset

**To grab the data and populate my subform I press button 1**

Private Sub Command1_Click()

  Dim cnn As ADODB.Connection

  'Open a connection
  Set cnn = Application.CurrentProject.Connection

  'Create the Recordset object
Set MyDisconnectedRS = New ADODB.Recordset
  MyDisconnectedRS.CursorLocation = adUseClient

  'Populate the Recordset object with a SQL query
  MyDisconnectedRS.Open "SELECT FakeTable.* FROM FakeTable;", cnn, adOpenStatic, adLockBatchOptimistic

  'Disconnect the Recordset
Set MyDisconnectedRS.ActiveConnection = Nothing

'Attach the recordset to the subform
  Set MySubform.Form.Recordset = MyDisconnectedRS

End Sub

**At this point I make some changes to the data in the subform, then press button 2**

Private Sub Command2_Click()

Dim cnn As New ADODB.Connection
cnn.Mode = adModeReadWrite ' I suspect this line isn't even necessary...

'Re-open the connection
Set cnn = Application.CurrentProject.Connection

'Apply it back to the recordset
Set MyDisconnectedRS.ActiveConnection = cnn

'Attempt to update
MyDisconnectedRS.UpdateBatch adAffectAll

'Tidy up
MyDisconnectedRS.Close
cnn.Close

End Sub

Nothing happens. No error message is thrown up, but the source table isn't updated.

I've googled this repeatedly, and everything I've read indicates that this ought to work. Any ideas why it isn't doing?
Many thanks for any assistance you can offer.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:14
Joined
May 7, 2009
Messages
19,169
how can you say it's Disconnected when you a a Connection object set to your recordset.
to be disconnected you set its Connection to Nothing.

also did you test if the recordset you created Support updateBatch.
 
Last edited:

Bennet

New member
Local time
Today, 18:14
Joined
May 23, 2020
Messages
13
Hi arnelgp,

how can you say it's Disconnected when you a a Connection object set to your recordset.
to be disconnected you set its Connection to Nothing.

I assume it's disconnected because this line of code appears to successfully disconnect it:
Set MyDisconnectedRS.ActiveConnection = Nothing

If I don't run this line, editing the recordset (via the subform) edits the underlying table, so the disconnection seems to work. Also, all the guides regarding working with disconnected recordsets seem to suggest this is how you disconnect it.

also did you test if the recordset you created Support updateBatch.
Sorry, not quite sure what this means. How would I test that?

Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:14
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Just curious, do you have Option Explicit declared at the top of your code module? If not, try adding it. I am hoping that might help you trace the problem.
 

Bennet

New member
Local time
Today, 18:14
Joined
May 23, 2020
Messages
13
Hi. Welcome to AWF!
Just curious, do you have Option Explicit declared at the top of your code module? If not, try adding it. I am hoping that might help you trace the problem.

Hi there. Yes I do. Wouldn't be without it. :)

also did you test if the recordset you created Support updateBatch.

Just realised you might be asking if it's an updateable recordset. If so, the answer is definitely yes. It's just one table. No joins. Nothing weird.

I'm attaching a file that shows what I'm trying to do, incase anyone can help. (This is just a very simple test file at this stage.)

Thanks.
 

Attachments

  • TestingDisconnectedRS.accdb
    600 KB · Views: 387

theDBguy

I’m here to help
Staff member
Local time
Today, 11:14
Joined
Oct 29, 2018
Messages
21,358
Hi there. Yes I do. Wouldn't be without it. :)



Just realised you might be asking if it's an updateable recordset. If so, the answer is definitely yes. It's just one table. No joins. Nothing weird.

I'm attaching a file that shows what I'm trying to do, incase anyone can help. (This is just a very simple test file at this stage.)

Thanks.
Hi. Thanks for posting a demo. Two things:
  1. Your second button is not wired. It needs to have [Event Procedure] in the Click event, and
  2. Try adding an error handler, so you can trap the error message:
error.png
 

Bennet

New member
Local time
Today, 18:14
Joined
May 23, 2020
Messages
13
Sorry, I realised I have muddied the waters by having the "non-wired" button. This occurred because I changed the button name immediately prior to posting and forgot to wire it back up. This isn't part of the issue I'm having though.

The error message you are getting there only occurs, I think if you run the buttons out of order. Button 1 initialises the recordset, and button 2 closes it.. As long as you run button 1, then button 2, it doesn't crash - but neither does it successfully update the actual table, and that's the essence of the question. I'm just trying to get my changes written back to the table. I'm uploading an amended file where the button is correctly tied to the relevant event.

Many thanks for your help.
 

Attachments

  • TestingDisconnectedRS.accdb
    704 KB · Views: 384

Bennet

New member
Local time
Today, 18:14
Joined
May 23, 2020
Messages
13
(Just to add - I agree that error handling is highly advisable. I'm just trying to test/prove the concept at the moment, and no error is being thrown.)
 

sonic8

AWF VIP
Local time
Today, 19:14
Joined
Oct 27, 2015
Messages
998
I then manually edit the data in the subform (let's say I change some text in one of the fields), and now I want to apply my changes back to the original table using the "UpdateBatch" command - but nothing happens. No error is thrown up, but the source table data isn't modified.
I'm not absolutely sure on this, so if I'm wrong, by all means please correct me; particularly @theDBguy and @arnelgp who replied like this must be just a trivial problem.

I'm afraid this is a tough problem to crack. If you edit data in a bound Access form, Access will "force" the update on the database once the record in the form is "saved".
Of course this does not write the data back to the table if the recordset is disconnected. Nonetheless, by trying to do so Access resets the internal status information about changes in the recordset. So, once you run the UpdateBatch the recordset does not know anything about the changed records anymore. - Nothing is updated.

I'm not aware of any simple solution to this.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:14
Joined
Sep 21, 2011
Messages
14,046
Seems someone else is having the same problem?

 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:14
Joined
Oct 29, 2018
Messages
21,358
I'm not absolutely sure on this, so if I'm wrong, by all means please correct me; particularly @theDBguy and @arnelgp who replied like this must be just a trivial problem.

I'm afraid this is a tough problem to crack. If you edit data in a bound Access form, Access will "force" the update on the database once the record in the form is "saved".
Of course this does not write the data back to the table if the recordset is disconnected. Nonetheless, by trying to do so Access resets the internal status information about changes in the recordset. So, once you run the UpdateBatch the recordset does not know anything about the changed records anymore. - Nothing is updated.

I'm not aware of any simple solution to this.
Hi. I suspect @sonic8 has a point. Take a look at the attached copy of your db and open frmDemo. It does seem Access Forms add complexity to the situation. Perhaps you can use the Form's BeforeUpdate event to capture what it's trying to do in the background. I'll keep testing and let you know...
 

Attachments

  • TestingDisconnectedRS.zip
    87.2 KB · Views: 393

CJ_London

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2013
Messages
16,553
just curious to know why you are considering using a disconnected recordset when you want to update the underlying table? Using them in a multi user environment is fraught with problems around two or more users modifying the same record.

They are fine for batch processing of new records (e.g. insert invoice header and invoice rows) but in that scenario you can use dao and commit trans.

If it is to do with concerns around open connections then a better way to do it is take a copy of your disconnected recordset before the user has a chance to make changes, then when the user click the update button use vba to parse through the form recordset. For each record flagged as changed (either with a flag or by comparing with the copy), fetch the original record from the BE and compare with the copy - perhaps on a field by field basis or by using a hash algorithm, if it is the same, then OK to update the record. If it has changed then advise the user there is a data clash.

If you are just exploring, take a look at this link.

the main difference between the code there and your code is

a. the other code specifies cursortype and lock type
b. the recordset is not disconnected - but changes to the BE do not occur until you batchupdate

So perhaps you are trying to use batchupdate in the wrong context?
 

Bennet

New member
Local time
Today, 18:14
Joined
May 23, 2020
Messages
13
just curious to know why you are considering using a disconnected recordset when you want to update the underlying table?

Hi CJ_London.

My experience shows that holding open the connection to a network back end seems to lead to problems. Not sure if this is a general problem or a quirk on my office network, but I find that forms in a front end that directly reference a back end table, and hold that query open will (seemingly at random) cause crashes with an error message along the lines of:
"Access has lost communication with the data source" - or similar - I can't remember the exact terminology.
Frequently, this will also corrupt the back end which then requires a compact & repair.

In order to avoid this I've tried to find alternatives to holding open forms or subforms that query back end tables. Usually this means I hold whatever data I want to display in a local staging table.

I find this solution clunky and inelegant though.

So I started to look for ways to load data into a local recordset, disconnect from the back end, and display that.

Mainly I followed an article on Tech Republic. For some reason I don't seem to be allowed to post this link, so if you want to follow it you'll have to add the ww yourself: techrepublic.com/blog/how-do-i/how-do-i-pass-data-over-a-network-using-disconnected-recordsets/

I'm open to alternative solutions. I like your idea of having two copies of the local recordset which can then be compared, although I think it might require individual sql calls for each line that needs updating, which sounds like it might be quite slow if I need to update 20 or 30 lines. I like the idea of having a single operation that updates all my changes en masse. I appreciate that might not be possible though.

Many thanks for your input.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2013
Messages
16,553
your error implies users are connecting to the BE over wireless

this is the code in the link

Rich (BB code):
ub BatchUpdates()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    Dim strSQL As String
    Dim lngUpdated As Long

    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.CursorLocation = adUseClient
    rst.LockType = adLockBatchOptimistic
    rst.Open ("Select * from Products ")

    strSQL = "UnitPrice < 30000"
    lngUpdated = 0

    rst.Find strSQL

    Do Until rst.EOF
        lngUpdated = lngUpdated + 1
        rst("UnitPrice") = rst("UnitPrice") * 1.1
        rst.Find strSQL, 1, adSearchForward
    Loop

    rst.UpdateBatch

    Debug.Print lngUpdated & " Records Updated"

    rst.Close
    Set rst = Nothing

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:14
Joined
Sep 21, 2011
Messages
14,046
Mainly I followed an article on Tech Republic. For some reason I don't seem to be allowed to post this link, so if you want to follow it you'll have to add the ww yourself: techrepublic.com/blog/how-do-i/how-do-i-pass-data-over-a-network-using-disconnected-recordsets/
That is just until you have 10 posts. We have been having a lot of spammers posting links, so the site has set a limit before you can post.(y)
 

Bennet

New member
Local time
Today, 18:14
Joined
May 23, 2020
Messages
13
Hi. I suspect @sonic8 has a point. Take a look at the attached copy of your db and open frmDemo. It does seem Access Forms add complexity to the situation. Perhaps you can use the Form's BeforeUpdate event to capture what it's trying to do in the background. I'll keep testing and let you know...

Thanks for taking the time to look this. I see what you're getting at. I can update rows individually, immediately whenever they are edited, rather than en masse later on.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2013
Messages
16,553
took a look at your link - the example using updatebatch uses a connected recordset. The other example using a disconnected recordset works along the lines I was suggesting (but not checking to see if someone else has modified the record in the meantime, i.e. no recordset copy)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:14
Joined
Oct 29, 2018
Messages
21,358
Thanks for taking the time to look this. I see what you're getting at. I can update rows individually, immediately whenever they are edited, rather than en masse later on.
Right. So, that approach might be okay for a single view form, but not for a continuous one.
 
Last edited:

Bennet

New member
Local time
Today, 18:14
Joined
May 23, 2020
Messages
13
took a look at your link - the example using updatebatch uses a connected recordset. The other example using a disconnected recordset works along the lines I was suggesting (but not checking to see if someone else has modified the record in the meantime, i.e. no recordset copy)
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2013
Messages
16,553
in the comments it says

'Example connection is to active connection.
 

Users who are viewing this thread

Top Bottom