Welcome

Welcome! Parse Field History with Temp Vars

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Developer Level 43. In this course we will cover two main topics: the Append-Only property for long text fields, including why it is not recommended and how to extract and parse data from existing append-only fields; and temp vars, exploring what they are, their benefits over global variables, and how to use them for storing various types of data in memory. We will walk through setting, getting, and looping through temp vars using VBA, and discuss data type issues, use cases, and alternative solutions to the Append-Only property.

Navigation

Prev: D43
Next: Lesson 01

Keywords

Access Developer, append-only property, temp vars, tempvar VBA, parse column history, extract append-only data, string parsing, recordset loops, value data types, IsDate function, global variables vs temp vars, control source tempvar, remove tempvar, loop

Subscribe to Welcome
Get notifications when this page is updated

More Information

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 are going to ask you, "Hey, can we get rid of this, but still keep the column history that is in there?" I am going to teach you how to do that. We are going to learn how to parse the individual records. We are going to do some string parsing and some record set work and some good stuff.

Then we are going to learn about temp vars. What are temp vars? Temp vars are yet another tool in your box for storing values in the computer's memory. Temp vars are, in my opinion, better than global variables for most use cases in that they will not actually survive an unhandled error. That is good. Plus, they will store data of any type. You can store numbers, you can store dates, you can store string values, anything you want in them. We are going to learn all about temp vars: what they are, pros and cons. We will 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 var.

I will show you how to list all of the temp vars in memory, parse through them, determine if a temp var exists, if so, what value does it have, what type of value does it have: 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, and my developer series, especially Developer 1 through 13. Those are the big ones. Level 16 covers record sets. That is a big one for today's class, since we are covering the append-only property, which will require record sets. And yes, just take them all. Take them in order. Do not skip around. My classes are designed so that one follows the next. So for level 43, I assume you have taken 42, 41, and all that.

This 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 do strongly recommend a subscription, as you are guaranteed to have all of the latest and greatest newest features, and you will be able to follow along closely with what I have got.

If you have questions about the material covered in today's class, just scroll down to the bottom of the page that 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, so 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 are going to 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. 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 is going to involve record set loops, string parsing, and lots of cool stuff.

In lesson three, we are going to learn about temp vars, which is another way of storing data in the computer's memory. We are going to learn what temp vars are, why you want to use them, how to use them, and their pros and cons.

In lesson four, we are continuing on with temp vars. We are going to learn how to set and get a temp var using VBA. We are going to see how errors in your VBA code, any unhandled errors, will clear a global variable, but will not clear a temp var. We will learn how to read a temp var from a text box using the .Value property. We will learn how to use a temp var in the control source of a field and in a property like Default Value, and a property like Caption, in source code, and as query criteria using the bang operator. Lots to cover in this lesson.

In lesson five, we are going to continue working with temp vars. We are going to learn different ways to add temp vars: the add methods we saw before. There are ways to remove temp vars, like when the user logs out, you want to remove that value. We will check to see if a temp var is set. We will learn two different ways to loop through all of the temp vars that are in memory, with a 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? We will learn about IsDate and some other related functions. That is all coming up in lesson five.

Intro

Quiz

Q1. What is the main reason Richard Rost advises against using the Append-Only property in Access long text fields?
A. It is bad database design and makes data harder to work with relationally
B. It improves performance but uses too much memory
C. It automatically deletes records that are too old
D. It requires a subscription to Access 365

Q2. If you have a field with the Append-Only property that contains history, what does Richard recommend as the correct way to preserve the history?
A. Extract each entry into its own record in a related table
B. Leave the data in the Append-Only field for backup
C. Copy the field to a new short text field
D. Delete all old entries except the latest

Q3. What is a temp var in Microsoft Access?
A. A way to store temporary values in computer memory during a session
B. A way to permanently store data in a table
C. A variable limited to numeric data only
D. Another term for a query parameter

Q4. Which of the following is NOT a benefit of temp vars compared to global variables, according to the lesson?
A. They do not survive an unhandled error, which can be beneficial
B. They can store many types of data, like strings, numbers, and dates
C. They are directly saved into every table automatically
D. They can be user for storing data temporarily in memory

Q5. How can you use a temp var in Access, based on the class material?
A. As criteria in a query by using the bang operator
B. Only as a backup field in tables
C. Only for tracking system errors
D. For managing network logins only

Q6. What is necessary to understand when working with the Append-Only property and extracting its data as described in the class?
A. Working with Recordsets and string parsing
B. Using only simple queries
C. Relying only on macros
D. Importing data from Excel

Q7. What happens to a global variable in Access if your VBA code encounters an unhandled error?
A. The global variable is cleared
B. It is converted into a temp var
C. It duplicates its value
D. It remains unaffected

Q8. What are some ways you can interact with temp vars in Access according to the lessons covered?
A. Set them via VBA, use them in control sources, and as query criteria
B. Only read them from tables
C. Only use them in form captions
D. Use them only in SQL pass-through queries

