• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to update data from excel to Access not working Error-'Run-time error 3219-Operation is not allowed in this context'


New Member
Dear Friends,
I have code working fine for last one year, suddenly it stopped working without any reason. hitting my head on the wall, appreciate your help.
I wonder how it stopped working without doing any thing.

Dim vrange As Variant
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim appPath As String
Dim dbName As String
Dim cntr As Long, xPosition As Long
Public Function username()
    username = Environ$("UserName")
End Function
Public Function User()
User = Application.username
End Function

Sub openDB()
'dbName = Sheets("Home").Range("z1").Value
    If cnn.State = adStateOpen Then cnn.Close
    'C:\Users\veekay\Desktop\Excel Userform Application\Database12003Format.mdb
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source= \\ho-webstore\ncr\ServiceDue\ServiceDue.accdb;Jet OLEDB:Database Password=secreT;Persist Security Info=False"
    'cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source= \\ho-webstore\atm\ATM_REFUND_PROCESS\refund_database.accdb;Persist Security Info=False"
     cnn.CommandTimeout = 600
End Sub
Sub closeRS()
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
End Sub

Private Sub CommandButton1_Click()
'On Error GoTo Epopup:
If Unique.Value = "" Then
MsgBox "Please select a RegNo to update", vbOKOnly
Exit Sub
End If
If VCollected.Value = "" Then
MsgBox "Vehicle collected status should be yes or no", vbOKOnly
Exit Sub
End If
strSQL = "SELECT Master.* FROM Master WHERE (((Master.[ID])=" & Me.Unique & "));"
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
  If rs.RecordCount > 0 Then
With rs
'On Error Resume Next
         .Fields("AssignedTo").Value = AssignTo
         .Fields("BookingDate").Value = BookingDate
         .Fields("Remarks").Value = Remarks
         .Fields("VehicleCollected").Value = VCollected
    ' On Error GoTo 0
     End With

Set rs = Nothing
Set cnn = Nothing
  MsgBox "Saved Successfully.", vbOKOnly

AssignTo.Value = ""
BookingDate.Value = ""
Remarks.Value = ""
VCollected.Value = ""

  Exit Sub
End If
MsgBox "Connection lost, try again later time", vbOKOnly
Exit Sub

End Sub
stopping the process on 'rs.Update' Getting error message 'Run time error -2147467259(80004005) Query " is corrupt, when I add 'on error resume next' getting error 'Run-time error 3219-Operation is not allowed in this context'.

VBA Reference attached here with.

Appreciate any help on this :)



Last edited by a moderator:


New Member
Dear Friends,
I am really looking for a solution, can any one please help me.
Can I post the same to any other forum. is it against group policy?



Excel Ninja
Qs: If You or anyone haven't change that code
... then what has happened out there?
... ... Is Your data in same folder? Do it work with previous year settings? Do it have same structure? Those cannot verify by Your code!

Are ALL programs still same level as previous year?
Are ALL user permits same as previous year?
Have You checked/verified those?

Have You run it row-by-row?
... when (which row) something seems to need some modifications?


New Member
Thank you vletm for your time.
Nothing changed in the setting or code, its really strange why it stopped working..
I run the code line by line it stops on rs.update and getting error message
'Run-time error 3219-Operation is not allowed in this context'.
When i exclude code on error resume next,
Getting below error
'Run time error -2147467259(80004005) corrupt Query
Tried a lot to fix it but no way


Excel Ninja
Your Nothing changed
as well as You pasted same indications
... gives image: that You should recheck and reverify that everything is really same.
Some parts of those You could do Yourself if You have enough high rights or You should ask from Your company's IT-staff to check/verify those.


New Member
Do you get any indication from the error message
As of i know i have same access as before
Any guidlines please to reverify the same
Thank you so much for ur help


Excel Ninja
As of i know i have same access as before
Yes, but do it mean that everything is same? ... sometimes those IT-staff do something ... which will affect
... You should ask from Your company's IT-staff to check/verify those.


New Member
Sorry for the cross posting, I didn't know about the rule
I just got the reply by googling, let me update here so it will be useful to some one.
downloaded and installed the update from the below link, it is working fine now.


Excel Ninja
You could reread this for next Your thread:
#3 reply: if Your code is same, then somewhere out of code should be something which makes unwanted ...