Append Only
By Richard Rost
3 years ago
How Append Only Property for Long Text Works
In this Microsoft Access tutorial I'm going to teach you what the Append Only property is for long text fields. I'm going to show you how to use it and then explain why you shouldn't. I'll show you a couple of better alternatives.
Aria from Danbury, Connecticut (a Platinum Member) writes: I set the Append Only property in the notes field of my customer table to yes, but it doesn't appear to be working. What exactly does that property do?
Prerequisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, What is append only, Append Only Data Fields, AppendOnly, ColumnHistory, column history, move column history to another table
Subscribe to Append Only
Get notifications when this page is updated
Intro In this video, I will show you how the append-only property works for long text fields in Microsoft Access. You will learn what the append-only setting actually does, how to enable and view edit history, and why I do not recommend using it. I will also discuss the problems and limitations of append-only fields and suggest better alternatives for tracking changes in your data.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I am going to teach you what the append-only property is for long text fields. I am going to show you how to use it, then I am going to explain why you should not. I will show you a couple of better alternatives.
Today's question comes from Arya in Danbury, Connecticut, one of my platinum members. Arya says, I set the append-only property in the notes field of my customer table to yes, but it does not appear to be working. What exactly does that property do?
The append-only property is something I do not use. Essentially, what it does is it tracks changes in a long text field. It saves the entire edit history. In fact, append-only is kind of a misleading name. They should have changed it to "keep history," "track changes," or something like that. Append-only is weird. You would think append-only would mean that you can only add stuff to the end of it, but that is not what it is.
First, let me show you what it is and how to use it. Normally, if you have a long text field here, you come in here and make a change to it. That original change is now gone. With append-only, that change will be hidden, but it is visible if you know how to find it.
So let's go set it so that we can find it. First, you have to go into the table, go to Design View, come down to your notes field or whatever it is. It only works for long text fields, by the way. Come down here, you will see append-only. Change that to yes.
Now save the table, close it, nothing will appear to have changed. Come in here. You got "nice guy." Let's change this to "bad guy." Now leave the record and come back to it. "Bad guy" is there. But how do you see the "nice guy"? How do you see the original one? You have to right-click on the field and come down to "Show Column History." Then you get this window. You can see there is the "bad guy" and the date and time that it was changed.
Hit OK. If you come in here again and change this to "really cool dude," and go over to Captain Kirk and enter "a total ladies man," that kind of stuff. If I go back to me, right-click, and show column history, there are the previous two changes. All of your changes will be put in here. If I go back to James Kirk, right-click, and show column history, there is the previous change.
Notice that other one is gone now, because that is one of the problems with turning on the append-only property after the fact - it does not always save what is in here. Let's go find somebody else.
So, Jean-Luc Picard is "best captain ever" because he is. If I change that now to "Locutus of Borg" and leave it, come back to it, and right-click show column history, see it did not keep the old one.
So not only do I not recommend it, it is buggy. It does not work unless you turn this on from the beginning of the table as long as this field is empty - there is nothing in it. It will not always save your changes properly. Even if you close the table and come back in here, right-click show column history, it still does not have the original stuff.
I do not like it. Also, this stuff does get saved in the table. If you are just doing a little tiny edits like this, small little bits and notes, you are not going to have any problems. But if you are talking about long text fields - and I have some clients that use it to store the full text of books, whole chapters of that - you will eventually run into problems where your fields are getting too large. Remember, even though these can hold lots and lots of information, the visual interface here can only hold much, much less.
Do not rely on this to keep a complete history. It is not properly relational. It is not the best way to build your database. My recommendation, and I tell you this in my Access Beginner 4 class, do not use it. The append-only property joins the list of things like multi-valued fields, attachment data types, switchboards, and some things like that, that are on my Evil Access page. It is right down here. That was not there. I just added it, but it should have been on here. Evil things, things that are frowned upon, check this out. I'll put a link to this page down below. These are some things you should never do.
Another problem with these is that if you decide later on, if your database is getting big and you want to upscale it to SQL Server, SQL Server does not support the change history. Your change history will just be gone. Yes, I believe SharePoint does keep the change history, but I am not exactly sure if it imports properly from Access. I have not tried it. I am not going to try it. I do not recommend using it. Do not use it. Just do not.
What is the proper way to track something like this? I would recommend using something like this: a second table, like in this case, the contact table where you can put information like this. You can keep the date and the other information that you want to put in there in a separate table and it is all stored properly. I show that in my Contacts video. If you actually want to track changes to a specific field, I teach you how to do that in my "Track Changes in Data" video, where you can track the actual changes that were made and save them.
If you have a field, like your notes field, and you want to track the changes, you can use a separate table and I show you how to do that in this video.
Is it possible to do what you think it is going to do here, where you can have it so that you can only add stuff onto it? I mean, yes, you probably could with some VBA, make it so you can only add stuff onto here and lock it so you cannot delete the stuff. I could probably figure that out, but why would you want to? If you want to be able to only add data, just do this: make a second table and just add data. I do not get why you would want to only be able to add stuff to a notes field.
If you can come up with a good reason why, then I will do it and show it to you in a video. I cannot think of a good reason why.
Now let's say you have got this database already built. You have been using it for a year. People have been working with this and you do not want to lose all that history. The only way you can really come in here - you can access this information programmatically, but it is quite detailed and it is quite in-depth and it requires a good amount of programming. I am going to cover this in my Access Developer 43 class. I will put a link to that down below as well.
But in a nutshell, what I would recommend doing for now is to just copy this stuff. You can select this, copy it, maybe paste it over in Notepad. Bring up your Notepad, copy this stuff here, select it all, copy over here, Notepad. Close this and then make another note field on here and then just paste that stuff in. Once you copy it to your clipboard, you do not really need to drop in an op-amp. If you want to save that data, put it in here because once you change this, watch. If you come back in here in Design View, if you change this field back to a normal field with no append-only, save it, it is going to say, hey, the history for those columns will be lost. Are you sure you want to continue? I am going to say no because I am going to keep this around. I am going to actually escape out of that and not save it. I am going to use it in the other class.
That is append-only. Do not use it. I do not recommend it.
A couple of options again: use either a proper contact table like this or use track changes like I show in the other video.
If you want to learn more, if you are stuck with a database that maybe you built and you did not know better, or you inherited it from someone else and they have been using append-only, and you want to get rid of it but you have tons and tons of data stuck in your column history, you want to extract that into something like your normal contact table, I will show how to do that in my Access Developer 43 class. I just finished recording it. When I say "I will," it means I have it covered in Access Developer 43 lesson two.
I was going to make this an extended cut, but then after I started doing it, it is about 40 minutes long and it is a lot of advanced stuff, including recordset loops, crazy string parsing because this is all one string in here. You have got to pull out the pieces. You have got to take out the date, you have got to take that out, you have got to look for the CHR 13 and the end. It is a lot of work. It is a bunch of coding. If you want to learn how to do it, Access Developer 43. Do not just skip to this unless you know recordsets, unless you know the string functions Left, Right, Mid, all those things. But if you are interested, I will put a link down below.
Developer students, we will be covering this. It is a great example of something to do with all these things here.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper my friends. I will see you next time.Quiz Q1. What does the append-only property do in Microsoft Access long text fields?
A. It tracks and saves the change history for the field
B. It restricts users to only adding new data at the end of the field
C. It automatically formats text as you type
D. It encrypts data in the field for security
Q2. Why is the name 'append-only' considered misleading for this property?
A. It implies you can only add data at the end, but it actually keeps the edit history
B. It suggests automatic formatting, but it does not
C. It refers to file compression instead of tracking changes
D. It relates to permissions, not data storage
Q3. What is required for append-only history tracking to work correctly in an Access long text field?
A. The property must be set to Yes when the field is empty and before data is entered
B. The property can be enabled at any time, regardless of data already in the field
C. The field must be a short text field
D. The table must be linked to SQL Server
Q4. How do you view the edit history for a field with append-only enabled?
A. Right-click the field and select 'Show Column History'
B. Open the table in datasheet view and press F12
C. Use 'Compact and Repair Database'
D. Open the field's property sheet
Q5. What is one significant drawback of using append-only for long text fields?
A. The edit history is not properly relational and can cause database bloat
B. It enhances the speed of large queries
C. It allows live collaboration with multiple users
D. The field becomes read-only
Q6. What happens to the column history if you migrate an Access database with append-only fields to SQL Server?
A. The change history is lost since SQL Server does not support it
B. History is converted and stored in an audit table automatically
C. Every change is saved as a separate record
D. All long text fields become numeric fields
Q7. What is a recommended alternative to using append-only fields for tracking notes or changes over time?
A. Create a second related table to store each note or change with its corresponding date and user information
B. Use multi-valued fields in the same table
C. Store all history in a single memo field and parse it manually
D. Rely on switchboards for note management
Q8. Why should you be cautious about the size of long text fields with append-only enabled?
A. All change history is stored in the same field, which can cause the database to grow excessively large
B. The fields automatically compress data, leading to loss of information
C. Append-only shrinks the allowed size of the field
D. It makes the field unsuitable for indexing
Q9. What did Richard state as additional 'evil' features you should avoid besides append-only fields?
A. Multi-valued fields and attachment data types
B. Single-valued fields and auto-increment numbers
C. Query joins and form controls
D. Table relationships and lookups
Q10. What must you do if you want to extract column history data from an append-only field programmatically?
A. Write complex VBA code involving string parsing and recordset loops
B. Use the Export Wizard to directly export the history
C. Simply copy and paste the field into Excel
D. Use the field's context menu to export data
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on the append-only property for long text fields in Microsoft Access. I'm going to explain what it is, show you how to use it, discuss why I generally advise against it, and provide better alternatives for tracking changes in your database.
The append-only property is designed to keep a change history for long text fields in your Access tables. Despite what the name suggests, it does not actually restrict you to only adding information to the end of the field. Instead, whenever you edit a long text field where append-only is enabled, Access saves previous versions of that field in a history, which you can access later. A more accurate name for this would have been "track changes" or "keep history," because "append-only" is misleading.
To turn on append-only, you need to open your table in Design View, select your long text field - for example, a notes field - and set the append-only property to Yes. This feature only works with long text fields. Once you save the table, you can begin tracking changes. When you modify the content of the field, Access will store the previous version in the field's history. You can see this edit history by right-clicking the field and selecting "Show Column History." This brings up a window that lists previous versions, along with the date and time of each change.
However, there are some limitations and bugs to be aware of. If you enable append-only after there is already data in your field, it often will not save the existing values to the history. This means any old information could be lost or not properly tracked. Additionally, even after making new changes, sometimes the history does not update as expected. This makes append-only unreliable for serious or mission-critical tracking.
Another issue is database size. While long text fields in Access can theoretically hold large amounts of information, when you track history this way, all versions are stored in the table. For large notes or records, especially when users input lots of data (like entire chapters of a book), this can cause your database to grow rapidly and potentially exceed practical limits.
From a database best-practices perspective, append-only is also problematic. Access's implementation is not relational, and it does not integrate well with other database systems. For instance, if you decide to migrate your data to SQL Server in the future, your column history will not be carried over because SQL Server does not support this property. SharePoint might support something similar, but importing history between platforms is inconsistent and, in my experience, not worth the risk.
Because of these concerns, I recommend avoiding this feature. It joins the list of Access "bad habits" such as multi-valued fields, attachment data types, and switchboards. These are things you want to avoid in serious database development. I cover these topics in more depth on the Evil Access page on my website, which I'll link to below.
The best way to track changes to information like notes is with a related table. Instead of using a single long text field with append-only enabled, create a related table that records each note as a separate entry, along with the date, time, and any other relevant data. This method is fully relational, compatible with SQL Server and other RDBMS systems, and much more reliable. I cover how to set up contact tables and track changes to fields in detail in my Contacts video and my "Track Changes in Data" video.
If your intention is to truly restrict users so they can only add to a note and never edit or delete previous content, you might be able to set this up with some custom VBA code, locking the field after each entry. However, I've rarely encountered a scenario where this is necessary or beneficial. If you ever do need it, it's easier and more flexible to just use a related table to store the information.
Suppose you already have a database with append-only enabled and significant history stored in your long text fields. If you want to extract that data into a more relational structure, this can be accomplished programmatically, but it requires advanced knowledge of recordsets, string parsing, and VBA. I teach these advanced techniques in Access Developer 43, where I show you how to read the column history, break it down, and transfer it to a more appropriate data structure. If you are interested in these advanced topics, there is a detailed section on this in lesson two of that class. Just make sure you are comfortable with recordsets and string functions like Left, Right, and Mid before diving into that material.
If you simply want to save the existing history before removing append-only, you can copy the column history from the Show Column History window and paste it into another document, such as Notepad or another field, just to preserve the data. Be aware that once you disable append-only for a field, Access will warn you that all existing history will be lost, so be sure to save anything you need before making that change.
In summary, the append-only property for long text fields is a feature best avoided for most database projects. Instead, use a properly structured table to record the history you need. If you need to extract data from an existing setup, more advanced programming techniques are available and covered in my developer classes.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Append-only property for long text fields explained
How to enable append-only in Access tables
Viewing edit history with Show Column History
Limitations and bugs with append-only property
Impact of append-only on storage and performance
Issues when migrating to SQL Server with append-only
Recommended alternatives to append-only for history
Using a related table to track changes to notes
Risks of relying on append-only for audit trails
Copying column history data manually to preserve it
Reverting append-only fields and data loss warning |