Q9. What are some tasks covered in lesson five regarding temp vars?
A. Adding, removing, checking if set, looping through, and identifying data types
B. Only displaying temp vars to users
C. Creating permanent fields in tables for each temp var
D. Exporting temp vars to CSV automatically

Q10. What is the overall organizational approach to Access Learning Zone classes as described by Richard Rost?
A. They are designed to be taken sequentially without skipping
B. They can always be taken in any order you wish
C. Only developer classes are sequential, others are random
D. Lessons are self-contained and do not build on each other

Answers: 1-A; 2-A; 3-A; 4-C; 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 video from Access Learning Zone focuses on Microsoft Access Developer Level 43, where we tackle two major topics: the Append-Only property for long text fields and the use of temp vars.

First, I take a look at the Append-Only property, which is commonly used for long text fields. Even though a lot of developers choose it, I do not recommend it. In fact, I will explain why relying on it is poor database design and outline several reasons to avoid it. As developers, we sometimes inherit databases where this feature was used heavily. Quite often, clients will ask if it is possible to remove the Append-Only setting but still preserve all the history that has accumulated within those fields. I will walk you through how to handle this situation, including methods to extract and parse out the historical data. Along the way, you will see techniques involving string parsing and working with record sets.

After that, the focus shifts to temp vars. Temp vars are a powerful and flexible way to store values in memory while your application is running. While many developers rely on global variables, temp vars are generally superior for most uses, since they are not retained after an unhandled error, which helps keep things stable. One of the key benefits is that temp vars can hold any type of data: numbers, dates, strings, and more. In this video, I will thoroughly go over what temp vars are, their advantages and potential drawbacks, and show you practical use cases where temp vars are the best solution. I will also discuss some of the challenges you might encounter with data typing in temp vars, since you cannot always be certain what type of value is currently stored.

You will also learn how to list all temp vars currently in memory, loop through them, check if a particular temp var exists, and determine its value and data type. For example, is it a string, a currency value, or a date?

Because this is an advanced developer lesson, I recommend that you already have a solid foundation in Access by completing my beginner, expert, and advanced classes, plus the developer series from Developer 1 through Developer 13. Definitely make sure you have covered Level 16, which focuses on record sets. These are especially important for today's material as we work with the Append-Only property. The classes are designed to be taken in sequence, so it is best to follow them in order.

This course was recorded using Access 365 as of June 2023, which is compatible with Access 2021 for those using the retail version. However, I recommend using the subscription version for the latest features and the closest match to my demonstrations.

If you have questions about the material, you are welcome to post them at the bottom of the page. I suggest reading the existing questions, as you might find your answer has already been posted there. If you want notifications about updates or new questions and answers related to this class, be sure to subscribe. The Access Forum is also available on my website, where you can join discussions with myself, the moderators, and other students.

Here is an overview of what is covered in Access Developer 43:

Lesson one introduces the Append-Only property, explains why you should not use it, and presents better design alternatives.

Lesson two continues with the Append-Only theme. After discussing its drawbacks, I demonstrate how to deal with databases that already use it. Specifically, I show how to extract all historical entries from such fields and convert them into a standard relational format. Each history entry gets turned into its own record in a related table, following good database design. This involves looping with record sets and parsing strings to separate the historical data.

Lesson three introduces temp vars as another method to store data in memory. I explain what they are, why they are useful, how to set them up, their advantages, and potential pitfalls you need to be aware of.

Lesson four builds on the temp var concept. I show you how to set and retrieve temp vars using VBA code. You will see firsthand how global variables are wiped out by unhandled errors, while temp vars persist. I demonstrate reading a temp var from a text box with the .Value property and go over how to use temp vars in the control source of a field, in properties like Default Value and Caption, directly in your VBA code, and as query criteria by using the bang operator.

Lesson five continues with even more ways to work with temp vars. You will learn about different ways to add temp vars, as well as how to remove them, such as when a user logs out and you need to clear stored data. I cover methods to check if a temp var is set and show two approaches to looping through all temp vars in memory, using a for loop and a for-each loop. I also explain how you can figure out what type of data is stored in each variable, whether it is a date, currency, or something else, by using functions like IsDate and others.

You will find a complete video tutorial with step-by-step instructions for everything I have 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 you should not use Append-Only
Parsing and extracting Append-Only column history
String parsing of Append-Only field data
Using recordsets to process field history
Better alternatives to Append-Only fields
Introduction to temp vars in Access
Pros and cons of temp vars vs global variables
Setting and getting temp vars in VBA
Reading temp vars from text boxes
Using temp vars in control sources and properties
Using temp vars as query criteria
Removing temp vars when users log out
Checking if a temp var is set
Listing all temp vars in memory
Looping through temp vars with for and for-each
Determining temp var data types
Using IsDate and related functions with temp vars

Primary Topics

append-only property, Access long text fields, extracting column history, string parsing, record sets, temp vars, VBA, data typing, memory storage

Secondary Topics

global variables, error handling in VBA, variable looping