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

• Microsoft Access version 1.1

SUMMARY

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.

MORE INFORMATION

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.

REFERENCES