批量更新

zpy2

http://www.devsuperpage.com/search/Articles.aspx?G=8&ArtID=93090

zpy2

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"

zpy2

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).  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 details here. Any help would be greatly appreciated!  Thank you, Yarik.  Re: Cannot update a disconnected (and then

zpy2

绑定到窗体就无法批量更新了？

zpy2

实验了-一下，的确如此，Acc的窗体可能无法实现，好遗憾

wuwu200222

学习