更新记录集而不更新数据库 [英] Update recordset without updating database

问题描述

我使用ADODB连接在Excel VBA中连接到我的Access DB，并使用记录集获取记录。我想更新记录集，但是当我更新记录集时，我还会更新数据库中的表。可以更新记录集而不是数据库吗？这是我的代码，

I use an ADODB connection to connect to my Access DB in Excel VBA and get the records with a recordset. I want to update the recordset, but when i update the recordset i also update the table in the DB. Is it possible to update the recordset but not the db? Here is my code,

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jasons\Documents\UPD.accdb"
rs.Open "SELECT ITEM, SL AS SL FROM Table2", con, adOpenDynamic, adLockPessimistic

rs.MoveFirst
  Do
        rs.Update "SL", 250
        rs.MoveNext
    Loop Until rs.EOF

con.Close
Set con = Nothing

推荐答案

感谢pony2deer的链接，我只通过添加

Thanks to the link by pony2deer i adjusted my code only by adding

rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic

这是完整的代码，

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
sql = "SELECT * FROM Table2"

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jasons\Documents\UPD.accdb"
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.Open sql, con

Set rs.ActiveConnection = Nothing
con.Close

rs.MoveFirst
  Do
        rs.Update "SL", 20
        rs.MoveNext
    Loop Until rs.EOF

这篇关于更新记录集而不更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！