Hi everyone,

The puzzle described below is a fragment of a Access 2003 ADP project
linked to the well-known "pubs" sample database in SQL Server 2000.


The following simple procedure (in Access nodule) works perfectly,
exactly as expected:


Public Sub Test()

Dim conn As ADODB.Connection: Set conn =
CurrentProject.AccessConnection
Dim rs As ADODB.Recordset: Set rs = New ADODB.Recordset

With rs

' Open recordset
.Source = "SELECT * FROM jobs"
.LockType = adLockBatchOptimistic
.CursorType = adOpenStatic
.CursorLocation = adUseClient
Set .ActiveConnection = conn
.Open

' Disconnect recordset
Set .ActiveConnection = Nothing

' Move around and change some records
.MoveNext
.Fields("job_desc") = .Fields("job_desc") + " xxx"
.Update

.MoveNext
.Fields("job_desc") = .Fields("job_desc") + " xxx"
.Update

' Re-connect recordset
Set .ActiveConnection = conn

' Save changes
.UpdateBatch

End With

End Sub


The "job_desc" fields of the second and third record are changed (and
those changes become "visible" to other DB clients only after the
recordset is re-connected and batch-updated).


Now, when I try to use the same technique with a recordset bound to a
form (i.e. when I try to let a user to change some records in a
continuous form and then commit those changes all at once by pressing
"Save" button), it does not work. Here is the code behind the
continuous form which is bound to the "jobs" table at design time:


Option Compare Database
Option Explicit

Private m_conn As ADODB.Connection
Private m_rs As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)

Set m_conn = CurrentProject.AccessConnection
Set m_rs = New ADODB.Recordset

Me.RecordSource = ""

With m_rs

' Open recordset
.Source = "SELECT * FROM jobs"
.LockType = adLockBatchOptimistic
.CursorType = adOpenStatic
.CursorLocation = adUseClient
Set .ActiveConnection = m_conn
.Open

' Disconnect recordset
Set .ActiveConnection = Nothing

End With

' "Give" recordset to the form
' so that user can move around and change some records.
Set Me.Recordset = m_rs

End Sub


Private Sub cmdSave_Click()


With m_rs


' Re-connect recordset
Set .ActiveConnection = m_conn


' Save changes
Call .UpdateBatch(adAffectAll)


End With


Me.SetFocus
DoCmd.Close


End Sub


None of the changes made by the user are applied to the database. :-(

Interestingly enough, when I inspect the form's recordset just before
reconnecting it, all the records touched by the user through the form's
controls do have new values in their fields. But, because of some
reasons - reasons that I do not understand yet - those changes are not
respected by UpdateBatch method (or by the adFilterPendingRecords
filter).

Another interesting observation: If I type something into a field on
the form and then click "cmdSave" button while input focus is still on
the changed record, that very record does get updated by UpdateBatch.

If connected-ness of ADO recordsets was meant to be transparent to
Access 2003 forms (and I really want to believe so), then it feels like
I am missing some small but important detail here (like some incorrect
value of a property of the form and/or the recordset). Any help in
resolving this problem would be greatly appreciated!


Thank you,
Yarik.

Yarik wrote:
> with a recordset bound to a
> form (i.e. when I try to let a user to change some records in a
> continuous form and then commit those changes all at once by pressing
> "Save" button), it does not work. Here is the code behind the
> continuous form which is bound to the "jobs" table at design time:
>
>
> Option Compare Database
> Option Explicit
>
> Private m_conn As ADODB.Connection
> Private m_rs As ADODB.Recordset
>
> Private Sub Form_Open(Cancel As Integer)
>
> Set m_conn = CurrentProject.AccessConnection
> [snipped]

Just a thought: rather than using CurrentProject.AccessConnection, try
opening a new connection object that explicitly has client-side cursors
e.g.

Set m_conn = New ADODB.Connection
With m_conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "<<connection string here>>"
.CursorLocation = adUseClient
<<other connection properties here>>
.Open
End With

Jamie.

--

> Just a thought: rather than using CurrentProject.AccessConnection, try
> opening a new connection object that explicitly has client-side cursors
> e.g.
>
> Set m_conn = New ADODB.Connection
> With m_conn
> .Provider = "Microsoft.Jet.OLEDB.4.0"
> .ConnectionString = "<<connection string here>>"
> .CursorLocation = adUseClient
> <<other connection properties here>>
> .Open
> End With
>
> Jamie.

I did not try this, but I really, really doubt that this is because of
using a "wrong" of connection (CurrentProject.Connection, or
CurrentProject.AccessConnection, or my own connection).

The first reason of my doubt is that the connection I used did have its
adCursorLocation set to adUseClient.

The second (and probably the strongest) reason is that, after a closer
look at what happens to the recordset, I finally found one difference
that seems to be important. When updating the disconnected recordset
programmatically, the calls to Update() do not change the fields'
OriginalValue and UnderlyingValue properties. However, when the same
recordset (originally opened with the same connection) is "operated" by
the form, every record's update (e.g. when user moves to another
record) somehow changes the OriginalValue and UnderlyingValue
properties (namely, sets them equal to the Value property). More
specifically, in BeforeUpdate event handler, only the Valuer property
appears to have been changed, whereas within AfterUpdate handler, all
three values are the same. My theory is that (a) differences between
Value, OriginalValue, and UnderlyingValue is exactly what UpdateBatch()
uses to do actual changes and resolve possible conflicts and (b)
somehow form manages to misuse or abuse this mechanism.

