INF: Copying Query That Cannot Be Opened in Design View

Article ID: Q109955
Creation Date: 10-JAN-1994
Revision Date: 01-DEC-1996

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

If a query's SQL statement is too long, you cannot open the query in Design view and you may receive the following error message:

   Expression is too long.

This article describes a sample function you can use to retrieve a copy of the query's SQL statement to use as a basis for re-creating the query.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

The following example demonstrates how to create and use the sample function Print_SQL():

 1. Open the database that contains the query you want to retrieve.

 2. Enter the following code in a new or existing module:

      Function Print_SQL(QueryName As String)
        Dim db As Database, q As QueryDef
        Set db = CurrentDB()
        Set q = db.QueryDefs(QueryName)    ' Microsoft Access 7.0 and 97
                                           ' only.

      ' In Microsoft Access 1.x and 2.0, delete the above line for 7.0 and
      ' 97 and substitute the following line:
      ' Set q = db.OpenQueryDef(QueryName) '  Microsoft Access 1.x and 2.0
                                           '  only.

        Debug.Print q.sql
      End Function

 3. Compile the function.

 4. To test the function, type the following in the module's Debug window
    (or Immediate window in versions 1.x and 2.0), and then press ENTER.
    Make sure to substitute the name of your query for <QueryName>:

       ? Print_SQL("<QueryName>")

    Note that the SQL-command version of the query is printed in the
    Debug window.

 5. If you want to use the query being displayed in the Debug window
    as the basis for a new query, select the entire SQL command in the
    Debug window, and then click Copy on the Edit menu.

 6. Create a new, unbound query.

 7. On the View menu, click SQL.

 8. If there is any text in the SQL window, delete it.

 9. On the Edit menu, click Paste.

Note that the SQL command appears in the SQL window. Make any necessary
    changes to the SQL command to shorten it, and then click OK. Note that
    your query appears in the query grid.

REFERENCES

For more information about QueryDefs, search the Help Index for "QueryDefs," and then "QueryDefs Collections," or ask the Microsoft Access 97 Office Assistant.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.