ACC: How to Base Subforms on SQL Pass-Through Queries

ID: Q112746

The information in this article applies to:

SUMMARY

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

This article describes 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:


MORE INFORMATION

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 an attached table with a unique index.

The Query Builder Does Not Save the Connect String

The RecordSource property's Query Builder displays a window that looks similar to a query's Design window. 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 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 error message "Couldn't find input table or query '[Table from ODBC Server]'" because 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.

SQL Pass-Through Queries cannot use LinkChildFields/LinkMasterFields

The best way to use server-based data in a subform is to attach the remote table and then base the subform on the attachment.

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

   In Microsoft Access 7.0 and 97:

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

   In Microsoft Access 1.x and 2.0:

      The crosstab query underlying a subform or subreport must have fixed
      column headings.

This error will occur in Microsoft Access 7.0 and 97 if you have LinkMasterFields and LinkChildFields defined for the subform or subreport. In order to use an SQL pass-through query as a subform's record source in Microsoft Access 1.x and 2.0, you have to dynamically set the subform's RecordSource property in the main form's Load event. The following is an example of an OnLoad property event procedure in the main form MainForm that dynamically sets the RecordSource property in the subform SPTSubForm: 

   Sub MainForm_OnLoad
     Me!SPTSubForm.Form.RecordSource = "[<Name of SQL pass-through query>]"
   End Sub

The subform itself should be saved without a RecordSource property. Note that the subform will not work if the LinkMasterFields and LinkChildFields properties are set. If you need to filter the records in the subform based on a value in the main form, either base your query on attached tables, or modify the QueryDef so that the query includes the value in the main form.


REFERENCES

For more information about SQL pass-through queries, search the Help Index for "pass-through queries," or ask the Microsoft Access 97 Office Assistant. 


Keywords          : kbusage FmsProp 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 3, 1999