Quick Queries #63
By Richard Rost
24 days ago
Is This Lazy Habit Quietly Bloating Your Queries?
In this Microsoft Access tutorial, we'll talk about best practices for optimizing your queries and forms, including avoiding a lazy habit that can slow down your database, especially over a network. I'll answer viewer questions on topics like removing leading zeros from address fields, handling linked tables from QuickBooks, and mapping out database designs in Excel. We'll discuss issues like conditional formatting glitches and which backend database systems work best with Access, plus share advice on navigating Microsoft's confusing product versions. This video is part of my Quick Queries series.
Kendra from Shreveport, Louisiana (a Platinum Member) asks: Some of my forms and reports are very slow, even though they don't have a lot of records. Others run fast with no issues. What could be causing certain forms or reports to load or run so slowly?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, lazy query design, network bandwidth, slow forms, query optimization, select specific fields, avoid asterisk, SQL Server performance, proprietary data, screenshots design view, remove leading zeros, query string functions, update query, ODBC linked table, QuickBooks IDs, surrogate key, natural key, database mapping Excel, filter as you type, combo box arrow keys, union tables, conditional formatting transparent, back style solid
Transcript
Today we're going to talk about that lazy habit that's quietly bloating your Microsoft Access queries and wasting network bandwidth. I'll be honest, I'm guilty of this myself sometimes. This is especially noticeable if you've got a big database that you're pulling over a network wire or you're using SQL Server online. Anytime you're dealing with a database across a network or across the internet, this is going to be a big problem.
Today's question comes from Kendra in Shreveport, Louisiana, one of my Platinum members. Kendra says some of my forms and reports are very slow even though they don't have a lot of records. Others run fast with no issues. What could be causing certain forms or reports to load or run so slowly?
Questions like this are very difficult to diagnose without seeing the database. I don't accept file attachments, but what I sometimes do with my Platinum members is say, post some screenshots, post some images of your design view. Let me see your queries. Let me see your form design. And if you can't post it in the forums, email me some screenshots. I'll take a look. Sometimes. Don't everybody do that now. I get too much email as it is.
One of the reasons why Kendra didn't want to post screenshots on my website is because her database is proprietary. Her boss obviously wouldn't want some of that being made public, but I mocked it up here in my TechHelp database to illustrate the point. Let me show you a couple of pictures and see if you can figure out what might be slowing down her queries, which are slowing down the forms if the form is based on a query.
Here's number one.
Here's number two.
And here's number three.
See if you can figure it out. Pause the video and see if you can figure out what might be slowing things down.
Did you get it? Did you figure it out? It's that star. It's that asterisk that says, "give me everything you've got." So even if your table only has a dozen or so records, but each one of those records has 50 fields in it, by you saying give me that star, you're saying give me all of the fields. And this across multiple tables especially can add up to a lot.
So what's the solution? The solution is just get the fields you need to be displayed, either in your query or in your form.
Here for example, don't use all of those asterisks. Just get what you need: the customer ID, the first name, the last name, maybe the order date, is paid, product name, and quantity. If those are the fields you're using, just get those fields. So get them all order details.
Sometimes you've got a small table with only a handful of fields, and if you need all of these, that's fine. Get the asterisk, and this is a calculated one. This one might be okay as is. But take a look and make sure you need all these fields, and if you do, use the star.
Technically, when I build a database, I usually start with the star and then I'll go back later when I'm trying to optimize performance and then I'll say, okay, what fields am I actually using? Then I'll get rid of the star and put all the individual fields in. Sometimes you need the star though. And always try, if you've got multiple tables, always try to only use the star from one of those tables. It just improves performance. I don't know why, it just does. If you bring in multiple stars like that first example, it really slows things down.
And this guy again, same problem bringing in all the fields when you're only using some of them. You're only using customer ID, first name, last name, state, customer since, and credit limit.
When you use forms, especially over a network, limit the number of fields you're pulling. And don't just pull the star and don't just pull from the table like that. That can also slow things down.
Make a select statement in here or a query and just pull the fields in that you need. I know I teach the beginners to put the table name in here, but that's for beginners. Once you get a little more advanced, just pull in the fields that you need.
And Kendra put in the little changes that I suggested and guess what? Everything's running a lot faster. Maybe about 50% faster now. So glad I could help. And there you go. Hope that helps someone else.
Here's one from the forum on my website. David said, I have a list of addresses and some addresses have leading zeros like that. In order for it to match up with another table, I got to remove the leading zeros from in front of the addresses. There are formulas you can include in a query to take care of this. Kevin pointed him to my string functions video, which I'll give you a link to in a minute. But yeah, absolutely. Use a query equation that you can use. Say your new string is, use the if function: if the left of s, s being whatever your field is, comma one, equals a zero, and the zero's got to be in quotes like that. It's a text string. This is a text field. I couldn't have an end if it wasn't, or spaces in it.
So if that left one character is zero, then your new field will be the right of s, the length of s minus one. That says chop off everything except the right x minus one characters. So it basically gets rid of that left one character.
I'll put a link to my string functions video and the if function down below. And if you want to permanently change all of them, use an update query and just feed this into the update query. Just give it that equation. It'll go through the entire list of all the records and change anyone that has a leading zero to not have that leading zero.
Here's another question from Ryan. Ryan says I'm bringing my customer list into Access via an ODBC link table to Access open database connectivity, ODBC. That's when you can connect to a different data source. When this table comes in, QuickBooks has already assigned a customer ID, shipping address ID, billing address ID, and parent ref ID. For any sub customers, the IDs assigned by QuickBooks are short text, and I'm not sure if it's important to get IDs in an auto number format. Is it best practice to import this linked table into Access to work with, or should I just set my relationships through the linked table?
Sammy gave him some advice, and Sammy's advice is right on. It all depends on what you do and what other information you're bringing in from QuickBooks.
I chimed in and I said if you're syncing the data with QuickBooks, in other words, you're opening up a connection to QuickBooks and then importing any new data or changed data into your database, then I would keep the QuickBooks ID and just assign your own auto number to each person in your table, but store theirs as a reference.
In other words, keep their ID, but you can also create your own for anything that you do in your database that's just your stuff. Like if you want to match that up with a contact table or something else that you've got, a to-do list for them, if that's in your database, you can have both. There's nothing wrong with having both IDs: a natural key and a surrogate key, for example.
This way, every time you do an import, you just match up the QuickBooks ID and you can get their new information. If you're working with a live copy of their data, in other words, your Access database is just working live with their customer data, then just work with their ID. Don't worry about assigning your own ID because you've basically got a linked table at that point.
First up, I got to acknowledge a funny comment on YouTube. SSD, USD said, this is the best information I've ever known in my life. Wow, thanks. I got a chuckle out of that. That's the best information ever.
ZVIG says, what do you think about making a database map out, like map out a new database like you use an Excel spreadsheet to design the database? Yeah, that's exactly what I do in a lot of my classes. I do that in my Access Beginner 1 class. I do that in a lot of my series that I do, like the association database. You can see right back here, there's an Excel spreadsheet. I map out all the tables and the fields and the relationships. I talk about it in my regular course. So yeah, that's a great idea and I do it a lot.
This one goes back a couple of weeks to my happy birthday Excel video I did. It says Excel has all those cells, but doesn't let me enter more than a hundred thousand rows. I'm going to stop right there. You shouldn't be anywhere near a hundred thousand rows in Excel. If you've got over, I'm going to say a couple hundred rows, you should be thinking in Microsoft Access. You should be thinking database at that point. A hundred thousand rows in a spreadsheet is just asking for trouble.
A Sancho 2.3 says, I love this stuff. Please keep doing this forever. Every day I look forward to you. I have no plans on quitting anytime soon. I think I've often said this: even if I won the lottery or became independently wealthy or whatever, I would probably still do this just because I enjoy it. I have fun doing this every day myself. You keep watching. I'll keep making.
Dardiv says, I had an idea inspired by another community I follow in the mechanical watch world. There's a YouTuber who publishes videos where collectors show their own watch collections. I think something similar could be great here too. Community members could share their Access databases, either showing how they structured them or just the design layout choices they made. It could be really inspiring to see real world solutions, just an idea.
I think it's a fantastic idea. In fact, on my website, I encourage my users to post screenshots. Let us see your database. Not just if you have problems or need help, but just if you want to show it off. I love to see what my students have built using my lessons. It puts a smile on my face every time.
The problem with YouTube is that you can't post images in the comments here. There's no way for you to post stuff like that unless you post your own video. If you guys would like to do that, if you want to post your own little videos or links to screenshots that you've made that have your databases that you've built, if I like them, I'd be happy to do a video montage or a collection of all the best ones. But yeah, sure, absolutely. Great idea.
Next up, Morrow asks, I'd like to ask you a question about the if function. I have a query that works perfectly with two if functions. Is it possible to replace the false argument so that it doesn't appear in the query?
I think I understand what you're trying to say. You want to say that if the answer to your if function is false, then you don't want to see that record. If that's the case, yes, you can do that, but you can't do it in that query. Save that query with all of your records, true and false, then make another query that just shows the ones where it's not false. So if let's say you're asking for a number of children, for example, hypothetically. You want to see all the ones, two, three, fours, whatever. But if it's a zero, then just show all the records where it's not equal to zero. Do the same thing with the if function. Save your query, then make another query that's based on that query and just make the criteria not zero. If you can't figure it out, let me know. I'll do a follow up video on this one.
Kate says, oh my goodness, thank you. It's almost impossible to figure out what you can actually buy from Microsoft. I'm still successfully running Office 2003 if you can believe it, but I'm trying to move on. She's replying to my how to buy Access 2025 edition. Yeah.
Microsoft is confusing as blank when it comes to marketing their products. I will be the first to admit, I'm a Microsoft MVP myself. I love Microsoft. I've been promoting and teaching Microsoft products for 30 years. And even I still don't know what they're doing as far as marketing their products go. They got this version, that version, home edition, professional edition. It used to be so much simpler. It used to be years ago. I wish they'd go back to that and hey, Office 2003, that was one of my favorite editions. I loved that one.
Before 2007 came out, I was one of those people who really resisted 2007 because I hated the ribbon when it first came out. In fact, if you're still running 2003, that's going to be your biggest stumbling block moving to Access and Excel and all that is the ribbon.
But I didn't believe it at the time, but trust me, once you get used to the ribbon, it is much, much better than the old menus. The file, edit, view, with all the toolbar, a million toolbars. The ribbon is dynamic, so it'll change based on what you're doing. And once you get used to it, it is really nice. So that was a positive upgrade. But at the time, I hated it. I couldn't stand it. I wanted a way to go back and I'm glad I just dealt with it and figured it out.
But yes, Microsoft needs to be a lot more straightforward with their packaging. Why can't they just do, for Microsoft Office, the home edition and the business edition? Home's got the simple things: Word, Excel, PowerPoint, whatever. Business has the extra things like Access and SharePoint, whatever else you need for business. It's too complicated. Too many editions.
Next up, Ryan DeGuzman says, these videos are so cool. I've got it working in my database. He's talking about the filter, filter as you type. That's where as you type letters into a combo box, the combo box results themselves filter, so the list gets shorter. So if you type in chicken, for example, you'll just see the records that have chicken in them. It makes things more intuitive.
I've got a table of 2300 ingredients, but I do have a problem when I start typing the ingredient and the drop-down of options appears. I want to use the down arrow to choose which ingredient from the drop-down instead of using the mouse.
That is correct. The database as you've got it right now from that video will not allow you to do that. I did come up with a solution and it's covered in my fitness database series. I'm pretty sure lesson 40 covers that. You don't have to watch all of them, although I do recommend you watch all of them. But I do teach you how to fix that, how to ignore arrow key presses in the combo box events. That way you can handle them yourself. Ignoring it means you're not going to let Access process that, you deal with going up and down yourself. It's either this one or one of the two around it.
But everyone looks at fitness database and says, I don't care about fitness. I don't want to track calories. But that's not the point. The point of this series is to teach you guys cool database stuff and the fitness database is just something that I'm building mostly for myself. I'm showing you guys cool tricks along the way like this one. It's got the filter as you type built into it. It unions two tables together, so you've got all your meals and all of your food items in one combo box. What you type in can appear anywhere in there. So if you type in chicken, for example, you'll see chicken, chicken, chicken. This one's got chicken over here. So it doesn't have to be at the start of the combo box, which is how they work. I teach you how to use the arrow keys to be able to go up and down and then press enter on the one you want. So that's covered. It's either this lesson or like 39 or 41, but I did a search for combo box and this one came up. It's right around here somewhere. I'll put a link to this one down below for you.
But yeah, watch the fitness series. There are tons and tons of really good tricks in there. I'm giving away too many free tricks in here. I should be putting these in my developer lessons, but I've already covered them in most of my developer lessons.
Have a nice day. Says, yes, me like you. Me like you too. This is one of my Word videos. Believe it or not, I've got a handful, just a handful, of Word videos and they get more views than a lot of my Access videos do. That's why I got to kick myself in the butt one of these days and just rerecord all my Word stuff. There's 50 times the number of Word viewers or Excel viewers than there are Access viewers. I love my little Access group, but I'm surprised all the time how many views these old Word videos get and that's been like Word 2007 too. Maybe not this one, but some of them are.
John shared his story with me in relation to why so many IT pros hate Access. He said he had a glorious 35 year career mostly developing Access VBA, owns a house in Toronto. I love Toronto, one of my favorite cities. Raised the family solely on my Access income. How bad can it be? No, it's not, Access is wonderful. I started my career as far as development with Access and I've been teaching almost Access exclusively for what, 10, 15 years now, just on just Access with a few other here, you know, here and there, Excel, whatever.
Every time I thought my Access days were numbered, something popped up. In 2015 I had a contract up from higher. The bank chased after me specifically for my Access skills, still there 10 years later. Unfortunately they've been shying away from Access for the last couple of years, but I still have a few core Access databases they can't drop.
Yeah, that's the story I get from a lot of places. There are hundreds of thousands of businesses out there, both big and small, that still use Access databases. And a lot of them are like, "Well, we want to go cloud, we want to go this, we want to do that." They don't realize that you can do all of that and there's no reason to have to get rid of your Access databases. They think that these Access databases are just for some small, you know, departmental thing, but they don't have to be.
If you want to go cloud, sure, take your data out of that Access database and put it in SQL, right, SQL Server. That's fine. Then from there you can branch off. You can build a web-based interface. You can build a Power App for your mobile phone devices, but there's no reason to get rid of Access for your desktop users. You're still going to have people in the office that still need to get into that database, and there's no better rapid application development tool than Access as a front-end database design. You can't beat it.
That's what I tell people all the time: "Oh, my company's moving away from Access." Why? You want to get your data up in SQL Server, you want to build something for the cloud? Sure. No reason to drop Access completely.
Anyway, that's my frustration, but John, thank you very much for sharing your story. I used to live in Buffalo, New York, so I've spent a lot of time in Toronto, been to many Blue Jays games, a couple of Maple Leafs games, several Rush concerts up there. Love that city. It's wonderful. There's nothing like walking up and down Yonge Street on a negative 35 degree day. Nice thing about Toronto is you can go through most of the city underground without ever having to go up into the negative 35 degree weather.
Stuart says, if your proficient in VBA, SQL, etc., then using PHP and MySQL really isn't that much of a leap. The logic, writing functions, queries and if statements are really very similar. You do have to re-create from scratch because not only do you have functionality to deal with, but there's presentation too via CSS. And if you really want things to run nicely, some JavaScript or jQuery.
I notice you always talk about SQL Server rather than MySQL, any reason for the preference? Most websites seem to run on MySQL and it works fine as a backend for Access in my experience, just a few settings to tweak.
No, not really. It's just personal preference. I know that MySQL really got popular because for many, many years, decades in fact, I think MySQL is open source, so anyone can use it. PHP and MySQL have been basically free. You run on Apache server too and now you're really cheap. Whereas if you wanted to go with the Microsoft ecosystem, you had to run a Windows server which cost money. You had to run SQL Server which for the longest time cost money, they didn't have a free edition. I don't think it's relatively recently. And remember, I'm old. So when I say relatively recently, I think it was around 2005 was when SQL Server Express first came out. So before that, like the early days of the web, you couldn't run Microsoft.
So when I started web developing and I got into SQL because I had an SQL Server, I had my own web server, so I was able to run SQL Server. But now that the free version's out there and Windows hosting is pretty cheap, I've always just stuck with SQL Server because I'm a Microsoft guy. It's the same reason why I use Excel and Word instead of, in the old days, when I used to use WordPerfect and Lotus. So no real preference, that's just my choice.
I used MySQL years and years ago and I had issues connecting Access to it. But like you said, there's probably a few settings to tweak and I gave up and just went back to SQL Server. So it's just, again, just me. No real reason. I hear it's a fine piece of software.
Going back to the why do people hate Access, CBBCBB, whatever. I think I got that right. I never liked the table creation wizard. It should look more like the one that Visual FoxPro had. I used Visual FoxPro back in the 90s, way, way back when. So I don't remember it. But honestly, I've never used a table creation wizard. Building a table is always something that I've done manually. I just think that that's something that doesn't really need a wizard. But I'll be honest, even form wizards, I don't like them. I like the fields out myself. Some of the wizards are okay like the combo box wizard, I'll use some time at time. Or the command button wizard for beginners is fine. But I've never used a table creation wizard. So I don't know.
BeamingIn J says it's inferior to SQL Server. I think he means Access is inferior to SQL Server. And the same thing it is used for can be achieved in SharePoint.
I disagree 100 percent. First of all, saying that Access is inferior to SQL Server is like saying that a Ferrari is inferior to a cruise ship. They both have different purposes. You want to go fast, you drive a Ferrari. You want to bring a lot of people someplace in style, you use a cruise ship.
Access is a front-end designer. It's for building an interface; the database behind it is SQL Server optionally. So saying that one is inferior to the other, they are two different tools used for one common purpose. You use Access to build your forms and reports and to build all your tables and queries and stuff in SQL Server.
As far as saying the same thing can be achieved in SharePoint, no. SharePoint, I've used SharePoint. It has far less flexibility and capability than Microsoft Access does when it comes to building something for the desktop. Yeah, you can use SharePoint in a web browser, but it's got nowhere near as much functionality as Access does as far as VBA and data control and all that stuff. SharePoint is fine, but it's a different tool. SharePoint is for mostly for connecting to SQL Server, for example, in your web browser. Or you can use SharePoint lists, but I don't like SharePoint lists. They got problems.
Thanks for the feedback, but no. I have to disagree with you, and that's okay. We can agree to disagree.
And finally, Gregory has a suggestion for the user back from QuickWary is 56. See, sometimes these get buried and I don't even see them. I've got thousands and thousands of comments that I haven't yet gotten to. I'm trying to get to all of them, but it's like as I go through a few dozen new ones, more ones just keep coming in. I love it. Keep posting comments, folks. I love it. But as you can see here, this one's a month old already because I start with the newest ones and go backwards every week. So I don't always get to the older ones.
But Gregory says, suggestion for the user with conditional formatting issues. Conditional formatting is glitchy if the field back style is transparent. Yes, I've noticed that too. So make sure that your field's back style is a solid color. Don't use transparent. Otherwise, conditional formatting will glitch out. So Gregory, thanks for sharing. And whoever that user was, I hope you see this because you should be watching all of my videos, including this one. So if not, you're not getting that help. But thanks, Gregory.
And with that, we're going to call it, folks. That's it. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free. And make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that show more link right there. YouTube's pretty good about hiding that, but it's there. Just look for it.
If you have not yet tried my free Access Level One course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on. And did I mention it's completely free? If you like level one, level two is just one dollar. That's it. And it's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there's hundreds of them by now. They also get one free beginner class each month. And yes, those are from my full courses.
Gold members get the previous perks, plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault, where I keep tons of different functions and all kinds of source code that I use. And gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks, plus they get all of my beginner courses, all of them from every subject, and you get one free advanced or developer class every month after finishing the expert series. And you can become a Diamond sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by accesslearningzone.com. I hope you enjoyed. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Problems with using SELECT * in Access queries
How using SELECT * can slow down forms and reports
Optimizing queries by selecting only needed fields
Reducing network bandwidth by minimizing data pulled
Strategy for replacing SELECT * with explicit field lists
Impact of multiple SELECT * from joined tables on performance
When it is appropriate to use SELECT *
Reducing fields in form record sources for better speed
Using a query to strip leading zeros from address fields
Using the Iif function in a query calculation
Permanently removing leading zeros with an update query
Working with ODBC-linked tables from QuickBooks
Managing natural keys versus surrogate keys when importing data
Keeping external system IDs and assigning your own AutoNumbers
Using the filter as you type technique in combo boxes
Combining data from multiple tables into one combo box list
Handling arrow key navigation in combo boxes with filtering
Conditional formatting glitches with transparent back style in Access forms
Performance considerations for large data sets in Excel versus Access
COMMERCIAL:
In today's video, we're discussing some of the most common habits that slow down your Microsoft Access forms and reports, like using the asterisk to pull every field in your queries, which wastes bandwidth and makes your databases run slower. We'll talk about simple ways to clean up your queries, how to strip leading zeros from addresses, managing IDs when working with ODBC linked tables, best practices for designing your databases, and a few fun stories and helpful advice from viewers just like you. We'll also answer questions about combo box filtering, the pros and cons of Access versus SQL Server and SharePoint, and troubleshooting conditional formatting issues. 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 reason using SELECT * (the star or asterisk) in Access queries can cause performance issues?
A. It forces Access to fetch all fields and all records, even unnecessary ones, which increases data transfer and slows queries.
B. It increases the security risk of your database.
C. It prevents queries from running at all.
D. It automatically sorts all records alphabetically, which takes extra time.
Q2. When is it generally acceptable to use SELECT * in a query?
A. When you always need every single field in a large table.
B. When you are working with a small table and actually need all of its fields.
C. When your database is only used locally on your own computer.
D. When you do not know which fields you will need until later.
Q3. What is a good strategy to optimize Microsoft Access queries for performance?
A. Only include the fields you need in your query instead of using the asterisk.
B. Always use macros to run your queries.
C. Create a separate query for each table in your database.
D. Use only local tables rather than linked tables.
Q4. Why can pulling all fields from multiple tables (using SELECT * in joined tables) be especially problematic?
A. It creates circular references in the database.
B. It drastically increases the amount of data being transferred and slows down the form or report.
C. It requires more user permissions for each table included.
D. It converts all values to text format.
Q5. If you want to remove leading zeros from address fields using a query, what technique was discussed in the video?
A. Use the LEFT and LEN string functions to trim zeros in the table design.
B. Use the IF function in a query to check for a leading zero, then use RIGHT and LEN-1 to remove it.
C. Manually delete zeros in datasheet view.
D. Use conditional formatting rules.
Q6. What should you do if you want to permanently remove leading zeros from all addresses in your table?
A. Use a report to hide the leading zeros.
B. Run an update query with the trimming calculation.
C. Create a macro to delete records with zeros.
D. Set the field format property to currency.
Q7. What is the best practice when linking to external systems like QuickBooks via ODBC in Access regarding IDs?
A. Only use your own auto number ID and ignore QuickBooks IDs.
B. Use only the QuickBooks-assigned IDs in your Access database.
C. Keep both the QuickBooks ID and your own auto number ID for flexibility.
D. Manually re-assign new IDs each time you import.
Q8. How can you filter a query in Access so that certain records, for example where an "if" function's result is false, do not appear?
A. Write extra VBA code to skip those records.
B. Use a second query based on the first to apply specific criteria.
C. Use macros to remove unwanted records.
D. Delete those records from the table directly.
Q9. What is a recommended way to design a new database, especially for mapping out tables and fields?
A. Design the entire database in formatted Word documents.
B. Only use the Access Table Creation Wizard.
C. Use an Excel spreadsheet to lay out tables, fields, and relationships before building in Access.
D. Build everything directly in Access and make changes later.
Q10. What is the main benefit of using the ribbon interface in modern versions of Access and Excel compared to the old menu system?
A. It cannot be customized by the user.
B. It presents one static set of features at all times.
C. It dynamically changes to show commands relevant for your current task.
D. It removes the need for keyboard shortcuts.
Q11. When moving data to the cloud, what is the instructor's advice about continuing to use Access?
A. You must stop using Access and move to only web-based apps.
B. Convert all Access databases to SharePoint lists.
C. You can use Access as a front-end with the data stored in SQL Server.
D. You should build everything from scratch in Power Apps.
Q12. What is a key reason why some developers prefer SQL Server over MySQL, according to the video?
A. MySQL is less secure than SQL Server.
B. SQL Server was more expensive in the past, but now has a free edition, making it accessible for Microsoft-focused developers.
C. MySQL cannot be used with Access.
D. Microsoft only allows SQL Server with Access.
Q13. According to the instructor, how should you handle functionality like conditional formatting in Access forms when the field back style is set to transparent?
A. Leave the back style transparent so formatting works.
B. Change the field's back style to a solid color for reliable conditional formatting.
C. Use macros to force formatting updates.
D. Do not use conditional formatting.
Q14. What general advice does the instructor give about database design wizards in Access?
A. Always use table creation wizards for fastest setup.
B. Wizards are less flexible, and manual design is usually better except for simple tools like command buttons or combo boxes.
C. Wizards should always be avoided under all circumstances.
D. You must use wizards to ensure best performance.
Q15. What does the instructor suggest about sharing screenshots of your database with the community?
A. Never share screenshots for privacy reasons.
B. Sharing screenshots can be helpful for getting feedback or showing off your design, but be mindful of proprietary data.
C. Only use YouTube comments to share images.
D. It is not possible to share database design except by emailing the file.
Answers: 1-A; 2-B; 3-A; 4-B; 5-B; 6-B; 7-C; 8-B; 9-C; 10-C; 11-C; 12-B; 13-B; 14-B; 15-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
Today's TechHelp tutorial from Access Learning Zone focuses on a common bad habit that can seriously slow down your Microsoft Access databases, especially when you're working with data across a network or pulling information from an online server like SQL Server. This issue affects not just speed but can waste network bandwidth, leading to sluggish forms and reports. I have even caught myself making this mistake from time to time.
The question that brought up this topic asks why certain forms and reports in Access run much slower than others, even when they do not have many records. This can be tough to answer without looking directly at your database, but I often tell my Platinum members to send me screenshots of their design views or queries (without sending the actual file, especially if it's proprietary).
To illustrate the issue, I created some example queries that mimic the problem. When looking at your queries, a big hint is if you see that star, or asterisk, included. That asterisk tells Access to return every single field in the table, even the ones you do not actually need. This might seem harmless with a table of just a few rows, but if each record has dozens of fields, and especially if you are joining multiple tables, this can add up quickly and slow everything down.
The solution is pretty simple: only pull the fields you need. For example, instead of using the asterisk, just select the fields you actually want to use like customer ID, names, order date, product information, and so on. There are rare cases where it makes sense to use the star, like when you genuinely need every field from a small table, but always double-check that you really need them all.
Personally, when I first build databases, I often use the asterisk while getting things set up. But as I optimize for performance later, I go back and replace it with just the fields actually in use. If you find yourself pulling from multiple tables, try to only use the star from one of them, since grabbing everything from multiple sources will really drag performance down.
When working with forms, especially over a network, be sure to limit the number of fields you are pulling in. Pulling in all fields unnecessarily adds to the data load and increases wait times.
Kendra, who asked the original question, made these changes based on my recommendations and noticed about a 50 percent improvement in speed. It really makes a difference.
Let me move on to a few more community questions and comments from the website and YouTube.
David asked about removing leading zeros from addresses to match another table's formatting. There are formulas you can include in an Access query to handle this sort of text cleanup. For example, you can write an expression that checks if the first character is a zero, and if so, strips it off. You can apply this logic with an update query if you want to make the change permanent to all records. For more details, I have a video specifically on string functions and using the if function in Access, and you can find the link below.
Ryan had a question about linking his customer list from QuickBooks into Access via ODBC. The table coming in already has QuickBooks-assigned IDs for customers and addresses. He wondered if it's important to also assign auto numbers in Access. My opinion lined up with advice from another member: if you are importing or synchronizing QuickBooks data into Access, keep their original ID as a reference and feel free to assign your own auto number if you want for things you do within your database. There's no harm in having both a natural key and a surrogate key. However, if you are only working with the linked table in Access and not maintaining separate data, just use their existing ID.
Other community questions and comments touched on database design in Excel before building in Access, which is a strategy I often use and recommend. Mapping out tables, fields, and relationships in Excel can be a great planning tool before you jump into Access.
There was also mention of users bumping up against Excel's row limits. If you are managing huge datasets, thousands of rows or more, you should probably be thinking about Access from the start. Excel is powerful, but for really large datasets, Access is far better suited to the task.
Some viewers enjoyed seeing community database examples and suggested sharing design layouts or solutions. While YouTube doesn't allow sharing images in comments, you can post screenshots of your databases on my website. I always love seeing what my students build.
Another question came up about filtering queries using the if function. If you want to exclude records where the result is false, do that in a second query based on the first. This lets you keep all your records in the source query and filter out the records you do not want in the next query with specific criteria.
A longtime Office 2003 user commented on the confusion around Microsoft's different Office versions. I completely agree that Microsoft's product packaging and versioning have become unnecessarily complicated. Many people miss the days when you could pick up a straightforward home or business version. I also remember resisting the shift to the ribbon interface, but once you get used to it, it's definitely more powerful than the old menus.
There was a question about filtering as you type in combo boxes, particularly for a large ingredient list. The original video covered how to filter the combo box as you type, but added navigation with arrow keys wasn't built in. I did provide a solution for this in my fitness database series. Even if you are not interested in the fitness theme specifically, that series includes lots of useful database tricks, such as combining multiple tables in a combo box and better keyboard navigation.
Some viewers mentioned my Word tutorials and how those seem to get even more views than my Access content. This reminds me that I really need to refresh my Word videos one of these days because there is huge interest in Word and Excel tutorials.
I also highlighted a story from a viewer who had a long, successful career using Access and whose skills are still very much in demand. Many businesses continue to rely on Access databases for daily operations, despite pressure to move everything to cloud or web-based solutions. It is important to remember that you can move your data backend to SQL Server for scalability but still keep Access as a powerful frontend application. There's no faster tool for rapidly developing desktop database solutions than Access.
One comment asked about my preference for SQL Server over MySQL. Honestly, it mostly comes down to personal preference and my long experience with Microsoft's ecosystem. MySQL has long been a popular free option, especially for web development, but these days, SQL Server has a free Express edition and Windows hosting is much cheaper than it used to be. Both are solid choices and work with Access as a backend, though connecting to MySQL may take a little more setup.
On the subject of database wizards, I never really cared for the table creation wizard, preferring to build tables manually. The only wizards I occasionally use are the combo box and command button wizards, particularly for beginners.
A comment suggested Access is inferior to SQL Server or that SharePoint offers the same features. I strongly disagree with that kind of comparison. Access is a frontend design tool, while SQL Server is a backend database engine; they have different purposes and are often used together. SharePoint, while useful for some web solutions, does not offer the same level of functionality as Access for desktop applications.
Conditional formatting issues were also raised, such as fields glitching if the back style is set to transparent. The fix is to use a solid back style color for the field if you want conditional formatting to work reliably.
That wraps up today's TechHelp video. I hope you found these tips and answers helpful. You can find the complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Problems with using SELECT * in Access queries
How using SELECT * can slow down forms and reports
Optimizing queries by selecting only needed fields
Reducing network bandwidth by minimizing data pulled
Strategy for replacing SELECT * with explicit field lists
Impact of multiple SELECT * from joined tables on performance
When it is appropriate to use SELECT *
Reducing fields in form record sources for better speed
Using a query to strip leading zeros from address fields
Using the Iif function in a query calculation
Permanently removing leading zeros with an update query
Working with ODBC-linked tables from QuickBooks
Managing natural keys versus surrogate keys when importing data
Keeping external system IDs and assigning your own AutoNumbers
Using the filter as you type technique in combo boxes
Combining data from multiple tables into one combo box list
Handling arrow key navigation in combo boxes with filtering
Conditional formatting glitches with transparent back style in Access forms
Performance considerations for large data sets in Excel versus Access
Article
Today I want to talk to you about a common habit that can quietly slow down your Microsoft Access databases and waste network bandwidth: overusing the asterisk symbol (the star, *) in your queries. Even experienced users fall into this trap occasionally, and I admit, I have done it myself from time to time. But if your database is located on a server or across a network—or especially when using an online SQL Server—this practice can add up quickly and cause real performance headaches.
Let me set the scene with a real-life question from Kendra, who noticed that some of her forms and reports loaded slowly, despite not having a lot of records to display. Others ran fine. Troubles like this can be tricky to diagnose without digging into the database design, but one very frequent cause comes down to how queries are built.
I want you to think about how often you rely on the asterisk in your SELECT statements or queries. When you use SELECT *, you are telling Access to fetch every field from a table—even if you only need two or three. Imagine your table has 50 fields but you only want to show three of them; using * means Access and your network have to carry all 50 fields' data, for every record retrieved. This is not a big deal in small, local applications, but the inefficiency becomes very noticeable over the network or with complex reports joining several large tables.
The solution is simple, but a lot of people overlook it: always specify only the fields you actually need. For example, if you want to display just customer ID, first name, last name, order date, payment status, product name, and quantity, write your query as SELECT CustomerID, FirstName, LastName, OrderDate, IsPaid, ProductName, Quantity FROM Orders. Avoid SELECT * unless you absolutely need every field. Not only does this help your forms and reports run up to twice as fast, but it can dramatically reduce network load and wasted resources.
Of course, if you have a small table and you truly need every field for a particular use, it is fine to use the star. But always review your queries: Are you really using all those fields? Only use SELECT * from one table if you must, and try not to include * from multiple tables in one query. Multiple stars especially can slow things way down.
I encourage you to make a habit of designing queries for speed and purpose. When starting a new database, I often use * for convenience, but then go back and change it to list out individual fields as I optimize for speed. In forms, especially those used over a network, avoid basing them directly on tables or queries that include all fields—select only what you need. This is one of the surest ways to improve performance.
Kendra tried this fix and reported that her slow forms and reports loaded much faster—up to 50 percent faster—which goes to show how important this is for anyone working with larger databases or remote data sources.
Moving on to a related topic, sometimes you need to clean up data as it comes in. Let's say you have a list of addresses and some have leading zeros, which you want to remove to match another table. You can do this in a query using string functions. Here's how you can use a query expression to remove a leading zero in Access:
If you have a field called [Address], use this expression in a calculated column in your query:
NewAddress: IIf(Left([Address],1)="0", Right([Address],Len([Address])-1), [Address])
What this does is check if the first character (using Left) is "0". If so, it returns the rest of the string minus that first character using Right, otherwise it returns the address unchanged. For a table-wide fix, you can use this expression in an update query to strip out leading zeros across all records.
Another frequent question is how to handle unique IDs from external sources like QuickBooks when importing data through linked tables. Say QuickBooks assigns each customer a unique text-based ID and you are using ODBC to link to their customer table. Should you import these into your own Access table, or just work with the linked table? The answer depends on your needs. If you are regularly syncing data with QuickBooks, it is good practice to keep their ID in your table for easy matching, but you can also add your own AutoNumber field as a local key for anything you do exclusively in Access. There's no harm in having both a "natural" key (their ID) and a "surrogate" key (your own AutoNumber). If you are using a live link and not storing your own data, you can just work with the source IDs.
Here's another practical tip: If you want to map out a new database, use Excel or another spreadsheet tool. Set up your table names, field names, and relationships visually before you start building, especially for bigger projects. Planning ahead in a spreadsheet often makes the database design phase much smoother and helps you spot problems early.
Speaking of large data sets, if you are working with more than a few hundred rows, you ought to be thinking in terms of a database rather than a spreadsheet like Excel. Once you have tens of thousands of rows, databases are designed to handle that kind of data much more efficiently than a spreadsheet is.
For Access developers working with combo boxes or list controls that need to filter as the user types, you may want to enhance usability by allowing up and down arrow key navigation within the filtered list, much like you'd expect in modern interfaces. You can use VBA to intercept key presses in the combo box's KeyDown event, giving you control over the behavior and improving user experience significantly.
Sometimes, we encounter situations in queries where a field's value should conditionally display or be hidden. If your query calculates a value with an IIf statement and you do not want to show rows where the result is false or zero, make a second query based on the first, and filter out undesired rows with the appropriate criteria in the second query. This keeps your logic clear and your results tidy.
Also worth noting, when using conditional formatting on your forms, glitches can appear if the field's back style property is set to transparent. Make sure your fields have a solid back style for reliable formatting.
A frequent point of discussion in the Access community is the relationship between Access and larger tools like SQL Server or SharePoint. Some claim that Access is "inferior" to SQL Server. But remember, they serve different purposes. Access is a rapid application development tool for building desktop front ends, forms, and reports efficiently, and it can serve as a front end to SQL Server databases. Each has its strengths: SQL Server for scalability and heavy lifting, Access for front-end interface design and rapid prototyping. SharePoint, meanwhile, is for web-based solutions, but it does not offer the flexibility and features for form and code development that Access does for desktop applications.
On the topic of wizards in Access, some developers love them, and some, like myself, prefer to build things manually for greater control and understanding. Wizards are great for learning or quick setups, but you'll progress faster and solve more problems by designing tables, forms, and reports yourself as much as possible.
For anyone moving from older versions of Office like Office 2003 to more recent releases, the biggest adjustment you'll face is the ribbon interface. While strange or awkward at first, many people (myself included) have found that the ribbon is ultimately a better, faster way to work once you get used to it.
Lastly, a quick answer about why I prefer SQL Server over MySQL: it is mainly personal preference and background. Both are great database backends for Access or websites, and both work well as long as you study their setup and connection details. I used SQL Server simply because I worked in the Microsoft ecosystem, but MySQL is absolutely a fine choice—especially if you are building web applications.
So, to sum up: always optimize your queries, limit the number of fields you bring into forms and reports, clean and match your data as needed, and choose the best tools for your workflow. Don't be afraid to mix approaches, plan your design before you build, and always tweak your queries for speed and efficiency as your database grows. By adopting these habits, you will build faster, more reliable Access applications, whether you are working alone or as part of a team, and your users will thank you for the improved performance and reliability.
|
