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:
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.
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.