run time error 3251 current recordset does not support updating

Question

I am using excel 2007. i have successfully updated record sets in past using this code but Don't know why i start getting "run time error 3251" on this one. Can someone help me to find out. please.

for information, i am fetching data from access to excel vba user form & then doing some updation & deletion as per requirement.

 Private Sub Update_Finance_return_details_Click()

Dim rs As ADODB.Recordset
Dim strSQL As String
Dim myDB As String
Dim cn As Object

On Error GoTo ErrorHandler

  Set cn = CreateObject("ADODB.Connection")

  myDB = "X:\COMMERCIAL\New Automated Invioce 
  Tracker\Commercial_Invoice_Tracker.accdb"

  cn.CursorLocation = adUseServer

  With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"    'For *.ACCDB Databases
    .ConnectionString = myDB
    .Open
  End With

  Set rs = New ADODB.Recordset
  If rs.State <> adStateClosed Then rs.Close

  strSQL = "SELECT * FROM [CT] WHERE [Invoice_ID] = " & Me.ID.Value & ""

  rs.CursorLocation = adUseServer
  rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

  With rs
    If (Me.Clarification_Rec_Date_return.Visible = True) Then
        !Fin_Query_Dt = Me.Clarification_Rec_Date_return.Value
    Else
        !Fin_Query_Dt = Null
    End If

    !Fin_Query = Me.Clarification_Received_Finance_return.Value

    If (Me.Clarification_Resolve_date_return.Visible = True) Then
      !Fin_Query_Resolve_Dt =  Me.Clarification_Resolve_date_return.Value
    Else
        !Fin_Query_Resolve_Dt = Null
    End If
    !Last_Updated_by = Environ("Username")
    !Last_Updated_on = Now

   .Update
   .Close
 End With

 MsgBox ("Record Updated Successfully")

CleanExit:
   cn.Close
   Set rs = Nothing
   Set cn = Nothing
   Application.ScreenUpdating = True

   Exit Sub
ErrorHandler:
   MsgBox Err.Description
   Resume CleanExit
End Sub

can anyone help me to guide where i am making mistake – techworld Apr 02 '15 at 03:21 — techworld, Apr 02 '15 at 03:21

score 0 · Answer 1 · answered Sep 26 '16 at 11:32

I know this is an old thread but I had the same issue sometime ago and felt it was worth updating everyone with what resolved it for me.

Similar situation to yourself, everything was working fine then all of a sudden it was unable to write to Access DB. This issue for me was down to an IT migration which recently happend and all the users only had Read NTFS access to the Access DB. Giving users write NTFS access resolved the issue.

score 0 · Answer 2 · answered Sep 13 '19 at 13:50

Some many years after this question I found a solution that I think it can help others.

Instead open the record set using a SQL statement you should want to open using the statement below:

rst.Open [TABLE_NAME], [ACTIVE_CONNECTION], adOpenKeyset, adLockOptimistic, adCmdTable

Adapting to your code:

rst.Open "CT", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Then you can use the method find to search the record in your database. It is the equivalent to SQL SELECT.

rst.Find "Invoice_ID = " & Me.ID.Value

Now you are able to edit the record set.

Example:

rst!Last_Updated_by = Environ("Username")

run time error 3251 current recordset does not support updating

2 Answers2

Linked