Anybody know why after Compressing an Access 2007 DB some of our queries are now showing "recordset not updatable"

Posted on 2008-10-31
Medium Priority
Last Modified: 2013-11-29
recordset not updatable after compression
Question by:mssupr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Assisted Solution

Serena2345 earned 80 total points
ID: 22854013
Most query recordsets are not updateable in the datasheet view.

Author Comment

ID: 22856409
you run these queries before compressing they are updatable, you run them after compressing they are not. How, why would that happen? Here is the SQL query
SELECT [Search Results File].[Contact Date], [Search Results File].[Search Id], [Search Results File].CID, [Master Internal Data].[Mr/Ms], [Master Internal Data].[First Name], [Master Internal Data].[Last Name], [Master Internal Data].Salute, [Master Internal Data].Title, [Master Internal Data].Region, [Master Internal Data].Status, [Search Results File].Note, [Master Internal Data].FacilityType, [Master Internal Data].SubacuteProvider, [Master Internal Data].NumFacilities, [Master Internal Data].CompanyInfo, [Master Internal Data].InterestInCompany, [Master Internal Data].PrevSearches, [Master Internal Data].PrevSearchesDesc, [Master Internal Data].SpecialtyArea, [Master Internal Data].NameType, [Master Internal Data].ChainNumber, [Master Internal Data].[Corp/Reg], [Master Internal Data].Search, [Master Internal Data].Mailer, [Master Internal Data].[Letter Date], [Master Internal Data].[Letter Type], [Master Internal Data].Enclosure, [Master Internal Data].[Sent By], [Master Internal Data].[Response Requested], [Master Internal Data].Newsletter, [Master Internal Data].[Tickle Type], [Master Internal Data].[Facility Number], [Master Internal Data].[Marital Status], [Master Internal Data].[Spouse's Occup], [Master Internal Data].Children, [Master Internal Data].Degree, [Master Internal Data].[Date of Degree], [Master Internal Data].[Current Commute], [Master Internal Data].[Open to Relocation], [Master Internal Data].[Ideal Situation/Relocation Preference], [Master Internal Data].[Open to Other Opportunity], [Master Internal Data].[Start Date], [Master Internal Data].[Finish Date], [Master Internal Data].Responsibilities, [Master Internal Data].Accomplishments, [Master Internal Data].Strengths, [Master Internal Data].Weaknesses, [Master Internal Data].[Why Consider Change], [Master Internal Data].[Current Compensation], [Master Internal Data].[Other Benefits], [Master Internal Data].[Projected Commute], [Master Internal Data].[Last Date Contacted], [Master Internal Data].[Website Address], [Master Internal Data].[Email Address], [Master Internal Data].[Cell Phone/Pager], Switch([Master Internal Data].[Facility Number] Is Not Null,[NH96].[Address],[Master Internal Data].[ChainNumber]>0,[Chain Companies 96].[Address],[Master Internal Data].[Facility Number] Is Null,[Master Internal Data].[home address2]) AS Street, Switch([Master Internal Data].[Facility Number] Is Not Null,[NH96].[Zip Code],[Master Internal Data].[ChainNumber]>0,[Chain Companies 96].[Zip Code],[Master Internal Data].[Facility Number] Is Null,[Master Internal Data].[home zip]) AS Zip, Switch([Master Internal Data].[Facility Number] Is Not Null,[NH96].[State],[Master Internal Data].[ChainNumber]>0,[Chain Companies 96].[State],[Master Internal Data].[Facility Number] Is Null,[Master Internal Data].[home State]) AS State, Switch([Master Internal Data].[Facility Number] Is Not Null,[NH96].[City],[Master Internal Data].[ChainNumber]>0,[Chain Companies 96].[City],[Master Internal Data].[Facility Number] Is Null,[Master Internal Data].[home City]) AS City, Switch([Master Internal Data].[Facility Number] Is Not Null,[nh96].[Phone Number],[Master Internal Data].[ChainNumber]>0,[Chain Companies 96].[Phone Number],[Master Internal Data].[Facility Number] Is Null,[Master Internal Data].[Phone Number]) AS [Phone Number], Switch([Master Internal Data].[Facility Number] Is Not Null,[nh96].[Facility],[Master Internal Data].[ChainNumber]>0,[Chain Companies 96].[Name],[Master Internal Data].[Facility Number] Is Null,[Master Internal Data].[home address]) AS Company, [Master Internal Data].[Facility Number], [Chain Companies 96_1].Name AS ChainName
FROM ([Chain Companies 96] RIGHT JOIN (NH96 RIGHT JOIN ([Master Internal Data] INNER JOIN [Search Results File] ON [Master Internal Data].ID = [Search Results File].CID) ON NH96.[Facility Number] = [Master Internal Data].[Facility Number]) ON [Chain Companies 96].[Chain Number] = [Master Internal Data].ChainNumber) LEFT JOIN [Chain Companies 96] AS [Chain Companies 96_1] ON NH96.ChainID = [Chain Companies 96_1].[Chain Number]
ORDER BY Switch([Master Internal Data].[Facility Number] Is Not Null,[nh96].[Facility],[Master Internal Data].[ChainNumber]>0,[Chain Companies 96].[Name],[Master Internal Data].[Facility Number] Is Null,[Master Internal Data].[home address]);

Open in new window

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22882989

Access 2007 does not contain any "Compression" that I know of.
Do you mean *Compaction" as in, using the Compact/Repair utility?

Is this a database that was converted from Access 2003 or earlier?
If so, create a brand new blank Access 2007 database and import all of the objects from the Access 2003 version into the new blank Access 2003 database.

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 80 total points
ID: 22882991
... and make sure you have Service pack 1 installed for Access 2007

Accepted Solution

mssupr earned 0 total points
ID: 22909082
We found our solution : This is what was the answer:

the issue with "The Recordset is not updateable" comes about primarily because one of the tables in the query doesn't have a primary key, so it cannot determine where to make the updates.  Granted, this should not happen just because of a compression of the database, but that's what appears to be the case.
After doing the research on "Why"  I started to compare the database that worked and the one that didn't.  Using the scrn8-Zip Code Search Query and examining all the underlying tables, I found that "Master Internal Data" had a primary index in the non-compressed database, but not in the compressed one.
Easy enough - just put a primary key on the ID, right.  Yeah, well when I tried to do that, I got error messages saying that the data in the table would violate the Primary key constraint.  I searched the table and found that there were 2 records with ID 268901 (how this could happen on an autonumber - don't know)
All the data for both records were identical, so I deleted one, put a Primary Key on the ID of the Master Internal Data table, and now the query is updatable.  Simple fix, but took a while to find out what had to be done.

Author Comment

ID: 22909091
We figured out the solution on our own. Thank for everyone's input.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question