标题: 批量更新 [打印本页] 作者: zpy2 时间: 2015-6-8 06:06 标题: 批量更新 http://www.devsuperpage.com/search/Articles.aspx?G=8&ArtID=93090 作者: zpy2 时间: 2015-6-8 06:08
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 时间: 2015-6-8 06:09
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 时间: 2015-6-8 06:10
绑定到窗体就无法批量更新了？作者: zpy2 时间: 2015-6-9 04:43
实验了-一下，的确如此，Acc的窗体可能无法实现，好遗憾作者: wuwu200222 时间: 2021-1-15 10:16
学习