Access Developer 49
Transactions, Dynamic Temp Tables, Nav Subform
Welcome
Welcome to Microsoft Access Developer Level 49, brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
This course covers using transactions in Microsoft Access to ensure data integrity, handle errors, and roll back changes when needed. You will learn to process orders with composite keys, prevent duplicates, and maintain consistency.
We will also explore dynamic temporary tables to improve form loading times and create a button navigation subform for seamless record management and integration.
Resources
Topics Covered
In Lesson 1 we will learn how to work with transactions in Microsoft Access, focusing on ensuring data integrity and handling errors effectively with DBFailOnError. You'll learn how to initialize and use a workspace, begin and commit transactions, and roll back if needed. We'll explore how to execute SQL commands using db.Execute for efficiency and how to handle errors gracefully without breaking your code. We will demonstrate how transactions ensure all-or-nothing execution, akin to a bank transfer's reliability.
In Lesson 2 we will learn how to handle more complex transactions using an order processing example. You will learn how to add an order and multiple items while ensuring that no duplicate items are included, using a composite key to enforce unique product entries. Along the way, I'll demonstrate setting up transactions so that if any item fails to process, the entire order will be rolled back, ensuring data integrity.
In Lesson 3 you'll learn how to significantly improve form loading times in split databases using a practical approach with temporary tables. I'll guide you through creating a dynamic function that generates temporary tables to store read-only data, such as customer lists, enabling faster form loads without constantly querying a remote server. This process is both flexible and table agnostic, meaning it works across various table setups. Experience the immediate difference as I demonstrate the conversion of slow-loading forms to ones that open almost instantaneously.
In Lesson 4 I will show you how to create a drop-in navigation subform featuring a series of buttons for navigating records. We'll cover how to utilize the recordset properties to display the current record's position and implement navigation buttons using code. The tutorial guides you on integrating these buttons into a subform, which can then be effortlessly added to any parent form without the need for additional VBA code. Learn how to set up automatic record count updates and ensure smooth navigation across different forms by simply configuring a few properties.
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, Transactions, DBFailOnError, Rolling Back Changes, Ensuring Data Integrity, Using Workspaces, db.Execute, Order Processing, Composite Keys, Avoiding Duplicates, Handling Failed Entries, Dynamic Temporary Tables, Faster Form Loading, Reducing Queries, Split Database Performance, Button Navigation Subform, Recordset Properties, Automating Record Counts, Parent Form Integration, Smooth Navigation
Intro
In this lesson you will learn about working with transactions in Microsoft Access, creating dynamic temporary tables to improve performance in split databases, and building a reusable drop-in navigation subform. I will show you how to handle multi-step transactions such as committing or rolling back changes, use temporary tables to make slow forms faster no matter what backend you use, and design a navigation subform that can be easily reused across different forms without needing extra code in the parent form. This is Microsoft Access Developer Level 49.
Transcript
Welcome to Microsoft Access Developer Level 49, brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today's class is going to focus on three topics: transactions, dynamic temporary tables to speed up your split databases, and a drop-in navigation subform.
Lessons 1 and 2 are all about transactions. A transaction is where you can issue multiple SQL statements, like insert some records, delete some records, copy some records, and so on. If any one of those fails, all of them fail. That's a transaction.
Think of it like bank transactions. You're transferring money from one account to another, out from one and in to another. If either one of those fails, the whole transaction fails. That's what we're going to be doing in Lessons 1 and 2.
In Lesson 1, we're going to keep it simple. We'll do some simple transactions with customer records. I'll teach you how to start the transaction, commit the transaction, and roll it back if you need to.
Then in Lesson 2, we'll do something a little more complicated. We'll add an order with a bunch of detail items, and of course check the inventory level on each of those items as you add it. If it's too low, we'll cancel the whole order. That's the power of transactions.
In Lesson 3, we're going to focus on dynamic temp tables. What is this? Well, if you've got a split database, whether it's on the network or on the internet, you might use an SQL server or just a backend Access database. If you've got forms that pull a lot of data in from other tables, like a list of customers, orders, products, or whatever, you may notice that some of those combo boxes or other controls on your forms are running slowly.
So in this lesson, I'm going to teach you how to make them faster. When the form loads, or even when the database loads, you're going to copy that data down to a temporary table. We're going to make it table agnostic, so it's not going to matter what fields are in your table. You just give it the table name, and it will create the backend table copy for you. You'll just use that.
In Lesson 4, we're going to make a drop-in navigation subform - this little guy right here. You know how to make buttons to go to the first record, go to the next record, go to the last record. That's all easy stuff. That's beginner stuff.
In this video, I'm going to show you how to manipulate the recordset of the main form and display what record you're on, as well as the total number of records using the recordset properties and the absolute position property, which we really haven't covered yet.
Then we're going to take that and make that navigation bar a subform. In the future, if you want to use it on a different form, you just drop that subform in, make a change to the properties, and you're done. No VBA has to go in the parent form at all. The subform does all the heavy lifting. This is a great solution. You'll see how this works in Lesson 4.
This lesson, of course, follows Access Developer 48. Do you have to have watched 48? No, not really. Once we get up to this level, there are some core things that you really should know - definitely the beginner and expert material, Developer 15 (error handling), 16 (recordsets), and tabledefs are important.
These lessons are designed so that I assume you understand everything from the previous ones. But if you come across a topic that you don't know, you can always look it up on the website and find out what other lesson covers that. Or just don't skip levels and you won't have to worry about it.
It is currently March of 2025, so I am using an Access 365 subscription. My version of Access is roughly equivalent to Access 2024. Get a subscription. It's not that expensive. It's like eight bucks a month and it's worth it if you're running your business on Access.
Got questions? Post them down below on the page that you're watching this video on. You can post questions right at the bottom, and we'll do our best to answer you.
Alright, so sit back, relax, and enjoy Access Developer 49.
Quiz
Q1. What is the primary purpose of using transactions in Access, as discussed in Lessons 1 and 2?
A. To enforce user permissions on tables
B. To execute multiple SQL statements together, ensuring all succeed or all fail
C. To make table modifications easier
D. To automatically back up the database
Q2. In the bank transfer analogy for transactions, why is it important for both parts of the transfer to succeed together?
A. To prevent the customer from going overdrawn
B. To ensure that money is neither lost nor created by a partial transfer
C. To save time with faster transactions
D. To notify users of changes immediately
Q3. What happens during a transaction if one of the SQL statements fails?
A. Only the failed statement is skipped
B. The database shuts down automatically
C. All statements are rolled back, so none of them are applied
D. An error message is shown, but the rest succeed
Q4. What will you learn about handling customer records in Lesson 1?
A. How to encrypt customer data
B. How to start, commit, and roll back a transaction involving customer records
C. How to merge duplicate customer records
D. How to export customer data to Excel
Q5. In Lesson 2, what additional step is added to the transaction process involving orders?
A. Apply discounts to orders automatically
B. Check inventory levels for each order detail item, cancelling the transaction if inventory is too low
C. Automatically calculate taxes for each order
D. Merge orders from the same customer
Q6. What problem do dynamic temporary tables help to solve in split databases?
A. Allowing easier database backups
B. Improving speed when pulling large sets of data into forms or controls
C. Keeping track of user logins
D. Automatically encrypting all table data
Q7. What does it mean for the dynamic temp table approach to be "table agnostic"?
A. It only works with customer tables
B. It works regardless of the fields present in the table
C. It only recognizes tables with primary keys
D. It needs manual field mapping for each table
Q8. What is the main advantage of creating a drop-in navigation subform as described in Lesson 4?
A. It encrypts sensitive information on forms
B. It enhances security settings for subforms
C. It can be easily reused on different forms without additional code in the parent form
D. It offers automated data validation features
Q9. What properties are important when displaying the current record number and total record count via the navigation subform?
A. Only the SourceObject property
B. Recordset properties, especially absolute position
C. ControlSource property for all controls
D. Macro settings for the subform
Q10. Which prior Access Developer lessons are especially important prerequisites for this course?
A. Developer 1, 2, and 3 on layout basics
B. Developer 15 (error handling) and 16 (recordsets), as well as familiarity with tabledefs
C. Only beginner-level material
D. No prerequisites are necessary
Q11. Why is an Access 365 subscription recommended by the instructor?
A. It is required by law for all users
B. It provides the latest features and is cost-effective for business users
C. It doubles your database speed
D. It automatically generates all VBA code
Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-C; 9-B; 10-B; 11-B
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 three advanced topics in Microsoft Access: transactions, dynamic temporary tables for optimizing split databases, and creating a reusable navigation subform.
In the first two lessons, I discuss transactions. A transaction allows you to execute several SQL commands as a single unit of work. For instance, you can insert, delete, or update several records together. With transactions, if one task fails, they all fail. This gives you the confidence that your data will never end up in a half-complete state. Think about how banks transfer funds between accounts. If either the debit or credit part fails, the transaction does not go through. That same level of security and integrity is what we are establishing here.
Our first lesson keeps things straightforward, focusing on transactions with customer records. I explain how to start a transaction, commit the changes if everything goes smoothly, or roll everything back if there is a problem.
The second lesson builds on that foundation with a more complex example. We add an order and several associated detail items, checking inventory for each one as they are added. If the stock isn't high enough on any of them, the entire order is canceled. This shows how powerful transactions can be when dealing with groups of dependent operations.
Lesson three shifts to improving performance with dynamic temporary tables. This is especially useful if your database is split and the backend is stored either on a network or online. Many developers notice that forms can be sluggish, particularly when combo boxes are populated with large data sets from tables like customers, products, or orders.
I will show you how to improve form speed by copying data from those backend tables into a temporary local table when the form opens or when the database loads. The solution I teach is flexible and not tied to any particular table. All you have to do is specify the table name, and the process will create a temporary copy for you to use. This method saves time and resources, particularly when working over slower connections.
In lesson four, the focus is on building a plug-and-play navigation subform. Creating record navigation buttons for moving first, next, or last is basic Access functionality. But here, I teach you how to work directly with the recordset of your main form, displaying information such as which record you are currently viewing and the total number of records, using the recordset properties and the absolute position property.
From there, we take it a step further by putting this navigation bar inside a subform. That way, you can reuse it on any form you like in the future. All you need to do is drop the subform in and update its properties. There is no need to write additional VBA on the parent form, because the subform handles everything, making it highly efficient and modular.
This course follows on from Access Developer Level 48. While you do not have to watch Level 48 first, you should already be familiar with core beginner and expert concepts. Previous developer lessons, particularly Lesson 15 on error handling, Lesson 16 on recordsets, and the material on tabledefs, are especially important as the concepts build on one another. If you encounter topics that are unfamiliar, the website has resources that can help you track down the relevant lessons. I encourage you not to skip levels to get the best learning experience.
As of March 2025, I am using an Access 365 subscription. My version is equivalent to Access 2024. If you are using Access for business, I strongly recommend getting the subscription. It is reasonably priced and allows you to stay current.
If you have any questions about the topics discussed in this video, feel free to post them directly below the video on the website, and I will do my best to get you the answers you need.
For complete video tutorials including step-by-step instructions covering everything mentioned here, visit my website at the link below.
Live long and prosper, my friends.
Topic List
Understanding and using transactions in Access
Starting, committing, and rolling back transactions
Handling complex transactions with multiple steps
Managing inventory levels within transactional orders
Creating dynamic temporary tables to optimize split databases
Copying backend table data to local temporary tables
Automating temp table creation for any table structure
Improving form performance with local temp tables
Building a reusable navigation subform for record navigation
Displaying current record and total records using recordset properties
Utilizing absolute position property for record navigation
Embedding the navigation bar as a drop-in subform on forms
|
