Sluggish Form 2
By Richard Rost
2 years ago
Optimize Form Performance for Faster Access Part 2 In this Microsoft Access tutorial, you will learn how to optimize form performance for faster loading by utilizing temporary tables and efficient query management. I will show you step-by-step how to switch data sources to improve speed and implement user interface tweaks for a smoother experience. This is part 2. 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!
PrerequisitesRecommended Courses
Keywords TechHelp Access, Microsoft Access sluggish form, optimizing form performance, Access form optimization, Access VBA tutorial, record source change, temp table update, improve form speed, Access database performance, make-table query, temporary table, delete query SQL, append query SQL, Access development tips.
Intro In this video, we continue working on speeding up slow-loading forms in Microsoft Access by creating a temporary table with a make-table query, updating it using delete and append queries, and switching the form's record source for faster performance. I will show you how to lock the form to prevent user edits, use VBA to manage status messages and the hourglass pointer, and prompt users with a message box to refresh the temp table data on demand. We also discuss best practices for handling temp tables and optimizing query placement. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part two of my sluggish form video. You know that form that just takes forever to load, and then even after it loads, it still performs very slowly and you feel like you're walking through caramel? Go watch part one first and then come on back.
Alright, so here's our database. We made a couple of queries. We got this customer worth query, and let's pretend it's got millions of calculations in it. This thing takes a lot to load on its own, but then when the form it's based on loads, it runs really slow and sluggish. So what we're gonna do is start off by making a temporary table with the same fields as this guy since it's got the same data. Then every time this form loads, we just have to update that temp table.
Alright, let's start off by creating the temp table now. We can do that with a simple make-table query based on this guy. It'll get all the same data, the same fields, and everything. So we'll go to create query design. Bring in the customer worth query, bring down all the fields - there's a little star there. Change this to a make-table query. Let's call this customer worth temp table. I like to put the word temp in there so that future me and other people know that this is a temp table, meaning the data in here isn't, you know, set in stone. It can change any minute by other queries or whatever, so it's not set in stone, right? It's not an actual table full of actual data.
Okay, hit okay; that should be all you need to do once you run this. Ready, go... there it is. There's all the same data that's in that query, but now it's in a table. And these are actual numbers now, not calculations. You can immediately see how this is gonna run much faster. Now, do you need to keep this query around? No, but I'm gonna save it just so the gold members have it. Customer worth temp make query. Alright, that's the make-table query, which you should never have to run again unless you somehow delete this query. Alright, now, get rid of that.
Now we're gonna change the record source. Close this guy now, right? Change the record source where this guy gets its data from the customer worth query to the customer worth temp table. Save it. Close it, and now when you open it, it immediately should open faster if this is your actual form that you're working with in your database. For me, I can't tell a difference because there's only a couple of records. But there it is.
Now, the first thing you might want to do at this point is simply let the user know that they can't make any changes in here. You can make these gray if you want to, or I like to just go into the data settings. We're gonna set allow additions, deletions, and edits to no. I've got separate videos on each of these things if you want to look them up on my website. Basically, you can't add records, you can't delete records, and you can't change records because this is a temporary table. And you wouldn't want your user opening this up thinking they can make changes in here. Can't type, can't delete stuff.
Okay, so this is locked; this is set in stone. But that's okay because most of the time when you've got a form like this, all you really want to do is use it to see data. And if you want to drill down into, like, Malcolm Reynolds's orders, you double click on it and here you go, there are his orders. This is a summary form.
Okay, now how do we go about updating this? What we're gonna do is whenever this form opens, we're gonna run a delete query to delete all the records from the temporary table. And then we're gonna run an append query to take all the current data from the customer worth query and put it in the temporary table. Now, that process can be slow. But it's not something that is gonna hinder the form's performance once the form opens up. The problem is if you put this code in the form itself, in the form's on open event even, if this form has to deal with all that, then this form uses memory resources and it could run sluggishly.
Okay, so don't put the stuff to update the form in the form itself. We're gonna put it in the button that opens the form. Make sense? Okay, plus if you put it in the form here, you're gonna run a delete query, so all these are gonna show delete, and then you're gonna do the append query and it's gonna have to requery this. It's just a pain. Okay, so we're gonna put all that code in the customer list button before the other form is even open. Okay? In fact, one of the last things we're gonna do is open up the form.
Now, like I said before, you can do this with just some queries. You don't have to have any programming at all, but I like to program. So that's what we're doing. Okay, build event for the button, we are in the customer list button click. This is gonna be one of the last things we do is open that form. First thing we're gonna do is set the hourglass to true. You know that spinny thing, it used to be called an hourglass. It used to actually be an hourglass, remember from like Windows 3? I had to use the Google machine to remember the actual hourglass that would sit there and flip. And then later versions of Windows, it got a little more sophisticated. Right now it's a spinning circle.
But in VBA, it's still called the hourglass. So, do command hourglass true, that says turn the hourglass on. Now, the last thing you want to do in here, don't forget, is guess what? Do command hourglass false. Otherwise, the hourglass will stay on. And yes, I've written code before where I forgot to turn it off and your database will still work just fine except your mouse pointer will be an hourglass spinning, or the spinny circle of whatever. Alright, we're gonna tell the user what we're doing. Status, that's my status function that I cover in my blank database template. I think I'll hold a whole separate status video too. Status, processing data.
Okay, and now here's where we put our SQL. Now, if you're not great with SQL, you can make these regular queries out here and then just open them. You could make a delete query and then an append query, and you could do a do command dot open query in here to run them. I, on the other hand, like writing SQL. So, current DB dot execute delete from customer worth temp t. That says delete all the stuff that's in that table, goodbye. Right, you are the weakest link, all the data in your table is so, goodbye.
Current DB dot execute, someone's beaming in. Now we're gonna run our append query, and in SQL, it's insert into. Where are we inserting into? Customer worth temp t. Where are the records we want to get? Well, they're in the customer worth query. Now, if you want all of them, which we do, it's gonna be select star from customer worth query, that's it. That's an append query. It is just: take all the records, select star, all the fields from customer worth query, and put them into the customer worth temp t.
Okay, status, opening form now. The status will still show on the main menu status box right over here, and there's a do events in here which forces it to update the screen. But even though it's a temporary table, if you've got lots and lots of records, even opening the form might take a moment if it's pulling it across the network. By the way, I do recommend keeping this, if you have a split database, keep your temporary table in your front end. That way you now are working with a copy of that data locally. Don't put that in the split back end, and then everything you do now, from now on, with this form is gonna be nice and fast because it's in your local database. Don't forget to compact your front end once in a while too.
Okay, now we've got the do command open form customer list up. By the way, that's control y deletes lines in your VBA editor. It technically cuts them so they'll be in the clipboard too. Okay, and then if you want, you can do, like, a status done. Okay, and then your hourglass, and if you want, like a beep here to let the user know that it's ready. Okay, save it. Debug, compile, once in a while. That's my new favorite catchphrase. Okay, close it, save it, save it, close it, close it, open it up, and now, boom.
And just to prove that it's working, let's take the Deanna Troi and give her a sale. So I'll go, actually I'll go right from here. Deanna Troi, let's go to orders. Let's put in here an order date of today for some stuff, and she's getting, oh, I don't know, a back scratcher. Why? I don't know, I just put something up my head. Mark that paid. Alright, now keep in mind this will not update until you close it and reopen it, and now Deanna Troi shows some sales.
Okay, that's it, and now that form, once it's open, should run much faster than it was before. And it might still take a while, you know, it's got to process all that query stuff in the background one time. You could even, if you wanted to, and this is getting a little bit beyond what I was planning on talking about, but you could put a checkbox here to indicate whether or not you want to refresh the content, or even do it with a yes-no. You know, let's do it with a message box. Let's do it with a message box.
So let's say in here we're gonna ask the user, do you want to update the information right? So if message box, do you want to update the data? Okay, we'll do a VBS no update, right? And if this, if the response to this equals VBS then we're gonna do, let's see here, we're gonna do this right to there. Right, we're gonna turn the hourglass on. We're gonna run that stuff. We're gonna put our end if here. I'm gonna say, let's put the hourglass, because this is really what takes the longest amount of time, we'll put the hourglass stuff there. Okay, and if they say no, it'll just open the form with the data that was in there previously.
Because if you're doing month-end reports, right, and this is all last month's data, you don't have to keep constantly updating this information unless you know that someone went in and put in some sales that they forgot to log or something, you know what I mean? But at least now you have the choice of whether or not you want to update this information. And yes, I got videos on message box too if you want to go search my site for them all. Okay, debug, compile, long time. Well, right? Okay, and now you want to update the data, yes. Okay, now let's just test and make sure it's working. Let's go back to Deanna Troi. Where are you? Let's delete that order. Delete, delete.
Okay, now I'm gonna go in here and say do not update the data. And it's still showing her order in there. Okay, so we know it's working. Let's update it now. Now that order's gone. So it's working. Of course, it's working. I built it. Psha. I'm just kidding. No, I've built some databases with some doozies of bugs before, and yeah, I still find bugs and stuff I wrote 20 years ago. But fortunately, none of my clients have called me from years back when I was a consultant complaining about bugs in their databases. They usually show up within the first, like, 30 days or so of a client actually working with a database. Because if you're a consultant, that's kind of how it works, you build the database. You can test it all you want, but until the client actually starts working with it.
Right, first, you tell them, okay, I want you to run your current system that you got now, whether it's paper or Excel. Do that in tandem with the Access database for at least a week, right, so you can watch them both and make sure, you know, everything's working great. You'll catch a bunch of bugs there, but until they start actually using it, using it as their primary sole, you know, this is now our database, that's when you find the really weird bugs.
Alright, if you like this stuff and you like learning with me, check out my developer lessons. I've got tons of them on my website, and if you want to learn SQL, check out my SQL seminars. I cover bits and pieces of SQL throughout my developer course and even earlier in the expert lessons. One of the things for me as a programmer, I started off programming in Basic and then C, and I didn't learn SQL until years after I was a consultant already, right? I started doing Access databases a few years into being a consultant, and switching from, you know, basic knowledge in Basic and C programming to SQL was a big paradigm shift for me.
So I strongly recommend for any would-be Access developers, learn SQL. It's really really powerful. You can do with one line of SQL what it might take 15 lines of code to do, so it's really good. I got a three-part seminar on it. The first two parts are the ones most people need, right? The basics of SQL and then action queries like what we did today. Those are action queries: delete query, update, insert, make table. Right, part three is optional. That's more advanced stuff. It's all like working on the structure of tables. You can add fields, you can add indexes, all that kind of crazy stuff. So check this out if you're interested.
But there you go. That's gonna do it for today, little two-part series here. Hope your forms run faster, and if you have any questions I want to hear from you. In fact, let me know if you use this technique and you speed up some forms that you were working with before that were running really slow and now they're working fast. I'll accept a thank you, and let me know. I want to hear from you. Tell me, you know, tell me how much time I'm saving you with this technique.
Alright, that's gonna do it. That's a TechHelp video for today. Hope you learned something. Live long and prosper my friends. I'll see you next time. Bye.
TOPICS:
- Sluggish form issues and performance
- Creating a temporary table
- Make-table query creation
- Change form's record source
- Preventing user data modifications
- Delete query for temporary table
- Append query for updating temp table
- Use of VBA for form events
- Hourglass command in VBA
- Status function usage
- SQL execution in VBA
- Temporary table best practices
COMMERCIAL:
In today's video, we're continuing with the topic of speeding up sluggish forms in Access. This is part two of our series. You'll learn how to create a temporary table from a query, update it efficiently, and change your form's data source. We'll cover how to lock down the form so users can't make changes and demonstrate running queries with VBA to delete and append data efficiently. We'll also touch on using message boxes and manage your form's performance with strategic query placement. Reduce your form's load time and keep things running smoothly. 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 main issue addressed in this video tutorial?
A. How to create a new table in Access
B. How to create a split database
C. How to speed up a sluggish form in Access
D. How to add new fields to a query
Q2. What is the purpose of creating a "temporary table" in the database?
A. To store permanent data records
B. To speed up form performance by storing non-permanent, easily updated data
C. To back up all the data from the database
D. To share data between different databases
Q3. Which query type is used to create the temporary table based on the customer worth query data?
A. Select query
B. Update query
C. Make-table query
D. Crosstab query
Q4. Why should the temporary table be created in the front end of a split database?
A. To keep data secure from users
B. To minimize network traffic and increase local processing speed
C. To allow multiple users to edit data simultaneously
D. To store historical data on the server
Q5. What should be set to "no" in the data settings of the form to prevent users from changing the temporary table data?
A. Allow filters
B. Allow printing
C. Allow additions, deletions, and edits
D. Allow form resizing
Q6. What VBA command is used to give users visual feedback when data is processing?
A. DoCommand.ShowProcessing
B. StatusBar.ShowProgress
C. DoCommand.Hourglass True
D. Show.LoadingScreen
Q7. What SQL command is used to insert data into the temporary table from the customer worth query?
A. Update
B. Delete
C. Insert Into
D. Select Into
Q8. Why is it recommended to debug and compile the VBA code occasionally?
A. To translate the code into multiple languages
B. To ensure the code is running without errors
C. To increase the overall file size
D. To enable real-time collaboration
Q9. How can a message box be used in this context?
A. To alert users whenever an error occurs
B. To display detailed records from the temporary table
C. To ask users whether they wish to update the form data
D. To automatically refresh the database connection
Q10. What is a major advantage of learning SQL for Access developers?
A. SQL allows for creating multiple interfaces for users
B. SQL can automate the entire Access program without any user input
C. SQL can condense complex data operations into shorter, more efficient code
D. SQL eliminates the need to use any other form of programming language
Answers: 1-C; 2-B; 3-C; 4-B; 5-C; 6-C; 7-C; 8-B; 9-C; 10-C
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 brings you guidance on improving the performance of a slow-loading form in Microsoft Access. I am your instructor, Richard Rost, and today's lesson is the second part of our series on sluggish forms. If you've ever experienced a form that takes forever to load and continues to run slowly, then you're in the right place. Be sure to review part one before continuing.
Today, we'll work with our database, which includes a customer worth query. This query, with its extensive calculations, is causing our associated form to perform sluggishly. To address this issue, we will create a temporary table with the same fields as the query. The idea is that every time the form loads, it only needs to update the temp table, which will expedite the process.
Let's start by constructing the temporary table using a make-table query based on our cumbersome query. This will transfer all the data and fields from the customer worth query into a new table we'll call "customer worth temp table." Including "temp" in the name signals to both me in the future and to others that the data here is not permanent and can be modified.
After running the make-table query, you'll find that we now have all the data from the query stored in a table. These are actual numbers now, not ongoing calculations, which means it will run noticeably faster. Although you may not need this query anymore, I recommend saving it for reference.
Next, we need to change the record source of the form to the customer worth temp table instead of the original query. This small adjustment should make the form load faster and run more smoothly. You may not notice much difference with just a few records, but with larger datasets, the improvement can be significant.
At this point, inform users that no changes can be made directly in this form to avoid misunderstandings. Set the data options to disallow additions, deletions, and edits, since this is a temporary table and not meant for direct user modification.
To keep the temp table updated, we'll implement a process to run a delete query to clear all previous records whenever the form opens, followed by an append query to populate it with current data. Running these queries outside of the form's code ensures the form remains responsive, even though the update process can be slow behind the scenes.
It's important to integrate this update code with the button that opens the form, rather than embedding it within the form itself. This separation prevents the form from consuming excessive resources and ensures it opens promptly.
For those who enjoy programming, as I do, we'll handle running the necessary delete and append queries using SQL code. However, using regular queries is also an option for those less comfortable with SQL coding.
Consider placing your temporary table in the front end of a split database to enhance speed by working with a localized copy of the data. Also, don't forget to compact your database periodically to maintain optimal performance.
Finally, consider adding a prompt asking users whether they would like to update the data each time the form is accessed. This option is practical, especially during routine reports, and provides the flexibility to update only when necessary.
For additional insights and to hone your skills, explore my developer lessons and SQL seminars, which cover a wide range of topics including basic and advanced SQL functionalities. Learning SQL is crucial for anyone aspiring to develop in Access, as it allows you to accomplish complex tasks with succinct, efficient code.
I hope this two-part series helps improve the speed of your forms. I would love to hear if you implement this technique and how it impacts your database performance.
You can find a 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 - Sluggish form issues and performance
- Creating a temporary table
- Make-table query creation
- Change form's record source
- Preventing user data modifications
- Delete query for temporary table
- Append query for updating temp table
- Use of VBA for form events
- Hourglass command in VBA
- Status function usage
- SQL execution in VBA
- Temporary table best practices |
