Hello, I am relatively new to ADODB in VBA but I am not new to programming theory. I have an xlsx list of records which I am successfully reading and manipulating in VBA form application. However, when it comes time to overwrite the records which have changed as a result of manipulation in the form, I am unable to correctly write even a test field back to my xlsx "database."
I get the error:
The debugger brings me to the highlighted line in the code below. RS.State is 1 and adStateClosed is 0 according to the debugger, which triggers the RS.Close
"Run-time error '3219':
Operation is not allowed in this context."
This code works perfectly (while it may not be streamlined) in the same context using different RS actions for finding and reading records. I would very much appreciate some guidance. This is the last major hurdle in this project. I have been able to research and overcome everything else up until this point. If i can write just one sample field, I will have the ability to go forward independently.
Public Function writeEmployeeRecord(EEID) As Boolean
If IsFileOpen = False Then
Dim RS As ADODB.Recordset '* Record Set
Dim sQuery As String '* Query String
Dim cN As ADODB.Connection 'connection
'On Error GoTo ADO_ERROR
'Create connection string
Set cN = New ADODB.Connection
cN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myfile.xlsx;Extended Properties=Excel 12.0;"
cN.ConnectionTimeout = 40
Set RS = New ADODB.Recordset
sQuery = "Select * From [Existing$] Where [EE_ID] = " & EEID
RS.ActiveConnection = cN
RS.LockType = adLockOptimistic
RS.CursorLocation = adUseClient
RS.Source = sQuery
If RS.RecordCount < 0 Then
MsgBox "Unable to retrieve EE record. (" & EEID & ")"
writeEmployeeRecord = False
MsgBox RS.Fields("EE_ID").Value & " found."
If RS.EOF = True And RS.BOF = True Then
MsgBox "Employee not found."
RS.Fields("EE_INFO_NAME").Value = UserForm1.TextBox1.Value
writeEmployeeRecord = True
If RS.State <> adStateClosed Then
If Not RS Is Nothing Then Set RS = Nothing
If Not cN Is Nothing Then Set cN = Nothing
Workbooks("Budget 2013 - Payroll Master.xlsm").Activate
If Err <> 0 Then
Debug.Assert Err = 0
'end of record actions
If MsgBox("File not available.", vbRetryCancel) = vbRetry Then GoTo TopOfPage