Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Use Column History to store historical data in memo fields

Return to Home Page

Starting with Access 2007, ACCDB files include an AppendOnly property for Memo/Long Text 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:

 

 

Colin Riddington      Mendip Data Systems       Updated 30/01/2019

Return to Top
* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Return to Access Articles

Click to download:

    The example database:                   Column History Example                            (ACCDB file - zipped)

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

 

 

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

 

See also: http://www.fmsinc.com/MicrosoftAccess/2007/ColumnHistory/Index.asp

Difficulty level :   Moderate

 

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

1.   Using Column History

Create a table with a memo (long text) field.

Set its ‘Append Only’ property to Yes to store the history of all changes to this memo field

ColHist1-TableDesign

Click any image to view a larger version

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

ColHist2-table

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

For example, by typing this in the VBE Immediate window:

 

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

ColHist3-immediate ColHist4-Proc

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 control with control source

=ColumnHistory([RecordSource],"MemoField","[ID]=" & Nz([ID],0))

 

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

ColHist5-FormDesign ColHist6-Form1 ColHist7-Form2

The default view is:

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

2.   How the ColumnHistory property works

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

 

NOTE: Tick Show System Objects in Navigation Options to view this table

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

ColHist8-MSysComplexColumns ColHist9-MSysObjQuery ColHist10-Error1

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

NOTE:

There is some 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 even more difficult for us to view the contents of these tables

 

If we create this query, a reserved error occurs

 

  SELECT * FROM MSysComplexTypeVH_0AEB418F6C7B493CBAC9A09A81355820

Trying to view the other table gives a different error.

Square [] brackets are needed due to the table name ending in ‘-‘

 

  SELECT * FROM [f_185CEC69CB3440E0BF69D8330221AE41_VersionHistory_F5F8918F-0A3F-]

ColHist11-Error2 ColHist12-VHTable1 ColHist13-VHTable2 ColHist14-VHTable3 ColHist15-Form3

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 in the process of being edited:

Any changes made to the column history are, of course, then displayed 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 information for these deep hidden system tables is documented anywhere.

 

As far as I am aware, very few people know how to view the contents of the deep hidden system tables.

In fact, I found out how to do so mainly by trial and error.

3.   Editing ColumnHistory

The ColumnHistory property is only really suitable 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 shows #Error as the system table no longer exists - delete the control and button as these are no longer needed

 

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.

    This 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 advice would be to AVOID using this method to retain historical data if editing is ever anticipated.

 

Instead create a separate table with the 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

ColHist16_Edit1 ColHist17_Edit2 ColHist18_Edit3 ColHist19_Remove ColHist20_Replace ColHist21_Removed

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 £60 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 email info@mendipdatasystems.co.uk with details of your file(s)

4.   Upsizing to SQL Server