Quick Queries #90
By Richard Rost
2 days ago
Speeding Up Report Loading, Query Optimization Tips In this lesson, Quick Queries #90, we address several practical Microsoft Access topics including why Access Reports might run slow and ways to speed them up, password and pass phrase strategies, considerations for 32-bit versus 64-bit Access, best practices for importing Excel data, handling date formatting, the importance of table normalization, and moving Access queries to SQL Server. We will also discuss the benefits of select case versus if then statements in VBA, handling composite keys with nulls, and the value of clear documentation for Access projects. Several questions from users are answered with practical advice and real-world examples. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, AccessLearningZone.com, slow report loading, speeding up reports, report performance, pass phrases, password manager, 32-bit vs 64-bit, importing Excel data, date formatting issues, table normalization, moving queries to SQL Server, Kanban board database, composite key duplicates, temporary tables, dlookup performance, index optimization
Intro In this lesson, Quick Queries #90, we address several practical Microsoft Access topics including why Access Reports might run slow and ways to speed them up, password and pass phrase strategies, considerations for 32-bit versus 64-bit Access, best practices for importing Excel data, handling date formatting, the importance of table normalization, and moving Access queries to SQL Server. We will also discuss the benefits of select case versus if then statements in VBA, handling composite keys with nulls, and the value of clear documentation for Access projects. Several questions from users are answered with practical advice and real-world examples.Transcript Ever open a Microsoft Access Report and wonder if it's actually loading or just taking a coffee break?
Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today we're going to talk about why Access Reports can run painfully slow sometimes and what you can do to speed them up. We'll also talk about passwords and pass phrases, 32-bit versus 64-bit Access, importing Excel data safely, date formatting gotchas, table normalization, and moving Access queries to SQL Server.
Alright, let's jump in.
Starting off today, we have Donald saying that I mentioned using Control Alt Delete to lock my computer, but there's actually a faster, easier shortcut: Windows key L. Alright, let me try that one last second here. Yeah, that works. That's just one of those things I learned way back in the day, on a Tuesday. The first thing I learned was Control Alt Delete and then lock workstation, so Control Alt Delete and then space is what I've been doing for 20 plus years. That's going to be a hard habit to break. So yes, there's a quicker one. Stirring it now, that's a whole different story. I appreciate it, and please do keep the shortcuts coming. Maybe one of these days these new shortcuts will stick in my brain. Thanks, Donald.
Next up we have Chuck. He posted a question about whether he should be using nested if then statements or a select case, sometimes called a switch, to control form behavior like showing and hiding tabs. This one turned out to be partly a syntax issue, but it sparked a bigger question about which approach is actually better. This is a great question because everyone runs into this once they start writing more VBA.
Short answer, there's nothing wrong with if then statements, the if then else if else. I use it all the time, especially when you've got multiple lines of code in each condition. That's exactly what Chuck had here: toggling tab visibility. That's the perfect use case for if then. He had a little bit of a mistake in here that I pointed out, but that's not really the issue of the post here. I showed him it's the same thing, really. It's just a matter of which you find easier to write.
Here's a typical if then: if this equals this, then do these lines and so on else if that, else if that. The same thing written as a select case statement looks about the same. What I find select case handy for is for one-liners. That's when they really come in handy because it can condense a whole bunch of code down.
Here's the select statement, and here's the same thing written out with if then else if else if else it and so on. Of course, there are some videos to watch about select case. Yeah, I beat Kevin to it. But really, don't overthink it: VBA doesn't care which one you use. It's a readability thing. Pick the one that makes your code easier to read six months from now. That's the real test.
If you want to learn more, of course, I have videos for it. Here's one about if then, and here's select case.
Now we have Peter, a gold member. He shared his approach to passwords and I'll put it on the screen. You can pause it and read that whole thing if you want to.
He's not a big fan of long random strings. He prefers using memorable phrases like lines from poems combined with a short site-specific word. He's basically asking: is that approach just as good as random passwords, and are we overcomplicating password security? That's a great discussion because there's a lot of dogma out there about passwords. Some people say it's got to be uppercase, lowercase, a number, and three special characters. Those people make passwords a whole lot more complicated than they need to be. So Peter, you're not wrong.
Now, long memorable pass phrases are actually one of the best approaches: something you can remember that's long enough with some variation per site, that's solid as far as I'm concerned. Password complexity doesn't really matter because you're not going to have your password brute-forced. Even if you had just eight characters and they were all lowercase letters, someone would need, even with a fast computer and dozens of computers, weeks to crack that. I watched a YouTube video myself a while back on password security. That just doesn't happen. Hackers aren't going to brute-force guess your password because most websites have lockouts in place. Even mine, if you type in the wrong password five times, your account gets locked first for like an hour, then for a day, and then it's locked period until you contact me. Banks do the same thing. Most websites are going to lock you out after a few bad attempts. So, real world password brute forcing doesn't happen.
Most breaches come from phishing, where the bank sends you an email that looks like it's your bank, but it's actually a hacker, and then you type in your password and now they have it. It's social engineering or using the same password on multiple sites. That's stupid. Don't do that. You need a unique password for every site. Keep it in your password database, or like I use, Google Password Manager. It leaks sometimes, but if a data leak happens and one website gets hacked and all their passwords get out, well, guess what? If you use the same password on multiple sites, then you have a problem. But it's not going to be someone brute-forcing your password on a website or guessing it.
So my advice: if you like long random strings with multiple things like special characters and numbers, fine, use those. If you like that, use those. If you like a password manager, use a password manager. If you like long phrases like Peter suggested, great. The most important thing is don't reuse passwords across sites and be very careful when you type in your password somewhere. That's one of the reasons why I like Google's Password Manager: because it lives in your browser and it looks at the domain, and if it doesn't see that it's a domain that's in your list of passwords, it doesn't give you the password. If it does see it, it puts it in there for you. That's my recommendation.
Next up, we have Thomas, a long-time student, who asked a really good question about 32-bit versus 64-bit Access. He says, if a database was built in 32-bit and you're having trouble running it, why would installing the 64-bit runtime help instead of just sticking with 32-bit? The thread turned into a long, big discussion about mixing environments and when it actually works. I'll put a link to this one down below. There's lots of good stuff in this thread, you might want to read the whole thing.
This is one of those topics that confuses a lot of people, and honestly, Microsoft doesn't make it super clear. Here's the deal: if you already have 64-bit Office installed, you cannot install the 32-bit Access runtime alongside it. That's the key point. So your only compatible option in that situation is the 64-bit runtime.
Now, Thomas and Dave also both brought up something I've seen too: trying to mix 32-bit and 64-bit on the same machine. Yes, it can be done with some hacks, but like Donald said in the thread, it's not something you want to rely on. It's fragile. There is Donald again. It's one of those things that works until all of a sudden it doesn't, and I don't like that situation. So the best practice is simple: first, test the database itself in 64-bit. A lot of them work just fine, unless they're using old API calls. If it breaks, then either you update the code for 64-bit or you go all in on 32-bit Office for that environment. Don't try to Frankenstein both together on the same machine unless you like pain. I've tried to do it before. It's not fun. You can still get the 32-bit version of Office. For 99 percent of people, 32-bit Word and Excel and PowerPoint work just fine, unless you have gigantic monster spreadsheets.
Speaking of gigantic, why don't you do me a gigantic favor? Hit that like and subscribe. Thanks.
Next up, we have Larry, a long-time, 10-year student. Thanks for sticking around, Larry. He ran into a weird issue importing Excel data into Access. Even when he selects short text during the import, Access keeps turning those fields into long text and it's causing problems later. The thread had a few different theories, including a possible bug. The bigger question is: how should you really be handling imports like this? This is one of those classic Access import headaches. Honestly, Larry, your instinct to fix it after the import is fine. That's what I would do.
In fact, Thomas had the best advice in the thread: don't rely on the import wizard to get everything right. It guesses, and sometimes it guesses wrong. Donald also pointed out that the wizard itself can act a little funky depending on your version, especially if you're running some of the beta builds. What I recommend and what I do in my own databases is import everything into a staging table first, make all the fields long text, and don't try to force data types on the way in. Just get it all into Access. Then once the data is in Access, you control the conversion. You decide what becomes a date, you decide what becomes a number, you decide what you can truncate to short text, and so on. This gives you way more control, avoids weird import bugs, and makes your database a lot more stable in the long run. So yeah, import it all as text, figure it out later. That's what I do.
Tom, one of my gold members, had a question about working with dates in Access. He's trying to use functions on dates formatted like this. He initially typed in that, but he corrected it to this. Then the thread quickly turned into a discussion about formatting versus actual date values. Once he switched approaches, everything worked fine. This is a common gotcha in Access. The big thing to understand is there's a difference between how a date looks and what the date value actually is.
If your field is a true date/time value, you can format it however you want to display it. But once you format it, now it becomes text. Now you're dealing with a text field. I cover this in a couple of different videos. Once you format it as text, then you lose all the built-in date functionality, and that's what was tripping Tom up.
John made a great point in the thread too. You can use functions like year and month on real date fields. I have videos on those too. You can sort and group properly. Kevin pointed out the ISO date format. In case you're not familiar with it, it's this guy. It's one of my life's goals to get everybody using the ISO date format. It works all around the world. It prevents international incidents. It avoids confusion. It makes me happy. I've been writing this on everything: checks, and when I go to fill out paperwork at a doctor's office. They look at me like, oh, okay. Everyone gets it though. There's no ambiguity. You know what that means. If it's the other way, month-year, and you've got someone in Europe looking at it, why? So, the rule is simple: store dates as real dates. Once you format them, you lose the date value. Then you have to use other tricks to massage the date value back out of the text. Format is for display only. Don't store them as text unless you absolutely have to because of some weird format.
I've actually seen stuff come in like this format, and that's just a text format. You can convert that, but you have to use string functions. You take the left two, you take the middle three characters starting at position three, and you take the right four, and then you can use different functions to put that back together into a date. But now you're doing massaging that you don't normally have to do, only if you're importing something like this, like from a text file or some old DOS systems used to do.
Next up, we have Mitchell, gold member. He started with a design question about whether to use one table or two for houses and people. He ended up refactoring his whole database into a proper one-to-many setup. Then he hit the next challenge: how do you handle cross-table logic, like checking if everyone in a household is under 55 and keeping everything in sync? Again, this is another long conversation. I'll put a link to this one down below if you want to read through the whole thing. There's a lot to this.
But the takeaway here is that it's a fantastic real world progression. Mitchell basically went from, should I use one table, to, okay, I split it properly, now how do I make it all work together? That's exactly the journey that a lot of people have when it comes to figuring out how to put your data properly into the tables. The first big win is splitting the house and people into two tables. That's absolutely the right move. Like Juan and Matt pointed out, that's the difference between Excel thinking and database thinking. Even Mitchell said he ended up deleting hundreds of lines of code, which is always a good sign.
Now, you have the cross-edit part. This is where a lot of people overcomplicate things. You don't always need to loop through record sets like Mitchell started doing. You can use functions like dcount, dmax, dmin, and those often answer the question instantly. For example, is there at least one person 55 or older? That's a simple query. You don't have to loop through everybody in the table. Again, this entire thread is definitely worth a read. I'll put a link to this down below.
But the takeaway is: normalize your data first, then use queries to answer questions about it, and only drop into loops like record set loops when the logic really demands it. Try to keep it simple, let Access do the heavy lifting. Later on when you move up to SQL Server with this, you'll be much happier if you've got SQL handling it with a query than if you have to write a record set loop and loop through records that are up on the server. Then it can get really complicated.
Next up, we have a visitor. Yes, I do have a visitor form on my website that anyone can post on. You don't have to be a paid student or a member. It's Rafael. Rafael asked a really interesting question about my fitness database. He said with all the different videos and features being added over time, he was wondering if there's a single overview or a summary that explains what the whole application is supposed to do, especially if you want to hand it off to someone else who's not a developer.
That's a great question, Rafael. You're thinking like a developer who actually has to ship a product, not just learn it. The answer is no, there really isn't a formal overview of the fitness database, and that's kind of intentional.
Like Juan said in the thread, and here's what Juan said, and I'm going to show you a picture that he provided in just a second. The fitness database really isn't meant to be a finished product. It's a teaching tool. It's me building things piece by piece, and it's kind of like a bucket of Legos. I love the Jordy and the MacGyver reference. One video might be about tracking workouts, the next could be about food, the next could be about reporting. It grows organically, and a lot of it is based on feedback I get from you all. And a lot of it is what I kind of want the database to be myself.
Now, where you're absolutely right is if you want to hand this off to someone else, you can't just give them the raw project. You have to take the pieces that you need, clean it up, simplify the interface, build your own help system around it. Yes, writing documentation is usually as hard as, if not harder than, building the database itself. That's why good commenting in your code is necessary, and every developer learns that one the hard way.
As far as the databases that I build (I have some explanation here - I'll put the link to this down here too), oh, I have a help system video that Matt pointed out, the databases that I build are not meant to be finished products. Even the seminar templates and course templates that I have, everything that's on my website, none of it is really meant to be a finished product. I teach this stuff. My goal is to teach you how to use this stuff. So I'm going to show you, like I say in my videos, all the different pieces, all the different Legos; it's your job to put them together into something that you can use. Here are the tools for your toolbox. You get to work. I don't sell software; I teach you how to build it.
Next up, Joseph, gold member, asked a great forward thinking question. If you build your database using Access queries and later move your tables to SQL Server, will everything break, and do you have to rewrite all of your queries as SQL scripts?
That's a really smart question. The good news is no, your database isn't going to just explode the second you move to SQL Server. Most of your basic select queries, forms, and reports will continue to work just fine when you link those SQL Server tables back to Access. In fact, Access and SQL Server work very well together. It does a good job at saying, OK, I know what you are trying to do with your Access query, let me see if I can do it better connecting to SQL Server. So it will actually optimize some of that stuff behind the scenes without you having to change anything.
Where things get wacky is with the more advanced stuff. If you're trying to use Access specific features and functions, like dlookup, dsum, or other VBA functions inside your queries, those don't translate to SQL Server. Same thing with action queries; well, some action queries (update, append, etc.), especially if SQL Server has any constraints or triggers involved, can be problematic. If you decide you want to move the logic into SQL Server itself, then yes, you'll have to rewrite that in TSQL or SQL Server's version of SQL, but that's a choice, not a requirement.
You can take your queries that you use a lot, especially the ones that pull lots of data, rewrite those as views on your SQL Server, and it will probably run a whole lot faster. Then you can either use a pass-through query, or you can link that as a table. There are lots of things you can do. I cover a lot of this in my SQL Server course.
The key takeaway is: start simple, move your tables, test everything. You'll probably find that most stuff, including your existing queries, will work just fine. Fix what breaks, if anything breaks. Don't assume you have to rebuild the whole thing from scratch. I'd say most of it will come along just fine as it is, but you can't know for sure until you try it. So always keep your backup so you can go back in case it doesn't work.
I've moved dozens, if not a hundred or so, databases for myself and for clients from Access to SQL Server, and very, very few of them have had problems that have caused me to have to stop and pull my hair out. Once I learned SQL Server, obviously in the early days when I was still learning, I had lots of problems, but that's what builds character. That's how I know what I know today, from pulling my hair out 20 years ago. But yes, great question.
Heading over to YouTube. A couple of days ago I posted that I'm building a Kanban board database. Kanban, Conbot. I've heard a couple of people try to tell me how to pronounce it, but whatever, Kanban.
Shadow Dragon posted that he never knew they were called Kanban boards. Neither did I. Somebody asked me if I could do a Kanban board, and I was like, what? So I had to Google it and I figured out what it was. And yes, I've done task progression or sales progression type databases before; I just never knew what they were called.
For everyone who's wondering, yes, I was hoping to have this done yesterday. I'm still working on it. Should hopefully be ready tomorrow, but don't quote me, but it's coming very soon. It's coming soon, relax. That's why I hate giving dates because I never can keep them. That's why this Friday's quick queries is now coming out on Sunday, and the Kanban board video isn't even out yet, just a preview. I'm still getting questions about it.
This user says that I don't have a whole bunch of information on there. Let's say you want to have the prospect phone number showing. How would you arrange that if possible? Well, yes, it's possible. I'm just building a simple one here. Let me show you what I have for those of you who haven't seen it yet. Here's the preview that I put up. I have the sales process: lead, prospect, customer, completed. I've just got the customer's first and last name in here. You could put whatever information in here that you want. These are just list boxes. If you want this to be three columns across and you want to have name, phone number, and the shoe size, whatever you want to list in here, or you could do something where you click on this and then down below it shows more information or double-click on the list box and it opens up the customer record. You could do whatever you want. I'm just showing you how to set this up and later on how to move stuff between the different boxes.
Xavier said you could even show how to change the mouse shape using some Windows APIs when dragging and dropping items. I didn't have this in the preview, but I figured out how to do this afterward without even using a Windows API. This is the developer lesson version. Watch: ready, click, and look at that. You've got the horizontal mouse thing.
Next up, we have Brian asking: my Access report takes forever to load. What can I do to speed it up? That's the classic "why is my report so slow" question. This is a good one because there's not usually just one problem. It's death by a thousand cuts.
First, the biggest rule is don't load data you don't need: filter your report before it opens. Don't open a report on 100,000 records if you only need 20. So make sure your report is based on a query that's properly filtering out stuff you don't need. Don't pull in fields you're not using. Every extra field has to come across the network and get processed. If you've only got first name, last name, phone number on the report, don't be pulling in all the long text note fields and other stuff you don't need on the report. Cut that stuff down. That's why I usually teach people to use the asterisk in the queries to bring in all the fields when you're first developing it, but then later on, when it comes to the optimization step, you want to get rid of those asterisks and only bring in the fields that you need.
Bring only what you need to survive. It's my industrial strength hair dryer and I can't. Anybody else excited to see Spaceballs, the new one? I am. I'm sad we have to wait till 2027.
Next, look at your queries. If you have nested queries on top of other queries, Access is doing a ton of work behind the scenes. Flatten things out where you can. Simplify your joins. Make sure your key fields are indexed. That's a huge one. Index any fields that you're searching or sorting on. That will dramatically improve performance, even for foreign key fields. If you have a customer table and an order table, you want to make sure that customer ID is indexed in both tables. Index no duplicates as your primary key, index duplicates as your secondary, as your foreign key.
Another one: calculated fields. If you have a bunch of expressions or VBA functions running for every row, those add up fast. Sometimes it's better to pre-calculate that stuff in a query instead of doing it in the report. A lot of times, aggregate queries will run faster than putting domain aggregate functions inside queries. Do an aggregate query before you put a dlookup in a query. Those always slow things down. Dlookup, dsum, those can absolutely kill performance in reports because they run once per record. So if you have a dlookup in your report field and it's got to run that on 100 records or a thousand records, it's doing a thousand dlookups. You're better off putting that in an aggregate query.
Don't forget the environment. If your backend is on a slow network or a shared drive that's lagging, that's going to hurt no matter what. Same thing if your database is huge and you haven't compacted it in a while.
So, short version: limit your data, simplify your queries, avoid per-row calculations, make sure your environment isn't the bottleneck. Fix a lot of that stuff and all of a sudden those slow reports start getting a whole lot faster.
One of my favorite tricks is if it's a report that you're running a lot and it's based on the same data, then pull that data down to a temporary table first. I had a customer that was doing their month-end report once a month, but they had to run multiple different reports on the same set of data. For each of those ten reports, they were pulling all of that data down for each report every single time. Their month-end took six hours to run because all these slow queries were churning through all the data, and they weren't archiving old data, so the server had years and years of data on it.
So, first thing I did was make a form with a couple of buttons on it. The first button took all the data they needed and copied it down to a local table in the front end. That took five minutes to run, pulling all that data down. Once that temporary table was created for their month-end (that's last month's data; it's done being created), you're good for last month. Now all of the reports fired up within seconds instead of waiting every time. That's another thing you can do: temporary tables.
There are lots of things you can do to speed up your database; it's just a matter of how bad is the pain. This is a very generic question so I'm giving you all of the advice I can think of off the top of my head, but there's a lot you can do to speed up your queries and your forms. A lot of the form tricks that I show you are just user interface things, like making it so the user doesn't have to see nothing happening. That's why I like to use that status box so it can show you: "pulling down data now," "loading up this now," "running that now." As long as the user can see something, even a spinning wheel, it looks better than nothing. So, hope that helps.
Next up, we have Nancy. She says she's using first name and last name in a composite key. If you haven't learned about composite keys, it's when you make a key on two or more fields. She says, but it's still allowing duplicates when only a first name is entered. Why isn't Access catching that? This one trips up people a lot. What's happening is Access treats null values as unknown, so it doesn't consider them duplicates. If your last name is blank, Access says, I don't know what this is, so it allows multiple records.
There's actually a setting in the composite keys. Yes, I'm in dark mode. There's a setting called ignore nulls, right down here. If that's turned on, it will skip those blanks when enforcing uniqueness. But the better fix is just don't allow nulls in the fields that are part of your key. If it's required for uniqueness, make it required. That way, Access can actually enforce the rule properly. Just make your first name and last name required. But then again, we get into my problem where I always say that no data is better than bad data; you don't want people putting in a fake last name just to get around your required field. That's up to you. It's however you want to handle it. But that's an option you can use there.
Hey, make sure you stop by my website and check out all the new stuff that I've released in the last week or two. I just finished Developer 55, 56, and 57, working on the whole dark mode system I've been building, which is really cool. Of course, I have my Captains Log page where I write about all kinds of different stuff. Just today, in fact, I wrote an article on why over-reliance on AI can bite you. I'll put a link to it down below. Basically, I have a little picture generator that I've been using every day, built in Access. I send my wife a cute little penguin or something, and it just stopped working. Nothing that I did; OpenAI just changed the model in the background and all of a sudden, my whole workflow that has been reliable for a month just stopped working. If you want to learn more, it's a good read. I put a link to it down below, check it out.
A little while back, I posted an article about quality over quantity and how I'm going to start spending more time working on Developer lessons and my SQL Server course and things like that. But over the last couple of weeks, I don't like not having videos on my YouTube channel or my website. For five years, I've been trying to produce a video every day, and I have pretty much, holidays aside, for five years now. People are emailing me saying they miss my videos. So I'm going to start doing the daily video again. I'm trying to get back on schedule. I've been busy the last couple of weeks, but even if it's just a short, like three-minute video, I'm going to try to release something every day because I don't like dead air. I'll talk more about it there. So look for me to go back to the daily stuff. I don't like not having stuff coming out. It's just weird. I'm so used to it now. They might be shorter, and I'm still going to do a TechHelp video every week with an extended cut for the members - that's what members are paying for - plus all the free classes you get on the website. I know you like the extended cuts. I'm still going to do a Developer lesson. I'm still going to be making the Quick Queries videos because I have fun doing these too, and they allow me to answer questions that don't necessarily need a whole video for themselves. I like Quick Queries. I'm very much about your feedback, so I'm curious to hear what you have to say down below. Let me know.
You know the drill. Merch store: get your T-shirts, get your mouse pads, all that. Pick up a copy of my book. Post your questions in the forums or down below. If you're on YouTube, that's fine too. Obviously, my forums are better because I have lots of people that help out there too. Need help, need a consultant, need a tutor? Check out my Developer Network page. While you're on the website, make sure you subscribe to my mailing list, because YouTube doesn't always notify you every time I release something new. They say they do, but they don't. I know. I love YouTube, but I used to like it when YouTube used to send emails to subscribers. Some of the stuff that I follow (like Neil deGrasse Tyson and a bunch of other people), I used to like getting an email that "oh, there's a new StarTalk," so I can go watch. I miss that. You don't always get the notifications on YouTube.
Here's my thing: you can set it up to get either a daily, weekly, monthly, or as-it-happens update, so I won't fill up your inbox unless you want me to. I'll keep sending you emails.
Today we learned to keep your reports fast by filtering your data first, simplifying your queries, and avoiding things like domain functions running on every record. Also, remember to use long pass phrases or a password manager, store your dates properly as real dates, and test your database environment before blaming your code. Post a comment down below. Let me know how you liked today's video and how you plan to use this stuff in your databases. Don't forget to leave your questions for next week.
That's going to do it for today's Quick Queries video, brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is one of the quickest keyboard shortcuts to lock your Windows workstation?
A. Control Alt Delete, then L
B. Windows key L
C. Control Shift L
D. Alt F4
Q2. When controlling form behavior in Access VBA, what is a main benefit of using Select Case over If Then statements?
A. It always runs faster than If Then
B. It is mandatory for complex conditions
C. Can make code easier to read for one-liners
D. Select Case is required for toggling tab visibility
Q3. According to Richard, what is the most important factor in password creation?
A. Length and complexity with multiple symbols
B. Not reusing passwords across sites
C. Using only lowercase letters
D. Always using a pass phrase from a poem
Q4. What is the main reason brute-force password attacks rarely happen on websites?
A. Passwords are usually too complex
B. Most websites lock accounts after several failed attempts
C. Websites use unbreakable encryption
D. Hackers do not target passwords
Q5. What is a primary compatibility consideration between 32-bit and 64-bit Microsoft Access?
A. Only 64-bit Access can use VBA
B. 32-bit Access cannot open databases created in 64-bit
C. You cannot install 32-bit runtime with 64-bit Office installed
D. 64-bit Access cannot work on any network drives
Q6. When importing Excel data into Access, what is Richard's recommended best practice?
A. Assign strict data types during import
B. Import everything into a staging table as long text
C. Use only the import wizard defaults
D. Only import numeric values
Q7. What is an important difference between date formatting and date storage in Access?
A. Formatted dates are more easily grouped
B. Formatting determines how Access stores dates
C. Once you format as text, you lose true date functions
D. You cannot convert formatted dates back to date values
Q8. What is the main advantage of normalizing data into multiple tables in Access?
A. It makes forms more complicated
B. Reduces code readability
C. Improves data management and allows better queries
D. Prevents the use of VBA
Q9. What method did Richard recommend for cross-table logic, such as checking if all people in a household are under 55?
A. Use nested recordset loops exclusively
B. Use aggregate functions like DCount or queries
C. Only use forms to check this
D. Manually check each record
Q10. When moving Access tables to SQL Server, what generally happens to basic select queries?
A. They all break and need to be rewritten
B. Most will work fine if you link SQL Server tables back to Access
C. Only access-specific functions will work
D. They only run in Access runtime mode
Q11. What is a performance optimization tip for speeding up slow Access reports?
A. Always include all table fields in the report query
B. Filter your data so only necessary records and fields are loaded
C. Avoid using indexed fields in queries
D. Run all calculations directly in the report
Q12. Why can using DLookup or DSum functions slow down Access reports?
A. They only run once per report
B. They are automatically converted to SQL Server functions
C. They are evaluated for each record and can be inefficient
D. They are designed for high-speed aggregation
Q13. What should you do if your Access report runs slowly but is being run for month-end data multiple times?
A. Always query the server live each time
B. Archive the old data first
C. Pull the necessary data into a temporary local table
D. Minimize columns displayed on the report
Q14. What setting in a composite key can allow duplicate entries when a field is left blank?
A. Require all fields
B. Allow duplicates option
C. Ignore nulls setting
D. Indexed with no duplicates
Q15. What is Richard's approach for distributing his teaching databases like the fitness database?
A. Sell them as finished software solutions
B. Provide full documentation for users
C. Use them as modular teaching tools for you to customize
D. Restrict features to only paid members
Q16. What is a main point Richard suggests for handling notification of updates from his website versus YouTube?
A. YouTube always notifies subscribers of new content
B. The website offers more flexible and reliable notification options
C. Only YouTube provides email notifications
D. You must check both daily to get updates
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-B; 7-C; 8-C; 9-B; 10-B; 11-B; 12-C; 13-C; 14-C; 15-C; 16-B
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 In today's Quick Queries video from AccessLearningZone.com, we cover a broad selection of questions and topics that come up frequently when working with Microsoft Access. I'm Richard Rost, your instructor, and I'll be sharing insights on why Access reports can sometimes run slowly and what steps you can take to improve their performance. Along the way, we'll discuss password strategies, differences between 32-bit and 64-bit Access, best practices for importing Excel data, how to avoid issues with date formatting, proper table normalization, and what to expect when moving Access queries to SQL Server.
Let's start with a simple trick. While I've traditionally used Control Alt Delete to lock my computer, one of our viewers pointed out a quicker shortcut: pressing the Windows key and L. It surprised me how easy it was, and I'm always open to learning about helpful shortcuts like this. Developing new habits is a challenge, especially with something I've done a certain way for years, but it's always worth considering more efficient options.
Moving on to programming questions, one viewer asked whether to use nested if/then statements or a select case (also called a switch statement) to manage form behaviors, such as showing or hiding tabs. Both are valid options. I tend to use if/then statements, especially when there are multiple lines of code in each condition, but select case works just as well. It usually comes down to readability. If select case makes the logic cleaner and easier to understand months down the road, use that. Ultimately, both are functionally similar; choose the one that makes your code clearer for your future self.
On security, another subscriber shared his preferred strategy for passwords, opting for memorable, unique phrases rather than complex random strings. This sparked a great conversation about what truly matters in password security. The main takeaway is that long, unique passphrases are highly effective. Passwords do not need to include every imaginable character type or be cryptic - in practice, most breaches occur via phishing or password reuse, not brute force. It's crucial not to use the same password on multiple sites and to use a password manager if possible. Features in password managers, such as domain checking, add another layer of protection. Pick the approach that keeps your logins unique and memorable.
Next, we addressed 32-bit vs. 64-bit Access. If you have 64-bit Office installed, you cannot also install the 32-bit Access runtime; you are limited to whichever version matches your Office installation. While there are hacks to mix environments, it is unreliable and not recommended. The right approach is to test your database in the 64-bit environment when required. Most databases work fine unless they rely on outdated API calls. If you discover issues, you may need to update your code or fully switch to a compatible version.
Importing Excel data into Access brings its own headaches. Someone reported that even when specifying short text fields, Access sometimes imports them as long text. The import wizard has its quirks and can be inconsistent, so the best practice is to import all your data first to a staging table, letting everything come in as long text. Once the data resides in Access, you can clean it up, convert fields to the appropriate types, and gain full control. This extra step avoids many conversion bugs and keeps your database stable.
Another frequent challenge is handling dates in Access. There is a crucial distinction between a field's underlying value and its displayed format. If you store dates as true date/time values, you can control their display format at any time; but once you convert them to text for special formatting, you lose native date functionality. Use display formatting only for presentation; always keep dates stored as date/time data types, unless you are forced to work with unconventional formats, in which case you can always use string manipulation functions to convert them later.
Data normalization is another big topic. One student working through how to structure data for houses and their residents went from using a single table to refactoring everything into a proper one-to-many setup. It's a typical journey - splitting entities into separate tables is the right step. Once you have your data normalized, you can handle logic like "does everyone in the house meet this criterion" much more efficiently using aggregate functions and queries, instead of looping through record sets manually. Let Access or SQL Server handle the heavy lifting with optimized queries, especially as your data grows.
A related question comes from someone interested in the evolution of my fitness database. They asked if I ever provide an overall summary of its design and functionality. The answer is no, and that's by design. The database grows incrementally, with features added in response to teaching goals and feedback. It's meant as a training tool, not a finished application to hand off. If you want to give it to someone else, you must extract the parts you need and build your documentation and help system around it. Good comments in code partly fill this need, but there is no substitute for dedicated documentation when you want to deliver a product.
When it comes to moving Access tables and queries to SQL Server, someone asked whether you have to rewrite all your queries after migrating. The short answer is no. Most select queries, forms, and reports will work when linked to SQL Server tables - Access handles much of the optimization behind the scenes. Problems tend to arise only if your queries use specific Access functions such as Dlookup or custom VBA code, or when working with certain action queries. For those cases, you might eventually want to build SQL Server views or rewrite the logic in TSQL, but it's not necessary for every part of your project. Always test thoroughly, make backups, and transition incrementally.
Earlier this week I posted a preview of a new Kanban board database I'm building. A few comments came in about customizing the display, such as adding extra columns to show more information, or implementing mouse cursor changes while dragging items. These features are possible and may be demonstrated in future lessons, using either standard listbox arrangements or clever interface tricks. Flexibility is there; you just need to design your data display and interface accordingly.
A classic concern is slow reports in Access. The answer typically involves several best practices. First, always filter your data as much as possible before running the report; don't load thousands of unnecessary records. Only bring in fields actually used by the report. After initial testing, refine your queries to remove the asterisk and list only those fields you need. Second, be aware of complex, nested queries - flatten them and simplify joins. Index all fields used for searches or sorts, especially primary and foreign key fields. Minimize calculated fields and avoid domain aggregate functions inside reports, since these run once per record and quickly become a performance bottleneck. Instead, calculate aggregates beforehand in a query.
Network performance and file bloat can also have a major impact, especially if your backend data is housed on a slow server or you have not compacted your database recently. In situations where many reports are based on the same data set, consider pulling the required data into a temporary local table before running reports, which dramatically reduces repeated network hits and can improve speed from hours to minutes.
Another viewer asked why Access sometimes allows duplicates when using composite keys of first and last name, even when it should be unique. The root cause here is that Access treats null values as unknown, so it will allow 'blank' records to bypass the uniqueness rule. To solve this, set all key fields to required, so nulls are not permitted, and Access can enforce your unique key properly.
I encourage everyone to check out the new video updates, recent course releases, and blog entries on my website, where I write not just about Access, but also things like why over-reliance on AI tools can disrupt your workflow. As I plan to return to releasing daily content, expect shorter videos but steady updates on all things Access and beyond, along with weekly TechHelp and Quick Queries videos.
To stay in the loop, don't rely only on YouTube's notifications - subscribe to my mailing list on my website and choose your frequency for updates. And as always, your feedback is appreciated. Please post comments and questions, either below the video or in the forums, where you can also connect with other Access enthusiasts and find additional support.
Today, remember to keep your Access reports efficient by filtering and simplifying your queries, use strong and unique passphrases for passwords, store your dates with the correct data type, and test your environment before assuming code problems. Let me know in the comments how today's tips will help you in your projects, and feel free to submit your questions for next week's video.
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 Shortcut for locking Windows (Windows key L)
Choosing between If Then and Select Case in VBA
Password strength: pass phrases vs random strings
Why password brute-forcing is not a common threat
Importance of unique passwords for each site
32-bit vs 64-bit Access runtime compatibility issues
Importing Excel data into Access using staging tables
Handling data type conversion after import
Difference between date formatting and date values in Access
Using date fields vs text fields for date data
Benefits of ISO date format in Access
Refactoring tables for proper one-to-many relationships
Cross-table logic using aggregate functions
Database normalization: splitting tables
Advantages of queries over looping through recordsets
Moving Access tables to SQL Server and query compatibility
Access-specific functions in SQL Server-linked databases
Optimizing report speed by limiting data loaded
Filtering report queries to necessary fields
Flattening nested queries for better performance
Indexing key fields to improve query/report speed
Avoiding calculated and domain aggregate fields in reports
Pre-calculating data in temporary tables for reports
Composite keys and handling null values
Setting required fields for keys to enforce uniquenessArticle Have you ever opened a Microsoft Access report and wondered if it is actually working in the background, or just stuck being slow? Microsoft Access reports and databases can sometimes run slowly, leaving you frustrated and unsure where to even start troubleshooting. Fortunately, there are many practical steps you can take to speed things up, as well as strategies for a handful of common pitfalls and issues in Access development. Let's walk through some real solutions that can help, whether your issue is slow reports, complicated imports, or confusion about storing data securely and efficiently.
Let's start with the classic slow Access report problem. Usually, slow reports are not the result of just one issue, but rather a combination of several small things. The most important thing you can do is filter your data before the report loads. Don't base your report on every record in your database if you only need a subset. For example, if your table contains 100,000 records but your report only needs 50, always use a query with criteria to limit which records the report pulls from. Similarly, don't add fields to your report's data source unless you need them. Every field you include increases the load time, especially across a network. While it's okay to use the asterisk (*) in queries during initial development to bring in all fields, you should pare it down later. Only bring in the columns needed for the report.
Another area to examine is how your queries are structured. Nested queries can slow things dramatically. Try to flatten or simplify your queries wherever possible. Also, check your join structure. Make sure any fields you are joining, filtering or sorting on are indexed on the table. For example, if you have a customer table and an order table, both should have their customer ID fields indexed. Index the primary key as No Duplicates and the foreign key as Duplicates OK.
If your report or its source query uses calculated fields or functions like DLookup, DSum, or other domain aggregate functions, remember these run for every record and will slow things down significantly on larger reports. It's better to pre-calculate values with aggregate queries rather than using domain functions inside the report or its query. When you need to perform per-row logic, move as much as possible into the query, and use Access' built-in aggregate capabilities rather than VBA, unless necessary.
Don't overlook your environment. If your database's backend is stored on a slow or heavily used network drive, performance will suffer no matter what you do in your queries and reports. Similarly, if your backend .ACCDB or .MDB file is several gigabytes and hasn't been compacted and repaired in a while, this can also slow things down. Try copying your data to a new file to see if fragmentation is an issue.
A powerful trick for recurring reports that need to summarize or analyze the same set of data multiple times - such as during month-end reporting - is to first pull your working data set into a temporary table in your front-end database. For example, create a button or macro that runs an append query to dump all the necessary records for the month to a local table. You can then run your multiple summary or print reports against this smaller, static set instead of querying the entire live data set each time. This often reduces wait times from minutes to seconds.
Moving to a different topic, passwords and pass phrases are another common area where people can get lost in unnecessary complexity. There's a misconception that the best passwords are always long, random strings containing uppercase, lowercase, numbers, and special characters. In reality, a long pass phrase you can remember - like a sentence from a poem plus a site-specific word - is just as secure for most uses. Modern sites and apps usually lock your account after a few failed attempts, so brute-force guessing is not how hackers most often get in. Far more common are phishing and using passwords stolen from data leaks. The real danger is reusing the same password across sites - never do this. Instead, use a password manager, or at least a unique pass phrase for each site. Memorable, lengthy phrases work as long as you don't reuse them. Complexity is less important than length and uniqueness.
A question that comes up often is whether to use nested If...Then statements or Select Case for conditional logic in VBA. Both are perfectly fine, and VBA will handle either just as well. The choice comes down to readability and your preference. If you have lots of complex, multi-step actions per condition, If...Then...ElseIf blocks may be clearer. If you have many single-value conditions, Select Case can tidy it up. For example, to show or hide tabs conditionally, either approach works:
An if/then example:
if SomeCondition = "A" then
'Do something
elseif SomeCondition = "B" then
'Do something else
end if
And the same logic using select case:
select case SomeCondition
case "A"
'Do something
case "B"
'Do something else
end select
Pick the way that will make your own code easiest to read and maintain six months from now.
Another common headache is importing Excel data into Access. The import wizard is helpful, but it often guesses the wrong data types for your columns - turning numbers into long text, or misclassifying short text as long text. This can especially be a problem if you have inconsistent or unusual data in the Excel sheet. The safest practice is to import everything first into a staging table, using long text fields for all columns, so nothing gets truncated or misinterpreted. Once you have the data safely in Access, use append or update queries to move and convert the data into your correctly typed working tables. This gives you full control over conversions, and you avoid errors caused by the wizard's guessing.
Working with dates brings its own gotchas. It's critical to store dates as actual Date/Time fields in Access, not as text. The way a date is displayed - its format - does not change the underlying value. But as soon as you use the Format function or store a date as text, you lose the ability to use Access's date functions like Year(), Month(), and to sort or filter dates properly. Always store date values as Date/Time at the table level, and use formatting purely for display purposes, not for storage. Text-based dates require messy string parsing if you ever need the underlying year or month, so don't go that route unless external requirements force you. The internationally friendly date format is YYYY-MM-DD (ISO format), and it avoids ambiguity between countries. Use it when possible.
A crucial concept in database design is normalization - splitting out your data into properly related tables rather than trying to track everything in one. For instance, if you start by keeping both houses and people in a single table, you'll quickly run into logic and maintenance problems. The proper way is to have a House table and a People table, linked by a foreign key, reflecting the one-to-many relationship (one house, many people). This makes it much easier to answer cross-table questions, such as checking whether all people in a household meet a certain age requirement. Don't overcomplicate this part: you can often answer these questions using aggregate functions like DCount, DMax, or a totals query, instead of looping through every row in VBA.
Many users wonder what happens if you decide to move your Access tables to SQL Server later. Usually, your basic select queries, forms, and reports will continue to work just fine as long as the tables are linked. The main issues arise if you have VBA functions, domain aggregate functions, or Access-specific features in your queries. Those do not translate to SQL Server at the server level, so if you want your database to run more logic directly on SQL Server for speed, you'll need to re-write that logic as SQL Server views or stored procedures. But you aren't required to do so; Access will attempt to handle things for you as long as you stay within the compatible features.
If you have ever experimented with 32-bit and 64-bit versions of Access, you've probably learned that you cannot mix and match them on the same computer without headaches or hacks. If you have 64-bit Office installed, you must use the 64-bit Access runtime. Some legacy VBA code, especially API calls, may not work until you update or patch them for 64-bit. It's best to pick one bitness and stick with it for all your Office apps to avoid conflicts.
To help with key constraints, keep in mind that Access allows composite keys - unique constraints across multiple fields - but will not enforce uniqueness when one of those fields is null. For example, if you define a composite key on first and last name, but allow last name to be blank, Access treats every blank as "unknown" and not as a duplicate. The workaround is to make all key fields required and disallow nulls.
All of these recommendations should help make your Access projects faster, safer, and easier to maintain. By focusing on efficient queries, smart table design, proper data types, and up-to-date security habits, you'll save yourself a great deal of time and frustration. If you run into tricky scenarios - like slow reports or complex data imports - start by isolating and limiting the data you're working with, and move control into queries or temporary tables wherever possible.
Finally, don't forget the value of documentation and clear code comments. If you build up a project over time or with many features, like a fitness tracking database, keep in mind that someone else might have to understand or maintain your work someday. Documentation and a short "project overview" go a long way, even if your database started as just a personal experiment or teaching tool.
In summary: filter your report data in advance, simplify queries and avoid doing calculations row by row if possible, store dates as real date types, use unique passwords or pass phrases for each site, and normalize your data schema. If you move to SQL Server, most of your Access queries will survive the transition - just double-check anything that uses VBA logic or domain functions. With these best practices, your Access databases will be faster, more reliable, and much easier to manage in the long run. If you have any additional questions or topics you'd like to see addressed, leave a comment or ask a question in an Access forum. |
