Access 2007 problem with Extended Properties using Accesss 2003 Pr

[Matthew ORiordan]

Hi

I have had a persistent problem since I have upgraded to Access 2007 which I
have been unable to resolve in code or through any level of debugging.
However, if I open the same Access Project file now in Access 2003 the
application works which indicates to me that Access 2007 has a bug of some
sort with Extended Properties.

Whenever I choose a drop down value for a new record in a sub-form (linked
by Primary Key) I get an error as follows:
[Accesss Application Name] can't find the object 'SELECT &,
sql_variant_property(value, 'basetype') as type FROM
::fn_listextendedproperty(N'MS_WSSTemplateID',N'user',N'LemonAccessApp',N'table',N'[Table name used by parent form],NULL,NULL)'
The master form is a relatively simple form in Data View, with a sub-form
control containing another form which is in Data View. I have tried changing
the sub-form to Form View and this makes no difference. Strangely there is
no code interfering in this i.e. there are no onChange events etc. so this is
a pure Access form issue.

I have searched the web for any references to MS_WSSTemplateID and cannot
find anything. It appears that for some reason access is trying to retrieve
extended properties from the table of the master form which do not exist. If
run that SQL in Query Analyzer I get an empty result table returned.

I would appreciate any advice on how to get around this problem or what may
be causing it. This has had me stumped for weeks now and is making me think
about downgrading everyone back to 2003!

Thanks.

--
Regards,

Matthew O'Riordan
Lemon Foundation
http://www.lemonfoundation.com/

[Sylvain Lafontaine fill the blanks, no spam please]

Maybe you have some squeletton left; so the first thing to try would be to
remove all Extended Properties on the server, both for table and all of its
columns.

Other things that can cause trouble is when you change the width or set the
sort order of a column in Data View but later remove this column from the
table. Access still see the sorting order for the now missing column and
try to get its extended property from the table but now as this column is no
longer there, you got an error message similar to this.

However, in your case, I don't understand why you get this error only with
Access 2007. Maybe you should try creating a blank ADP file and import
everything into it and also delete all extended properties on the server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Matthew ORiordan" <Matthew...@discussions.microsoft.com> wrote in
message news:13035207-34C1-4192...@microsoft.com...

[Aaron Kempf]

yeah you could get rid of the items in the extended properties table

that's what I'd reccomend

"Matthew ORiordan" <Matthew...@discussions.microsoft.com> wrote in
message news:13035207-34C1-4192...@microsoft.com...

[kristof.van...@gmail.com]

Hello,

We experience the same problem using Access 2007 and ADP project.
There are no extended properties on any table object; could it be a
bug ?
Even Access 2003 on the same computer has the same issue since the
installation of Office 2007 (Access 2003 worked fine before)

Have you found a solution yet ?

Regards,

Kristof Vandenbogaerde

[Sylvain Lafontaine fill the blanks, no spam please]

How can you be sure that there is not extended properties added by ADP on
any table object? Did you verify this?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

<kristof.van...@gmail.com> wrote in message
news:1178115318.8...@n76g2000hsh.googlegroups.com...

[Cosa]

Hello,

I have checked the extended properties using 'SQL Server Management
Studio Express', Table Properties on the table, Extended Properties.
I have done this for both tables [Support] and [Support_Action]. There
are no extended properties visible in the list.

I only experience the problem when using a form with a subform !
( Tables: [support] and [Support_Action])
The forms are linked as follow: (link master field=ID; link child
id=support_ID, source object=Support_Action_subform, master
form=Support_Action_Form).

Whenever I choose a drop down value for a new record in the sub-form

(linked by Primary Key) I get an error as follows:
[Accesss Application Name] can't find the object 'SELECT &,
sql_variant_property(value, 'basetype') as type

FROM ::fn_listextendedproperty(N'MS_WSSTemplateID',N'user',N'LemonAccessApp',N't­
able',N'[Support],NULL,NULL)'

I figured out that 'MS_WSSTemplateID' has something todo with
Sharepoint integration. I have no sharepoint services on my sql
server. We use SQL 2000 SP4.
Also: The same ADP file worked fine on several computers using Access
2003. Since the installation of Access 2007 we experience the problem
on Access 2007 and on Access 2003 (this worked well before).

Regards,

Kristof

[Sylvain Lafontaine fill the blanks, no spam please]

You must also check it for each column of the table and not only just for
the table itself.

For the link master and child field ID, ADP should not use them for a
subform when the record source of the subform is a Table (or a View?)
instead of a stored procedure, an EXEC statement or a Select query.

If you are using a Table or a View as the record source of your subform, I
would suggest that you use anything else but that.

For the rest, I cannot really help you because I don't have at this moment
an installation of Access/ADP 2007 (because my old faithful Pentium 4 is not
really enough powerful to run that inside a virtual machine).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Cosa" <kristof.van...@gmail.com> wrote in message
news:1178119914.6...@e65g2000hsc.googlegroups.com...

[Cosa]

Hello,

I'm Kristof's colleague.

I have checked all the extended properties for all columns in those 2
tables, the only extended properties who appear on some (not all)
columns are the following:
MS_DisplayControl 109
MS_Format [null]
MS_IMEMode 0

I even checked the table and columns from the table who fills the
dropdown-box in the subform.
I see the same extended properties on other tables, which works fine
(in a form, not a subform). I haven't found any MS_WSSTEMPLATEID.

Why should it be a server or ADP error if the same adp-file is working
fine on a computer with Access 2003 installed ? Why is the same adp-
file not working anymore after installing access 2007 on the same
computer when using the old Access 2003 ? Couldn't it be a bug from
Access 2007 or ADO ?
We have experiences this on Windows XP/Windows Vista.

Can we send you additional information to investigate this problem ?

Best regards,

Andries Muylaert
Microsoft Small Business Specialist

[Sylvain Lafontaine fill the blanks, no spam please]

The fact that your code doesn't work anymore after the installation of
Access 2007 is a good question but for which I doesn't know the answer. If
I would know the answer or - even better - a solution to your problem, I
would tell you but for now, all I can suggest to you is that either you open
an incident report with MS, stay with A2003 or try to recode your subform in
order to find a workaround for this problem under A2007. Access 2007 is a
very new version; so bugs and compatibility problems are to be expected.

Trying to recode your subform might be a solution; particularly if you are
using a Table or a View instead of a Select query, a stored procedure or an
EXEC statement as the record source of the subform but of course, I cannot
tell you for sure if this will work or not.

Personnally, I don't work for MS so no, you cannot send me additional
information for investigating this problem.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Cosa" <kristof.van...@gmail.com> wrote in message

news:1178128119....@n59g2000hsh.googlegroups.com...