Y
Yarik
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.
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.