Excel 2010 VBA ADODB write to XLSX

[fmaa]

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:

"Run-time error '3219':

Operation is not allowed in this context."

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

Public Function writeEmployeeRecord(EEID) As Boolean

TopOfPage:

    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
            cN.Open
        
            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
            RS.Open
            
    
            If RS.RecordCount < 0 Then
                MsgBox "Unable to retrieve EE record. (" & EEID & ")"
                writeEmployeeRecord = False
            Else
                
                MsgBox RS.Fields("EE_ID").Value & " found."
                
                If RS.EOF = True And RS.BOF = True Then
                    MsgBox "Employee not found."
                    GoTo TakeNextRecord
                End If
                
                RS.MoveFirst
                
                RS.Fields("EE_INFO_NAME").Value = UserForm1.TextBox1.Value
                

                MsgBox RS.Status
                writeEmployeeRecord = True
            End If

TakeNextRecord:
            If RS.State <> adStateClosed Then
                RS.Close
            End If
            
            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
    
    
    
ADO_ERROR:
If Err <> 0 Then
    Debug.Assert Err = 0
    MsgBox Err.Description
    Err.clear
End If

            
        'end of record actions
    Else
    
        If MsgBox("File not available.", vbRetryCancel) = vbRetry Then GoTo TopOfPage
    
    End If

End Function

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.

[westconn1]

IMPORTANT: An ODBC connection to Excel is read-only by default. Your ADO Recordset LockType property setting does not override this connection-level setting. You must set ReadOnly to False in your connection string or your DSN configuration if you want to edit your data. Otherwise, you receive the following error message:
Operation must use an updateable query.
ODBC Provider Using a DSN-Less Connection String

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"
.Open
End With

this is from previous versions of excel connection. but i guess must still apply

see http://support.microsoft.com/kb/257819 for full reference

[fmaa]

Edit

You can edit Excel data with the normal ADO methods. Recordset fields which correspond to cells in the Excel worksheet containing Excel formulas (beginning with "=") are read-only and cannot be edited. Remember that an ODBC connection to Excel is read-only by default, unless you specify otherwise in your connection settings. See earlier under "Using the Microsoft OLE DB Provider for ODBC Drivers."

So, unfortunately, that link does not specific the key info required. I am specifying the FileLock type as I try to edit/update/save a record as queried. I must be missing something obvious like removing the file lock before trying to close the connection. I have no idea what the code for that is, though. I have browsed through the context menus in the IDE looking for the command but, obviously, it still escapes me.

Also:

Excel Limitations

The use of Excel as a data source is bound by the internal limitations of Excel workbooks and worksheets. These include, but are not limited to:

Worksheet size: 65,536 rows by 256 columns
Cell contents (text): 32,767 characters
Sheets in a workbook: limited by available memory
Names in a workbook: limited by available memory

These limitations definitely do not apply to Excel 2007 and 2010 but I am concerned. Perhaps ADO was not changed as Excel 2007/2010 row/column limitations were increased.

Are there any specific references to RS operations using excel as data source? This is seems to be a really undocumented topic.

[westconn1]

So, unfortunately, that link does not specific the key info required.

i would have thought the above was the info required

.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"

[westconn1]

So, unfortunately, that link does not specific the key info required.

i would have thought the above was the info required

.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"

check out at connectionstring.com

[fmaa]

i would have thought the above was the info required



check out at connectionstring.com

Thank you for your post. that link does not point to a valid website.

Your quote's connection string is for a deprecated driver for prior versions of Excel. I am using the 12.0 ACE for XLSX files.

[westconn1]

Thank you for your post. that link does not point to a valid website.

i am sure if you had googled at all you would have found the correct website was connectionstrings.com

[fmaa]

It had nothing to do with the connection string.

The SQL query should have been "Update X Set bla bla bla where Y = lols"

Due to column limitations of Excel, I switched to Access with cascading AfterUpdating macros. It is absolutely outrageous that Microsoft allows for 16000ish columns in Excel 2010 but ADO is still restricted to 255 columns.

[westconn1]

The SQL query should have been "Update X Set bla bla bla where Y = lols"

glad you found your problem, pls mark thread resolved

It is absolutely outrageous that Microsoft allows for 16000ish columns in Excel 2010 but ADO is still restricted to 255 columns.

i doubt the ADO driver was ever intended to make excel into a full database as it has many limitations when used with ADO