I must be one of the few people left who still use Access Data Projects (.adp) as a quick and easy way to get a front-end into SQL Server databases. Since Access 2013 killed that feature (Access 2013 can’t even open .adp files…) I’ve just kept a copy of Access 2010 installed. The reasons why are a subject for a different post, maybe.
I ran into an error I don’t recall seeing ever before in all the years I’ve used ADPs. I was unable to open one table in the database with the following error:
This didn’t just happen though. It happened after I renamed a column in that table. And ‘Column Name’ just happened to be the old name of the column.
Still, I have renamed many columns and never run into that issue; something was different this time. The long answer is below, here is the short one: an Extended Property (MS_OrderBy) referenced the renamed column and couldn’t be automatically updated when the column was renamed. I manually deleted that extended property using
EXECUTE sp_dropextendedproperty @name = N'MS_OrderByOnLoad' , @level0type = N'SCHEMA', @level0name = N'<schema name>' , @level1type = N'TABLE', @level1name = N' <table name>';
This removed the extended property that contained a value (rather than a column reference) that referenced the old name of the field. I also could have just reset the value to remove just the offending column name, but it’s easy enough in Access to just recreate that extended property by sorting the table and saving changes.
Extended Properties
If you’re not familiar with extended properties, it’s a way in SQL Server to add additional metadata to a database, table or field. It can be pretty much anything you like (you get to pick the name of the property and its value). Extended properties are managed using system stored procedures, like
sp_addextendedproperty
and
sp_dropextendedproperty
Access uses extended properties to store values like the width of each column in the table datasheet, etc. Now, SQL Server is pretty smart and if you rename a column, it will also update any extended properties that reference that column. That’s made possible by the fact that extended properties are defined using different levels of references, where level0 is the schema, level1 the table and level2 the field (there are other hierarchies possible, depending on the type of object you’re attaching the property to).
When you rename a column, SQL Server can go and find any extended property that references that just renamed column (or schema, or table, or… you get the idea) and update the value.
Access and Order By
Access stores the last used sort order of a table using an extended property defined something like this:
EXECUTE sp_addextendedproperty @name = N'MS_OrderBy'
, @value = N'[table name].[column1 name], [table name].[column2 name] DESC'
, @level0type = N'SCHEMA', @level0name = N'dbo'
, @level1type = N'TABLE', @level1name = N'Divisions';
As you can see, because sort order can be defined using multiple columns, the extended property is defined at the table level (level1) and the value lists the columns in a comma-separated format. SQL Server’s smarts to rename column references in extended properties doesn’t work!
The next time you try to open that table using Access, Access will read the extended properties and attempt to sort the datasheet by the columns specified. When that fails, apparently Access is content to just not open the table at all.
What’s the solution? One simple idea is not to save changes when you’re asked to by Access after changing sort order or the filter. I usually don’t, so that probably contributed to the fact it took so long for me to run into this problem. Another idea is to keep this blog post handy and just execute the sp_dropextendedproperty stored procedure when you run into this trouble.
If Access Data Projects were still supported by Microsoft, I would expect to file a bug and ask Microsoft to just let Access silently ignore the MS_OrderBy extended property if it’s bogus, but alas, that’s not going to happen.
Learn More
Read more about Extended Properties at https://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx.
Jamie Thomson has a good overview of support for Extended Properties in SSDT: http://sqlblog.com/blogs/jamie_thomson/archive/2014/09/23/extended-property-support-in-ssdt-10-days-of-ssdt-day-5.aspx.
Sven Aelterman 