The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

How to Prevent Changes in Queries – The Snapshot Recordset

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:
  1. 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.
  2. 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.
  3. 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.


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.

Shameless Self-Promotion Department

This past September I was made President of my Professional Association – The Society of Professional Consultants (SPC).

I first became aware of the SPC when I attended some of their workshops in 2004. I formally joined the organization in 2011 and became a member of the Board of Directors shortly after that.

The mission of the SPC is to help consultants grow and develop their businesses and effectively serve their clients. I know from direct experience that they succeed in this effort.

If you are a current consultant or are thinking about becoming a consultant, know that the SPC has a special benefit of for new members where they are able to engage in a mentoring relationship with an experienced consultant.

You can learn more about the SPC at their website, If you decide to attend one of our meetings, please come by and say hello.


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886