Binding Form to Query or Recordset?

[bullfrog83]

I'm working in an Access 07 project linked to SQL Server db. Right now I'm trying to determine which is better performance wise: binding a form's recordsource to a query or a recordset in the OnOpen event?

Here's an example. I have a form's recordsource as a query:

SELECT * FROM StdDegReqClass WHERE StdDegReqDiscId = [Form]![Control] ORDER BY SortOrder

Or I bind it to a recordset in the On Open event:

    Dim cn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim lngStdDegReqDiscId As Long
    
    cn.Open CurrentProject.Connection

    Set rs = New ADODB.Recordset
    lngStdDegReqDiscId = Forms!f_StdDegReqDisc.txtStdDegReqDiscId
    
    rs.Open "SELECT * FROM StdDegReqClass WHERE StdDegReqDiscId = " & lngStdDegReqDiscId & " ORDER BY SortOrder", cn, _
        adOpenKeyset, adLockOptimistic
    
    Set Me.Recordset = rs
    
Exit_Procedure:
    cn.Close
    Set rs = Nothing
    Exit Sub

I've read that one way to improve performance is to limit the data by using recordsets because when a form is tied to a table or query Access issues a command to the db server to modify the records and retrieve the data again every time the object is opened or modified. While this makes sense I guess I'm looking for a second opinion on the topic. Your advice is much appreciated!

[nico5038]

The best way is to use a "Pass-Through" query.
This will be send to the database engine and only the filtered result will be sent back. When using an Access query, Access will first fetch all rows before filtering....

Nic;o)

[bullfrog83]

The best way is to use a "Pass-Through" query.
This will be send to the database engine and only the filtered result will be sent back. When using an Access query, Access will first fetch all rows before filtering....

Nic;o)

I've read that pass-through queries are not updatable (they return read-only data) and so are not useful for forms. Is that your understanding?

[nico5038]

When you use a SELECT query for your form, the data will indeed be "read-only".
To maintain updates You'll have to create your own UPDATE query. So for the most effective way you should have to switch to "unbound" forms and handle updates and deletes by code. All things come with a price.....

Nic;o)