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