Saturday, November 15, 2008

MS Access - Error 3326 - Recordset is not updateable

On a recent project where I was porting an Access application so that it would use an Oracle database as its backend data store I came across a frustrating error where when I opened a form that was directly linked to a table (via the form's datasource property) I would get the following error:

3326 This recordset is not updateable.

After almost 3 days of following various leads that did not lead anywhere I finally found out what was causing the error.

If you are having the same issue that I had, then if you open the linked table in Access, you will see that the table cannot be edited (The insert menu will be disabled).

image

So you can immediately stop fiddling with the code, form properties, field properties, etc. as that is not what is causing the problems. The problem is entirely database driven. In my opinion either this is a bug in Access or Access is unable to determine the complete properties for the table and hence decides to put the table in a read-only mode.

In my case what I did was I removed all the constraints and indexes on the table and added them one at a time. Magically this fixed my problem. (Each time I changed a property on the table I would refresh the affected linked table in access and make sure that the table was still editable).

Unfortunately at this stage I do not have an exact cause for the problem. In the beginning I thought it was because the table did not have a primary key and then I thought it was default values that were strings and finally I thought it had something to do with indexes on the table. But once I blew away all of those and re-added them to the table, Access suddenly decided that the table was kosher for editing!. Oh! how I hate Access!.

4 comments:

jdauie said...

Don't we all...

Cameron said...

I had a similar problem today but managed to fix it.

I had two tables in my query, one linked to a SQL database, the other just a table in Access.

I created two Join Properties and Viewed the results and ended up with the read-only query results.

I then removed the second Join Property in the query designer and Viewed the results and it lets me edit/delete the results.

Anonymous said...

Raj you are a champion. Thanks so much for doing this post, you pointed me in the right direction with a problem which was unsolved after 6 hours.

Jessica

Lady B said...

Ok, I've been using this database and updating this particular table for several months in 2007 version, and YEARS in older versions...
And SUDDENLY, for absolutely no reason that I can fathom, this error message is now popping up, and only on ONE record that I know of so far...
Can anyone give me some idea of what this freaky thing has decided to do? lol
I appreciate any and all help...