One of the things that complicates larger Microsoft Access databases is the number of database objects - tables,
queries, forms and reports.
This situation can make maintenance confusing and complicated, especially if you didn't create the
database in the first place.
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.
In addition to using my naming conventions (see:
Database Naming Conventions) many programmers combine many lookup tables into a single table.
Here is an example of a standard design for such a table:
Note the primary key fields. Also set the LOV_ID field to Indexed (no duplicates) - this makes it equivalent to
a primary key field for linking to other tables. Also set the LOV_Category field to Indexed (duplicates OK) - this will speed up
the queries. Here's an example of the data entered into this table:
See the LOV_Category - this field makes it possible to combine multiple lookup lists into this single table.
The query design to create a lookup list for Product Color looks like this:
And the final result looks like this:
Now you may say... hey! you've only substituted having many tables to having many queries!... and you would be
correct. To solve this situation consider not saving the Q_LOV_Product_Color as a query in the database. Save the SQL statement
for this query as the Row Source for the Combo Box:
Having all your lookup lists in one table probably won't increase the speed of your database. If you have a
split database (front end/back end) then you can significantly speed up your forms if you store the main L_LOVs table in the front
end. If the entries in this table change or are added to frequently then consider running a query, when the front end database
starts up, that clears out the local (front end) L_LOVs table and inserts the values from the table stored in the back end
database. If you have many lookup lists on your forms then this can dramatically speed up the database once the front end is