Use of recordset in a form

[Pierre]

Hi all,

I have a form that is displaying data from a table A.

When the user is filling the form, i have to make a seek in an another
table B to fill a certain field.

Actually, each time the seek is made, we create a recordset from table
B, perform the seek, then close the recordset.
(This is based on example of program we saw in documentation.)

My question is:

Would it be better to open the recordset B when the form is loaded
let it open and made the search when necessary and simply close it when
the form is closed?

I saw number of example where recordset are open and close immediately
after an operation. Is there a reason?

I use to program in clipper where you open the files one time at the
beginning of a module and close them when the module terminate.

Table B is shared with an other application.

Regards
Pierre

 

[Marshall Barton]

I have a form that is displaying data from a table A.

When the user is filling the form, i have to make a seek in an another
table B to fill a certain field.

Actually, each time the seek is made, we create a recordset from table
B, perform the seek, then close the recordset.
(This is based on example of program we saw in documentation.)

My question is:

Would it be better to open the recordset B when the form is loaded
let it open and made the search when necessary and simply close it when
the form is closed?

I saw number of example where recordset are open and close immediately
after an operation. Is there a reason?

I use to program in clipper where you open the files one time at the
beginning of a module and close them when the module terminate.

Table B is shared with an other application.

Very difficult question. The "best" way really depends on
all kinds of details about your specific situation. There
is no pat answer, you need to apply a fair amount of deep
thinking to your app's over all design.

In a very simple situation, opening the recordset once might
be faster. But leaving it open may interfere with other
users/processes getting to the data or require you to make
sure the data is properly refreshed.

If opening the recordset every time better suits your app's
operations (and you only want a single field's value), then
you may as well use the slightly easier to use DLookup.

A third approach that you should also consider is to join
the two tables in the form's record source query. This
should take care of all the form's records in one fell swoop
so you can just bind a text box to the table B field (just
remember to lock the text box).