Update recordset without updating database

问题:

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

回答1:

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

rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic

Here is the full code,

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