Sluggish Form
By Richard Rost
2 years ago
Optimize Form Performance for Faster Access In this Microsoft Access tutorial, I will show you how to optimize form performance for faster loading by addressing sluggishness due to complex queries and calculations. Learn effective indexing, data limiting, query optimization, and how to use temporary tables to significantly improve your form's speed and efficiency. Nolan from Buena Park, California (a Platinum Member) asks: I have a form that I use to view quarterly sales summaries from my database, which contains thousands of orders. The issue is that the form is extremely slow to load and navigate between records. I suspect it might be due to the calculations I'm performing directly in the form's queries. Is there a more efficient way to enhance the performance of this form, so it loads faster when I need to generate and view these sales reports? MembersThere is no extended cut, but here is the database download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, Microsoft Access form optimization, optimizing form performance, faster form loading Access, slow Access form navigation, Access form efficiency techniques, improve Access query speed, using temporary tables in Access, Access VBA performance tips, split database speed boost Access, indexing fields for performance.
Subscribe to Sluggish Form
Get notifications when this page is updated
Intro In this video, we'll talk about how to make slow-loading forms run faster in Microsoft Access. You'll learn tips for optimizing form performance, including indexing key fields, limiting data using criteria, compacting and repairing your database, and the benefits of split databases. We'll cover why complex queries and calculations can make forms sluggish and discuss using aggregate queries instead of domain functions. The main focus is on improving speed by running calculations ahead of time and storing results in a temporary table, so your forms load much more quickly and efficiently.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Today we're going to talk about optimizing forms for loading faster, because you know you've always had that one form that is just really sluggish. It's got lots of data in it. It's based on a huge query or a very complicated set of functions, and it's just not loading fast. And once it does load, it's still slow and sluggish. Well, in today's video we're going to talk about it.
Today's question comes from Nolan in Buena Park, California, one of my platinum members. Nolan says, "I have a form that I use to view quarterly sales summaries from my database, which contains thousands of orders. The issue is that the form is extremely slow to load and navigate between records. I suspect it might be due to the calculations I'm performing directly in the form queries. Is there a more efficient way to enhance the performance of this form so it loads faster when I need to generate and view these sales reports?"
Yes, optimizing your form so that it's fast is one of the things I spent a lot of time on, and I've got a particular sales form myself. It was a continuous form that shows all of the sales grouped by date. Then I try to match it up with all the payments that come in. Before I learned the trick that I'm going to teach you today, this thing not only took forever to load, but even once it was loaded it was slow as molasses. It was like I was walking through caramel just to click from record to record, and there's a reason why - we'll talk about it in a few minutes.
Now, before we get into the main problem that's causing this, there's a checklist of things you should do first with any form and any database. Index your key fields, right? Any fields that you're going to be searching and sorting on, make sure they're indexed. There's a list of videos here on the right side there for each one of these topics.
Make sure your fields are properly indexed. Make sure that you're limiting data with criteria where possible. If you're doing something where - and this could matter if you've got multiple queries involved - if you've got your final query, let's say it's based on two or three other queries, you've got some criteria in there, you're doing some calculations, right? Don't do all of these steps with calculations and stuff and then make the last step the criteria where you say, "Okay, take this big monster query now and just give me the records from the last 30 days." Do that part first. The first step should be, "Okay, take all these orders and just give me the ones in the past 30 days," then the next step can be your calculations and your aggregates and all that stuff, so limit the data with criteria first before doing all your calculations.
Next, of course, compact and repair - do it regularly. I have an event that runs nightly in my database. Use a split database - even if you're the only user, sometimes you can get a speed boost by using a split database. Why? Just one of the access quirks. If it's a matter of the form, when you click the button and it takes forever for the form to appear, I've got a separate video on that you can watch. That'll give you the appearance of loading faster. Essentially, what it does is it pops the form up, then you use a timer event to load the data in the background. We're not going to get around that one today; that's just another idea. And then, of course, optimize your queries. If you've got six queries when three will do the job, do that. If you've got functions that you can simplify, do that. If you've got data in there that you don't need, get rid of it. So there's a lot of optimizing steps if stuff's running slow.
But the main problem that we're dealing with is that if you've got, let's say, a continuous form, right? It displays a large amount of data, and that data is coming from queries that have numerous calculations, aggregates, lookups. It can significantly slow down the performance of the form. Not only does it load slower, but once it's open it will still be sluggish. This is because Access has to rerun complex calculations when the form is loaded, when you navigate between records, and so on. Why? Again, it's just the way that Access is built. If all that work is done in the form, the form somehow uses up a lot of memory and it just acts slow.
Now, the solution to improve performance of your big forms like that is to take all the complex calculations and the queries, aggregates, whatever functions, run them beforehand before the form is loaded, and we're going to store those results in a temporary table. That's the key. Instead of having the form's record source rely on these big resource-intensive operations and these gigantic queries that slow things down, we're going to execute some action queries ahead of time. It's going to summarize, transform the data, run your aggregates, all that stuff, and save all that data that you want to see into a temporary table. Then we'll make the temporary table the record source for our form. It'll be read-only, but generally you just want to see data. This way the form will load more quickly and it will simply pull in the quick data from that temporary table, and all the calculations are already run. This will significantly boost the performance of the form.
Now, I do have to pause and talk about this image. I asked AI, I asked GPT to make me some images, it made a pretty cool one here. I said, "Give me a, you know, a whole bunch of data and stuff going into a form," and that's pretty cool. And then for this one, I said, "Okay, make me a picture of all the complex queries and stuff feeding a temporary table and then, and it got that, calculations in a temporary table." And then I said, "And then use the temporary table as the record source for a form and then maybe just have an arrow go to the table and the form, and that form is happy and pretty and use bright colors." That's what I said to it. I guess the form is all happy and pretty in its bright colors. I got a good laugh. I just, I had to use it. I had to share it with you guys.
Alright, so before we get into it, this will be a developer-level video. Now, you can do this without programming. You could manually run some action queries. You'll need a make-table query once and then a delete query and an update query or an append query. So you can do this without programming or you could write a macro, but I really just think this is much easier to do with a little bit of VBA. So if you've never done any VBA programming before, go watch this video. It'll teach you everything you need to know about VBA in about 20 minutes to get started to follow along. And, of course, we're going to use a little SQL, so if you're not too familiar with SQL go watch this video and make sure you understand what the different action queries are. We're gonna use a delete query, a make-table query, and an append query today. So there's links here to different videos that teach you about those.
Alright, so here I am in my TechHelp free template. This is a free database that you can download off of my website if you'd like to. Now, let's say in here, I've already got a customer list. The customer list form has all my customers, and I've only got 33 customers and a couple of dozen orders. Again, this really takes into consideration gigantic databases. You've got tens of thousands of customers, hundreds of thousands of orders, maybe you're pulling data over the network, so it's gonna be running slow. Obviously, it's gonna run fast on my machine and it's difficult to simulate that stuff, but you get the point, right? I'm sure you've all run into forms before that run really slow, so each customer has orders, right? They have multiple orders, and let's say that I want to generate a list on this form. Let's say I want to do sales summary information.
I'll put instead of credit limit over here, I want to replace this with the customer's total worth, right? The total of all the orders ever placed. Now if you've got a lot of data in your database, calculating that number for each customer in here can take a long time, and this is just a simple example. Now you definitely do not want to do it with a DSum in a continuous form. That's the slowest way to do it. Try to avoid all of the domain aggregate functions in a continuous form or in a query. We have something that's much better called an aggregate query. These are much, much better and faster than using the domain functions. Domain functions are good for like a single form. Like if you open up the customers form and you want to put down here like this customer's total sale, that's fine. Don't put them in continuous form. It takes way too long to load.
Now I already have another query here called order summary, Q and I build this in the invoicing video, where this basically is an aggregate query that gives you the order total for each order. So order one, there's a total for that because remember the total is based on the sum of all the line items, right? So now I have this query, I can do another aggregate query based on this one to give me the sum for each customer because I got the customer ID right there, right? So if I aggregate this and group it by customer ID it should add those together. So customer one should be about 3560, right? Well, this one's not paid - too. Let's pretend we're dealing with all the orders instead of just the paid ones.
So I'm going to come in here and create another query, so create, query design. Let's bring in the order summary Q, and then I need the customer ID first. And I will bring an is paid, why not, is paid, and then the order total. And then we're going to turn this into an aggregate query, turn the totals on, and this will be set to sum. If you want paid only, put criteria down here is true for is paid, and now when I run it, there you go. Let's get rid of that criteria. Let's just get rid of this here. We'll just do the total of all their orders, whether they're paid or not, so we have more data. Okay, there we go.
Okay, now let's bring in the other fields that I need for my continuous form out here. I need the first name, last name, and let's get rid of state and customer since... so let's save this guy. We'll call this the order summary by customer queue, which is just customer ID and order total, but now I can bring that order total into another query and set up a - let's call it the customer worth query. So create, query design, and this will be the query that feeds that form, right? So we'll bring in the query we just built, we'll link to that the customer table, which is right here. You can bring it from both, either over here or over here, depends on what I'm doing. Let's flip this around like this, right? Because we want the customers first. I'm going to need the ID, the first name, the last name. Let's bring over some of order total, but I don't want it to be called some of order total. Let's alias this and call it worth. It's just worth: and then the field name. It's called an alias, I got a video on aliasing. I'll put a link to that down below as well. We also want to make sure we don't miss anybody, so we're going to make this into an outer join. I want all the records from customer T, all the customers, and the ones over here that match because you're only going to be in this query if you have an order in the system, right? We don't want to miss anybody, so hit okay, and it should look like that then. And now when I run this bad boy, there you go, there's all the customers and the worth of the ones that have a value.
Okay, alright back to design view. Let's save this guy, we'll call this the customer worth queue. Now, this is the query that I can use to fill in the customer list form, this thing. So to do this we'll come in here, design view, we decide that we're going to get rid of state and customer since... I'll slide this over to the left here like that, and we're going to change the record source to that new query that we just built, which is customer worth queue. Now immediately you could see credit limit goes green, right? That's because there's no credit limit field in that query. So we're going to change this now to worth and don't forget to also change the name of the box, worth and worth, and then of course down here we want the sum of worth. We'll call this sum worth and of course change our label worth. Okay, and you don't want to let your customers see this form. You might not want to let your other employees see this form. Okay, so now when we close and open it, there we go, it's working and it's working just fine. But again, this is a tiny database with only 33 customers in it and a couple of dozen orders. You get the point, though. This is based on a query which is based on an aggregate query which is based on another query which is based on another query, and if this was all running with hundreds of thousands of records over a network, this form could take 30 seconds to load. Right, so what we're going to do is we're going to replace this by doing all the queries and the calculations and stuff in the background. We're going to give the user a nice friendly, you know, please wait message, and then we're going to feed a temporary table that this thing's based on, and then open the form and we'll do all of that in tomorrow's video. So tune in tomorrow, same bat time, same bat channel, I love that. Or if you're a member, you can watch it right now because I'm going to record it in just a few minutes. Alrighty, okay.
So that's going to be your TechHelp video for today. Hope you learned something, live long and prosper my friends. I'll see you tomorrow for part two.
TOPICS:
Optimizing forms for faster loading
Indexing key fields
Limiting data with criteria
Using compact and repair
Benefits of split databases
Optimizing form appearance with timer events
Optimizing queries
Issues with continuous forms
Storing results in a temporary table
Creating aggregate queries
Converting domain functions to aggregate queries
Creating and using make-table queries
Creating and using append queries
Outer joins in queries
Aliasing fields in queries
Changing form record source
Updating control names in forms
Handling large datasets in forms
COMMERCIAL:
In today's video, we're talking about optimizing slow-loading forms in Microsoft Access. Struggling with sluggish forms bogged down by complex queries and calculations? You're not alone. We'll walk through solutions to enhance form performance by indexing key fields, using efficient query criteria, and applying other techniques like compact & repair and database splitting. But the main event is running complex calculations in advance, storing results in a temporary table for rapid form access. Join us to learn these strategies and improve your form efficiency. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary reason for forms being slow to load according to the video?
A. The form's aesthetic design
B. Forms not having enough color
C. Forms being based on large queries or complicated calculations
D. The database being too small
Q2. What is one of the initial steps listed to optimize the performance of a form?
A. Increase the font size on the form
B. Index key fields used for searching and sorting
C. Use as many queries as possible
D. Avoid using any criteria in queries
Q3. Why is it suggested to limit data with criteria first in queries?
A. To make the form look more colorful
B. To ensure the final query has as much data as possible
C. To reduce the data size early on, which makes subsequent operations faster
D. To ensure all calculations are performed first
Q4. What regular maintenance task is recommended for improving database performance?
A. Convert the database to Excel format
B. Change the form's theme daily
C. Compact and repair the database regularly
D. Color-code each query
Q5. What is the suggested method to improve the performance of forms that run complex calculations?
A. Run the complex calculations within the form as it loads
B. Use more domain aggregate functions in continuous forms
C. Perform calculations and store results in a temporary table before opening the form
D. Minimize the number of forms used in the database
Q6. Why would you avoid using domain aggregate functions in a continuous form?
A. They are too aesthetically simple
B. They do not provide accurate results
C. They are much slower than using aggregate queries
D. They increase the number of records in the database
Q7. What is an aggregate query used for in this context?
A. To change the font style in forms
B. To sum up data and perform calculations more efficiently than domain functions
C. To display images in a form
D. To add additional records to the database
Q8. What is an outer join used for in a query?
A. To exclude records that do not match
B. To ensure records from both tables are completely excluded if they do not match
C. To ensure all records from one table are included, regardless if there is a match in the second table
D. To change the form's layout
Q9. When using VBA, what is suggested as a key action to improve form performance?
A. Use VBA to randomly change the form background color
B. Use VBA to manually update the records one by one
C. Use VBA to automate running action queries to store data in a temporary table
D. Avoid using VBA entirely
Q10. Why does the instructor suggest using a split database even if there is only one user?
A. It provides a significant speed boost for the database due to Access quirks
B. It allows users to edit the database structure more easily
C. It helps reduce the database file size
D. It makes the database easier to navigate
Answers: 1-C; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-C; 9-C; 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 TechHelp tutorial from Access Learning Zone focuses on enhancing the speed of loading forms in Microsoft Access, a common issue when you have a form bogged down by extensive data or complex queries and functions. This particular lesson arose from a question posed by a member who encountered sluggishness in a form used to view quarterly sales summaries. The main culprit seemed to be the intensive calculations carried out within the form queries.
To improve the performance of such forms, it's crucial to ensure that all key fields in your database are indexed for efficient searching and sorting. Additionally, apply any limiting criteria to your data early in the query process. By first narrowing down the records you need, your subsequent calculations and operations will run more smoothly.
Regular database maintenance, like compacting and repairing, helps with performance, as does using a split database even for single users. These steps can provide noticeable improvements. Another tip is to restructure your queries to reduce unnecessary complexity. If fewer queries can accomplish the same task, then simplify.
The core issue with slow continuous forms often lies in their reliance on complex queries with heavy calculations and aggregates. This processing burden significantly affects the form's speed, both in initial loading and while navigating records. The solution is to perform these complicated calculations before the form is loaded and store the results in a temporary table. The form should then use this temporary table as its record source, which allows for quicker data retrieval since all calculations are pre-processed.
This process can be further streamlined by employing some VBA programming, although it can be done manually with action queries. Using VBA can simplify the task and automate creating and maintaining the temporary table. Familiarity with SQL will also be beneficial here, as you will need to use delete, make-table, and append queries.
For instance, if you have a form listing customers and their total worth from orders, you can aggregate this data in advance and store it in a temporary table. This way, you avoid the slowdown caused by recalculating these figures every time the form is accessed.
Keep in mind this lesson is geared towards developers, and pursuing some basic VBA and SQL learning would be extremely advantageous in implementing these optimizations.
For a detailed, step-by-step video tutorial on everything covered here, visit my website through the link provided. Live long and prosper, my friends.Topic List Optimizing forms for faster loading
Indexing key fields
Limiting data with criteria
Using compact and repair
Benefits of split databases
Optimizing form appearance with timer events
Optimizing queries
Issues with continuous forms
Storing results in a temporary table
Creating aggregate queries
Converting domain functions to aggregate queries
Creating and using make-table queries
Creating and using append queries
Outer joins in queries
Aliasing fields in queries
Changing form record source
Updating control names in forms
Handling large datasets in forms |
