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

recordset not updatable after compression
mssuprAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Serena2345Commented:
Most query recordsets are not updateable in the datasheet view.
0
mssuprAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
... and make sure you have Service pack 1 installed for Access 2007
0
mssuprAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mssuprAuthor Commented:
We figured out the solution on our own. Thank for everyone's input.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.