Symptoms When you use ActiveX Data Objects (ADO) with Active Server Pages (ASP), you may encounter the following common errors:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access 97 Driver] Operation must use an updateable query.
Cause This article describes the four main causes of this error and the corresponding workarounds. Although this article discusses Microsoft Access databases, the information provided here applies to other types of databases as well.
Solution You typically encounter this error when your script attempts to perform an update or perform some other action that changes 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 by default belongs to the "Everyone" group) has no access to the database file (.mdb) Write permission. 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 using a Microsoft Access database with ADO, you must also grant the Internet Guest account write permissions to the directory containing the .mdb file. This is because Jet creates an .ldb file that handles database locking. Because Jet may create temporary files in the "Temp" directory, you may also need to grant read/write permissions to that folder.
2. The second reason for this error is that the database was not opened in the correct mode with write permissions. If you perform an Open operation on a Connection object, you should use the Mode property to indicate the permissions you have on 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: By default, MODE is set to 0 (adModeUnknown), which usually allows updates.
3. Another reason for this error is that in the ODBC Administrator, the "Read Only" setting in the "Options" page of the DSN may be selected.
4. The last issue and workaround apply to all SQL data sources. SQL statements that violate the referential integrity of the database can cause this error to occur. Here are some of the most common failed queries:
• The simplest set of queries are those that you cannot change: crosstab queries, SQL pass-through queries, union queries, or update (that is, make-table) operation queries that have the UniqueValue property set to Yes.
• Another very common reason is that the join contains indexes on linked ODBC tables that are not unique. In this case, SQL cannot guarantee that the records in the table are unique and the values of the fields in the table will change with the query.
• There is a reason there is indeed a reliable alternative. If you try to update a join field on the "one" side of a "one-to-many" query, the operation will fail unless you enable cascading updates. Because then you can delegate referential integrity to the JET engine.