Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/104979

From BetaArchive Wiki

ACC1x: How to Use a Main/Subform with Attached SQL Server Tables


The information in this article applies to:

  • Microsoft Access 1.1


When you are using a main/subform and the RecordSource properties of both forms are attached SQL Server tables, performance is reduced. The select syntax is sent to ODBC as a parameter query, in which the data types of the linked fields are not known.


Using a main/subform with attached SQL Server tables results in a parameter query for the subform's RecordSource property. All fields in which the linked field of the subform is equal to the linked field in the main form are selected for the subform. Parameter queries based on attached ODBC tables run slowly when the data type of the field in the WHERE clause is unknown.

To work around this problem, use the following procedure:

NOTE: The following example is based on the sample database NWIND.MDB, in which the Employees and Orders tables are exported, attached, SQL Server tables. The main form is called frmMain and the subform is called frmSub. The linked field is Employee_ID. This example also assumes that the RecordSource properties of the two forms are the Employees and Orders tables, respectively.

  1. Delete the Employee_ID field name in the LinkChildFields and LinkMasterFields properties of the subform control on the main form.
  2. Create a query based on the attached Orders table. Specify Forms!frmMain!Employee_ID as the criteria of the linked field.
  3. Switch to Design view. From the Query menu, choose Parameters.
  4. Specify the query parameter as Forms!frmMain!Employee_ID and the data type as Long Integer.
  5. Change the RecordSource property of the subform to reflect your new query.

The main/subform behaves the same as before, except that the subform's query and the control on the main form maintain the link through a parameter query.

These concepts apply to any form where the subform is based on an attached ODBC table.


Microsoft Access "User's Guide," version 1.1, page 184

Keywords : kbusage
Issue type : kbhowto
Technology :

Last Reviewed: November 4, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.