Disconnected Recordsets and BatchUpdate

[LeanneGodney]

Hi there,

I've read a couple of posts about disconnecting recordsets and then running the batchupdate method of a recordset. Only trouble is this doesn't work for me!

If I do the inserting and editing through code it seems to work, but this is what I want to do:

1. Set a forms recordset to a disconnected recordset
2. Allow the user to then make the changes or additions that they require
3. When the user clicks save, reconnect the recordset and then run the batchupdate.

I've written code for this, but it doesn't update the underlying SQL tables... Boo... Getting a little frustrating. Does it matter if a recordset object is a public new variable?

This is at the top of my module:
Public LeanneRec As New ADODB.Recordset

This is my code:
First bit of code

With LeanneRec
If .State = 1 Then .Close
.CursorLocation = adUseClient
.Open "dbo.qryLookup_Regions", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
.ActiveConnection = Nothing
End With

Set Forms!frmRegionsTest.Recordset = LeanneRec

I then make changes to the data by adding a record (does it matter if it's assigning a zero to the key field??

Then I simply run this code to test if it works:

With LeanneRec
.ActiveConnection = CurrentProject.Connection
.UpdateBatch
.ActiveConnection = Nothing
End With


What am I doing wrong??

Thanks a lot!
Leanne

 

[cmmrfrds]

Since you did the work in
Forms!frmRegionsTest.Recordset
wouldn't you need to Set the updated data back to the LeanneRec before the update?

Set LeanneRec = Forms!frmRegionsTest.Recordset

 

[LeanneGodney]

Hey Cmmrfrds,

I've tried that. Still doesn't want to work... Re-created code to do this (but still not working):

'Open the recordset and assign it to a form
With LeanneRec
If .State = 1 Then .Close
.Open "select * from tblBooks", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
.ActiveConnection = Nothing
End With

Set Forms!frmBooksTest.Recordset = LeanneRec

'The user then makes changes in the form
'The user then clicks save, which runs the click event:

Set LeanneRec = Forms!frmBooksTest.Recordset

With LeanneRec
.ActiveConnection = CurrentProject.Connection
.UpdateBatch adAffectAllChapters
.ActiveConnection = Nothing
End With

Set Forms!frmBooksTest.Recordset = LeanneRec

 

[cmmrfrds]

Are you using the current version of Access or an older version.

 

[LeanneGodney]

Using Access 2002.