Worrying SQL Server and Access table displays
Posted on 2014-08-14
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.