CODEWITHSUNDEEP
excelvbarecordset
Jason Samuels
Jason Samuels

Reputation: 971

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

Upvotes: 2

Views: 1562

Answers (1)

Jason Samuels
Jason Samuels

Reputation: 971

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

Upvotes: 2

Related Questions