Microsoft KB Archive/209116

= HOW TO: Base Subforms on SQL Pass-Through Queries in Access 2000 =

Article ID: 209116

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209116



Advanced: Requires expert coding, interoperability, and multiuser skills.

For a Microsoft Access 2002 version of this article, see 288633.

IN THIS TASK
SUMMARY
 * SQL Pass-Through Queries Are Read-Only
 * The Query Builder Does Not Save the Connect String
 * SQL Pass-Through Queries cannot use LinkChildFields/LinkMasterFields

REFERENCES



SUMMARY
This article shows you how to use an SQL pass-through query for a form's record source. It assumes that you know how to build and use SQL pass-through queries.

You should keep the following considerations in mind when you use SQL pass-through queries for record sources of forms:
 * SQL pass-through query record sources are read-only.
 * The Query Builder does not save the connect string unless you save the query.
 * You cannot use a linked subform or subreport with a SQL pass-through query as its record source.

back to the top

SQL Pass-Through Queries Are Read-Only
Forms based on SQL pass-through queries are read-only because SQL pass- through queries are read-only. The recordset returned by an SQL pass- through query is a snapshot, or read-only recordset. This behavior is by design. In order for the form to be updateable, base your form on a linked table with a unique index.

back to the top

The Query Builder Does Not Save the Connect String
The Query Builder of the RecordSource property displays a window that looks similar to the Design window of a query. You use this window to build the SQL string or query for the RecordSource property. The Query Builder will set the RecordSource property to an SQL string if the SQL string is not saved as a query. If you save the string as a query, the name of the query will be used as the RecordSource property.

When you are using an SQL pass-through query created by using the Query Builder, the ODBC connect string defined in that query will not be returned as part of the RecordSource property SQL string. This can produce the following error message:

Couldn't find input table or query '[Table from ODBC Server]'

Without the ODBC connect string, the form will look for a local table, instead of a table on the server. If you save the SQL string as a query, the RecordSource property will contain the name of the query instead of the SQL string, and the form will be able to retrieve the remote data correctly.

back to the top

SQL Pass-Through Queries cannot use LinkChildFields/LinkMasterFields
The best way to use server-based data in a subform is to link the remote table and then base the subform on the linked table.

If you base a subform directly on an SQL pass-through query, you may receive the following error message when you open the form:

You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform or subreport.

This error occurs if you have LinkMasterFields and LinkChildFields defined for the subform or subreport.

back to the top

