INF: Snapshot and Underlying Table Updated Until Snapshot Full

Article ID: Q103402
Creation Date: 23-AUG-1993
Revision Date: 19-SEP-1996

The information in this article applies to:

Microsoft Access versions 2.0, 7.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

A snapshot, along with its underlying table, continues to update until the snapshot is completely filled with data. The link to the snapshot's underlying table is continued until you move to the end of the snapshot's recordset.

When you first create a snapshot recordset, memory is allocated to store data. In order to optimize memory, Microsoft Access does not load all the table's records at once, meaning that you have only a partial recordset in memory. When you move to the end of the snapshot, the link is then cut from the live table.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access for Windows 95, version 7.0) is called Access Basic in version 2.0.

MORE INFORMATION

The first function, Udate(), creates a snapshot of a table, adds a new record to the table, and then prints the snapshot to the Debug window (or Immediate window in version 2.0). The snapshot includes the new record in its output.

The second function, NoUdate(), also creates a snapshot of a table, moves to the end of the snapshot, adds a new record to the table, and then prints the snapshot. In this example, the snapshot does not include the new record since the MoveLast command is executed before the new record is added.

Function Udate()

Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

Copy the Categories table to a table called Cat.

Create a new module and add the following function:

      Public Function Udate()

NOTE: In Microsoft Access version 2.0 do not use the word "Public" in front of the function name.

         Dim MyDB As Database
         Dim MyTable As Recordset
         Dim MySnap As Recordset

         Set MyDB = CurrentDb()
         Set MyTable = MyDB.OpenRecordset("Cat", dbOpenTable)
         Set MySnap = MyTable.OpenRecordset(dbOpenSnapshot)

         MyTable.AddNew
            MyTable![CategoryName] = "TEST"
               '(or [Category Name] in version 2.0)
            MyTable![Description] = "Still being updated"
         MyTable.Update

         MySnap.MoveFirst
         Debug.Print "Category Name", , "Description"
         Do While Not MySnap.EOF
            Debug.Print MySnap![CategoryName], , MySnap![Description]
                          '(or [Category Name] in version 2.0)
            MySnap.MoveNext
         Loop
      End Function

In the Debug window (or Immdediate window in version 2.0), type the following line, and then press ENTER:
```
      ? Udate()
```
Note that the snapshot contains the new record.

Function NoUdate()

Open the Cat table in Datasheet view.

Delete the TEST record added above.

Add the following function to the module you created in step 2 of the previous procedure:

      Public Function NoUdate()

NOTE: In Microsoft Access version 2.0 do not use the word "Public" in front of the function name.

      Dim MyDB As Database
      Dim MyTable As Recordset
      Dim MySnap As Recordset

      Set MyDB = CurrentDb()
      Set MyTable = MyDB.OpenRecordset("Cat", dbOpenTable)
      Set MySnap = MyTable.OpenRecordset(dbOpenSnapshot)

      MySnap.MoveLast

      MyTable.AddNew
         MyTable![CategoryName] = "TEST"
            '(or [Category Name] in version 2.0)
         MyTable![Description] = "Still being updated"
      MyTable.Update

      MySnap.MoveFirst
      Debug.Print "Category Name", , "Description", Chr(13)
      Do While Not MySnap.EOF
         Debug.Print MySnap![CategoryName], , MySnap![Description]
                       '(or [Category Name] in version 2.0)
         MySnap.MoveNext
      Loop

End Function

In the Debug window (or Immdediate window in version 2.0), type the following:
```
      ? NoUdate()
```

Note that because the snapshot was filled (MoveLast was executed) before the new record was added, the snapshot does not contain the new record added to the Cat table.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.