Access Developer 43
Fixing Append Only Fields, Using TempVars
Welcome
Today's class is going to focus on two major topics. First we're going to learn about the Append Only property which is for long text fields and I'm going to teach you why you shouldn't use it. You may encounter databases as a developer where people have used it. It's quite popular. They're going to ask you how to get rid of it but still keep the column history that's in there. So I'm going to teach you how to do that. We're going to learn how to parse the individual records. We're going to do some string parsing and some Recordset work.
Then we're going to learn about TempVars. What is TempVars? Well, it's yet another tool in your box for storing values in the computer's memory. TempVars are, in my opinion, better than global variables for most cases in that they will actually survive an unhandled error, so that's good. Plus, they'll store data of any type. You can store numbers, dates, string values, anything you want in them. We're going to learn all about TempVars, what they are, pros and cons, and go through some sample use cases.
Resources
Topics Covered
In Lesson 1, we're going to review the Append Only property, why you should not use it, and what some better alternatives are.
In Lesson 2, we're going to continue with the Append Only property. Now that we know what the Append Only property is and why we shouldn't use it, we're going to deal with the situation where we get a database from someone else who has been using Append Only and we have to extract all of that data out of that field and put it in a normal relational table like our contact table where each one of those entries in the history should be its own record in the table. It's going to involve recordset loops, string parsing, and lots of cool stuff.
In Lesson 3, we're going to learn about TempVars, which is another way of storing data in the computer's memory. We're going to learn what TempVars are, why you want to use them, how to use them, and the pros and cons.
In Lesson 4, we're continuing with TempVars. We're going to learn how to set and get TempVars using VBA. We're going to see how errors in your VBA code, any unhandled errors, will clear a global variable, but it won't clear a TempVar. We'll learn how to read a TempVar from a text box using the .Value property. We'll learn how to use a TempVar in the control source of a field, in a property like default value, in a property like caption in source code, and as a query criteria using the bang (!) operator.
In Lesson 5, we're going to continue working with TempVars. We're going to learn different ways to add TempVars. There's TempVars.Add. There's the other methods we saw before. There's ways to remove TempVars, like when the user logs out you want to remove that value. We'll check to see if a TempVar is set. We will learn two different ways to loop through all of the TempVars that are in memory with a For loop and a For Each loop. We'll learn how to see what kind of variable data type it is. Is it a date? Is it a currency? We'll learn about IsDate and some other related functions.
Enroll Today
Enroll now so that you watch these lessons, learn with us, post questions, and more!
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
 Keywords
