ACC97: Querying Linked SQL Server Tables May Cause Error

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:

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.

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.

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:

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.

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.