Microsoft KB Archive/160893

= ACC97: Querying Linked SQL Server Tables May Cause Error =

Article ID: 160893

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q160893



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



SYMPTOMS
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:  Open the sample database Northwind.mdb. Create a new query in Design view. Close the Show Table dialog box without adding any tables to the query. On the Query menu, point to SQL Specific, and then click Pass-Through. 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;

 On the View menu, click Properties to display the query's property sheet.</li> Set the ReturnsRecords property to Yes.</li> 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.</li> On the Query menu, click Run. Note that the query's results are displayed and no error is generated.</li></ol>

<div class="status_section">

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 Download Center.

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

172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download

<div class="moreinformation_section">

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.

<ol> Open the sample database Northwind.mdb.</li>  Create the following new query based on the dbo_authors and dbo_titleauthor tables: <pre class="fixed_text">     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 </li> On the Query menu, click Run. Note that the query results appear in Datasheet view, and then the error occurs.</li></ol>

<div class="references_section">