Microsoft Access Quick Queries #20: Avoiding Lookup Fields, Dlookup, and Database Maintenance Tips

Today's TechHelp tutorial from Access Learning Zone focuses on addressing various questions that don't require an entire video on their own. This is episode 20 of our Quick Queries series, where I compile your frequently asked questions into one comprehensive session. Let's get started.

A common inquiry I receive is about my aversion to the lookup wizard and lookup fields in tables. The main reason is that using lookup fields in a table isn't considered proper normalization or good database design. For instance, if you have a customer table and want to store titles, suffixes, or prefixes, such lists should reside in separate tables, not as lookup fields within the customer table. This approach ensures better database design and facilitates easier manipulation and programming of data later.

Some people worry that using lookup functions in queries, such as Dlookup, could corrupt the database. While it's unlikely to cause corruption, it will significantly slow down performance. Each Dlookup call in a query must process a lookup for each record, which can be inefficient. Instead, I recommend using joins to link tables, which is much faster and more efficient.

Database corruption in Access is typically due to issues like improper shutdowns, large file sizes approaching the two-gigabyte limit, and network problems. Some bugs in recent Access versions can also cause corruption, which underscores the importance of regular backups. Using lookup functions is fine for small-scale lookups, like those on forms, but avoid putting them in queries to maintain speed.

Regarding the use of temporary variables (temp vars), it is best practice to clear them at the end of their use within a function. While temp vars persist for the entire database session and modern computers have ample memory, tidying up by removing or clearing temp vars can prevent unnecessary memory usage, especially in extensive databases.

Another handy tip from our forum moderator, Kevin, is about reverting to the older Access theme. If you prefer the look and feel of the 2013-2022 theme, you can change to it by navigating to File -> Options -> Client Settings, and selecting the checkbox to use that theme.

Eve asked about automatically importing multiple CSV or text files when they are added to a folder. You can set this up by having your database run a loop to check the folder or by adding a button that triggers the import process. This can be similar to how you would import images into a database.

For those who struggle with moving objects in Access forms without using a mouse, Matt Hall shared a useful tip. You can use arrow keys for moving objects, control-arrow keys for finer movement, shift-arrow keys to resize, and control-shift-arrow keys for precise resizing. These shortcuts offer more precision than dragging with a mouse.

Another common issue involves date formatting in forms. Even if you have set the date format in the table correctly, it might not reflect on the form because the form's settings override the table's settings. Ensure you check the format settings in both the table and the form.

Larry brought up a question about command button names not matching those in my videos. Every new control added to a form receives a sequential number, which is arbitrary. To avoid confusion, give meaningful names to controls as soon as they are created, and be cautious when renaming them to avoid breaking code.

Many users sometimes lose the little box or rulers in their form design view. To restore them, go to the Arrange tab, select Size & Space, and click the Ruler icon.

Adam contributed code to convert a form into a report, which can be useful if you need such functionality.

Finally, Donald asked about verifying