microsoft access, access 2016, access 2019, access 2021, access 365, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, Append Only, Application.ColumnHistory, Removing CHR 13, TempVars, .Value Property, TempVars in Queries, .Add, .Remove, .RemoveAll, List all TempVars, Loop thru TempVars, VarType Function, variable type
Intro In this lesson, you will learn about the Append-Only property in Microsoft Access, including why it is considered poor database design, how to extract existing column history when you inherit databases that use it, and techniques for parsing and moving this data into proper relational tables. You will also discover how to use temp bars to store and manage data in memory, explore their advantages over global variables, see how to handle their values and data types, and work with them in VBA code and forms. This class covers all of these topics in Microsoft Access Developer Level 43.Transcript Welcome to Microsoft Access Developer Level 43, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today's class is going to focus on two major topics. First, we are going to learn about the Append-Only property, which is for long text fields, and I am going to teach you why you should not use it and why it is bad database design to use it in the first place.
You may encounter databases as a developer where people have used it. It is quite popular; I have seen a lot of people use it. They may ask you if you can get rid of this feature but still keep the column history that is in there. I am going to teach you how to do that. We will learn how to parse the individual records, do some string parsing, and work with those records.
Then, we are going to learn about temp bars. What are temp bars? Temp bars are yet another tool in your box for storing values in the computer's memory. Temp bars are, in my opinion, better than global variables for most use cases in that they will not actually survive an unhandled error, which is good. Plus, they will store data of any type. You can store numbers, dates, string values, anything you want in them.
We are going to learn all about temp bars, what they are, their pros and cons, and go through some sample use cases. We will talk about issues with data typing them because you cannot always be sure what is in the temp bar. So, I will show you how to list all of the temp bars in memory, parse through them, determine if a temp bar exists, and if so, what value it has and what type of value it has. Is it a string? Is it a currency value? Is it a date, and so on?
Of course, this is a developer class. I strongly recommend you should have taken all of my Beginner, Expert, and Advanced classes as well as my Developer series, especially Developer 1 through 13. Those are the big ones. Developer 16 covers record sets, which is important for today's class since we are covering the Append-Only property, which will require record sets.
Take them all, and take them in order. Do not skip around. My classes are designed so that one follows the next. For Developer Level 43, I assume you have taken 42, 41, and so on.
Today's class was recorded with Access 365. It is currently June of 2023, so this will be roughly equivalent to Access 2021 if you are using the retail version. However, I strongly recommend a subscription, as you are guaranteed to have all of the latest and greatest features, and you will be able to follow along closely with what I have.
If you have questions about the material covered in today's class, just scroll down to the bottom of the page you are on and post your questions there. Also, make sure you take a minute to read through the other questions that might have been posted. Your question might have already been answered.
Be sure to click on that big red subscribe button if you want to get notified for any other questions or comments that are posted for today's class. While you are on the website, be sure to check out the Access Forum. You can join in conversations with me, the moderators, and all the other Access students.
Now let's take a closer look at exactly what is covered in Access Developer 43.
In Lesson One, we are going to review the Append-Only property, why you should not use it, and what some better alternatives are.
In Lesson Two, we will continue with the Append-Only property. Now that we know what the Append-Only property is and why we should not use it, we are going to deal with the situation where we get a database from someone else who has been using Append-Only, and we have to extract all of that data out of that field and put it in a normal relational table, like our Contact table, where each one of those entries in the history should be its own record in the table. That is going to involve record set loops, string parsing, and lots of other work.
In Lesson Three, we are going to learn about temp bars, which is another way of storing data in the computer's memory. We are going to learn what temp bars are, why you want to use them, how to use them, and their pros and cons.
In Lesson Four, we continue with temp bars. We are going to learn how to set and get a temp bar using VBA. We will see how errors in your VBA code, specifically any unhandled errors, will clear a global variable, but they will not clear a temp bar. We will learn how to read a temp bar from a text box using the .Value property. We will learn how to use a temp bar in the control source of a field, and in a property like Default Value, in a property like Caption, in source code, and as query criteria using the bang operator. There is a lot to cover in this lesson.
In Lesson Five, we continue working with temp bars. We are going to learn different ways to add temp bars beyond the methods we saw before. We will look at ways to remove temp bars, for example when the user logs out and you want to remove that value. We will check to see if a temp bar is set. We will learn two different ways to loop through all of the temp bars that are in memory, using both the For loop and a For Each loop. We will learn how to see what kind of variable data type it is - is it a date, is it a currency value, and so on. We will also learn about IsDate and some other related functions.
That is all coming up in Lesson Five.Quiz Q1. What is the main reason the instructor recommends against using the Append-Only property in long text fields?
A. It restricts the field to numbers only
B. It leads to bad database design and is difficult to manage
C. It slows down query performance
D. It is required for working with Access 2007 databases
Q2. If you inherit a database that uses the Append-Only property and need to keep the data, what is the recommended approach?
A. Deleting the field entirely
B. Parsing out the history and storing each entry as a separate record in a related table
C. Copying the whole field to a backup table
D. Replacing the field with a global variable
Q3. What are temp bars used for in Microsoft Access VBA?
A. To create backup copies of forms
B. To store values temporarily in the computer's memory
C. To enforce referential integrity rules
D. To sort large amounts of data
Q4. Why does the instructor prefer temp bars over global variables for most use cases?
A. Temp bars are more secure against hacking
B. Temp bars survive closing the Access application
C. Temp bars will not survive an unhandled error, so they clear automatically when needed
D. Global variables support more data types
Q5. Which of the following is NOT a feature of temp bars as described in the lesson?
A. They can store any type of data, such as numbers and dates
B. They survive unhandled VBA errors
C. You can list all the temp bars in memory
D. You can use them as query criteria
Q6. When converting data from an Append-Only field, what programming constructs are needed?
A. SQL aggregate functions
B. Record set loops and string parsing
C. Crosstab queries
D. Table macros
Q7. Which lesson covers the process of extracting Append-Only field data into a relational table?
A. Lesson One
B. Lesson Three
C. Lesson Two
D. Lesson Four
Q8. Which property is often cleared by an unhandled VBA error but temp bars are not?
A. Indexes
B. Field properties
C. Global variables
D. Table relationships
Q9. What are some of the ways you will learn to use temp bars in the class?
A. Only as default values in forms
B. As sources for field captions and as query criteria
C. Only for debugging and testing purposes
D. Only for setting tab order in forms
Q10. How should students approach the Microsoft Access Developer series according to the instructor?
A. Skip around to only relevant lessons
B. Only take the advanced lessons
C. Take all classes in order as each builds on the previous one
D. Start with Developer 43 and work backwards
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-B; 7-C; 8-C; 9-B; 10-C
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 video from Access Learning Zone covers Microsoft Access Developer Level 43. I am your instructor, Richard Rost. In this class, I focus on two central topics: the Append-Only property for long text fields and temp bars for memory storage.
First, I discuss the Append-Only property, which is a feature that often appears in databases you may encounter as a developer. Many people use it because it keeps a history of changes to long text fields. However, I strongly recommend against using it, as it leads to poor database design. I will explain exactly why it is not ideal, and instead, share better alternatives.
Sometimes, you may be asked to remove the Append-Only feature from a database while still preserving the existing column history. In this course, I will demonstrate how to accomplish that task. I will show you how to extract those historical entries, break them down into individual records, and handle the necessary string parsing to store that historical information in a proper relational format.
The second major topic is temp bars. Temp bars are another effective tool for storing values in your computer's memory. In many situations, I consider temp bars superior to global variables. Unlike global variables, temp bars do not survive after an unhandled error, which can be beneficial for maintaining your application's stability. Also, temp bars can hold any data type, including numbers, dates, and string values.
We will thoroughly explore temp bars, looking at their strengths, their weaknesses, and how to use them. I will share various cases where temp bars can be particularly useful, and we will discuss some of the challenges around data typing with temp bars, since their contents are not always obvious. You will learn how to list all temp bars currently in memory, how to check if a temp bar exists, and how to determine the value and type of each temp bar, whether it be string, currency, date, or another data type.
This is a developer-level class, so I expect that you have already completed all my Beginner, Expert, and Advanced Access lessons, as well as the Developer series through Developer Level 13. Developer Level 16 is particularly important as it covers record sets, which are essential for handling the Append-Only property discussed in this class. It is best to follow the series in order to ensure you build a proper foundation for each new concept.
This class was recorded using Microsoft Access 365 in June 2023. If you are running Access 2021, the version is very similar, but I always advise using the subscription to guarantee that you have all the most up-to-date features and a better match for my tutorials.
If you have questions about today's material, simply scroll down to the bottom of this page and post them there. I also encourage you to look over the existing questions and answers, as you might find your question has already been addressed. Remember to subscribe if you want notifications when new answers or comments are posted related to this class. While you are on the website, check out the Access Forum to participate in discussions with me, my moderators, and other Access students.
Here is a breakdown of the lessons in Developer Level 43.
Lesson One covers the Append-Only property. I explain why you should not use it and outline better approaches to tracking data changes.
Lesson Two continues with the Append-Only property. You will learn what to do when you inherit a database using Append-Only fields. Specifically, I show you how to extract each historical entry and store those as individual records in a separate relational table, like a Contact table. This involves working with record set loops, string parsing, and various other techniques.
In Lesson Three, we move on to temp bars. I introduce what they are and cover why and how you would use them, along with an analysis of their advantages and disadvantages.
Lesson Four builds on this by showing you how to set and retrieve temp bars using VBA, and highlights the differences between temp bars and global variables, especially when errors occur. I demonstrate how to work with temp bars in text boxes, set their values, and use them in properties like Default Value, Caption, as well as in your source code and query criteria using the bang operator.
Lesson Five takes your understanding of temp bars further. Here, I discuss additional methods for adding and removing temp bars, for instance, when a user logs out and you need to clean up those values. You will see how to check if a temp bar is set, loop through all the temp bars in memory using both standard For loops and the For Each construct, and identify the data type of each temp bar using functions such as IsDate.
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
Why not to use the Append-Only property
Alternatives to the Append-Only property
Extracting column history from Append-Only fields
Parsing and processing Append-Only data
Moving history records to a relational table
Recordset loops for data extraction
String parsing for history extraction
Introduction to temp bars in Access
Advantages of temp bars over global variables
Storing different data types in temp bars
Setting and retrieving temp bar values with VBA
Effect of unhandled errors on temp bars and globals
Reading a temp bar from a text box Value property
Using temp bars in control sources, Default Value, Caption
Using temp bars in source code and query criteria
Removing temp bars when users log out
Checking if a temp bar exists
Looping through all temp bars in memory
Detecting temp bar data types
Using IsDate and related functions with temp bars |
