Since this data is lookup data it is usually static, so it might be an option (especially for lookups that are frequently used throughout the application) to load or preload the data once, locally, on the client, and use this data to provide the lookup options. This also ends the necessity of making all the joins-you are effectively joining in the user interface.
Thus it can sometimes be advantageous for performance to have (largely) static data loaded locally on the client PC in a Jet MDB file database, rather than pulling it across a network every time we need to display it. We have to address the problem of when the static data does change and we need to refresh the static local copy. We could have a central source to check, to compare the state of our data with the central data. This could take the form of a table on an RDBMS, which has a record for each lookup table, held in the data source. When that relatively static data is changed, a date value should be set (indicating the date and time of the change). When the client application starts up, one of its initialization actions should be to check this table and compare each of the lookup table dates with the dates for its own local copies of the lookup tables. If it finds a date difference, it should update its local data.
If it is extremely unlikely that the data will change with great frequency or that working practices will not require frequent data changes during the working day, it is safe to assume that application startup is frequent enough to check for changed lookup data status. However, there is risk here. It might be necessary to design a mechanism that can check the timeliness of our static data every time we would normally have downloaded it. Even then, there is a performance gain: the more static the data, the higher the gain. We would only download the changed tables, and then perhaps only the changes, not all the lookups.
When you have the static data, where do you put it? Such static data can be stored in an MDB and accessed via DAO code, or in flat files, or now in persistable classes (more on this in a little while). It can be either loaded by code directly into interface controls, or accessed via DAO code and then the resulting resultset attached to a data control, which is in turn bound to interface controls. (Although in this second option, there is a tradeoff of performance, code volume/ ease of coding, and resource usage.)
