ADP Error opening tables on SQL SERVER 2005 and Access 2007

[Jonathan Crawford]

Hi

I can't access various tables from a newly created (or old) adp file
pointing to a sql server 2005 database

When I try to open the table I get

"can't find the object "select * ,sql_variant_property(value,'basetype') as
type from :: fn_listextendedproperty(N'MS displayViewson
sharepointsite........."

Some tables work, some don't.

Please help

thanks

jc

 

[Sylvain Lafontaine]

If this the complete and exact message, including spaces/absence of space
and the "........." or if it has been truncated or edited?

If this database is or has been associated with a Sharepoint site?

One option would be to delete all the extended properties of the tables.
These options are set by some MS programs - like ADP itself - to store
various settings for the display of tables like any sorting order but that
are otherwise unecessary for running the ADP application itself and will be
recreated automatically by the program if and when necessary.

Also, you shouldn't open directly a table in ADP; you should do all of your
data work through forms.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Jonathan Crawford]

Hi Sylvian

No, it is most of the error messages.
One of the annoyance of using MS products is discovering that some
error messages can be copied by control c, and other can't.

This one can't, so I had to remember a bit of the message, minimize the
screen,
type it out. I tried to be as exact as i could but left out the end of the
message.

The database and the sql server have never ever been anywhere near
sharepoint.

Also, you shouldn't open directly a table in ADP; you should do all of
your data work through forms.

That makes no sense. Why have the functionality if you can't use it. I
actually
only ever use the adp for checking and analyzing data, and testing sql
queries.
I also sometimes write sps and the views through the adp

Please don't tell me I should use linked tables for this because
a) you can test sp's easily
b) every table comes through with dbo_ in front which is very annoying
c) in sql server 2005 if you select all tables to link you get hundreds of
systems table linked - why would they do this, why?

jc

 

[Sylvain Lafontaine]

Then if you have never used this database with Sharepoint, the first thing
to do is probably to delete all those extended properties. (BTW, it won't
hurt you if you make a backup of the database before playing with this.).

For the rest, if you are the only one user of this database and if you don't
mind losing some part or the totality of your database if you ever make a
big mistake then yes, you don't really need to use forms to access your
data. A production database should never be accessed directly - or at
least, never without making first a backup - but a development or a testing
database or a database that you don't mind to lose is another situation.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Jonathan Crawford]

Hi

It is not clear to me where I delete these extended properties.

thanks

jc


===============
Jonathan Crawford
(e-mail address removed)
===============

 

[Sylvain Lafontaine]

With SQL-Server 2000, you can use the browser objects view (F8) of the Query
Analyser (but not the Enterprise Manager) to do it: rigth click on the item
and choose "Extended properties...". Don't forget that you can have
extended properties for both the tables and/or the columns of each table.

With SQL-Server 2005 & 2008, they are accessible directly from the SQL-Sever
Management Studio (SSMS) under Properties. Again, don't forget that you can
have extended properties for both the tables and/or the columns of each
table.

You can also access these properties via t-sql code, search Extended
Properties on the Internet.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)