Quick Queries #62
By Richard Rost
26 days ago
The Missing Index Killing Your Database Performance
In this Microsoft Access tutorial, we'll talk about the importance of indexing foreign keys for better database performance, answer questions about migrating old Access databases to newer versions, discuss tips for improving form and combo box speed, and cover various user-submitted topics including data migration, dynamic record copying, counting records by criteria, working with linked tables, and common feature requests from the Access community. This is Quick Queries number 62.
Spencer from Orlando, Florida (a Platinum Member) asks: As my tables keep growing, my forms are loading slower and slower. I checked my network speed and that seems fine. I've got relationships set up, and I compact and repair regularly, but my combo boxes still take forever to load. What could be causing this? Are there any other tips to speed up my database?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, missing index, foreign key index, combo box slow loading, index design view, naming conventions, convert mdb to accdb, backup database, import old forms, linked tables, aggregate query, dynamic copy records, after update event, pick customer address, database performance, continuous form control, conditional formatting, frontend customization, tab control, increase max database size, split database backend, export to docx
Transcript
Today we're going to talk about one very important missing index that could be killing your Microsoft Access performance. I see this a lot. People never think to index this thing. I'm going to show you in just a minute.
Plus, we've got your questions from YouTube or just my reply to some of your comments. YouTube and other sites have questions from different places too. I am on different social media from time to time. I know I have a lot to go over. This is Quick Queries number 62.
Today's question comes in email from Spencer from Orlando, Florida, one of my Platinum members. Spencer has been a member for a while now. He says, "As my tables keep growing, my forms were loading slower and slower. I checked my network speed; that seems fine. I've got relationships set up, and I compact and repair regularly, but my combo boxes still take forever to load. What could be causing this? Are there any other tips to speed up my database?"
So I had Spencer send me some screenshots of his database design. I don't accept file attachments. That takes me too long to go through a database file. I don't do that anymore. Once in a while, I'll ask someone to send me some screenshots, just like I tell them to post in the forums with screenshots - it's easier with screenshots. I don't have to go digging around. But I said, send me all your design from your tables and things like that. And I saw that he's missing something very important that a lot of people don't think to do.
So what is this thing that's missing, you ask? Well, foreign key indexes. No one ever thinks to index their foreign keys. Normally you set up indexes for fields that you search and sort on, and that's what I teach people in my regular classes and stuff. But you don't think to index the foreign keys because you're not thinking in your brain that you're searching and sorting on that. But every time Access makes a relationship between two tables, it is basically searching and sorting on those columns.
What does that look like in the real world? Well, we have customers. Customers have orders. So in the order table, the foreign key is the customer ID. So if you go to the order table and go to design view, here's our order ID - that's our primary key. Here's our customer ID - that's a number, foreign key, number type long - and down here, index should be set to Yes (Duplicates OK).
In Spencer's database, he had them all set to No, and so every time Access makes that relationship, if you have customers and orders joined, Access has to look through all of the orders for every customer in that join, and that will slow things down, especially over a network.
In Spencer's database, he told me that he's been using this database file forever, so it's probably something that he upgraded over the years. I think he said he started with 2000 or 2003, so Access didn't always set that to Yes (Duplicates OK) for number fields.
Now, here's what actually happens, and this is why naming conventions are so important. If you just add a field down here, like let's just call it "customer" and make it a number of type long integer, notice that it's not indexed. Access will only make that index if it sees the last two characters of the field name say "ID." So if I call this something "ID" and make it a number, look at that - it made it an index. So that's why you should always end your indexed fields, whether they're primary keys or foreign keys, with "ID." He doesn't have that either. He's got, for example, "customer" in his order table or contact table.
So I told him, and it's a lot of work for him to take this 20-some-year-old database and change all the field names. The solution for him was just go through and make sure you've got everything indexed properly. But that's why you should stick with my naming conventions. I know what I'm doing. Beginners always ask me in the beginner classes, why do we have to do it this way? Just do it this way for now. I'll explain why later in the expert classes. Do as I say, not as I do; some of my old databases were bad too.
If you want to learn more about indexing, go watch this video. I also cover it in several of my classes. I've got lots more links on this page too. I start covering indexing in Access Beginner 4, and then I go over more in the more advanced classes. There's composite keys, which is an index between two different fields, multi-field indexes, all kinds of stuff on here, so check it out. I'll put a link down below.
Next up is a question posted on my Access Learning Zone Facebook group. I don't usually promote Facebook because I hate Facebook. In fact, I have a whole video as to why I hate Facebook. You can go watch it if you're curious. But this post from Tanny says, "I was wondering if it's possible to migrate my current 2003 database I've been using with Windows 10 over to 365, Access 365, Windows 10 updated. Now every time I try to save data, it just shuts down. I would lose over 30,000 customers if it can't be moved to an updated version or fixed to still operate properly. I had this custom built years ago."
Yes, you should be able to move it over to Access 365. First thing is make sure you've got a good backup copy before you start messing with stuff. Make sure you've got it backed up. That's important. I cannot emphasize this enough. Back up, back up, and then back up your backups.
Sometimes you can open a 2003 MDB file right in Access 365 and it will convert it. Sometimes it does, sometimes it doesn't. I haven't actually done this in a long time myself. It's been a long time since I've had a 2003 MDB file. I've got some on some old CDs that I just recently copied over to my hard drive that I found in my storage unit. So I'll be playing with those a little bit later, but sometimes it works, sometimes it doesn't.
If you can still open the old database, hold down the shift key and open the database up. That will bypass any startup code. You should be able to get to the tables and export those tables. If nothing else, just export them as a text file or an Excel file or whatever. So even if you can't copy the program part of it over - the forms and imports - you at least still have your data and you won't lose your 30,000 customers. That's the most important thing.
Usually, if Access does open and import your database, usually the forms and the reports themselves will come through fine, but it's usually the fancy stuff that breaks, like any weird VBA code you've got in there or custom controls, things of that nature. It's like restoring an old car and putting a 2025 engine into it. Then, of course, you've got to worry about 32-bit, 64-bit issues. There's a lot going on there. But give it a try. It might go smoothly.
You might have to create a brand new blank Access database shell using Access 365, the newer version, and just import the objects out of your old database, bringing all the forms, all the queries, bring them all in separately. Sometimes that works. Sometimes you've got to bridge the versions in the middle, because sometimes the new version of Access can't import something that old, so you might need to find someone with a version in the middle, like 2013 or 2016, that might be able to import your old file, and then you can convert that over to something else.
There's a lot of moving parts. Give it a try, see what you can do. If you have problems, post more screenshots in the group there, or post them in the forums on my website. Or if you want someone to help you through this, I have a whole bunch of consultants, Access consultants, on my website on my Access Developer Network page. Check it out. I'm sure one of them will be able to get you up and running.
While we're doing the tour of other social media sites, this is one that I saw posted on Reddit. Someone said, "Anyone know where I can take some Access classes in person ideally in New Jersey or Manhattan if that's all that's out there?" One of the moderators, NRGins, said, "Check out your local community college. They often have classes in Access," which is true, a lot of them do.
Then, of course, I had to chime in - in-person classes are so 1990s. So much great stuff online now - hint, hint. Just kidding. I couldn't help myself. That said, there are benefits to in-person training, especially if it's a small class. You can get one-on-one time with the instructor, ask your own questions, and sometimes just learn better when you want a real classroom setting.
I used to teach live in person. I used to have my own training center back in Buffalo, New York, in the early 2000s, and I used to love the little interaction with four or five students at a time. In addition to community colleges, check local businesses. You might find a lot of computer companies that have a training room set up in the back. That's what I used to do, and a lot of those guys really know what they're doing. That's how I got started. Go to Google Maps and search for computer training and ask them if they do Microsoft Access.
Definitely be careful and check the instructor's credentials. When I was in college - we're talking 1990 through, I think, 92 or 93, I don't remember, I dropped out, I forgot exactly when - but I had teachers who barely knew where the power switch was. That's not an exaggeration. Some of them needed help turning the projector on, the projector that was connected to their laptop. A lot of the times, you don't want an instructor who's just one chapter ahead of you in the book.
I'll be honest, I was sometimes that instructor. Sometimes I got companies who'd come to me for Word or Excel or Access training, and then they'd be like, "Well, hey, we have three or four people that want to learn Photoshop. Do you teach that?" And I was young and hungry, I needed the money. "Yeah, yeah, of course I teach Photoshop. Let's schedule it for two weeks from now," and I'd go home and learn Photoshop. So be careful with who you get.
That in-person training reminded me, I travel quite a bit for a few different reasons and I've been thinking it might be fun to do some local meetups. Nothing big or formal, not like a formal training setting, but like an Access coffee talk. Just get together in maybe a local bookstore or a coffee shop, we could talk databases, that kind of thing. So if that sounds like something you might be interested in, drop a comment down below and let me know what city you're in, and if enough people are interested maybe I'll start doing this. I travel a lot and I've always thought it'd be nice to hang out and meet up with some of you guys. Let me know.
Once in a while, speaking of Reddit, I get comments like that - they really make me smile. These comments really make my day, and I appreciate it when you guys post stuff like this. It really makes it feel rewarding for doing what I do, so thank you, thank you very much.
All right, let's head over to YouTube now for comments. Mr. Lin Skill says this is cool, and he's talking about my Excel-style equations where you can put, in any text box, if you hit equal, it'll pop up an input box and ask you for an equation. Then you can type in an equation, it'll save that in the field so you can do basic sums and any kind of equation inside of a cell, just like in Excel.
The way that I accomplish this is by coming down here and saying, if you hit equal as the first key, it pops up an input box, and you can type in "10+3" or whatever, hit enter, and it evaluates and puts that in the box.
Mr. Lin Skill says, "It's cool, though I think it'd be a little tidier without the input box and the after update event checking if the left of the string is equal, trimming and evaluating, and then returning the result to the same text box." I thought the same thing, but the after update event never fires because it doesn't see it as a valid number. So if you got it to work with the after update event, I want to know about it.
I have another solution, and I'm going to share it in the next fitness video. But if you try to put something like that in the after update event, let's use family size here for example. After update event - if you try to say, "if the left of family size, one equals equals, then just message box 'Hi'," save that - and now if you come back here and try to do that, "=1+1," bam, you see the form tries to validate that that's a valid number before the after update event even runs. You can't even get to it. I can't even click off of that field, so this after update is never going to run. Let me hit escape here.
Even if you put the message box running here, and you go "=1+1," see, it doesn't even get to after update. I don't even think it gets to before update. Let's try before update too. I played around with this for a while before I settled on the input box. Let's try this in before update. "=1+1," see, it's throwing an error before any of those events run.
I do have a solution, and it's a little complicated, and I will share that in the next fitness video. It's probably going to be fitness 54 since I already have through 53 recorded, so look for that.
Dingus Baddest is talking about my pick address series where I show you how to pick an address for a customer because customers could have multiple addresses and you want to pick one of them to go on their invoice. Dingus Baddest says, "If the customer is changed, I would be tempted to disable the invoice button to remind users to pick the address." That's right. You could do that - for example, if the address combo box is null or it has an invalid value. If the user changes the customer combo box, you might also want to set the address combo box equal to null and then, if that's null, don't let them print the invoice without an address.
But that's a business rule - that's something you want to do for your business. If you have a cash and hand walk away type business, then you might not need to require an address, so it's totally up to you, but definitely a good idea.
Shadow Dragon appreciates my "checking off a check box." Check off - get it? Right up there with Klingon. I see what you did there.
Another comment from Dingus Baddest: the part I wanted to mention was that he's interested in a dynamic way to copy records between tables, dynamic meaning if you add a new field the code can detect it and copy it. I actually have something like this that I'm planning on covering in an upcoming developer lesson, like Developer 51 or 52, whatever I'm on right now, because I need something like that myself.
Basically, what you have to do is analyze all of the fields in the source table and then analyze all the fields in the destination table, and if a field doesn't exist, add it. I run into this problem all the time because when I archive old contacts or old emails, if I add a field to the source table, it throws an error if that field doesn't exist in the destination table, and you can't just use a basic update query. So that's definitely coming up in a developer lesson. I'll be building that very soon, so stay tuned.
Next up, Brian says, "How do I count grades for a specific class from a table with different classes?" There are a million different ways to do this stuff. The technique that I show you in this video would work using a count function in a form footer if you just put a criterion on your query that's underneath it. Put a criterion on for the specific class and then you're good, or you could use an aggregate query and get counts for all of them. There are lots of different ways, and it all depends on how your data is set up. Let me give you some videos you can watch to get more information.
Here's a video on basic query criteria so you can just say, when the form opens, have it prompt you for what class ID you want or something like that. If you want to put the value in an open form, like have it on your main menu, have, "What's the class you want?" and then you hit a button, it puts the criterion in the query, and then it shows you on the form what class you're looking for. That's another option. Or you could use an aggregate query, which will give you the count of all of the classes grouped together. Again, it depends on your data. Check these out. If you still have questions, post another comment, and don't post a reply on this comment - I almost never read reply comments. Just post a new top-level comment.
Next up, we have Crono Meta saying, "You have to actually store the data." I think you mean you're asking if you have to store the data in Access. "I just want to make relational searches for our customer data that sits in an intranet." I would need to make sure the data is not stored. That's fine. In Access, you can use something called a linked table. If you've got your data stored in some other data source, whether it's SQL Server or even just a collection of Excel files or whatever, you just link to them. You don't have to actually import that data into Access, and you can use all of Access's tools to work with the data. That's one of the beauties of Access; Access is really a front end. Yes, it's got its own little simple database stuff, but it's not really a database server like SQL Server. It's just a front end, so you can connect it to whatever data that you want. That's what Access is good for.
This video will give you some additional information. I show you how to link to Excel, but the data source really doesn't matter. There are lots of different data sources you can link to.
Once in a while I do what I call a conversation starter, just to get some feedback, get your ideas, see what everybody's thinking in the Access ecosystem. Last time I said, "What is the one feature in Access you wish Microsoft would improve or update?" Some people gave me a lot more than just one, but that's okay. I like your feedback anyway.
Raymond had one of the most thoughtful replies. He said that reports and subreports need to work as well as forms and subforms. Good luck. Me, personally, as a developer, I know that I spend probably 10 times as much time working on forms as I do on reports. Reports, basically, just in my case, are for presenting information to someone else who's not using the database. I do most of my work in forms, but I get what you're saying.
He says not having code that runs in subreports is crippling. So yeah, I get it, especially if you're trying to make a subreport that is made up of a bunch of unrelated reports. He also talked about aesthetics - more modern aesthetics - that's a good one, and VBA's reference is inconsistent. Now, we don't want Microsoft making the VBA reference library too good because then you guys wouldn't need me.
Sammy said he'd like to see closer integration with SQL Server. On that note, I personally would like to see Microsoft do more to make it easy to link Access and SQL Server, like with linked tables, without having the password visible in the connection string. That's one of the things I hate the most.
Donald said that he'd like to allow more granular control over continuous form rows. I get that - CSS styling or CSS-like styling.
Adam said that his complaint is about the weakness of doing things at runtime in comparison to what you can do with things like a web-based app. I get that one. Would like to see adjustable checkboxes. Definitely. I've got a video on how to make a big check box, but it's basically just a graphic.
Debbie says she'd like to see when you close the office application, when you close an Access application, you get prompted to save changes. You should - normally that's how Access works if you make design changes, at least. It should say, "Hey, do you want to save this form or report that you're working on?" Data, not so much. Data is just automatically saved. So not sure exactly what happened there, but something might have glitched on you.
Gary wishes that Access would create standalone exe applications. I see that a lot from people who want to make it so that it doesn't look like an Access database, but they can distribute it to other people. I get that a lot.
Glenn says that when the navigation pane opens up, it pushes over forms to the right where that can be annoying because the rightmost thing gets pushed off the edge of the window. So that'd be better as an overlay. I agree with that - because then you get this looking thing and this on YouTube.
We got some comments. I post the same thing on a bunch of different places just to see what all the feedback is going to be like. More options to edit the visual interface, update the VB IDE. That's from the 90s. The calendar, the ability to move back and forth by years, the developer section of its offline help. Now, like I said, we don't want that to get too good because then you don't need me. I'm just kidding. I still end up either Googling or ChatGPT-ing a lot of stuff like that, too.
Connection - do a web app so you don't have to use a server. Well, you'd need a server for the data, like SQL Server at least, but a nice front end developer. It would be nice if you could take your Access forms and just make them into web pages. I've got kind of a prototype I've been working on that kind of does that, but it's not ready for prime time yet.
While that's a loaded question, it's a conversation starter. I want to see what you guys have to say. Sean pointed out that if you reference one library file from another file, the functions and stuff are visible but not editable. That's a good catch. Access should throw a warning if you try to edit some reference library code.
This one is for Wego Asmarilda. This one is on their list. This should be - it was supposed to be out last month. Hopefully, it's out soon. Zooming in on forms and stuff.
Andrew's talking about resorting IDs - remember, those IDs are not for you, those are for Access to use internally. But I do agree that they should make some kind of easier way to have an incremental counter instead of having to make them yourself.
Update the original charts instead of the new Metro ones. Maybe. Proper Gantt chart, that could be handy. Regex, definitely. All good ideas, folks.
Not that many comments in my Reddit group, but we did get one down here - when a form label contains all the letters in design view, it's fine but then it's too short in form view, cutting off the ending letters. Yeah, that is annoying.
Got a bunch of replies in the Facebook group - ability to control zoom for zooming. Again, they're supposed to be adding that. Database run online in the web, Android applications, keep bringing back the web app - seeing that everywhere. When you create a form, size online to grid to be automatic. You can set up a template for that. I use a template.
Co-Pilot - they should put Co-Pilot in it. I mean, it's in Word and Excel, why not? Auto size according to modern resolution - that would be nice. I've got a template for that, but again, it's a lot of work.
There's another - web version, version control from Boyd, better compatibility with touch screens. That's more about how you design your forms than anything, but sure.
No replies on Twitter. I'm sad. I refuse to call it X, it's still Twitter. Then again, it only got 45 views so I can't complain. What do I have, 500 followers on Twitter? So if you're on Twitter still, here I am, follow me.
No replies on LinkedIn either. Sad. I forgot I also posted it as a short, too, like my little picture. Yes, that's AI, by the way - I took my face and put it on somebody else.
Open up the source code. Microsoft has essentially ended development of this great tool. No, they haven't. No, they have not ended development of this tool. They're not putting a ton of resources into it, and I think they should. I think it could be a much better tool than it is if they'd put in some actual significant investment into it. But, yeah, why not put it in the hands of open source developers? There's a huge install base of Access users - hundreds of thousands, if not millions of users. So I'm sure when it comes to the time, and they might - they just released the source code to something like Windows 95 or one of those old DOS things.
Maybe update the control for modern interfaces, additional options for conditional formatting, definitely. Adjustable font sizes - when you zoom in and out at different resolutions of your monitor, that stuff too, sure.
Allowing Access to automatically repair and fix the file on closing - it does have compact on close. I don't like using it, but that does exist for preventing it from growing unnecessarily in size. That feature is already in there.
The check box - we talked about that already. Vertical alignment of text in controls. Executable - that'd be nice. There are some other tools. I used one years ago that let you package and deploy it, but you still need Access.
Export to docx - you can make a docx file using Word automation from Access. It's not easy. I have a couple videos on that.
Nitpicking - no, this isn't a nitpick, this is something that I wish they would do. When you add an existing field, set the field's name equal to the control source, or add the ability for the command button or combo box wizard to ask for the name of the combo box. That's something I always end up having to go back and do.
Modern code editor like VSCode. Rank in the column like Excel; you can do it, it just requires a little work, I've got a video on it.
Modern forms that are tested and worked before being released to users. Update the query by interface to allow nested subquery options. Now, that might be a lot to ask.
Great query to delete duplicates - you can do that, you just have to use the count function. I have a video on it.
First, I like the photo, looking good. I wish I could say that's an actual photo, but it's not - I took my photo and put it on someone else with one of those face swap AI tools. But that is my face, kind of from 10 years ago.
Change in the appearance of the front end so the user doesn't know it's Access. I have a couple videos on that. There are some things you can do, but your average user can be fooled; a developer won't be fooled.
Better implementation of the tab control. I don't like the tab control at all, I've hated it since it was released, and yes, I like to use a subform. I think I answered you with this one. I think I answered it in the last Quick Queries. I didn't go over all these, though, but I do cover using a label to jump around. AI is another popular one - how about increasing the max size to one terabyte?
That would be tough, but remember, you can have multiple backends. Even though there's a two-gigabyte limit, you can have multiple backend files. You just can't have one table that's over two gigabytes. Even in my 30-year career, I've never needed a table that was more than two gigabytes. If you have more than two gigabytes in there, you've got something in there that shouldn't be in there. If you're storing attachments, you shouldn't be storing attachments in your database.
The dbo naming convention - that's kind of annoying, but I've got its point though.
No comments over on TikTok. I've been trying with posting some of these shorts on TikTok, but I don't think TikTok is working for what I do. I need at least five to ten minutes at least, and TikTok seems to be people who are mostly just quick scrollers. I've tried posting a few short tips and stuff, but I get a couple hundred views, tops. YouTube is still where it's at for me.
That's about it for this week's Quick Queries. Thanks for watching.
Make sure you stop by the website, see what's new, always something new. If you're curious about the things that bounce around in my head, stop by and check out my Captain's Logs, little blog where I try to write something every day - like yesterday was "If Star Trek transporters were real, is that you or is that a copy and you're dead?" So that's a philosophical question.
Make sure you stop by the merch store. Get a hat, get a sweater, get a teddy bear, get a mouse pad, whatever. And remember, if you set it, you got to forget it, and debug compile once in a while - all my stupid things I put into my mouse pads.
If you want to get a copy of my Access book, it's on Amazon. It's basically the same thing as Access Beginner Level 1, but you get a nice printed copy of it. If I meet up with you in one of those meetups we were talking about, I'll sign it for you - sound cool? It's also available if you have Kindle Unlimited, like I do because I read a lot; it's also available for free with Kindle Unlimited.
That is going to do it for your TechHelp Quick Queries video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Identifying and indexing foreign keys in Access tables
How lack of foreign key indexes impacts performance
Setting foreign key indexes to Yes (Duplicates OK)
Access naming conventions for auto-indexing ID fields
Network performance issues caused by missing indexes
Differences in index defaults for various Access versions
Migrating Access 2003 MDB files to Access 365
Using Shift key to bypass startup code in old databases
Exporting tables from legacy Access databases
Importing objects into a new Access database shell
Bridging Access versions for legacy database migration
Handling issues with forms and VBA during migration
Using linked tables to external data sources in Access
Linking Access to Excel and other external data sources
Using Count function in query or form footer
Adding criteria to queries for specific record counts
Aggregate queries for grouped counting
Handling after update and before update events
Why after update event fails with invalid number input
Controlling invoice button based on address selection
Automatically nulling dependent combo box values
Dynamic copying of records between tables
Detecting and copying new fields between tables
COMMERCIAL:
In today's video, we're talking about a common missing index in Microsoft Access that could be killing your performance, especially if you are not indexing your foreign keys. We'll discuss database speed, how to fix indexing issues, and answer questions about upgrading old Access files, taking in-person Access classes, handling Excel-style equations in text boxes, copying records dynamically, and ways to count specific records in your tables. You will also hear thoughts from other viewers about features they wish Microsoft would add to Access, like better reports, more SQL Server integration, modern design tools, and more. Plus, we'll talk about linking Access to other data sources and which features developers want most. 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 a common missing index in Microsoft Access databases that can hurt performance?
A. Indexing the foreign key fields
B. Indexing every text field
C. Indexing only date fields
D. Indexing only primary keys
Q2. Why is it important to index foreign key columns in Access?
A. Access searches and sorts on those columns when joining related tables
B. They are always used as lookup fields for users
C. Indexing foreign keys prevents data entry errors
D. Indexing foreign keys encrypts the data
Q3. According to the video, what naming convention helps Access automatically index foreign keys?
A. Field names ending with "ID"
B. Using all-uppercase field names
C. Prefixing all field names with "FK"
D. Giving fields descriptive names only
Q4. If you add a numeric field called "customer" to your orders table, why might Access not automatically index it?
A. The name does not end with "ID"
B. Numeric fields cannot be indexed
C. Only text fields are indexed by default
D. The table already has the maximum number of indexes
Q5. What is the recommended approach before trying to migrate an old Access 2003 database to Access 365?
A. Make a full backup copy
B. Remove all VBA code
C. Export all reports to PDF first
D. Convert it to SQL Server
Q6. If opening an old Access database in a new version fails, what is a good fallback to preserve your data?
A. Export your tables to a text or Excel file
B. Only copy your forms to a new file
C. Delete unused queries
D. Reinstall the old version of Access
Q7. What often causes forms in Access to load slowly as tables grow larger, especially with combo boxes?
A. Missing indexes on foreign key fields
B. Old field names longer than 10 characters
C. Excessive use of macros
D. Lack of parameter queries
Q8. What should you check if Access will not let you enter or edit data in a form's text box using the After Update event for Excel-style equations?
A. Access validates data type before running After Update
B. You forgot to set the field's Required property to No
C. Only Before Update events are allowed on text boxes
D. The Control Source is not set
Q9. If you cannot move an old MDB file straight to Access 365, what should you try next?
A. Use an intermediate Access version like 2013 or 2016 to convert the database
B. Switch to a different database tool
C. Only copy the queries and ignore the forms
D. Break up the tables by year
Q10. What feature allows Access to connect to external data sources like SQL Server or Excel without importing the data?
A. Linked tables
B. Subqueries
C. Data macros
D. Save As command
Q11. When might you consider using an aggregate query with a count function in Access?
A. To count grades by class in a table with multiple classes
B. To generate primary keys automatically
C. To sort combo box results alphabetically
D. To encrypt data in a table
Q12. What is a composite index (or composite key) in Access?
A. An index made from multiple fields
B. An index that only works on primary keys
C. An index that is manually updated
D. An index that prevents duplicate records only
Q13. What is an advantage of in-person Access training mentioned in the video?
A. Direct interaction with the instructor for customized help
B. Free lifetime access to training content
C. Local classes are always more advanced
D. Certification is always included
Q14. What was one complaint about Access that came up during the conversation starter?
A. Difficulty in integrating with SQL Server securely
B. Lack of support for PDF export
C. Can't sort text fields in queries
D. Unable to import CSV files
Q15. According to the video, what is a good practice when designing tables with relationships in Access?
A. Use field names ending with "ID" for keys and foreign keys
B. Never use numbers in field names
C. Avoid using relationships altogether
D. Always use text fields for keys
Q16. What was said about the use of attachments in Access databases?
A. Storing attachments in your database is not recommended
B. Attachments are required in all modern databases
C. Only external attachments are allowed
D. You can store unlimited attachments with no issues
Q17. What database file size limit was mentioned for Access?
A. 2 GB per database file
B. 1 TB per database file
C. 10 MB per table
D. No file size limit
Q18. What is a linked table in Access primarily used for?
A. Connecting to and working with data stored outside the current Access file
B. Storing file attachments
C. Creating lookup fields
D. Automating VBA code generation
Q19. What is one reason people want Access to create standalone executables (EXEs)?
A. To make distribution to other people easier without revealing it is Access
B. To prevent data entry errors
C. To enable VBA macros to run faster
D. To connect directly to the internet
Q20. What is the role of forms in Access compared to reports, based on the instructor's experience?
A. Forms are used much more for interaction and data entry
B. Reports are used more often for automation
C. Reports have more coding involved than forms
D. Forms are only for read-only views
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-A; 20-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
In today's Quick Queries video from Access Learning Zone, I want to address a very common issue that can significantly hinder your Microsoft Access database performance. This is something I encounter frequently: people forget to set a critical index, and it persists even in databases that have been in use for many years. Let me explain what you might be missing.
First, I'm going to answer viewer-submitted questions from various platforms. This is Quick Queries number 62, and I've picked out a few inquiries from email, Facebook, Reddit, and YouTube.
Let me start with a question I recently received in my email. Spencer asked why his forms were becoming slower and slower to load, despite maintaining good network speed, keeping up with relationships and regular compact and repair. He pointed out that combo boxes in particular were taking a long time, and he wondered what else he could do to improve database speed.
I asked Spencer to send me screenshots of his table designs rather than full database files. Working with images is much quicker for me, and it allows me to spot issues efficiently. It turned out he was missing indexes on his foreign key fields, which is a surprisingly common oversight.
Why are foreign key indexes important? Many people think only to index fields they frequently sort or search. What is often overlooked is that Access relies heavily on the foreign keys when performing joins between tables. So, for tables like Orders, which have a CustomerID field linking to a Customers table, that CustomerID should be indexed. The index setting should be set to "Yes (Duplicates OK)" for these foreign keys.
In Spencer's case, his database started life many years ago, likely with a version that didn't automatically set foreign key indexes. Access will only set this index automatically if your field names end in "ID." For instance, if your field is named just "customer" instead of "CustomerID," Access will not recognize it as something that should be indexed. This highlights the importance of sticking to naming conventions and always including "ID" at the end of your key fields.
If you have an older database or have inherited a database that uses inconsistent field names, you'll need to go in and set those indexes manually. I always tell people, especially beginners, to use solid naming conventions even if you don't fully understand the reasons at first. It pays off in the long run for performance and maintenance.
If you want more instruction on proper indexing and how to use indexes effectively in Access, I have introductory and advanced classes that go over these topics, including composite and multi-field indexes.
The next question I came across was posted in my Access Learning Zone Facebook group. Someone asked about migrating a very old Access 2003 database, which they still use on Windows 10, to Access 365. They were worried about losing data since every time they tried to save, the application would crash.
First and foremost, before doing any kind of conversion or upgrade, make sure you back up your database. I can't stress the importance of backups enough. Sometimes Access 365 will open and convert older files automatically, but sometimes it won't. If you can still open the old file, you can bypass startup code by holding the shift key and exporting your tables. If you can't migrate the forms and reports, at least you'll have your data.
Usually, when Access does import older databases, basic forms and reports will come across. Custom code and controls are more likely to cause issues, especially across such a large jump in versions. You might find the best approach is to create a brand new, blank database in Access 365 and import your old objects one at a time. Sometimes, when moving between very different versions, it helps to use an interim version, like Access 2013 or 2016, to bridge the gap.
If you run into trouble, share screenshots in the forum or the Facebook group. If you need one-on-one help, I maintain a list of consultants in my Access Developer Network.
Switching over to Reddit, someone asked about in-person Access training, especially in the New Jersey or Manhattan area. Community colleges are a great option; many offer Access classes. I chimed in that there is plenty of excellent online training available as well, but in-person classes can be beneficial, especially for smaller groups or if you need direct access to an instructor.
Over the years, I have taught many live, in-person Access classes, and I agree that interaction can be a big benefit. If you're looking for instructors, check out computer training centers in your area, but always check their credentials first. You don't want a situation where your instructor is barely ahead of you.
On the topic of meetups, I'm considering organizing informal gatherings to talk Access and database design at coffee shops or bookstores when I travel. If that's something you're interested in, let me know your city in the comments.
Now, a quick round of feedback and comments from YouTube. Mr. Lin Skill commented on my Excel-style equations feature in Access. In this setup, you can type an equal sign in a text box, and an input box pops up so you can quickly run simple calculations. He suggested a cleaner approach using the AfterUpdate event, but due to how Access validates form entries, the event never actually fires if the value isn't a valid number. So, the input box solution works best, but I am working on a more advanced method for a future video.
Another frequent commenter, Dingus Baddest, talked about dynamically copying records between tables even when new fields are introduced. That's something I also need for my own projects, and I plan on covering it in an upcoming developer lesson. The idea is to build logic that checks both source and destination tables and matches up fields dynamically, adding or ignoring fields as needed.
Brian asked how to count grades for a specific class from a table that holds data for multiple classes. There are numerous solutions for this in Access, such as using criteria in your queries or grouping data with aggregate queries. I have videos on each of these methods for people who want to learn more.
Crono Meta wanted to know if you had to actually store data in Access or if you could just use Access as a front end for data stored elsewhere, like on an intranet. The answer is you can use linked tables in Access, connecting to all sorts of external data sources without importing the data. Access works very well as a front end for these types of scenarios.
Every once in a while, I pose a conversation starter question to my viewers. Recently, I asked what features in Access you wish Microsoft would enhance. The responses were interesting: more robust report and subreport handling, modernizing the design interface, improved integration with SQL Server, more flexible formatting and styling, making Access forms and code portable to the web, and even the ability to package applications as standalone executables. Many also wanted more advanced code editing tools, Gantt chart support, regex, and better zoom options.
Some people voiced the common request for Microsoft to open source Access or invest more heavily in its development. While development hasn't stopped, there hasn't been significant innovation in recent years. Others asked for improvements like better handling for continuous form rows, easier customization, and options for conditional formatting or automatically updating control names.
Access's 2GB file size limit came up as well, with viewers pointing out that you can use multiple backend files. You just need to avoid storing attachments or other large objects directly in your Access tables.
I also post these topics across other social platforms. Facebook and Reddit groups had some good discussions, but other platforms like Twitter and TikTok tend to get a smaller audience for this kind of content. YouTube remains the best place to interact with me and other Access fans.
That's it for this installment of Quick Queries. Thanks for tuning in. Be sure to visit my website, see what's new, check out my Captain's Logs for discussions on tech and philosophy, or pick up Access Learning Zone merchandise if you want to support the site. You can also find my Access book on Amazon, which is basically a printed version of Access Beginner Level 1, and it's available on Kindle Unlimited.
To see complete video tutorials with step-by-step instructions and all the details we covered here, head to my website at the link below.
Live long and prosper, my friends. I'll see you next time.
Topic List
Identifying and indexing foreign keys in Access tables
How lack of foreign key indexes impacts performance
Setting foreign key indexes to Yes (Duplicates OK)
Access naming conventions for auto-indexing ID fields
Network performance issues caused by missing indexes
Differences in index defaults for various Access versions
Migrating Access 2003 MDB files to Access 365
Using Shift key to bypass startup code in old databases
Exporting tables from legacy Access databases
Importing objects into a new Access database shell
Bridging Access versions for legacy database migration
Handling issues with forms and VBA during migration
Using linked tables to external data sources in Access
Linking Access to Excel and other external data sources
Using Count function in query or form footer
Adding criteria to queries for specific record counts
Aggregate queries for grouped counting
Handling after update and before update events
Why after update event fails with invalid number input
Controlling invoice button based on address selection
Automatically nulling dependent combo box values
Dynamic copying of records between tables
Detecting and copying new fields between tables
Article
Today, I want to address an important topic that often gets overlooked in Microsoft Access databases: indexing your foreign keys. Many users carefully index primary keys and fields that are frequently searched or sorted, but they forget about foreign keys, even though missing these indexes can lead to major performance issues, especially as your tables grow over time.
A foreign key is a field in one table that links back to the primary key in another table—think of an Orders table where each order has a CustomerID pointing back to the Customers table. As your database grows and you join these tables, Microsoft Access has to repeatedly match records across those keys. If the foreign key is not indexed, Access must scan through the entire table each time it performs a join or pulls related records, which can make forms and combo boxes load painfully slowly.
Here is what you need to do: Open your table in Design View and look at your foreign key fields. For example, in the Orders table, you might have an OrderID as the primary key and a CustomerID as the foreign key. Select CustomerID, and at the bottom of the screen you will see an Index property. Make sure to set it to Yes (Duplicates OK). This tells Access to index the field while allowing duplicate values, since multiple orders can reference the same customer.
Older versions of Access might not automatically create this index for you, especially if you are migrating a database from as far back as Access 2000 or 2003. Naming conventions also play a role: Access will sometimes auto-index fields ending in "ID" (like CustomerID), but it will not index a field just named "Customer." That is why I always recommend naming all your key fields with the "ID" suffix and being consistent with your naming strategy.
If you have an old database with many tables and relationships, you will want to check all your foreign key fields manually and set the index as needed. While it's a bit of extra work, the performance improvements can be significant, especially in larger databases or when working over a network.
You do not need to index every field in your database—just the fields that are used as keys in relationships or that are frequently used for searching and sorting. Over-indexing can actually slow down data entry and take up space, so focus on where it really counts: primary keys and foreign keys.
Moving on to other common Access issues, some users find themselves needing to upgrade old databases, such as moving a 2003 MDB file to the latest Access 365. First, always back up your files before you attempt anything. Often, you can simply open an old file in Access 365 and the system will help you convert it. If you cannot open it directly, try holding down the Shift key as you open it—this bypasses any startup code, letting you get straight to your tables so you can at least export your data, even if the forms or code do not come over cleanly.
Be aware that older forms, reports, and especially custom VBA code might have trouble converting directly due to changes in Access over the years, like 32 vs. 64-bit issues. If you have trouble, try importing your objects into a brand new blank Access database created with the newest Access version. Sometimes you need to upgrade in stages, moving first to an intermediate version like Access 2013 or 2016 if you can find one, then finally to Access 365. If you get stuck, there are plenty of Access consultants who can help guide you through the process.
Another common question is about Access training resources. Some people prefer in-person classes, often held at community colleges or computer training centers. These can be great, especially for hands-on learning and direct instructor feedback. If you are looking for in-person classes, search local colleges or businesses in your area, and always check the instructor's experience. However, there is also a wealth of online resources available, which are often more current and flexible for different learning styles.
Occasionally I am asked about advanced topics like evaluating equations in a textbox, similar to how Excel allows you to type in formulas. The basic trick is to catch when a user starts an entry with "=", pop up an Input Box, let them type an equation, and then evaluate and store the result. Access forms do not always allow event triggers like AfterUpdate in this scenario because they may try to validate the data as a number before your code runs. In such cases, you may need to handle the input outside of the usual form events or use alternative logic, which can be a bit complex.
Sometimes users ask about dynamically copying records between tables, making code that adjusts automatically when fields are added or changed. This kind of automation generally requires you to enumerate all fields in the source and destination tables, detect differences, and handle field additions as needed. It can be done with VBA, though it takes some effort to get right. If you would like to experiment with this, start by looping through the Fields collection of both tables using DAO, compare their names and types, and then generate your INSERT statements accordingly.
For those interested in aggregating data, such as counting grades for specific classes, Access offers a few different approaches. You can add criteria directly to a query to only include the class you are interested in, or create an aggregate query to group and count by class across the entire dataset. Which approach you use depends on how you want to present your results and how your data is organized.
Another feature often overlooked is linking to external data rather than importing it. Access allows you to create linked tables pointing to sources like Excel files, SQL Server databases, or other data sources. This way, your data lives elsewhere and Access just acts as the front end, which allows for easier data management and integration.
I also enjoy asking the community which Access features they wish Microsoft would improve. Popular requests include better report and subreport functionality, modern UI aesthetics, more consistent VBA libraries, deeper SQL Server integration, easier visual customization, and better code-editing tools. There are also complaints about the 2GB file size, though you can work around this by splitting your database into multiple backends. Many users also want to see more support for web-based forms and automation features like integration with Microsoft Co-Pilot.
For those who wish Access could create standalone executable applications, unfortunately, Access still depends on having Access or the free Access Runtime installed, though there are third-party tools to package Access databases.
Lastly, remember to take care of routine maintenance, like compacting and repairing your database, having a solid backup routine, and using good, consistent naming conventions. If you are looking to develop your skills further, there are plenty of online tutorials, books, and communities—including forums and discussion groups—where you can ask questions, share tips, and keep up with best practices in Access development. If you want to read more, or have specific issues to troubleshoot, there is a growing wealth of community-driven content out there, and you might even consider joining meetups if you want to connect in person.
In summary, always remember to index your foreign keys—this one step can make a huge difference in the speed and usability of your Access applications, particularly as your data grows. Take the time to name fields clearly, maintain your relationships properly, and explore the powerful features Access offers both for the seasoned developer and for those just getting started.
|
