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:
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.
- Set the ReturnsRecords property to Yes.
- Set the ODBCConnectStr property to the following:
ODBC;DSN=<DSNName>;SERVER=<ServerName>;UID=<UserName>;PWD=<Password>
- 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 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
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.
- Open the sample database Northwind.mdb.
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
- 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 msjet msjet35 dll
Keywords: kbbug kberrmsg kbusage KB160893