Cache Locally
By Richard Rost
3 years ago
Cache Data Locally to Improve Report Performance
In this Microsoft Access tutorial, I will teach you how to cache (copy) remote data to your local system to improve the performance of your read-only forms and reports.
Karen from Troy, Michigan (a Platinum Member) asks: I have a series of reports that I have to run for our month-end sales. These reports take forever to run. I have several slightly different reports that are all based on the same set of sales data. It literally takes my whole day waiting for these to process. Is there any way I can speed this up? The rest of the database runs perfectly fine day-to-day. It's just these reports that are a nightmare.
Members
Members will see a walkthrough of how to set this up. We'll split a database, create the local temp table, create the needed queries, write the necessary VB code, and watch the whole thing work. Magic? Yeah, maybe.
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!
Prerequisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, cache data, improve performance, read-only form, report, remote data, local system, action query, speed up reports, slow reports, speed up forms, slow forms, cache data locally, does access have a cache, Cached Data in Access Tables
Subscribe to Cache Locally
Get notifications when this page is updated
Intro In this video, we talk about how to speed up slow reports in Microsoft Access by caching temporary data locally. You'll learn why running reports in a split multi-user Access database can be slow, especially when working with large datasets over a network, and how local temporary tables and action queries can help reduce report processing time. We'll cover when this method is appropriate and discuss best practices for setting up temp tables to improve performance in your month-end or periodic reports.Transcript Today we're going to talk about speeding up your reports. I'm going to show you how to cache temporary data locally in order to improve the performance of your reports and maybe some forms in your Microsoft Access database.
Today's question comes from Karen in Troy, Michigan, one of my Platinum members. Karen says, I have a series of reports that I have to run for our month-end sales. These reports take forever to run. I have several slightly different reports that are all based on the same set of sales data. It literally takes my whole day waiting for these to process. Is there any way I can speed this up? The rest of the database runs perfectly fine day to day. It's just these reports that are a nightmare.
I talked to Karen a little more about her setup. She's running a split multi-user Access database, just Access, no SQL Server. It's a typical small business installation.
One of the problems with a split database like this, just using Access, is that if your computer needs to query the sales from last month, your PC still has to get all of that data from the Access database on the server machine. It's not SQL Server, so it literally has to pull down all of the records in your sales table. If you've got 10 years' worth of data in there, it's got to pull it all down over the network wire, all of it. Then your local PC figures out which records it needs. If you're doing the same thing on multiple reports, it has to pull that data down all the time. You get 10 reports to run based on that data. It's going to pull that data down 10 different times, each time you run that report.
One of the major benefits of a database server like SQL Server is that the server actually does the crunching on its end. It will chew through all 10 years of data and just send you what you need from last month. It will definitely speed up the process and add extra security, but we'll save SQL Server for a different video.
Karen says her database runs fine normally, so I don't think it's a problem with her network speed because just adding records, deleting records, looking through stuff, that all runs fine. She says it's just these month-end reports.
For the rest of you, if everything about your database runs slow, it could be your network speed. I recommend you run Access on a gigabit wired Ethernet network. Don't try running Access over a wireless network. You're not going to be happy with the performance. If you've got older 10 Base-T or 100 Base-T networking, it's time to upgrade if speed is a problem. Every machine should have a gigabit network adapter, a switch, and you should all have gigabit cabling, Cat 5e at a minimum, Cat 6 or Cat 7.
If you've got one computer on the network, you always have that one guy who complains the database is too slow, and he's got a fast enough computer. It could be a bad network run to his computer. Check the cabling. I did a lot of time troubleshooting this kind of stuff in the 90s, and I do not miss it.
There's a really good network tester. It's free. It's an app you can download. It's made by Toadusoft. You basically install it on each PC, and then you tell it what your server folder is. Then when you start it, it runs packets back and forth between the workstation and the server and it tests your network speed.
If you've got a gigabit Ethernet setup, the theoretical maximum, basically, you're never going to get it, is a thousand megabits per second. But you can see here, this is mine, I just ran it. You should be getting at least 700 reading and at least 300 writing. If it's slower than that, then contact a network professional. Have them come out and figure out where the problem is. It could be a problem on the computer, it could be the network run, whatever.
Now, how about networking? Karen said that the only time she has a problem is running that month-end report. Again, it's because her local Access database has to go out to the server, pull down all of the sales data from all time, and then figure out just what it needs from last month.
So if you're not going to upgrade to SQL Server and you want to just keep with your Access, what you're going to do is you're going to download all of those records once from the server, and then you're going to create a local table.
If you're dealing with customer data, for example, you're going to create a customer temp T. If you're dealing with sales data, your order table, order details, that kind of stuff, you're going to create temporary tables for that stuff, just the structure of the table. Then you're going to make some action queries. First, you're going to delete whatever's in that temporary table. Then you're going to run append queries to copy the data down from the server to your local database.
Now what you're going to do is rewrite your reports so that instead of pulling off of the data on the server, they're going to pull off of those local temporary copies. What will happen is, especially if you're running the same thing 10 times, you'll pull down that data once. Then you can run all your reports locally off of that, so your local computer is going to churn through that stuff.
If you set up your action queries correctly with the right criteria, then your local temporary tables will only have the data that you need. So even though the queries once have to pull down, for example, 100,000 records, what you'll have left in your temporary tables after the queries run is just the data that you need. Your reports will run much, much faster because they're pulling off of local data, and the data has already been filtered.
When you're all done with your month-end reports, make sure you delete it and compact your local database because it's going to have lots and lots of empty space in it from that.
This setup is only good if you don't care that the data may change in real time. This is good for doing last month's reporting or yesterday's stuff. If it's a situation where you need real time data, if people can make edits right now and that has to show up in your report, then this is not a good setup. Do this for stuff that's in the past. If people add an order today and that matters, then don't do this. It does not need to be up to the minute.
What are the specifics? How do you do this? If you don't know how to split your database, I have a video for that. I'll put a link down below. There it is.
As far as creating the action queries go, I have several different videos. You can start there with my action queries. They will teach you delete queries and append queries. Those are the two that you need. By using that, you can copy the data down to your local drive and then just base your reports off of that.
That, in a nutshell, is the theory behind how you do it.
Want to see specifics? In the extended cut for the members, I'm going to run through a sample. We'll take a split database right here. We'll create the customer temp T. I'll make the queries to delete whatever data is in there and then copy down from the customer table to the temp T. Once all that's done, we'll run the VB code to tie it all together and then you can run your individual reports off of that temporary data. That's all covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos, so check it out.
If you want to learn more about action queries, I start covering them in my Microsoft Access Expert level 13 class. We go over update queries and all kinds of other stuff. I'll put a link to this down below as well.
That is going to be your TechHelp video for today. I hope you learned something, my friends. Live long and prosper. I'll see you next time.Quiz Q1. What is the main reason reports run slowly in a split multi-user Microsoft Access database without SQL Server?
A. The computer must download all the records from the server before filtering locally
B. The reports have too many complex queries for Access to handle
C. There is limited hard drive space on the user's computer
D. Access databases are always slow by default regardless of setup
Q2. How does SQL Server improve report performance compared to using only Access with a split database?
A. SQL Server transmits all data for processing by the client machine
B. SQL Server processes the query on the server and sends only the required data
C. SQL Server compresses the database to speed up transfers
D. SQL Server disables multi-user access to prevent conflicts
Q3. What type of network setup is recommended for optimal performance with Microsoft Access databases?
A. Wired gigabit Ethernet network
B. Wi-Fi 6 wireless network
C. 100 Base-T Ethernet network
D. Bluetooth network
Q4. If one user experiences slowdowns in a generally well-performing Access database network, what is a potential reason?
A. The user's computer may have a faulty network cable or adapter
B. The server does not have enough RAM
C. The Access software is incompatible with Windows
D. The user is working in a different time zone
Q5. What is the function of local temporary tables in speeding up reports?
A. They store a filtered copy of data needed for the reports, reducing repeated network transfers
B. They permanently store sales data to replace the master table
C. They backup the entire database automatically
D. They replicate the server environment on each client for redundancy
Q6. What are action queries and which types are most relevant for local temporary data caching in Access?
A. Queries that automate UI styling; select and make-table queries
B. Queries that modify data; delete and append queries
C. Queries for advanced search; parameter and crosstab queries
D. Queries that only read data; select and pass-through queries
Q7. After running reports based off local temporary tables, what should you do to the local database?
A. Delete temporary data and compact the database
B. Upgrade to SQL Server immediately
C. Restore from the server backup
D. Reinstall Microsoft Access
Q8. In which situation is the local caching and temporary table technique NOT recommended?
A. When you need real-time up-to-the-minute data
B. For month-end historical reporting
C. For analyzing past week's sales
D. For generating static archival snapshots
Q9. What minimum category of Ethernet cabling is recommended for reliable gigabit network speeds?
A. Cat 5e or higher
B. Cat 3
C. Cat 4
D. Cat 2
Q10. What is the main advantage of re-writing your reports to use local temporary tables in Access?
A. Reports run faster because the data is already filtered and on the local machine
B. The database automatically normalizes all records
C. It keeps your server's hard drive less fragmented
D. It prevents users from updating data by mistake
Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-B; 7-A; 8-A; 9-A; 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 tips for speeding up your reports in Microsoft Access by caching temporary data locally. This approach can dramatically improve the performance of reports, as well as some forms, especially in split multi-user environments.
The inspiration for this lesson comes from a question about month-end sales reports that take an extremely long time to process. While daily interactions with the database—such as adding, deleting, or browsing records—work perfectly well, the trouble starts when it comes to generating large, comprehensive reports. This is a common scenario for many small businesses using split Access databases without SQL Server backing.
Here is the main issue: With a split database setup in Access, when you run a report querying, say, last month's sales, your local machine pulls an entire set of records from the sales table stored on the server. That means if you have ten years' worth of sales data, your computer will transfer all of that information across the network to process it locally, filtering for just last month's entries. If you run multiple reports, each one repeats this process, resulting in significant delays and excessive network usage.
Databases like SQL Server are designed to handle this situation far more efficiently. The server does all the heavy lifting—scanning through years of data—then sends only the necessary results to the client machine. This method is much faster and more secure, but today I am focusing on solutions you can implement within Microsoft Access by itself.
If your day-to-day database use is fast but only your reports drag, it likely points to this mass data retrieval as the bottleneck—not an underlying network or hardware problem. However, if everything feels slow, I strongly recommend checking your network setup. For best results with Access, use a wired gigabit Ethernet network. Wireless connections or old cabling will slow you down. Ideally, everyone should have a gigabit network adapter and high-quality Cat 5e or better cabling. Sometimes, just upgrading an old switch or fixing a problematic cable run can make all the difference.
A handy diagnostic tool you can use is from Toadusoft—a free app that measures the read and write speeds between your workstation and server. While the theoretical maximum for gigabit Ethernet is 1000 megabits per second, a realistic performance would be at least 700 megabits per second for reading and no less than 300 megabits per second for writing. If your speeds are lower, consult with a network professional to identify and fix the issue.
If network speed is not the issue, and you are not planning to move to SQL Server, there is still a solution available right in Access. The strategy involves downloading all the necessary records once, storing them in a local temporary table, and then having your reports reference this table instead of repeatedly fetching the same data from the server.
Let's break down the process. When you need to work with records—customers, orders, sales details, and so on—create local temporary tables that have the same structure as your main tables. Next, use action queries (specifically, delete and append queries) to clear the temporary tables and then copy just the relevant data from the server over to your local database. After this one-time download, you can run multiple reports quickly, working off the filtered local data.
It is important that your action queries have the proper criteria setup, so you only pull down what you actually need. For example, if you only need last month's orders, your local temp table should reflect that exact dataset. This way, you minimize the amount of data transferred and maximize performance when generating reports.
After you complete your reporting tasks, be sure to delete the data from the temporary tables and compact your database. This removes empty space left behind by large batches of deleted records.
Keep in mind that this temporary caching approach is best for historical or fixed data—like last month's reports or yesterday's sales—where the data does not need to reflect real-time updates. If reports require up-to-the-minute changes, this method will not be suitable, as any new data entered after you pull your copy will be missing from your reports.
For those unfamiliar with how to split a database or set up action queries, there are resources available. I have tutorials covering split databases and others focusing on action queries—specifically delete and append queries—which are key to automating this temporary caching process.
In summary, the concept is to pull all the necessary data from the server just once, store it locally, and then generate your reports based on that snapshot. This can make an enormous difference in report speed and system usability.
If you want to see an exact implementation of this technique, the Extended Cut for members covers a live demo where I walk through creating a split database, designing temp tables, crafting the necessary action queries, handling the VBA to automate the process, and running reports based on this local data.
Silver members and above can access all of my Extended Cut videos for an in-depth look. For more on action queries and broader topics, my Microsoft Access Expert level 13 class is a great place to start.
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 How network speed affects Access report performance
Issues with split Access databases and report queries
Downloading all report data multiple times over a network
Benefits of SQL Server for report processing
Assessing if report slowness is a network issue
Recommended network hardware for Access databases
Using network speed testing software (Toadusoft)
Understanding gigabit Ethernet speed requirements
Troubleshooting slow network connections
Creating local temporary tables for caching report data
Setting up delete and append action queries for local copies
Modifying reports to use local temporary data
Filtering temporary tables for relevant report data
Improving month-end report speed with local caching
Limitations of caching for real time report data
Maintaining and compacting the local database after reports |
