该范例连同 CancelBatch 方法说明 UpdateBatch 方法。Public Sub UpdateBatchX() Dim rstTitles As ADODB.Recordset Dim strCnn As String Dim strTitle As String Dim strMessage As String ' 将连接字符串赋值给变量。 strCnn = "Provider=sqloledb;" & _ "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; " Set rstTitles = New ADODB.Recordset rstTitles.CursorType = adOpenKeyset rstTitles.LockType = adLockBatchOptimistic rstTitles.Open "titles", strCnn, , , adCmdTable
rstTitles.MoveFirst ' 对记录集执行循环并询问用户是否要更改指定标题的类型。 Do Until rstTitles.EOF If Trim(rstTitles!Type) = "psychology" Then strTitle = rstTitles!Title strMessage = "Title: " & strTitle & vbCr & _ "Change type to self help?" If MsgBox(strMessage, vbYesNo) = vbYes Then rstTitles!Type = "self_help" End If End If rstTitles.MoveNext Loop ' 询问用户是否要提交以上所作的全部更改。 If MsgBox("Save all changes?", vbYesNo) = vbYes Then rstTitles.UpdateBatch Else rstTitles.CancelBatch End If ' 打印记录集中的当前数据。 rstTitles.Requery rstTitles.MoveFirst Do While Not rstTitles.EOF Debug.Print rstTitles!Title & " - " & rstTitles!Type rstTitles.MoveNext Loop ' 恢复原始值,因为这只是演示。 rstTitles.MoveFirst Do Until rstTitles.EOF If Trim(rstTitles!Type) = "self_help" Then rstTitles!Type = "psychology" End If rstTitles.MoveNext Loop rstTitles.UpdateBatch rstTitles.CloseEnd Sub
recordser.update
字段名和值要一一对应!
如果用UpdateBatch,你5000条记录不会出错吗?
Dim strCnn As String
Dim strTitle As String
Dim strMessage As String ' 将连接字符串赋值给变量。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; " Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenKeyset
rstTitles.LockType = adLockBatchOptimistic
rstTitles.Open "titles", strCnn, , , adCmdTable
rstTitles.MoveFirst ' 对记录集执行循环并询问用户是否要更改指定标题的类型。
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "psychology" Then
strTitle = rstTitles!Title
strMessage = "Title: " & strTitle & vbCr & _
"Change type to self help?" If MsgBox(strMessage, vbYesNo) = vbYes Then
rstTitles!Type = "self_help"
End If
End If rstTitles.MoveNext
Loop ' 询问用户是否要提交以上所作的全部更改。
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
rstTitles.UpdateBatch
Else
rstTitles.CancelBatch
End If ' 打印记录集中的当前数据。
rstTitles.Requery
rstTitles.MoveFirst
Do While Not rstTitles.EOF
Debug.Print rstTitles!Title & " - " & rstTitles!Type
rstTitles.MoveNext
Loop ' 恢复原始值,因为这只是演示。
rstTitles.MoveFirst
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "self_help" Then
rstTitles!Type = "psychology"
End If
rstTitles.MoveNext
Loop
rstTitles.UpdateBatch rstTitles.CloseEnd Sub