All my subforms broke when I upsized

[Dave]

I upsized an Access database to MS SQL Server 2000.

All went well but all the forms in the adp file that use a subform are
broken.

When I try to load the parent form I get the message: "The column prefix
"formula_question" does not match with a table name or alias name used in
the query."

But it does. The subform has a hidden textbox with the control source of
QuestionID. The parent form has a textbox with a questionid control source.

I click on the properties box of the subform and I set questionid as the
link child and link master fields but I still get the error.

It worked and still works fine in the mdb file.

What could I be doing wrong with the adp file?

 

[Peter YangMSFT]]

Hello Dave,

I understand that you encounter error message "The column prefix
"formula_question" does not match with a table name or alias name used in
the query." when you try to open parent form containing subform after you
upgrsize the mdb databaes to ADP. If I'm off-base, please let me know.

Based on my experience, this error can occur if the sub form is based on a
query that references two tables

and

one of the fields is sorted

and

the Order By Clause in the SQL Statement contains both the table name and
field name.

I'd like to confirm if the issue only occurs with a specific parment form.
Also, please let me know if the query subform based apply to above
descriptions. If not, please provide me with more detailed information with
the subform and the query.

If the issue applies to above symptoms, this is a limitation in Access
project. You may try the following steps to work around the issue:

creating a Stored Procedure using the exact same SQL statement that is used
in the query and using stored procedure as the
Record Source of the sub form

or

Removing the reference to the table in the Order By clause of the SQL
Statement.

or

Create a view based on the same select statement and use this as the record
source for the subform.


If you have any update or comments, please feel free to let me know. Thank
you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================
Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.

If this response time does not meet your needs, please contact CSS for more
immediate assistance:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607

 

[Sylvain Lafontaine]

If I remember correctly - but I might be wrong - with ADP, link child and
link master fields will works only when the record source for the subform is
a table or a view and they shouldn't work when it's either a query string or
a stored procedure.

 

[Dave]

Peter:

Yes, that was precisely my problem: the ORDER BY used in the subform record
source.

Thank you.

 

[Peter YangMSFT]]

Hello Dave,

My pleasure!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.