Access Data Project Error

I have a Access Data Project that connects to an SQL server database. Recently I modified the structure of the database from the SQL server. Now whenever I try to open one of the tables, I get the following error.

Microsoft Office Access can't find the object 'SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(N'MS_DisplayViewsOnSharePointSite',N'user',N'dbo',N'table',N'Hardware',NULL,NULL)'.

• You misspelled the object name. Check for missing underscores ( _ ) or other punctuation, and make sure you d开发者_运维知识库idn't enter leading spaces.
• You tried to open a linked table, but the file containing the table isn't on the path you specified. Use the Linked Table Manager to update the link and point to the correct path.

I tried to create a new access data project and see if it still happened there... and it does.

Apparently deleting the MS_Filter and MS_OrderBy extended properties on the table will resolve this.

Source and instructions: Strange Microsoft Access Error on ADP project (Sql Server 2005 Extended Properties)

We had this same error message come up after getting a weekend Severity21 error on the SQL DB backend. The SQL DB was not having a problem but the Access front end did. Deleting these 3 properties, MS_Filter, MS_OrderBy, and MS_DefaultView worked to resolve the error. Note too that this error only showed up on one of about 20 tables via the Access front end.

To remove all the Extended Properties, get Jamie Thompson’s script from

http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/25/generate-drop-statements-for-all-extended-properties.aspx

Run it, copy the output and run that…