Append Only Fields

As mentioned earlier, you can create an append-only field by setting the AppendOnly property of a Memo field. In DAO, you can set the AppendOnly property of the Field2 object to True. When this property is enabled, the memo field keeps its previous values as the data in the field is changed. This happens regardless of whether you change the value in the Access interface or in DAO. In the Access interface only the current value is displayed.

This can be useful in many scenarios such as:

• Call centers tracking correspondence with a customer
• Keeping a maintenance history for an asset
• Content tracking for a small content management system

While this feature is very powerful, there isn't a way to retrieve the history data for the field using DAO. Fortunately, the Access Application object has a method named ColumnHistory to retrieve this data. This, however, requires that Access is installed to retrieve this information. External applications will not be able to retrieve this data.

Some distinct limitations to the ColumnHistory method exist. First, the combined history is returned as a single string value. That means you have to parse the value to get something meaningful. You'll see an example of parsing this value shortly. Second, all rich formatting is removed. And finally, the date/time value in the string is localized, making it more difficult to write generic parsing code.

For tracking purposes, the column history also includes the date and time that the change was made. This data is stored in the order in which the changes were made and appears in the following format:

You can also view the column history for a memo field using the Access interface.

Let's say that in the issue tracking example that you would like to see the data sorted in descending order. The following code uses the ColumnHistory method in Access to retrieve the values that were in the column and add them to a list box named lstHistory:

Private Sub ShowColumnHistory(strTableName As String, strFieldName As String)
    'History data is in this format:
    '[Version: Date Time ] History Data
    Const VERSION_PREFIX As String = "[Version: "

    Dim strHistory As String
    Dim strHistoryItem As String
    Dim astrHistory() As String
    Dim lngCounter As Long
    Dim datDate As Date
    Dim datTime As Date
    Dim strData As String

    'Get the column history
    strHistory = Application.ColumnHistory(strTableName, strFieldName, "")

    'Make sure there is history data
    If Len(strHistory) > 0 Then
	'Parse the column history into separate items.
	'Each item in the history is separated by a vbCrLf, but
	'if there are carriage-returns in the memo field data
	'you will get unexpected results. Split on the VERSION string
	'in the history data.
	astrHistory = Split(strHistory, VERSION_PREFIX)

	'Adding these lines ensures this code works regardless of
	'how the control is configured on the form
	Me.lstHistory.RowSourceType = "Value List"
	Me.lstHistory.ColumnCount = 3
	Me.lstHistory.ColumnHeads = True

	'Add column headings to the list box
	Me.lstHistory.AddItem "Date;Time;History"

	'Enumerate the history data in reverse
	'to fill the list box in descending order
	For lngCounter = UBound(astrHistory) To LBound(astrHistory) Step -1
	    'Parse the history data
	    strHistoryItem = astrHistory(lngCounter)

	    If Len(strHistoryItem) > 0 Then

		'Parse the date from the history data.
		'This example parse the default US date format.
		datDate = CDate(Left(strHistoryItem, InStr(strHistoryItem, " ") - 1))
		strHistoryItem = Mid(strHistoryItem, InStr(strHistoryItem, " ") + 1)

		'Parse the time from the history data
		datTime = CDate(Left(strHistoryItem, InStr(strHistoryItem, " ] ") - 1))
		strHistoryItem = Mid(strHistoryItem, InStr(strHistoryItem, " ] ") + 3)

		'Add the history item to the list box.
		Me.lstHistory.AddItem datDate & ";" & datTime & ";" & strHistoryItem
	    End If
	Next
    Else
	MsgBox "There is no history information for the specified field"
    End If
End Sub