Solved

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

Posted on 2008-10-31
6
374 Views
Last Modified: 2013-11-29
recordset not updatable after compression
0
Comment
Question by:mssupr
  • 3
  • 2
6 Comments
 

Assisted Solution

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

Author Comment

by:mssupr
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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22882989
mssupr,

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.

JeffCoachman
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
0
 

Accepted Solution

by:
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.
0
 

Author Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

856 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