MS Access ADP Disconnected Recordset Reverts

Question

I have an Microsoft Access 2003 ADP, which uses a bound "main" form with several bound continuous-style subforms. They all utilize disconnected Recordsets via a helper class.

Several users have noticed the same strange behavior: they add/edit a record in the continuous subform, they leave the record (committing the edit to the Recordset), they lock the computer (Ctrl+Alt+Del), they unlock the computer, they go back to the form, after about 5 seconds it flickers and revert to the original unedited state.

I've been able to reproduce this following the steps above, further, after making a change in my form, bound to a disconnected Recordset, I went to SQL Server and changed a value. After the lock/unlock computer routine, the form flickers and refreshes, and the NEW value I just entered in SQL Server appears.

It's as if after about 5 seconds, my disconnected Recordset is reconnecting (on it's own) and requerying the Recordset.

I realize I'm not giving a lot of information here, but has anyone encountered an issue with disconnected Recordsets reconnecting and requerying? Or at least have an ideas of where I could start debugging?

I have the ADP and SQL script to create the database if someone would like to recreate this in their environment.

Here's how I'm creating the disconnected Recordset:

Dim cnn                 As ADODB.Connection
Dim stmTest             As ADODB.Stream

Set cnn = New ADODB.Connection
cnn.Open Application.CurrentProject.AccessConnection.ConnectionString

' Create recordset and disconnect it.
Set mrsTest = New ADODB.Recordset
mrsTest.CursorLocation = adUseClient
mrsTest.Open "SELECT * FROM [tblChild] WHERE ParentID = 1" _
    , cnn _
    , adOpenStatic, adLockBatchOptimistic
Set mrsTest.ActiveConnection = Nothing

cnn.Close
Set cnn = Nothing

' Copy recordset to stream in memory.
Set stmTest = New ADODB.Stream
stmTest.Open
mrsTest.Save stmTest, adPersistADTG

' Bind form to disconnected recordset.
Set Me.Recordset = mrsTest

' Open a copy of the recordset from the stream.
Set mrsTest = New ADODB.Recordset
stmTest.Position = 0
mrsTest.Open stmTest

Answer 1

Probably a little bit offtopic, but this is interesting code. I have never thought about working with datasets like this. Is there a particular reason why you use these 'disconnected datasets'?

Answer 2

Hi,

I too experimented with the same code a while back and had the same experience.

Here's the way I see why the recordset reconnects to the server...

If you check your form's recordsource (after binding the recordset) you will see your SQL statement placed there as the value. Since it is ADP your form is always bound to the server. Hence when the form refreshes, the recordset refreshes too.

If you find a solution to this please post!

Thank you.