This month, Doug Steele shows one way to add shortcuts to your application, demonstrates how to handle embedded quotes in SQL statements, and provides a quick documenter to list the objects in your application.
In a perfect world, the database design for your application would never change. In the real world, most applications grow and acquire new tables, new relationships, new fields, and new features. Garry Robinson and Scott McManus show you how they keep the databases for their clients up-to-date and in sync with each other.
In the early stages of database modelling and development, the users and the developers can exchange data-only (back-end) databases relatively easily. This tends to happen quite a bit early on while the developer is modifying the structure, relationships, descriptions, data types, and validation rules of the database. However, as time goes on and the number of users and the size of the back-end database increase, it may not be practical to send the data-only database over the Internet as modifications to the data structure are made. Instead, what you need is some way to modify the database design without losing the data that your users have in the tables. Continue reading
In this article, John Williams shows how to use the Tag property of a calculated control to help validate its contents.
I’ve always been a fan of the Tag property of Access forms and controls. The Tag property can be a useful place to store information, and I often use Tag entries to create “families” of controls that are to be treated in the same way (for example, to create a group of tags that are to be visible only to certain users). Taking things a step further, Tag entries can be used to define what are in effect custom properties, a technique that’s a valuable addition to the developer’s toolbox. In this article, I’m going to show you how I use the Tag property to create a generalized, reusable validation routine by turning the Tag property into a Validation property. Continue reading
Often, in order to get the best performance from your application (or determine what’s going wrong with it), you have to know what your application is really doing. That’s the role of logging: providing a trail of what was going on with your application. Garry Robinson shows you how to keep track of some key information about your Access database.
Key questions in managing a database include “How many users do I have?” and “How many simultaneous users are there at peak times?” Would you, for instance, like to know who is using your database the most? Would you like to know who is logging out of your database correctly and who is just using turning off their computer? Would you like to have discussions about database performance armed with real facts about how many users you have and who uses the database at peak times of the day, rather than just hearsay and opinion? I use the logging tool described in this article to keep my clients’ databases running smoothly, as the tool lets me gather a lot of the facts about the users’ environment. If you’re not already creating your own logs, you need this tool. Continue reading
DAO provides an object model that supplies almost every piece of information about a query you might want to know. One piece of information it doesn’t provide, however, is whether or not a query is compiled. Michael and Julianne discuss how you can determine this interesting and sometimes useful property of a query.
The first time you run a query in Microsoft Access, the Jet Engine must “compile” the query into a binary form. This compilation, which can take anywhere from half a second to 6-8 seconds for very complex queries, represents the plan Jet will use to run your query. Any time you save a query, the “saved” version of the query will be decompiled, since you might have made design changes that would change the plan that Jet would use to execute the query. Also, whenever you compact a database, the statistics for the database are updated, and you might find that the original plan for a query is no longer appropriate (for example, the plan that Jet will determine is best for a table with 10 rows might be very different from the plan that’s best for 100,000 rows). Therefore, when you compact the database, queries will be decompiled as the statistics are updated. Continue reading