Append Only (aka Column History)

In Access 2007, memo fields have now the capability of collecting change history. When you change an append-only field's data, the change and time stamp are recorded and appeneded to the version history of the field.

To enable this feature, first you need a memo field (this only works for memo fields) and change the "Append Only" option in the column's table design property to "Yes" (below). From that point on, history will be collected on the column, for each field.

To access each field's history, just right click on the field and select "Show Column History...", whereupon a dialog will show up with the version history information.

Additionally, we are also exposing OM for your programming pleasure. Off of the Application object there is a new ColumnHistory method. Given it the table name, column name and a where clause (so you can specify the fields you want to see the history), you will get the version history back. For example:

Dim t As String
t = Application.ColumnHistory("Table1", "Memo", "[ID]=2")

Where "t" will contain something like:

[Version: 7/31/2006 11:22:03 AM ] ab
[Version: 7/31/2006 11:22:41 AM ] c

There is also a DoCmd if you simply want to show the version history dialog (basically automating the right click on the field described above): DoCmd acCmdShowColumnHistory.

Finally, this feature is directly related to SharePoint's append-only fields, not only in appearance and design, but also in actual use: using append-only functionality in Access for SharePoint lists will get you SharePoint version history as if you were using SharePoint (well, you actually sort of are).

Of course, having SharePoint is not necessary to use this feature. However, this is an ACCDB (Access 2007 database format) only feature - it will not be available for 2000/2003 MDBs or ADPs.