PRB: "Operation Must Use an Updateable Query" Error Message When You Access Excel Through ODBC

Article translations Article translations
Article ID: 316475 - View products that this article applies to.
This article was previously published under Q316475
Expand all | Collapse all

On This Page

SYMPTOMS

When you edit an Excel worksheet through ADO and ODBC, you may receive the following error message if you use an ADO DataControl object:
[Microsoft][ODBC Excel Driver] Operation must use an updateable query.
If you use a Recordset object that is generated with ADO code, you may receive the following error message when you edit an Excel worksheet through ADO and ODBC:
Run-time error '-2147467259(80004005)': [Microsoft][ODBC Excel Driver] Operation must use an updateable query.

CAUSE

This problem occurs if you try to edit a worksheet that is saved or opened as ReadOnly.

NOTE: ReadOnly is the default setting for an ODBC connection to Excel, with or without a data source name (DSN). Therefore, the user must always change that setting to edit data.

RESOLUTION

To resolve this problem, use the following methods:
  • Make sure that the LockType property of the Recordset object is not set to ReadOnly.
  • Make sure that the file that you are trying to open is not saved as ReadOnly.
  • If you are connecting through a DSN, follow these steps:
    1. Open Control Panel, and then click ODBC Data Source Administrator.
    2. Double-click your DSN.
    3. In the ODBC Microsoft Excel Setup dialog box, click Options.
    4. Make sure that the ReadOnly check box is not selected.
  • If you are using a DSN-less connection, make sure to include the "ReadOnly=0" option in the connection string. For example:
    cn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\MyDoc.xls;ReadOnly=0;"
    					

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a new Standard EXE project in Visual Basic.
  2. On the Project menu, click References, and then add a reference to Microsoft ActiveX Data Objects Library.
  3. Add a Command button to Form1.
  4. Add the following code to Form1:
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    
    Private Sub Form_Load()
      Command1.Caption = "Edit"
    End Sub
    
    Private Sub Command1_Click()
      Dim DocPath As String
    
      DocPath = App.Path & "\Test.xls"
    
      Set cn = New Connection
      Set rs = New Recordset
    
      cn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & DocPath & ";ReadOnly=1"
      rs.LockType = adLockOptimistic
      rs.Open "TB1", cn
      rs.AddNew
      rs.Fields(1).Value = "New Value"
      rs.Update
    
      rs.Close
      cn.Close
      Set rs = Nothing
      Set cn = Nothing
    End Sub  
    					
  5. Save the application.
  6. Create a new Excel worksheet, and then save the worksheet as Test.xls.
  7. On the Insert menu, point to Name, and then click Define.
  8. Create a new table inside the Excel worksheet, and then name the table TB1.
  9. Save the worksheet in the same folder as the Visual Basic application.
  10. Press F5 to run the application.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA

Properties

Article ID: 316475 - Last Review: February 12, 2007 - Revision: 2.2
APPLIES TO
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbprb KB316475

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com