Recordset not updatable

[fangfacekitty]

We have a database with a number of queries created. The person who handles our reporting uses Excel 2003 with pivot tables linked to those Access queries. She goes in weekly to refresh the Excel tables with the current data in Access. Last week it worked fine.

Yesterday when she refreshed all the data disappeared and she is left with just the column headers.

When I go into Access I found that the data tables linked to an excel file (to get the volume data, not the same file she uses for reporting) had broken links so I re-connected the tables. There do not seem to be any errors in the relationships between the tables; in any case no one has made any changes except to add new records.

I can see all of the actual records on the record table. But none of the queries return any data (and there are no pop up error messages).

I did notice at the very bottom of the screen I got the message "recordset not updatable". I couldn't find any information on what this means in the Help function so I'm hoping you all can give me some ideas on why the queries don't pull any data so I know some areas to go look for the problem. Right now I've looked at everything I know to look at and can't see any reason why the queries suddenly stopped working.

Could it be due to the size of the file ? It is currently approaching 60 MB.

 

[strive4peace]

> "It is currently approaching 60 MB."

how often do you compact/repair? (always back it up before you do this -- never choose to do this automatically when the database closes)

What is the SQL for a recordset that is not updatable?

~~
does the Access code compile?

anytime there is an issue in a database, it may be because it doesn't compile so that is the first thing I would check

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions or operating environment, you should always compile and save before executing.

from the menu in a VBE (module) window: Debug, Compile

Fix any errors on the yellow highlighted lines.
Add needed references and remove missing references if necessary

keep compiling until nothing happens (this is good!) -- then Save


~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up

Option Explicit ' require variable declaration

 

[Joe4]

how often do you compact/repair? (always back it up before you do this -- never choose to do this automatically when the database closes)

Out of curiosity, why do you recommend not using the "Compact on Close" feature?
We have a number of automated databases that run nightly that no one touches manually, so we use "Compact on Close" to keep them from bloating over time.

 

[strive4peace]

Hi Joe,

thie biggest reason not to compact/repair a database when it closes is because this is a volatile process and should only be done, in my opinion, just after a database has been backed up -- so right after a database is opened. If the power blips or anything happens during this compact/repair, the database may get corrupted. While it is not likely to happen, it is better to err on the side of caution.

Corruption is not always detected immediately so good idea, of course, to keep backups. Anytime I CR a database, I note that in the backup file name.

In order to compact/repair, the database has to be opened exclusively.

Compact/Repair on a front-end does not compact the back-end

 

[Joe4]

Thanks for the info!

We have a number of data conversion automated databases that import tens of thousands of rows each night, and then export them in different formats (the database really doesn't hold or store the data, it is just a "pass-through" process). As you can imagine, these databases bloat very quickly. I have them all set to "Compact on Close". However, I keep personal backups of each database, and our network is backed up nightly, so I am not too concerned about the ocassional blips. It seems to work fairly well for us.

 

[strive4peace]

you're welcome, Joe

if there is no data in your databases and you have them backed up, then that option is good for you. However, for most people that use Access, Compact and Repair on close is not wise

 

[fangfacekitty]

Going back to the OP question...

I don't think we have ever compacted/repaired the database. It was created back in 2005 and each year we've just been saving a copy, deleting all the old data and entering the new. I've also made a lot of modifications to the tables, creating many more tables & relationships and letting Access queries to the computation work instead of forcing the users to do it all offline and then manually type in the figures.

So...just made a copy & compacted/repaired the copy. The queries still will not run.

I do not know what you mean by "What is the SQL for a recordset that is not updatable?" How do I find this out?

I think the code compiles, I just ran the Debug,Compile you suggested and nothing showed up. So that is good?

I have tried re-creating tables and queries and still can't get it to work.

Any other ideas on how to fix this issue? I am completely out of things I know how to do.

 

[strive4peace]

> "just made a copy & compacted/repaired the copy"

how big is it now?

> "The queries still will not run"

go to the SQL view of a query that isn't behaving from the Design View, get the SQL statement, and paste it here, thanks

 

[fangfacekitty]

I appreciate your time and now feel very stupid. I figured my only option was going to be to recreate the entire database so as I started building the tables I noticed that the user changed the data format in the currency rate table and was now using the European format (with commas instead of decimals) because she did a copy/paste from an e-mail instead of typing them in. That, plus spelling out the currency name instead of using the abbreviation, was what caused the queries to not return any data.

But I did learn some things from this so thanks again for your responses!

 

[strive4peace]

You're welcome ; ) Don't feel bad -- we all overlook things, forget to check stuff, and I am pretty sure not many of us are mind readers to know what someone else did .... Thanks for sharing your solution