How to track down "can't find the object" errors copying databases and viewing in Access?

Question

(This doesn't look like a programming question but it might boil down to a programming question.)

I'm using MS Access 2007 to view tables in a database (through an *.adp file) that lies on SQL Server 2000 and SQL Server 2005.

I use the Copy Database task in MS SQL Server Management Studio to copy the data from SQL Server 2000 (the "live" server) to SQL Server 2005 (on my local computer). The copy appears to execute successfully.

I have two separate *.adp files. One links to the DB on the live server and the other to the copied DB on my local machine.

I can work with most of the tables on the DB on my local machine fine, but a few of them result in Access throwing this error before I can even see the data or the design view:

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'tbl_Asset', NULL, NULL)'.

• You misspelled the object name. Check for missing underscores ( _ ) or other punctuation, and make sure you didn'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 have this error on five tables, and the only thing that changes is that "tbl_Asset" is replaced with the offending table name.)

I don't have the same problem when I use Access to view the tables on the live DB. Also, I don't have this problem when I view the tables on the local DB through SQL Server Management Studio.

Anyway, I have no clue where to start looking. I don't think the two suggestions beneath the error message apply.

Any suggestions? Thanks for your help.

UPDATE: Found what I think is the cause, but still can't resolve it. I'll post my progress below. Thanks

Answer 1

By any chance are the offending table names longer? I have received similar messages when the Windows path plus DB name plus table name is longer than 256 characters.

I could be way off - it's just a hunch.

Answer 2

The issue comes when I save the table layout in Access. It causes the addition of Extended Properties in the tables in SQL Server. The tables throwing the error have these extended properties. The tables without extended properties throw no errors.

Someone apparently tackled this error here but the solution he used doesn't work for me.

I deleted the extended properties in the offending table, but this didn't solve the problem either. When I go into SQL Server Management Studio and script the table as a CREATE, I see that the extended properties really don't seem to go away:

GO EXEC sys.sp _ addextendedproperty @name=N'MS _ AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbl _ PRStatus', @level2type=N'COLUMN',@level2name=N'ID _ PRStatus'

I'd really like these extended properties to go away for good, because I think they're the problem, but I don't know how to get rid of them.

Answer 3

When scripting the CREATE from SSMS it will by default include the Extended properties. You'll need to remove these by hand before re-creating the table.