エピソード

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

    Microsoft Access Quick Queries #20: Avoiding Lookup Fields, Dlookup, and Database Maintenance Tips
    2024/10/21
    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 required fields before committing forms to the database. While setting fields as required ensures they must be filled, it's often better to allow blank fields rather than risk invalid data entry. For complex validation involving multiple fields, consider table-level validation rules. Kenneth wondered why not all note fields use rich text. While rich text can be useful for formatting, it can complicate data export processes. Use it only when necessary. James suggested using filter properties to make A-Z jump buttons more efficient by filtering rather than jumping to specific records. This method could be particularly beneficial when dealing with large datasets. That's it for today's Quick Queries. For more detailed tutorials and step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends. For a complete video tutorial on this topic, please visit https://599cd.com/QQ20?key=Spotify
    続きを読む 一部表示
    27 分
  • What is Microsoft Access?

    What is Microsoft Access?
    2024/10/09

    What is Microsoft Access & What Do You Use It For?

    You've probably heard of Microsoft Word, Excel, and PowerPoint, but you might not know what Microsoft Access is. Access is a database application. It's designed for storing large amounts of data, and for making it easy to automate the things you do on a daily basis. You can track anything you want in Microsoft Access: Customers, Contacts, Orders, Products, Mailings, Finances, Collection, Scheduling... literally anything at all.

    A lot of people switch to Access when they outgrow Excel. Excel isn't very good at storing large amounts of data or automating repetitive tasks. Access makes it super easy to relate different types of data together. For example, if you're tracking customers and the cars they own, you can relate them together with a CustomerID. This way you can pull up the customer's record and instantly see the cars they drive. Same with contacts, orders, and anything else related to that customer.

    You can build a nice user-friendly interface in Microsoft Access very easily. This is great if you have other people using your database who may not be computer experts. You can do all of your reporting right in Access. No need for Microsoft Word. You can print out financial reports, order summaries, mailing labels. You can even write printed letters and send mass emails right from Access.

    Now you might not have Access on your computer even if you have Microsoft Office. That's because it's not part of the Personal version of Office. You need Office Professional or the Microsoft 365 for Business. But it's not that expensive. The whole Office Suite less than $13 per month, which is a bargain.

    The list of things you can do with Microsoft Access is too long to cover here. I've literally just scratched the surface. If you'd like to learn more about what Access can do and how it can help your business (even if it's just your side hustle) come to my web site and take my FREE 4-hour course that covers all of the basics.
    続きを読む 一部表示
    4 分