|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Welcome Welcome! Transactions, Temp Tables & Subforms Welcome to Microsoft Access Developer Level 49. In this course we will discuss transactions, dynamic temporary tables for speeding up split databases, and creating a drop-in navigation subform. Lessons 1 and 2 will cover how to use transactions to ensure multiple related SQL operations run reliably, such as inserting, deleting, or rolling back records, and will include an example with orders and inventory checking. In Lesson 3, we will talk about optimizing form performance by using table-agnostic temp tables. Lesson 4 focuses on building a reusable navigation subform that can be used to control record navigation on your forms. NavigationKeywordsAccess Developer, transactions, temporary tables, split database performance, navigation subform, recordset properties, rollback, commit transaction, dynamic temp tables, subform navigation bar, inventory check, order processing, tabledefs, error handling
More InformationTranscriptWelcome to Microsoft Access Developer Level 49 brought to you by AccessLearningZone.com. I am 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, a whole bunch of them. If any one of those fails, all of them fail. That is a transaction. Think of it like bank transactions. You are transferring money from one account to another, out from one and into another. If either one of those fails, the whole transaction fails. That is what we are going to be doing in Lessons 1 and 2. Lesson 1 is going to keep it simple. We will do some simple transactions with customer records. I will teach you how to start the transaction, commit the transaction, and roll it back if you need to. In Lesson 2, we will do something a little more complicated. We will 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 is too low, we will cancel the whole order. That is the power of transactions. In Lesson 3, we are going to focus on dynamic temp tables. What is this? Well, if you have a split database, whether it is on the network or whether it is on the internet, you use an SQL server or just a backend access database. If you have forms that pull a lot of data in from other tables, like a list of customers or orders or products or whatever, you probably notice that maybe some of those combo boxes or other controls on your forms are running slowly. So in this lesson, I am going to teach you how to make them faster. When the form loads or even when the database loads, you are going to copy that data down to a temporary table. We are going to make it table agnostic, so it is not going to matter what fields are in your table. You just give it the table name, bang, it will create the backend table copy for you, and you will just use that. In Lesson 4, we are 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 is all easy stuff. That is beginner stuff. But in this video, I am going to show you how to manipulate the record set of the main form, display what record you are on, the total number of records using the record set properties, and the absolute position property, which we really have not covered yet. Then we are going to take that and we are going to make that navigation bar a subform. So 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 are done. No VBA has to go in the parent form at all. The subform does all the heavy lifting, and this is a great solution. You will 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, I mean, once we get up to this level. But there is some core stuff that you really should know. Definitely the Beginner and Expert material. Developer 15, error handling, 16 recordsets, tabledefs are important. These are designed so that I assume you understand everything from the previous ones. So, yes, kind of. But if you come across a topic that you do not know, you can always look it up on the website and find out what other lesson covers that. Or just do not skip levels, and you do not 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 is not that expensive. It is like eight bucks a month, and it is worth it if you are running your business on Access. Got questions? Post them down below the page that you are watching this video on. You will see you can post questions right on the bottom, and we will do our best to answer you. Alright, so sit back, relax, and enjoy Access Developer 49. IntroWelcome to Microsoft Access Developer Level 49. In this course we will discuss transactions, dynamic temporary tables for speeding up split databases, and creating a drop-in navigation subform. Lessons 1 and 2 will cover how to use transactions to ensure multiple related SQL operations run reliably, such as inserting, deleting, or rolling back records, and will include an example with orders and inventory checking. In Lesson 3, we will talk about optimizing form performance by using table-agnostic temp tables. Lesson 4 focuses on building a reusable navigation subform that can be used to control record navigation on your forms. QuizQ1. What is the main benefit of using transactions in Microsoft Access? A. Multiple operations can be executed so that all succeed or all fail together B. They make forms load faster C. They allow you to split databases easily D. They are necessary for creating combo boxes Q2. In Lesson 2, what is checked before completing an order transaction? A. Customer address accuracy B. Inventory level of each order item C. Employee authorization D. Payment method Q3. What is a dynamic temporary table intended to solve? A. Slow performance due to pulling large amounts of data in split databases B. Security concerns on the backend C. Form design errors D. Conflicts with VBA modules Q4. How does the dynamic temp table feature handle different tables? A. It is table agnostic and can copy any table structure given the table name B. It only works with tables that have the same fields C. It requires you to design a new query for each table D. It cannot handle tables with more than five fields Q5. What is unique about the navigation bar subform covered in Lesson 4? A. It can be dropped into any form with no changes to the parent form's VBA B. It only works with single-record forms C. It requires manual button coding in every parent form D. It cannot show current or total records Q6. Which property is used in Lesson 4 to display the current record position in a form? A. Absolute Position property of the record set B. Tab Index C. Form Name property D. Record Source property Q7. What is a split database? A. A database where the tables are stored in a separate backend file B. A database split into different user accounts C. A table that has been divided into multiple tables D. An Access database only using macros Q8. According to the instructor, which previous lesson covers the topic of error handling? A. Developer 15 B. Developer 48 C. Expert 7 D. Beginner 5 Q9. What is the recommended version of Access to use with this course? A. Access 365 subscription B. Access 2013 C. Access 2010 D. Access 2007 Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-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. SummaryToday's video from Access Learning Zone is Developer Level 49, and I am Richard Rost. In this class, I'm going to walk you through three key topics: implementing transactions in Microsoft Access, using dynamic temporary tables to improve performance in split databases, and creating a reusable navigation subform. To start, the first two lessons are all about transactions. A transaction is simply a set of SQL operations, such as inserting, updating, or deleting records, that you want to treat as a single unit. If any one operation fails, all of them are rolled back so your data remains consistent. A classic example is a bank transfer - if money leaves one account and does not properly reach the other, the whole process should be cancelled. In the first lesson, we'll practice with customer records and keep things straightforward. I'll show you how to begin a transaction, commit changes when everything works, or roll back if there's a problem. When we move on to lesson two, things get a little more advanced. Here, we'll process an order that involves adding a set of detail items. As we add each item, it's important to check inventory levels to ensure you have enough stock. If you run into a shortfall at any point, we'll cancel the entire order. That is where transactions shine, because you don't want partial updates in your system when things go wrong. Lesson three covers dynamic temporary tables. This is particularly important for anyone working with split databases, whether your backend is on a network, SQL Server, or an Access backend file. You may have noticed that pulling data into forms from linked tables can be slow, especially with things like combo boxes or controls that load large lists. I'll show you how to speed this up by copying data into a local temporary table when your form or database loads. The method I will demonstrate is flexible; you give it any table name, and it will generate a local copy regardless of the fields. You can then use that local table in your forms for much faster performance. In lesson four, I'll walk you through creating a drop-in navigation subform. This is an advanced take on record navigation. While it's easy to set up basic buttons for first, next, or last record, we'll take it further. I'll show you how to work with the main form's Recordset properties to display the current record number, total record count, and work with the AbsolutePosition property, which is something we haven't spent much time on before. The best part is that we'll turn this navigation bar into a subform. That way, you can reuse it in any other form just by dropping it in and adjusting its properties. No additional VBA is needed in the parent form; the subform does all the work. This class continues from Developer Level 48. You do not absolutely have to watch Level 48 first, but there are core topics you should already know at this point, especially from the Beginner and Expert series, as well as error handling from Developer 15, and Recordsets and TableDefs from Developer 16. Each class builds on the skills from previous levels, so while you can jump around, I recommend working through the material in order. If you hit a topic you are not familiar with, you can always look it up on the Access Learning Zone site. Right now, it's March 2025 and I am using Access 365, which works much like Access 2024. If you do not already have a subscription, I highly suggest getting one. It is affordable and vital if you are running a business on Access. If you have questions, just head to the web page where you are watching this lesson. At the bottom of the page, you will find a place to post your questions, and I will do my best to answer them. For all of the instruction and examples you need, you can find the 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 ListUnderstanding transactions in Access Starting, committing, and rolling back transactions Simple transaction example with customer records Complex transaction with order and detail items Checking inventory levels during transactions Cancelling transactions if data does not meet criteria Using dynamic temporary tables to speed up split databases Copying backend data to temp tables for performance Creating table-agnostic temp table solutions Implementing a drop-in navigation subform Using recordset properties for navigation Displaying current and total records in a form Using absolute position property in recordsets Embedding navigation bar as a reusable subform Primary Topicstransactions, VBA SQL, recordsets, split databases, temporary tables, performance optimization, navigation subform Secondary Topicsrollback, commit, table agnostic design, inventory checks |
||
|
| |||
| Keywords: Access Developer, transactions, temporary tables, split database performance, navigation subform, recordset properties, rollback, commit transaction, dynamic temp tables, subform navigation bar, inventory check, order processing, tabledefs, error handling PermaLink How To Use Transactions, Dynamic Temp Tables, and Navigation Subforms in Microsoft Access |
