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

Posted on 2008-10-31
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 20 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.

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 20 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

697 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