Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

ACCESS PROJECT ADP FILE EXTENDED PROPERTIES ERROR

When you try to view data for a table in an Access project with an ADP extension, Access can throw a wobbly. Here's why!

Posted by Andy Brown on 21 December 2010 | 1 comment

Problem with Access Project ADP Table

Having just spent a while trying to solve an error with my Access project, I thought I'd share the solution!

The Problem

The error message in question is:

Access project error message

Our irritating message which appears within an Access project

The text equivalent of this is:

Microsoft 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'tblCourseware2',NULL,NULL)1'. 

* 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.

The message appears when you try to open a table in an Access project (that is, an Access database with file extension adp).  I'm currently using Access 2010 and SQL Server 2008 R2, but I believe this problem occurs in all versions of Access after 2003.

The Solution

This error arises because the Access project is trying to get at the extended properties of a table, and these have been altered (in my case it was because I had scripted the table from another database).

The solution is to remove extended properties from the table in SQL Server and from all of its columns. First, show the properties of the offending table in SQL Server:

Table selected in SQL Server, with Properties menu chosen

Go into SQL Server, and choose to change the properties of the offending table:

  1. Right-click on the table causing the problem in SQL Server.
  2. Choose to display its properties.
 

Next, delete all of the extended properties:

Extended properties dialog box

To do this:

  1. Selected to view the table's extended properties.
  2. Click on each property in turn, then click on the Delete button at the bottom of the dialog box (not shown here).
 

Now select each column of the table and do the same thing:

Column selected, with Properties menu chosen

For each column:

  1. Select the column.
  2. Right-click to show its properties.
  3. Delete the column's extended properties as for the table (not shown here).
 

This should solve your problem!

Note that you probably don't need to delete all of the extended properties, as this blog post suggests.

Comments on this blog

This blog has one comment:

Comment added by Newtronic on 03 April 2012 at 23:28 GMT
First try deleting the extended properties for the query before going column by column.  That worked for me.
Reply from Andy Brown (blog author)
Thanks - I'll try it next time! Apologies for the late moderation, by the way.

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.