Microsoft KB Archive/103402

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.

©1997 Microsoft Corporation. All rights reserved. Legal Notices.

Additional reference words: 2.00 7.00 tables snap shot
KBCategory: kbprg
KBSubcategory: PgmObj