Error
Symptoms
When you use ActiveX Data Objects (ADO) with Active Server Pages (ASP), you may experience the following common errors:
Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '
[Microsoft] [ODBC Microsoft Access Driver] Operation must use a updateable query.
Reason
This article explains the four main causes of this error and the corresponding workarounds. Although this article discusses a Microsoft Access database, the information provided here also applies to other types of databases.
Solution
This error is typically encountered when your script attempts to perform an update or some other action that changes the information in the database. This error occurs because ADO cannot write to the database for one of the following reasons:
1. The most common reason is that the Internet Guest account (Iusr_machine, which belongs to the Everyone group by default) does not have write permissions to the database file (. mdb). To resolve this issue, use the Security tab in Explorer to adjust the properties of this file so that the Internet Guest account has the correct permissions.
Note: When you use a Microsoft Access database with ADO, you must also grant the Internet Guest account write permission to the directory that contains the. mdb file. This is because Jet creates a. ldb file that is used to handle database locks. Because Jet may create temporary files in the "Temp" directory, you may also need to grant read/write permissions to the folder.
2. The second reason for this error is that the database is not open with the correct mode with write permission. If you perform an Open operation on a Connection object, you should use the Mode property to indicate the permissions you have for the connection, as follows:
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: mode is set to 0 (adModeUnknown) by default, which usually allows updates.
3. Another reason for this error is: in the ODBC tube, the read-only setting in the Options page of the DSN may be selected.
4. The last question and workaround apply to all SQL data sources. SQL statements that violate referential integrity of the database can cause this error to occur. The following are some of the most common failed queries:
• The simplest set of queries is the following query that you cannot change: A crosstab query with a UniqueValue property set to Yes, a SQL pass-through query, a federated query, or an update (that is, a build table) action query.
• Another very common reason is that the index of the linked ODBC table that the join contains is not unique. In this case, SQL cannot guarantee that the records in the table are unique, and that the values of the fields in the table will change with the query.
• There is a reason that there are reliable alternatives. If you try to update the join field on the "one" side of the "One-to-many" query, the operation will fail unless you enable cascading updates. Because of this, you can delegate referential integrity to the JET engine.