What my theory still lacks is understanding whether this problem is
avoidable in MS Access 2003. Anybody?

BTW, if someone could confirm that this problem does or does not exist
in any newer version of MS Access (e.g. 2007), that would also be very
helpful.

Thank you,
Yarik.

Yarik wrote:
> When updating the disconnected recordset
> programmatically, the calls to Update() do not change the fields'
> OriginalValue and UnderlyingValue properties. However, when the same
> recordset (originally opened with the same connection) is "operated" by
> the form, every record's update (e.g. when user moves to another
> record) somehow changes the OriginalValue and UnderlyingValue
> properties (namely, sets them equal to the Value property). More
> specifically, in BeforeUpdate event handler, only the Valuer property
> appears to have been changed, whereas within AfterUpdate handler, all
> three values are the same. My theory is that (a) differences between
> Value, OriginalValue, and UnderlyingValue is exactly what UpdateBatch()
> uses to do actual changes and resolve possible conflicts and (b)
> somehow form manages to misuse or abuse this mechanism.

Is it possible that an MS Access form uses its recodset's UpdateBatch
method (instead of Update method) when it updates a record? If it is,
then is there any way to force a form to use Update?

Thank you,
Yarik.

Yarik

Try this one:

Option Compare Database
Option Explicit

Dim rs As ADODB.Recordset, cnn As ADODB.Connection
Dim strSQL As String

Private Sub Form_BeforeUpdate(Cancel As Integer)
cnn.Open
Set rs.ActiveConnection = cnn
rs.Update
Set rs.ActiveConnection = Nothing
cnn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = CurrentProject.BaseConnectionString 'or other as
required
cnn.CursorLocation = adUseClient
cnn.Open
strSQL = "Select * From [Product]"
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.Open strSQL, cnn, adOpenStatic, adLockOptimistic
Set rs.ActiveConnection = Nothing
cnn.Close
Set Me.Recordset = rs
End Sub

It isn't perfect, but it avoids user's going to lunch and tying up server
resources with long-term connections (especially if they start and edit and
forget about it while they have an extended phone call!). It retains the
conflict resolution, if your surmise is right, but only connects when
necessary and for as long as necessary. Not quite as good as a batch update,
I agree, but an improvement over permanent binding in some situations.

HTH
Keith

Keithr wrote:
> Yarik

I apologize for a long delay with response, but I really thought the
discussion thread was dead soon after 12/12/06. I did not expect any
more responses, so I did not visit the thread for quite some
time...(When the heck is Google going to enable thread posting
notifications? Almost all other online forums can do that...)

> Try this one:
>
> Option Compare Database
> Option Explicit
>
> Dim rs As ADODB.Recordset, cnn As ADODB.Connection
> Dim strSQL As String
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> cnn.Open
> Set rs.ActiveConnection = cnn
> rs.Update
> Set rs.ActiveConnection = Nothing
> cnn.Close
> End Sub
>
> Private Sub Form_Open(Cancel As Integer)
> Set cnn = CreateObject("ADODB.Connection")
> cnn.ConnectionString = CurrentProject.BaseConnectionString 'or other as
> required
> cnn.CursorLocation = adUseClient
> cnn.Open
> strSQL = "Select * From [Product]"
> Set rs = CreateObject("ADODB.Recordset")
> rs.CursorLocation = adUseClient
> rs.Open strSQL, cnn, adOpenStatic, adLockOptimistic
> Set rs.ActiveConnection = Nothing
> cnn.Close
> Set Me.Recordset = rs
> End Sub
>
> It isn't perfect, but it avoids user's going to lunch and tying up server
> resources with long-term connections (especially if they start and edit and
> forget about it while they have an extended phone call!). It retains the
> conflict resolution, if your surmise is right, but only connects when
> necessary and for as long as necessary. Not quite as good as a batch update,
> I agree, but an improvement over permanent binding in some situations.

