Microsoft KB Archive/295235

= ACC2002: The Upsizing Wizard Does Not Upsize Nested Queries If Base Queries Are Upsized as Functions =

Article ID: 295235

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q295235



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
The Microsoft Access Upsizing Wizard does not upsize a nested query if both of the following conditions are true:
 * The query includes a lower-level query that includes an ORDER BY clause in its SQL statement.

-and-


 * The lower-level query is involved in more than one join.



CAUSE
The Upsizing Wizard converts queries with ORDER BY clauses to functions. Therefore, the lower-level query is converted to a function. When a function is involved in more than one join in a query, the Upsizing Wizard does not upsize the query.



RESOLUTION
To work around this problem, use one of the following methods.

Method 1
Copy and paste the SQL statement from the nested query in your Microsoft Access Database to a new query in the upsized Microsoft Access project. To do so, follow these steps:
 * 1) Open the database that contains the nested query.
 * 2) Open the nested query in Design view.
 * 3) On the View menu, click SQL View.
 * 4) Select the entire SQL statement so that you can copy it.
 * 5) On the Edit menu, click Copy.
 * 6) Close the query. Do not save the changes.
 * 7) Open the upsized Microsoft Access project.
 * 8) In the Database window, click Queries under Objects, and then double-click Create view in designer.
 * 9) In the Show Table dialog box, click Close.
 * 10) On the View Design toolbar, click SQL to open the SQL window at the bottom of the screen.
 * 11) In the SQL window, select the &quot;SELECT FROM&quot; text so that you can paste over the text.
 * 12) On the Edit menu, click Paste. If necessary, edit the query so that it conforms to the proper T-SQL syntax.
 * 13) Save the view and give it the same name as the query name in the Access database (.mdb) file.
 * 14) Run the query.
 * 15) Return to Design view.
 * 16) In the Sort Type column, select the sort order for the field that you want to sort by.
 * 17) Rerun the query.
 * 18) Close and save the query.

Method 2
Before upsizing your database, open the lower-level query in Design view, remove the sort order, and then reinsert the sort order in the upsized query.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce the Behavior
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

 Open the sample database Northwind.mdb. Create a new query in Design view. In the Show Table dialog box, click Close. On the View menu, click SQL View.  Type or paste the following text into the SQL window: <pre class="fixed_text">  SELECT Employees.LastName, Orders.OrderDate, Orders.ShipCountry, [Order Details Extended].ExtendedPrice, Products.ProductName, Categories.CategoryName FROM Employees INNER JOIN (Categories        INNER JOIN ((Orders            INNER JOIN [Order Details Extended]      ON Orders.OrderID = [Order Details Extended].OrderID) INNER JOIN Products ON [Order Details Extended].ProductID = Products.ProductID)            ON Categories.CategoryID = Products.CategoryID) ON Employees.EmployeeID = Orders.EmployeeID; </li> Save the query as qryNested, and then close it.</li> Upsize the database.</li> After the Upsizing Wizard is finished, note that the qryNested query was not upsized in the new Microsoft Access project.</li></ol>

Additional query words: pra upsize nested queries order by sort

Keywords: kbbug KB295235

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.