When I was a kid, there was a game called Mouse Trap. It consisted of a complicated Rube Goldberg-like device where you had to put all the pieces of this very intricate 3-D puzzle together. Then, you would pull a trigger that started a ball rolling down a chute that caused a bell to ring, which was followed by a series of events that eventually cascaded their way to catching a mouse in a trap: your ultimate goal.
It was a fun game. But if one thing went wrong, it didn’t work correctly and the mouse stayed free. In a like way, with a database, sometimes a data mistake is inconsequential, but other times it can lead to disastrous results, such as destroying your data. One way to prevent events like this from happening is to protect your data. You can always do this in your forms by adding restrictions on what users can enter, change, or delete, but sometimes you want to show data in a query or a data sheet. One way you can show the data while protecting it is the subject of this month’s
Wizard — The Snapshot Query.
The Query Recordset Type Property
When you open a query in Microsoft Access, you or another user is able usually to make changes directly within the query. In most instances, this is a good thing as it allows convenient updates. However, sometimes you might want to provide a tool to that allows you to see the data, but not change it. You do this in a query with a Snapshot Recordset.
To do this, open a query in design view, right-click on the gray space in the top half of the query window, and you will get the window below. This popup shows the property sheets of the query. One of the properties is the record set type.
The record set type property has three possibilities:
- Dynaset. This type of Recordset (the default) provides a direct connection to the data and is able to be updated in some instances when viewed in the query window or on a form.
- Dynaset (inconsistent updates). I recommend you avoid this type of recordset. In some cases, it allows updates to the data in a one-to-many relationship that may cascade in a way that you do not intend. Since data integrity is such a critical part of any database, you want to allow changes to the data in a deliberate way; the dynaset makes it easy to cascade your way to disaster.
- Snapshot. This is the recordset type that we are interested in here.
The Snapshot Recordset
When you build a database, one of your primary goals should be to make sure that you only let in good data and keep out the bad data. In most instances, you do this by putting controls in your forms where the user makes additions, deletions, and updates to the data. If you want to protect your data, you can have great control over what types of data are acceptable. However, sometimes you want to show the data in a query.
The Snapshot query will present your into your data in a way that explicitly prevents updates. This is especially helpful when you are showing data that you want to be certain will not be updated.
One the frequent ways are I use this technique is for applications where the users are allowed only to see or report on the data rather than create or make changes.
Conclusion
The snapshot query is a very easy technique to use while protecting your data from unexpected changes. High benefits and low cost — what could be better than that! Use this easy technique any time you want to have data easily available to your user without the possibility of changes.
