By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 1,966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

This recordset is not updateable when using filter or ado recordset

P: 18
I've looked at everything I can find on the Internet for why I get the error "This recordset is not updateable" when attempting to change a data element on a form. I created the form in Access 2007 directly from the source table. When I filter on the form manually, I can change a field succesfully. However, when I try to do anything programmatically, I get the error. I've tried adodb recordset from OpenArgs and with all records then a filter. My next step is to use a make table query to create temporary table and then an update query to use the temporary table to update the record table. Before I write all this code, I'm just wondering if anyone else has any ideas. The source table is an amalgamation from appending imported Excel files that I get every two weeks as .csv files. I thought maybe this was part of the problem, so I copied the table structure only and appended the data to see if it's some kind of conversion error. Same result. The form is set to updateable, Dynaset. I remember now that I was going to try setting the form property to Dynaset (inconsistent updates). I'll try that tomorrow, but I am going to post this since I've typed so much information. I've spent a day and a half on this and I'm running out of ideas. I can post code, but I've tried so many different things, I can't understand why any kind of filtering on the data causes it to be unupdateable.
Apr 20 '11 #1
Share this Question
Share on Google+
5 Replies


P: 18
I don't see a way to delete this post, so I'll just reply that I solved my problem by using an UPDATE query.
Apr 21 '11 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
You can try taking a look at this article on bytes:
Reasons for a Query to be Non-Updatable
Apr 22 '11 #3

P: 18
Thank you. I had read that article and I still don't know why my table cannot be updated. What I ended up doing was using a querydef and writing an update query and that worked. There are a couple hot buttons that my table pushes i the non-updateable query rules. 1. It's imported from Excel and 2. When imported it does not have a PK. I added a unique key, but that still didn't make it any happier. Very strange though. Maybe someday something will click and I'll know why I can't update the thing with a Select recordset.
Apr 26 '11 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
If you want to post code as well as the recordsource SQL of the form, I can look at it. Please identify the lines that err, and the exact err message given by Access.

Just to be sure, your table is IMPORTED from excel, not simply Linked to excel, correct?
Apr 26 '11 #5

P: 18
Yes. I do import the table and then use an append query to tack it to the end of the rest of the fuel records. Thanks for the kind offer, but I actually no longer have the code that didn't work. I did not originally import the tables with a PK, but have since added that. Wonder if that might some how be the issue. I'm using the PK in my update query and it seems to work now. However, I do still have a problem that you might be able to look at for me. The update will stop working and I have ao do manage and repair to the dB. Probably a memory leak? Or something not closing? I've attached (what I hope is) the pertinent code. I appreciate your suggestions and refinements.
Attached Files
File Type: txt UPDATE_ODOM.txt (1.7 KB, 340 views)
Apr 26 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.