Mendip Data Systems
Database applications for businesses and schools
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
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
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
Click any image to view a larger version
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 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 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)
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
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-]
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
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