troubleshooting Question

Worrying SQL Server and Access table displays

Avatar of Bernard Thouin
Bernard ThouinFlag for Switzerland asked on
Microsoft SQL Server 2005Microsoft Access
6 Comments1 Solution308 ViewsLast Modified:

I have a table (originally in an Access DB) now since a while in SQL Sever (2005). This table has a split primary (unique) key composed of 4 fields, one fixed-length string and 3 number (integer and byte) fields. Since ever, it displays in Access (2007 and 2013) always nicely in PK sequence, never had any problems with it.  However, when I migrated the contents of the table from Access to SQL Server, I was always puzzled by the fact that SQL Server did not display the data in PK sequence. But I had seen that in SQL Server in other tables in other projects and instances, so I did not worry, because in Access, which is used as front-end, it always displayed as expected, in PK sequence. Until this morning, that is !

Yesterday, I added an index on one field of this table in SQL Server. I certainly did not think that that would create the complete chaos that ensued.

This morning, some of my users reported a strange thing happening in a report based on that table. When I looked over Access at the table, I nearly had a heart attack. Lots of rows seemed to be missing. Looking more at the table, I discovered that all rows were actually there, but they were NOT displayed in PK sequence anymore !!!

I refreshed the link. No change. I de-linked and relinked the table. No change, still a completely jumbled display ! So I decided to delete the newly created index in SQL Server, as this was the only change done to the DB. And, presto, after refreshing the table link, it displayed again in PK sequence !

I have NEVER seen such a thing before. What can drive Access to show a table NOT in PK sequence, when it shows in design mode that it has a unique (albeit multi-part) key ?

Even weirder was the fact that it looked like that some queries and reports would obviously find the underlying table in PK sequence, and others not.

Have you ever experienced something like that ?

Thanks for your opinions and hints.
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros