Originally posted : 30 Jan 2019                             Last Updated : 6 Apr 2022                                       Difficulty level: Moderate

Section Links (this page):     Using Column History     How it works     Editing Column History     Upsizing to SQL Server     Downloads     Further Reading


Starting with Access 2007, ACCDB files include an AppendOnly property for Long Text/Memo fields.
This allows you to store a history of the changes made to the field.

The history of the Memo/Long Text field can later be retrieved using the ColumnHistory method, as explained below:


Using Column History                                                                                                         Return To Top

Create a table with a long text (memo) field.
Set its 'Append Only' property to Yes to store the history of all changes to this memo field


Enter some data in the field then edit one or more of the records


You can view a history of an individual record in various ways

For example, by right clicking on the record and selecting Show Column History from the context menu:


. A popup window appears showing the column history for that record:


NOTE: This method of viewing the column history works in a table, query or form. However, the column history data is READ ONLY

It can also be viewed by typing this in the VBE Immediate window:

?Application.ColumnHistory("Table1", "MemoField", "ID=1")


Or you can create a procedure to do this:


However, to make the feature have any real value, you can include the column history on a form.

In this case I have added an extra form control and set its control source:
=ColumnHistory([RecordSource],"MemoField","[ID]=" & Nz([ID],0))

The control is hidden and disabled (as it cannot be edited by end users)



The default view is:


After clicking the button, the column history is shown . . . but it CANNOT be edited by end users




How the ColumnHistory property works                                                             Return To Top

IMPORTANT:
The information in this section related to various system tables which are used by Access to make databases function correctly

Some system tables can be viewed & a few can be edited
But that doesn't mean you should do so ....UNLESS YOU ARE ABSOLUTELY SURE WHAT YOU ARE DOING
Altering one table may have 'knock on' effects on other tables

Incorrectly editing system tables may corrupt your database or prevent you opening it



When the column history property was specified (by setting ‘Append Only’ to Yes), a new record was added to the system table MSysComplexColumns.

NOTE: Set Show System Objects = Yes in Navigation Options to view this table


This record indicates new system tables have been created.

Two new system tables are created for each table with a memo field where the 'Append Only' property = Yes

These tables are 'deep hidden' and do not appear in the navigation pane list
However, the new system table names can be identified by creating a query on the MSysObjects system table

SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "*VH*" Or (MSysObjects.Name) Like "*Version*") AND ((MSysObjects.Flags)<>1)
AND ((MSysObjects.Type)=1));



NOTE:
There is some unfortunate confusion in nomenclature for this feature.
To enable it, you set Append Only = Yes.
However, the property is called ColumnHistory and the system tables refer to VersionHistory

As previously stated, system tables are required to ensure Access works correctly.
All are hidden. Most of them cannot be edited for security reasons.

However, by using deep hidden tables, Access makes it difficult for us to view the contents of these tables

If we create this query on the first table listed above, a reserved error (-3087) occurs

SELECT * FROM MSysComplexTypeVH_0AEB418F6C7B493CBAC9A09A81355820;


Trying to view the other table gives a different error.
NOTE: Square [] brackets are needed due to the table name ending in ‘-‘

SELECT * FROM [f_185CEC69CB3440E0BF69D8330221AE41_VersionHistory_F5F8918F-0A3F-];


To view the contents of these tables, we need to use a bit of trickery.
I am deliberately not going to explain how I achieve this in this article

Surprisingly the first of these tables is empty:


The version history data is stored in the second table:


Although Access has made it very difficult to view this table, once it is visible, it can in fact be edited.
For example, I have edited 2 records and added a new record.
The screenshot shows the table being edited:


Any changes made to the column history are then shown in the form we created earlier



It should be emphasised that editing the column history directly in the system table does NOT update the original table.

Even so, in this case, the ability to edit the table does make sense, as it means someone who knows how to do so can delete inappropriate entries from the column history.

However, none of the above is documented anywhere.

As far as I am aware, very few people know how to view the contents of the deep hidden system tables.
I found out how to do so mainly by trial and error.


Editing Column History                                                                                                     Return To Top

The ColumnHistory property is only intended for situations where historical data should be retained without changes.
If it is ABSOLUTELY necessary to edit the column history, there are 3 possible approaches/workrounds:

a) Remove all Column History for an individual record
      Copy the record then delete the original record. The ColumnHistory is NOT transferred



b)   Remove/replace Column History for all records
      Set the Append Only property to No. You will be warned that the ColumnHistory will be deleted.
      If no other field depends on the associated system table, it will be deleted automatically


      If you wish, you can then reset the Append Only property to Yes. The system table will be re-created automatically with no data


      Alternatively, leave Append Only = No to switch the feature off permanently for that field
      The form control now shows #Error as the system table no longer exists


c)   Delete an individual item in the column history for one or more records
      This is the most flexible and powerful solution and was described in section 2 above.

      However, to do this requires knowledge of how to view the deep hidden system table used to store the ColumnHistory.
      How this is done is not documented by Microsoft and has deliberately not been explained in this article



      As has been demonstrated, each of these methods of editing the ColumnHistory are far from ideal.
      My strong advice would be to avoid using this method to retain historical data if editing is ever anticipated.

      Instead create a separate table with the long text (memo) field and link it to the main table using a one to many join.

      If required, editing of previous records by end users can easily be prevented by locking the memo field control at form level
      However, the field could still be edited by system admins in the table itself when necessary


Upsizing to SQL Server                                                                                                       Return To Top

If, at any stage, you decide to upsize your datafile to SQL Server, you need to be aware that the column history data cannot be migrated.
If you no longer need the historical data, you should just switch off the feature before upsizing.

However, if you wish to retain that historical data, we can recover it for you as a standard Access table.
Please note that this is a chargeable service charged at £75 GBP per hour.
If you only have one column history memo field to convert, it is unlikely to be more than 1 hour’s work.

Similar conditions apply as for the database conversion feature but, in this case, ACCDB/ACCDE/MDB/MDE file types are all acceptable.

For further details of this recovery service, please send an email with details of your file(s)


Downloads                                                                                                                                     Return To Top

Click to download:
    Example database:                     Column History Example                             (ACCDB file - zipped)

    PDF version of this article:           Using Column History in memo fields         (PDF file)



Further reading                                                                                                                         Return To Top

For further information on this topic, see:
        http://www.fmsinc.com/MicrosoftAccess/2007/ColumnHistory/Index.asp

        https://sourcedaddy.com/ms-access/append-only-fields.html



I would be grateful for any feedback on this article including details of any errors or omissions

Colin Riddington                 Mendip Data Systems                 Last Updated 6 Apr 2022