Keep Some Tables in the Front End?

[Dewey]

I have a multi-user A2K application which I've optimized as much as possible
and split into a FE (.mde)/BE. The performance across my small office
network is acceptable for the amount of data moved. I was wondering though,
if the performance of some of my forms might be benefitted by keeping a few
of the underlying tables in the front end that are used to feed the row
source for combo boxes on a couple forms.

The combos do not allow "Not in List" entries, as they list items such as
committee names, budget groups and items that A.) are not likely to change
very often, and B.) are not allowed to be updated by users anyway.

With the help of Tony Toews' AutoFEUpdater utility (thanks, Tony), it would
be just as easy for me to update the lookup tables whenever necessary and let
the clients automatically update the next time they launch the app. Since
the load time increased most for the combo boxes following the split, why not
just keep them local?

I completely understand the importance of splitting a multi-user database,
but none of the resources I've read on the topic make any mention as to when,
if ever, it might be beneficial to keep tables in the FE. Is this commonly
done? What are the drawbacks?

 

[Pat Hartman\(MVP\)]

Keep your BE exactly as it is with all the tables and RI enforced. Copy the
lookup tables to the FE and change the combo's queries to refer to the FE
copies in their RowSource.
If you want, you can have the FE recopy the lookup tables each time it opens
or you can keep a table with dates and only recopy if the date in the BE is
newer than the date in the BE.

 

[Dewey]

I raised the question because my main data entry form has two sets of
parent/child related combo boxes, where the child's row source is determined
by the selection made in the parent. With this kind of arrangement, both
sets of parent/child combos must be requeried during the OnOpen and OnCurrent
events in order to synchronize and properly display existing data.

Since there is so much background interaction required by the four combos, I
just ran a make-table query for each pair - containing the hierarchical data
between parent and child. I then added indexes where necessary and saved
them in the FE to make all the requeries faster.

I suppose I could have created a scheme to automatically update the FE
tables whenever there's a change in the BE as Pat suggested, but I would
likely spend more time researching how to go about it than I would ever spend
updating the tables manually. (Although, I'm sure I'll go through the
exercise sometime in the not too distant future - if only for the experience.)