Introduction

Welcome! Transactions, Temp Tables & Subforms

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

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.

Navigation

Prev: D49
Next: Lesson 01

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

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome 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.

Quiz

Q1. 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.

Summary

Today'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 List

Understanding 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

Article

Welcome to this tutorial on advanced Microsoft Access development. In this lesson, we are going to cover three important topics: using transactions in Access, creating dynamic temporary tables to speed up split databases, and building a reusable navigation subform that you can easily add to any form in your application.

Let us start with transactions. In database terms, a transaction is a way to group several actions together so that either all of them succeed or none of them do. Think about a situation where you are transferring money from one bank account to another. You would not want to debit one account if the credit to the other fails. Both actions happen together as a transaction, or neither happens at all. Access supports transactions in VBA using the DAO or ADO library. For example, imagine you need to update a customer record and then insert an order for that customer. If something goes wrong in either step, you want the database to remain unchanged.

Here is a simple example of how you might use a transaction in Access VBA:

Dim db As DAO.Database
Set db = CurrentDb
db.BeginTrans

On Error GoTo Err_Handler

db.Execute "UPDATE Customers SET Address='123 New St' WHERE CustomerID=1"
db.Execute "INSERT INTO Orders (CustomerID, OrderDate) VALUES (1, Date())"

db.CommitTrans
Exit Sub

Err_Handler:
db.Rollback

In this code, the BeginTrans statement starts the transaction. If both the UPDATE and the INSERT statements run successfully, then CommitTrans saves those changes permanently. However, if an error occurs at any point, the code jumps to the error handler and rolls back the transaction, so none of the changes will be saved. This protects your data from partial updates.

You can use transactions with more complex logic as well. For example, suppose you want to insert an order with several order detail items, and for each one you need to check that there is enough inventory. If inventory runs out for any item, you want to cancel the entire order. To do this, you would loop through each order detail, checking the inventory before inserting, and if you run into a problem, roll back the whole transaction.

Here is a simplified version:

Dim db As DAO.Database
Dim rst As DAO.Recordset
db.BeginTrans

On Error GoTo Err_Handler

Dim ItemsOK As Boolean
ItemsOK = True

Set rst = Me.OrderDetails.Form.RecordsetClone
rst.MoveFirst
Do While Not rst.EOF
' Check inventory level
If DLookup("QtyOnHand", "Products", "ProductID=" & rst!ProductID) < rst!Quantity Then
ItemsOK = False
Exit Do
End If
rst.MoveNext
Loop

If ItemsOK Then
' Insert order and details
' db.Execute "INSERT INTO Orders..."
' db.Execute "INSERT INTO OrderDetails..."
db.CommitTrans
Else
MsgBox "Not enough inventory for one or more items. Order canceled."
db.Rollback
End If

Exit Sub

Err_Handler:
db.Rollback

This pattern ensures that all your related database changes are treated as a single unit of work.

Next, let us talk about speeding up Access forms in a split database environment. When your tables are stored on a shared backend, especially across a network or remote server, loading lots of data into forms or controls like combo boxes can become slow. One way to solve this is to copy the required data to a local, temporary table each time your form or database loads. This makes the local queries and controls much faster because they are not constantly requesting data over the network.

Rather than creating a separate process for every backend table, you can create a generic function to duplicate any table structure and its data locally whenever you need it. Think of this dynamic temp table as a local snapshot. To make this work for any table, not just a specific one, you can use the TableDefs and Fields collections in DAO to dynamically create the temp table based on the structure of the source table.

Here is an example of how you can create and fill a temp table:

Sub CreateTempTable(strSourceTable As String, strTempTable As String)
Dim db As Database
Set db = CurrentDb
On Error Resume Next
db.Execute "DROP TABLE " & strTempTable
On Error GoTo 0
db.Execute "SELECT * INTO " & strTempTable & " FROM " & strSourceTable
End Sub

You can call this function with the table name you want to cache locally:

CreateTempTable "Customers", "Temp_Customers"

Now you can bind combo boxes or form record sources to the Temp_Customers table and enjoy much faster load times. This approach works for any table, because we are using SELECT * INTO, which creates the table based on the structure and data of the source.

Finally, let us build a reusable navigation subform for your Access applications. Basic navigation buttons like First, Next, Previous, and Last are common, but it is even better to have a dedicated navigation bar that you can drop into any form without having to copy code or controls every time. The navigation subform should be able to display the current record number, the total number of records, and allow navigation, all while being aware of the main form's recordset.

To do this, design a subform with your navigation buttons and labels. In the subform's code, use the Parent property to refer back to the main form's recordset and its properties. For example, you can access the recordset like this:

Dim rs As DAO.Recordset
Set rs = Me.Parent.Recordset

You can then update the subform labels to show the current position and total records using the AbsolutePosition and RecordCount properties:

lblPosition.Caption = rs.AbsolutePosition + 1
lblTotal.Caption = rs.RecordCount

For the navigation buttons, you might use code like this:

Private Sub cmdNext_Click()
If Not Me.Parent.Recordset.EOF Then
Me.Parent.Recordset.MoveNext
End If
UpdateLabels
End Sub

Private Sub UpdateLabels()
Dim rs As DAO.Recordset
Set rs = Me.Parent.Recordset
lblPosition.Caption = rs.AbsolutePosition + 1
lblTotal.Caption = rs.RecordCount
End Sub

Once you have built and tested your navigation subform, you can add it to any main form by setting it up as a subform control. The beauty of this approach is that all the code lives in the subform. You do not need to add any additional VBA to the parent form. When you want to use the navigation bar on a new form, just drop in the subform and, if needed, set a property to point to the correct form or control.

In summary, in this tutorial, we explored how to use transactions in Access to make your data updates more robust, how to improve performance in split databases with dynamic temp tables, and how to create an efficient, reusable navigation subform. These techniques can make your applications faster, safer, and easier to maintain. If you have any questions as you try out these methods, be sure to reach out or consult the Access documentation for more details. With a bit of practice, these advanced techniques will become a natural part of your Access development toolkit.