ACC97: Querying Linked SQL Server Tables May Cause Error

This article was previously published under Q160893
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS
When you run a query based on two or more SQL Server tables, the queryresults 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 toproduce 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. Thisproblem no longer occurs in the Microsoft Jet Database Engine version 3.51,which is available from the Microsoft Download Center.

For information on how to obtain Microsoft Jet Database 3.51, please seethe following article in the Microsoft Knowledge Base:
172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download
MORE INFORMATION

Steps to Reproduce Problem

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

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

WARNING: Following these steps will cause an invalid page fault on yourcomputer. Make sure you save and close any open work on your computerbefore 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 theHelp Index for "pass-through queries, overview," or ask the MicrosoftAccess 97 Office Assistant.

For more information about customizing the system menu bar and databasetoolbars, search the Help Index for "customizing menus," or ask theMicrosoft Access 97 Office Assistant.
GPF IPF SPT msjet msjet35 dll
Properties

Article ID: 160893 - Last Review: 10/04/2013 18:06:22 - Revision: 3.2

  • Microsoft Access 97 Standard Edition
  • kbnosurvey kbarchive kbbug kberrmsg kbusage KB160893
Feedback