PRB: Causes of 'Operation Must Use an Updateable Query' Error

Last reviewed: October 20, 1997
Article ID: Q175168
The information in this article applies to:
  • Microsoft Active Server Pages, version 1.0
  • ActiveX Data Objects (ADO), version 1.0
  • Microsoft Visual InterDev, version 1.0

SYMPTOMS

The following is a common error encountered when using ActiveX Data Objects (ADO) with Active Server Pages:

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'

   [Microsoft][ODBC Microsoft Access 97 Driver] Operation must use an
updateable query.

CAUSE

This article explains the three primary causes of this error, and the workarounds. Although this article refers to Access databases, the information provided here also applies to other types of databases.

RESOLUTION

This error is typically encountered when your script attempts to do an UPDATE or some other action which alters the information in the database. This error occurs because ADO is unable to write to the database for one of the following reasons.

  1. The most common reason is that the Internet Guest account (IUSR_MACHINE) does not have Write permissions on the database file (.mdb). To fix this problem, use the Security tab in Explorer to adjust the properties for this file so that the Internet Guest account has the correct permissions.

    NOTE: When using Access databases with ADO, it is also necessary to give the Internet Guest account Write permissions on the directory containing the .mdb file. This is because Jet creates an .ldb file to handle database locking.

  2. A second cause of this error is that the database was not opened with the correct MODE for writing. If you perform the Open on the Connection object, you use the Mode property to indicate the permissions on the connection as shown here:

          SQL = "UPDATE Products Set UnitPrice = 2;"
          Set Conn = Server.CreateObject("ADODB.Connection")
    
          Conn.Mode = 3      '3 = adModeReadWrite
          Conn.Open "myDSN"
          Conn.Execute(SQL)
          Conn.Close
    
       NOTE: By default, the MODE is set to 0(adModeUnknown), which generally
       allows updates.
    
    

  3. Another cause of this error is that the "Read Only" setting may be checked in the Options page for this DSN in the ODBC Manager.

  4. The last issue and workaround pertains to any SQL data source. The error can be caused by SQL statements that violate referential integrity of the database. Here are a few of the most common queries that fail:

    - The simplest group to deal with are those you cannot change: crosstab,

         SQL pass-through, union, or update (or make-table) action queries that
         have UniqueValue properties set to Yes.
    

    - Another very common cause is when the join includes linked ODBC tables

         that do not have unique indexes. In this case, there is no way for SQL
         to guarantee that records are unique in a table that has fields whose
         value will change with the query.
    

    - One cause does have a robust workaround. If you try to update a join

         field on the "one" side of a "one-to-many" query it will fail unless
         you turn on cascading updates. This way, you delegate referential
         integrity to the JET engine.
    

STATUS

This behavior is by design.

Technology        : kbInetDev
Version           : WINDOWS:1.0; WINNT:1.0
Platform          : WINDOWS winnt
Issue type        : kbprb


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.