Performance and Best Practice - form recordsource (1 Viewer)

[BiigJiim]

Hi,

I have taken over a database which is running far too many concurrent users (60+) on an extremely slow network (over wifi! I know!)

One of the main forms has a query as its recordset which accesses a table with over 100 fields and 10k records. The query returns all records although the form only ever accesses one record. At present the form is opened in vba with an OpenForm statement and a string in the Where condition to filter the chosen record (based on another form.)

My question is this:

Is there any performance hit doing it this way, as opposed to putting a WHERE clause into the recordset query, so that it looks at the calling form and only returns the single record that needs to be displayed. The main form would then be opened without any filter or where condition.

Thanks for reading and for any input,
Jim

 

[moke123]

The less data that has to traverse the network would surely be preferable.

Sounds like a disaster in the making with Wifi.

 

[BiigJiim]

The less data that has to traverse the network would surely be preferable.

Sounds like a disaster in the making with Wifi.

That is what I wasn't sure about - if you open the form with a 10k recordset and a where condition in the Docmd.Openform statement, does it pull more data across the network than opening the same form with a recordset query that already has the WHERE in it?

Agree about the wifi - nothing I can do about it. We already have record corruptions. :-(

 

[zeroaccess]

Even WHERE brings more than one record in.

I am using criteria to pull only a single record at a time. The criteria would be something like the ID of the record you clicked on, for example. You can set it with TempVars on the click of a button:

        TempVars.Add "CurrentRecordID", Forms!frmForm.RecordID.Value

Then in the criteria of the form to open:

[TempVars]![CurrentRecordID]

You can also use the criteria to refer to a control on another form to get its value, for example:

[Forms]![frmForm]![cboBox]

Then I just open the form normally with DoCmd.OpenForm and the recordset criteria will do the rest.

 

[sonic8]

Is there any performance hit doing it this way, as opposed to putting a WHERE clause into the recordset query, so that it looks at the calling form and only returns the single record that needs to be displayed. The main form would then be opened without any filter or where condition.

No, there will be no difference between using the Where-Condition in the query directly vs. using it on the form.