Friday, October 3, 2008

"Record set is not updateable” Error in Access

In Microsoft Access, you can bind an Access form to an ADO recordset that is using data from an Oracle or SQL database. If you want to update the form, you must make sure that both requirements met such as The ADO connection that is used by the recordset must use Microsoft OLE DB Provider for Oracle and The ADO recordset must be opened as a client-side cursor. When you try to programmatically edit records in an access table by using an ActiveX Data Objects (ADO) recordset in a Microsoft Office Access database project, you may not be able to edit the records.

There could be many things that cause these behaviors as If the form is based on a query that is not updatable, you will not be able to modify data on the form done with your database or the query or the form is based on a linked table that does not have a primary key or a unique index and This happens when the field you are trying to change comes from a table without a key field joined to another table in the query. It may be possible your database is corrupted or virus affected but we will discuss it later. Let’s work around with above errors.

To fix this error review any table design changes you might have made change key fields, remove key fields, replaced table with one from another db. If it is then change it in original form. You can install new latest Microsoft Data Access Components which fix many errors because sometime it cause due to version issues. You can try compact the database, and if that doesn't help try recreating the query: open it in SQL view; copy and paste the SQL text to Notepad; delete the query; create a new query and paste the SQL back or you can go for this method. First open the database and check the user and group permissions via tools on the menu. Make sure that the objects (Tables) you want to update have the update permission checked. Secondly Ensure the table owner is you or admin and not the person who developed.

If still you are getting this message it points toward database corruption. You should use compact and repair utility but in critically damaged file compact and repair utility doesn’t support. Now you need a good Access Repair utility. You can search lots of repair utility on internet. I know one recover utility which is Stellar Phoenix Access Recovery tools. This access recovery software helps you to repair database file. It fixes errors in recordset and errors that cause database corruption in mdb file. This mdb repair or access repair utility supports every version of Microsoft access as Access 97, 2000, 2002/XP, 2003 and 2007