ACC2000: Memo and OLE Fields in a Snapshot Are Not Static

View products that this article applies to.

This article was previously published under Q210444

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

Symptoms

Data in Memo or OLE fields in a snapshot changes if the data in the underlying table for the snapshot changes.

↑ Back to the top

Cause

A snapshot is a copy of the record source data at the time when the snapshot was created. Unlike the data in a dynaset, snapshot data is not linked to data in the underlying tables. The snapshot data generally does not change as its underlying tables change.

However, Microsoft Access stores the contents of Memo and OLE fields differently than other data types. Memo and OLE field data is not stored in the data page that holds the actual record. Instead, the data page holding the record has an address pointer that specifies the data page (or pages) that contains the data.

When you create a snapshot, a static copy of the data page holding the record is copied. This copy contains only the address of the Memo or OLE information. To avoid performance degradation, the actual Memo or OLE information is not loaded into the snapshot. If the actual Memo or OLE information is changed, the snapshot looks to the same data page and finds the changed information.

↑ Back to the top

More information

Steps to Reproduce Behavior

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. You can use the following sample function to demonstrate that a Memo field in a snapshot can be changed:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.

Create the following new function in a module:

Public Function TestSnapshot()
   Dim SS As DAO.Recordset
   Dim DS As DAO.Recordset
   Dim DB As DAO.Database

   Set DB = CurrentDb()

   ' Create a snapshot and recordset of the Categories table.
   Set SS = DB.OpenRecordset("Categories", dbOpenSnapshot)
   Set DS = DB.OpenRecordset("Categories", dbOpenDynaset)

   ' Move to the first record.
   SS.MoveFirst
   DS.MoveFirst

   ' Print the contents of the CategoryName and Description
   ' .. fields from both record sets. CategoryName is a
   ' .. Text field and Description is a Memo field.
   Debug.Print
   Debug.Print "Snapshot:"
   Debug.Print "   CategoryName: " & SS![CategoryName]

   
   Debug.Print "   Description: " & SS![Description]
   Debug.Print "Dynaset:"
   Debug.Print "   CategoryName: " & DS![CategoryName]
   Debug.Print "   Description: " & DS![Description]

   ' Alter the Description and CategoryName fields using
   ' ... the dynaset.
   DS.Edit
      DS![Description] = "My Description"
      DS![CategoryName] = "My Category"
   DS.Update

   ' Print the new contents of the recordsets.
   Debug.Print
   Debug.Print "Snapshot:"
   Debug.Print "   CategoryName: " & SS![CategoryName]
   Debug.Print "   Description: " & SS![Description]
   Debug.Print "Dynaset:"
   Debug.Print "   CategoryName: " & DS![CategoryName]
   Debug.Print "   Description: " & DS![Description]
End Function

Type the following line in the Immediate window, and then press ENTER:

? TestSnapShot()

Note that the following text is displayed in the Immediate window:

   Snapshot:
      Category Name: Beverages
      Description: Soft drinks, coffees, teas, beer, and ale
   Dynaset:
      Category Name: Beverages
      Description: Soft drinks, coffees, teas, beer, and ale

   Snapshot:
      Category Name: Beverages
      Description: My Description
   Dynaset:
      Category Name: My Category
      Description: My Description

Note that the second Description reference for the snapshot reads "My Description," which is the same as the change made by the dynaset, because Description is a Memo field. Note also that the second Category Name reference for the snapshot is unchanged because Text fields are static in a snapshot.

↑ Back to the top

References

For more information about snapshots, click Microsoft Access Help on the Help menu, type snapshots in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top