recordset close when updatesql?

Question

I have an Excel VBA to update data in Access. I use the script below. I just have a small question. if I close the recordset with rs.close I get the message 'operation is not allowed when the object is closed'. If I do not use the rs.close then it works perfectly.

• Is the rs.close really necessary when using a sql?

• I also tried to use cn.execute updatesql. How to set the recordlocking property?

     Public Sub Upload_to_DB()
        Call setparameters
         ' exports data from the worksheet to a table in an Access database
        Dim cn As ADODB.Connection ws As Worksheet , updatesql As String, r_fld As Long, r_val As Long, c As Long, str As String, rs As ADODB.Recordset
  
  
  
  
        Set cn = CreateObject("ADODB.Connection")
        cn.mode = 16 + 3
        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
        "Data Source= " & Path & filename & ";"
        cn.CursorLocation = adUseClient
  
        Set rs = CreateObject("ADODB.Recordset")
        Set ws = Sheets("Opportunity_down_and_upload")
  
  
      With ws  
    r_fld = 13     
    r_val = 14     
    c = 3
  
    str = Empty
  
    str = .Cells(r_fld, c).Offset(0, -1).Value & " = " & .Cells(r_val, c).Offset(0, -1).Value
  
    continue = True
         Do
        str = str & " , " & .Cells(r_fld, c).Value & " = " & .Cells(r_val, c).Value
        c = c + 1
                If IsEmpty(.Cells(r_fld, c)) Then continue = False
        Loop Until continue = False
             End With
  
   'This part is important:'
  
    updatesql = "UPDATE tbl_D_opp_prod_offer SET "
    updatesql = updatesql & str
    updatesql = updatesql & " WHERE [Opp_ID] =  " & ws.Range("A10") & ";"
  
    rs.Open updatesql, cn, , adLockOptimistic, adCmdText
    cn.Close
  
    Set rs = Nothing
    Set cn = Nothing
  
    End Sub
  

Answer 1

• Is the rs.close really necessary when using a sql?

  Nope. In fact, rs.close is rarely necessary at all. VBA will automatically remove objects when they go out of scope, which means that if they were defined in a sub, they get removed at the end of it. For ADODB, there were some bugs in an old version causing this to be unreliable, which means old code often contains rs.close or connection.close while they're not really necessary

• I also tried to use cn.execute updatesql. How to set the recordlocking property?

  You can't. It doesn't really apply to action queries. The record locking options define if a record should be locked when starting to edit it, when updating it, or can't be locked at all because it's read-only, and since you're using an update query there's no time in between those