Hmm... I don't think this code does what I wanted. Once a user leaves a
changed record (e.g. by moving to another record) this code would
update the changed record immediately, right? But the whole point was
to defer actual updates until user changes multiple records and
indicates that he/she now wants all the changes to become persistent.

What I was trying to do is to use a disconnected recordset to implement
something that could be called "user-interface-level transaction" (not
to be confused with a database-level transaction).

Thank you, Keithr, for your response anyway.

Yarik

Yep! Looks like this is one of these long-running discussions. Hope you're
still watching this one.

As far as I can tell from some research, this won't do exactly what you
want. Unfortunately, this is seems due to the way Access uses the Value,
OldValue fields on its forms. The underlying recordset object has another
one for each field - called OriginalValue. The recordset uses all three
value to determine if the value has been changed and needs to be updated back
to the database. Because of the way Access forms work, by the time you get
to the AfterUpdate event of the form, the OriginalValue property is altered
and all thee value properties for the recordset field are the same. With no
differences to detect, the updatebatch method (when eventually called)
doesn't update the record - it thinks it's been done already. The method can
only ever update the current record at teh time it is called. Run some
Debug.Print sequences against your recordset in the form events and you'll
see what I mean.

I haven't tried it in Access 2K7 yet, but I don't think it will be any
different as far as the mdb format is concerned. The newer XML-based format
might do it, but I wouldn't hold out much hope. I think the problem is
inherent in the way the forms are constructed on screen and then attached to
the underlying recordset. It's simpler than VB, but not as flexible.

HTH

"Yarik" wrote:

>
> Keithr wrote:
> > Yarik
>
> I apologize for a long delay with response, but I really thought the
> discussion thread was dead soon after 12/12/06. I did not expect any
> more responses, so I did not visit the thread for quite some
> time...(When the heck is Google going to enable thread posting
> notifications? Almost all other online forums can do that...)
>
> > Try this one:
> >
> > Option Compare Database
> > Option Explicit
> >
> > Dim rs As ADODB.Recordset, cnn As ADODB.Connection
> > Dim strSQL As String
> >
> > Private Sub Form_BeforeUpdate(Cancel As Integer)
> > cnn.Open
> > Set rs.ActiveConnection = cnn
> > rs.Update
> > Set rs.ActiveConnection = Nothing
> > cnn.Close
> > End Sub
> >
> > Private Sub Form_Open(Cancel As Integer)
> > Set cnn = CreateObject("ADODB.Connection")
> > cnn.ConnectionString = CurrentProject.BaseConnectionString 'or other as
> > required
> > cnn.CursorLocation = adUseClient
> > cnn.Open
> > strSQL = "Select * From [Product]"
> > Set rs = CreateObject("ADODB.Recordset")
> > rs.CursorLocation = adUseClient
> > rs.Open strSQL, cnn, adOpenStatic, adLockOptimistic
> > Set rs.ActiveConnection = Nothing
> > cnn.Close
> > Set Me.Recordset = rs
> > End Sub
> >
> > It isn't perfect, but it avoids user's going to lunch and tying up server
> > resources with long-term connections (especially if they start and edit and
> > forget about it while they have an extended phone call!). It retains the
> > conflict resolution, if your surmise is right, but only connects when
> > necessary and for as long as necessary. Not quite as good as a batch update,
> > I agree, but an improvement over permanent binding in some situations.
>
> Hmm... I don't think this code does what I wanted. Once a user leaves a
> changed record (e.g. by moving to another record) this code would
> update the changed record immediately, right? But the whole point was
> to defer actual updates until user changes multiple records and
> indicates that he/she now wants all the changes to become persistent.
>
> What I was trying to do is to use a disconnected recordset to implement
> something that could be called "user-interface-level transaction" (not
> to be confused with a database-level transaction).
>
> Thank you, Keithr, for your response anyway.
>
>