ACC97: Querying Linked SQL Server Tables May Cause Error

Last reviewed: August 29, 1997
Article ID: Q160893
The information in this article applies to:
  • Microsoft Access 97

SYMPTOMS

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

When you run a query based on two or more SQL Server tables, the query results are returned, and then you receive the following error message:

   MSACCESS caused an invalid page fault in module MSJET35.DLL

CAUSE

There are five requirements that your database must meet in order to produce the error:

  • Your database must contain local tables.
  • You must have a relationship that enforces referential integrity between two or more of the local tables in your database.
  • Your database must contain linked SQL Server tables.
  • Your database must contain a query based on two or more linked SQL Server tables.
  • The primary key field from the one-side table in the query must be the first field in the QBE grid.

RESOLUTION

There are three ways to work around this error:

  • Upgrade to version 3.51 of the Microsoft Jet Database Engine.
  • You can modify your query so the primary key from the one-side table is not the first field in the QBE grid.
  • You can use an SQL pass-through query for your linked SQL Server tables.

The following example illustrates how to create an SQL pass-through query:

  1. Open the sample database Northwind.mdb.

  2. Create a new query in Design view.

  3. Close the Show Table dialog box without adding any tables to the query.

  4. On the Query menu, point to SQL Specific, and then click Pass-Through.

  5. In the SQL Pass-Through Query window, type the following SQL statement.

    NOTE: In the following SQL statement, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this statement.

          SELECT dbo.authors.au_id, dbo.authors.au_lname, dbo.authors. _
          au_fname, dbo.titleauthor.royaltyper
          FROM dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id = _
          dbo.titleauthor.au_id;
    

  6. On the View menu, click Properties to display the query's property sheet.

  7. Set the ReturnsRecords property to Yes.

  8. Set the ODBCConnectStr property to the following:

          ODBC;DSN=<DSNName>;SERVER=<ServerName>;UID=<UserName>;PWD=<Password>
    

    Replace <DSNName> with the name of your ODBC Data Source for the SQL Server database, replace <ServerName> with the name of your SQL Server, replace <UserName> with the logon name of a valid SQL Server user, and replace <Password> with a valid logon password.

  9. On the Query menu, click Run. Note that the query's results are displayed and no error is generated.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. This problem no longer occurs in the Microsoft Jet Database Engine version 3.51, which is available from the Microsoft Software Library.

For information on how to obtain Microsoft Jet Database 3.51, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q172733
   TITLE     : ACC97: Updated Version of Microsoft Jet 3.5 Available on MSL




MORE INFORMATION

Steps to Reproduce Problem

The following example uses the sample database Northwind.mdb because it contains local tables with enforced relationships, which satisfies two of the four requirements to produce the error.

This example assumes that you have linked the dbo.authors and dbo.titleauthor tables to your database from the SQL Server sample database called Pubs.

WARNING: Following these steps will cause an invalid page fault on your computer. Make sure you save and close any open work on your computer before following these steps.

  1. Open the sample database Northwind.mdb.

  2. Create the following new query based on the dbo_authors and dbo_titleauthor tables:

          Query: TestError
          --------------------------
          Type: Select Query
    

          Field: au_id
    
             Table: dbo_authors
          Field: au_lname
             Table: dbo_authors
          Field: au_fname
             Table: dbo_authors
          Field: royaltyper
             Table: dbo_titleauthor
    
    

  3. On the Query menu, click Run. Note that the query results appear in Datasheet view, and then the error occurs.

REFERENCES

For more information about creating SQL pass-through queries, search the Help Index for "pass-through queries, overview," or ask the Microsoft Access 97 Office Assistant.

For more information about customizing the system menu bar and database toolbars, search the Help Index for "customizing menus," or ask the Microsoft Access 97 Office Assistant.


Additional query words: GPF IPF SPT
Keywords : EvnGpf kberrmsg kbusage
Version : 97
Platform : WINDOWS
Hardware : x86
Issue type : kbbug
Solution Type : kbworkaround


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.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.