设为首页收藏本站Access中国

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

返回列表 发新帖
查看: 1097|回复: 5

[Access本身] 批量更新

[复制链接]

点击这里给我发消息

发表于 2015-6-8 06:06:45 来自手机 | 显示全部楼层 |阅读模式
http://www.devsuperpage.com/search/Articles.aspx?G=8&ArtID=93090

点击这里给我发消息

 楼主| 发表于 2015-6-8 06:08:51 来自手机 | 显示全部楼层
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"

点击这里给我发消息

 楼主| 发表于 2015-6-8 06:09:46 来自手机 | 显示全部楼层
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

点击这里给我发消息

 楼主| 发表于 2015-6-8 06:10:43 来自手机 | 显示全部楼层
绑定到窗体就无法批量更新了?

点击这里给我发消息

 楼主| 发表于 2015-6-9 04:43:34 来自手机 | 显示全部楼层
实验了-一下,的确如此,Acc的窗体可能无法实现,好遗憾

点击这里给我发消息

发表于 3 天前 | 显示全部楼层
学习
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )  

GMT+8, 2021-1-18 12:27 , Processed in 0.075747 second(s), 30 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表