Using field data to Fill Row Source of Combo boxes

[Rich K]

I am examining the design of a small access database. There is 5 tables,
with one main table and 4 tables for lookup values. Fairly simple design.
Most of the data entry is done on one form. This form has several fields
that are combo box with limit to list set to no. This is where it gets
interesting. Most of these combo boxes have a row source that uses a
distinct query against the field that it populates. So, in theory they can
grow with the database. I don't like this design at all. My thinking is
that as the "main" table grows, the populating of all of these combo boxes
will take more and more resources, causing the form loading to become slower
and slower. The designer claims to have plenty of forms in use that
implement this strategy without complaints.

Am I being overly sensitive, or will this become a problem? Anybody with
experience in this type of form design?

Thanks in advance.

 

[Hunter57]

Hi Rich,

Should you be concerned? It depends on a number of things. Is your
database on a network being used by more than one person at the same time?
If so, that would slow down the forms.

How small is small? Mainly, how many records does your database hold? How
large do you expect the database to grow? Access is supposed to be able to
handle 2 gigabytes. If the database is small and it is running slow now then
you should be concerned.

How many combo boxes are there and how many columns are in the the combo
boxes? Many Columns can slow down a form.

Most of all it depends on the skills and knowledge of your database
designer. There are some ways to speed up things considerably--if your
designer knows them. For instance, forms can be set up to load only one
record at a time by using a query that limits the form to only one record.
Also, if a form is opened in data entry mode it runs faster.

I hope this has been at least a little help to you. The more specific
details you give the more specific the help can be.

Hunter57

 

[Rich K]

Thanks Hunter,
All of your points were issues that I was concerned about. I went ahead and
rewrote this database. I didn't really want to get into specifics because if
the designer has been using this design charactoristic in forms in other
projects, I can prepare for slowness complaints if and when they bang on our
door.

I've never used this technique much, and I can see where it has it's uses.
But I agree, overuse of it, especially in a multi-user network environment,
will cause heartache for database users and the network guys.

Now, the hard part, convincing my colleague.