Microsoft KB Archive/210444

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

Article ID: 210444

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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).



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



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.



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.

 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.

