• 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

    Hui...

  • 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'

Robin916

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.

Code:
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
  
    cnn.Open
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
Unique.SetFocus
Exit Sub
End If
If VCollected.Value = "" Then
MsgBox "Vehicle collected status should be yes or no", vbOKOnly
VCollected.SetFocus
Exit Sub
End If
      
strSQL = "SELECT Master.* FROM Master WHERE (((Master.[ID])=" & Me.Unique & "));"
        
  closeRS
  openDB
  
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
        
     rs.Update
    
    ' On Error GoTo 0
    
     
     End With

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

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

  Exit Sub
                        
End If
      
Epopup:
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 :)

thanks
Robin
 

Attachments

Last edited by a moderator:

Robin916

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?

Thanks
 

vletm

Excel Ninja
Robin916
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?
 

Robin916

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
 

vletm

Excel Ninja
Robin916
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.
 

Robin916

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
 

vletm

Excel Ninja
Robin916
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.
 

Robin916

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.
 

vletm

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