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:

[Version: Date Time ] History Data

You can also view the column history for a memo field using the Access interface (see Figure 6-9).

Columnhistory Access 2007
Figure 6-9

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 & ";"

& strHistoryltem

End If

Next

Else

MsgBox "There is no history information

for

the specified

field"

End If

End Sub

The form with the list box is shown in Figure 6-10.

The form with the list box is shown in Figure 6-10.

Figure 6-10

Was this article helpful?

0 0

Responses

  • yvonne
    How to see history of memo field access 2010?
    8 years ago
  • atso
    Where are columnhistory data stored?
    8 years ago

Post a comment