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
370 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now