ACC2000: Saving Long Expression in SQL View Causes Access to Quit (241194)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q241194
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

In SQL view of a query, if you try to save a long SQL statement, you see one of the following behaviors.

In Windows 95 or 98

You may receive the following error message:
This program has performed an illegal operation and will be shut down.

If the problem persists, contact the program vendor.
When you click Details (on Microsoft Windows Millennium Edition, press ALT+D), you receive the following message:
MSACCESS caused an invalid page fault in MSACCESS.EXE at 0167:300ec09b.
NOTE: The actual memory address may vary.

In Windows NT

Access suddenly quits without error.

CAUSE

In SQL view, you tried to save a query that contains a criteria or an expression that has more than 511 characters.

RESOLUTION

To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

To work around this problem, do not try to edit a criteria or an expression of an existing query in SQL view if the criteria or expression has more than 511 characters.

If you do want to create a query with a criteria or an expression that has more than 511 characters, use the CreateQueryDef method in a Visual Basic for Applications procedure. The following example illustrates how to do this.

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.

  1. Open the sample database Northwind.mdb.
  2. Click Modules under Objects, and then click New.
  3. In the new module, type the following code:
    Option Compare Database

Sub CreateLongQuery()

   Dim db1 As DAO.Database
   Dim qdfNew As DAO.QueryDef

   Set db1 = CurrentDb

   With db1
      Set qdfNew = .CreateQueryDef("LongExpQuery", _
         "SELECT Employees.EmployeeID, [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] & [Employees]![Lastname] & " _
         & "[Employees]![Lastname] AS Expr1 FROM Employees;")
      .Close
   End With

   Application.RefreshDatabaseWindow

End Sub
  4. In the Immediate window, type the following line, and then press ENTER:

    CreateLongQuery

A new query called LongExpQuery appears in Access.

To make changes that require a criteria or an expression that has more than 511 characters, create a new query with the changes that you need by once again using the CreateLongQuery procedure.

You can run the query that you created in this example, but you cannot edit its expression in the query design grid if it has more than 511 characters. For additional information about this issue, click the article number below to view the article in the Microsoft Knowledge Base:

239565 ACC2000: Query Expression Truncated at 511 Characters

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.

MORE INFORMATION

NOTE: This behavior only happens with a criteria or an expression inside the SQL statement. Other parts of a SQL statement or the SQL statement as a whole can have more than 511 characters.

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb, and then create a new query.
  2. On the View menu, click SQL.
  3. Type or paste a SQL statement that contains an expression that has more than 511 characters, such as the following:
       SELECT Employees.EmployeeID, [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] & [Employees]![Lastname] & 
   [Employees]![Lastname] AS Expr1 FROM Employees;
  4. In SQL view, on the File menu, click Save.
Note that you see one of the behaviors mentioned in the "Symptoms" section.
Modification Type:MajorLast Reviewed:11/25/2002
Keywords:kbbug kberrmsg KB241194
©2002 Microsoft Corporation. All rights reserved.