Quick Queries #96
By Richard Rost
3 hours ago
Search Key Not Found Error Causes, Compact on Close In Quick Queries 96, we will talk about the "Search Key Not Found" error in Microsoft Access, including common causes like database corruption, file size limits, and issues with compact on close. We will answer questions from viewers on topics such as variable naming conventions, tracking progress in large databases, custom sort orders, handling related records to avoid orphan data, exporting large datasets to Excel, and overcoming the 255 field limit in tables. We will also discuss practical tips for database maintenance, cloud database options, and methods to keep your Access development efficient and reliable. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, Search Key Not Found, database corruption, compact on close, file size limit, backend, recordset variable naming, option explicit, alphanumeric sort order, orphan record, MSysObjects modified date, export row limit, 255 field limit, 1-to-1 relationship, name-value pair table, cloud database
Intro In Quick Queries 96, we will talk about the "Search Key Not Found" error in Microsoft Access, including common causes like database corruption, file size limits, and issues with compact on close. We will answer questions from viewers on topics such as variable naming conventions, tracking progress in large databases, custom sort orders, handling related records to avoid orphan data, exporting large datasets to Excel, and overcoming the 255 field limit in tables. We will also discuss practical tips for database maintenance, cloud database options, and methods to keep your Access development efficient and reliable.Transcript Have you ever seen Search Key Not found in Microsoft Access and wondered what Access is trying to tell you? Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today, we are going to talk about the Search Key Not Found error in Microsoft Access, what can cause it, and why the real problem might not be your code at all. Sometimes it is corruption, sometimes it is database size, sometimes it is compact on close doing something you do not want it doing, and lots more. We have also got other questions from YouTube, my website forums, Reddit, email, and lots of other places covering naming conventions, remembering where you left off, sorting custom lists, cloud databases, exporting large result sets, and a few other Access tips and tricks.
Are you ready? Let's jump in.
Leading off today, we do not have a question. It is more of a reminder about something Kevin, one of my moderators in the forums, mentioned. He was talking about my message box timer video - that is this guy right here where we build a message box that you can also have time out after a certain number of seconds. So you can ask someone a question, and if they do not answer in time, it does not freeze up your database if it is waiting for them to respond.
In the video, I mentioned something about not liking that the user can click on it. Here is the database, and yes, I am in dark mode. If you do this, there is a text box there with the countdown, and the user could click there, and then it gets focus, and it looks all nasty. So I am like, it would be nice if we could get that so it did not get focus.
And Kevin just said disable the timer text box, and it is like, duh, you are right. I knew that. I just was not thinking about it at the time I made the video. All you have to do is go to the properties, set enabled to no, and now when you are on it, it looks exactly the same, but you cannot click on it. See that? You cannot. So nothing that you do will mess up that box, and that is great. I love it when you guys either teach me something new or remind me of something that I forgot about, which happens more and more the older I get. So thank you, Kevin, once again.
Oh, and there is another one from Kevin as well. In the same video, whenever I am referring to sections, I always use the numbers. Zero is the detail section, but Kevin reminded me again that there is a constant AC Detail, and you can also refer to it as a string detail, and so there are many ways to do everything in Access. And Donald chimed in with those detail.backcolor, form header.visible, and so on. They must have added that recently because I do not remember that stuff, but sometimes they sneak new features in or new things that they do. I remember doing it from 1999, and I just learned it one way and just kind of stuck with that. Sometimes they sneak these new things on me. And yes, 2007 is new for me. I did most of my development work in Access as a contract professional developer from the mid-90s, 96 until probably 2005 or 2006, when I started getting into training, you know, like really full-time hardcore. So 2007, I remember that is the first version with the ribbon that I would consider past when I was actually doing serious development work all the time.
All right. So next up, we have Tom, one of our Gold members, who had a strange problem where his ACCDE file had been working perfectly for months and then suddenly started throwing a search key not found error when the program closed. The exact same file worked fine on his machine but failed on the client's computer whenever it tried to export tables and shut down. There is a lot of back and forth, but this one I will put a link down below if you want to read the whole thread. There is lots of good stuff in here. But in a nutshell, this turned into one of those classic, it is not your code, it is the environment situations.
We kicked around a bunch of different possibilities in the thread. Donald suggested looking at the file size limits and possible corruption. John brought up the possibility of a Windows update. What got my attention was that his databases were sitting right around that 2 gigabyte limit, which is never a good thing. I say if your database is over 1.5 gigabytes, it is time to start thinking about breaking that down into smaller files.
In the end, Tom did find the culprit. The storage databases had compact on close enabled. I am not a huge fan of compact on close. Once those databases get large enough, Access has a hard time compacting them when the program is shut down and sometimes that can trigger those errors. The fix was simply turning off compact on close for the storage databases and then splitting the data into smaller archive files.
I am not a fan of automatic compacting. I think you should compact often, like once a week or so, depending on the usage of the database, and compact manually and deliberately. Do it yourself, do it as part of a schedule, keep an eye on the database size, and compact on close, I have seen it corrupt databases. I do not know why, it just does. Plus, your users are generally working with front-end files. They are not compacting your backend. You are compacting the backend, or you are setting up some kind of an event on your server. So, yep, that is it. Compact on close.
Next up, we have Ryan, one of our Gold members. He inherited a database with more than 3,500 records and was slowly working through them cleaning up old entries. His question was very simple. How do you keep track of where you left off so you can come back later without hunting for the same record again?
This is one of those situations where the simplest solution is usually the best one. My suggestion was just to add a yes/no field to each record. Something like reviewed or processed or whatever, check it off as you go along. Then, when you come back tomorrow, just filter the records for the ones that are not checked, and you can pick right up where you left off.
Sandra suggested saving the current record ID to a settings table, which is a perfectly valid approach if you specifically want to jump back to the exact record you were on. There were a couple of other solutions suggested down here too. Again, I'll put a link to this in the comments if you want to check out the whole thread yourself.
Me personally, this would depend on if this was something that is fast and easy. I have a little database that I use. I get Google alerts sent to me by email. For Microsoft Access, as mentioned, or Access Learning Zone or my name, I get a Google alert. I want to know when stuff is being published about Access so I can keep my eye on it, different forums, and people posting articles, and all that stuff. So those emails get pulled into my Access database, and I save the URL so I can just go down the list. When I am done with each one, I just check a little box, so I know which one I am done with. The next time I open up that database, it just shows me all the ones that are not checked. Same situation.
Next up, we have Colin, one of our Platinum members. He is getting a strange error while looping through about 13,000 records in a record set and assigning consecutive numbers. At first glance, it looked like it was an Access locking problem, but after some digging, we discovered that the real issue was something much more simple.
If we scroll down here, there is this code. This ended up being a solution where naming conventions matter. Colin was using a variable named ZZ, and somewhere along the line, it got declared as a string instead of a long. Since the variable name did not tell him anything about what type it was supposed to be, the mistake was easy to miss. If you are not using option explicit, so you have to declare your names, then it is easy to accidentally just type in something like that, and you have got a variable that was not declared anywhere, and Access does not have any idea what it is supposed to be, so it becomes a variant. You can stuff anything you want in it.
This is one of the reasons why I recommend either meaningful variable names, or if it is just going to be a temporary variable name, at least use some kind of basic naming convention, S for a string, L for a long, D for a double, B for a Boolean. Or whatever system works for you, S1, S2, that kind of stuff. That way, when you come back to the code six months later, or five minutes later after your coffee break, you will know what kind of variable it is supposed to be. ZZ tells you nothing. Even counter or recordNum, that would have immediately raised a red flag, because you know that a counter is not going to be a string variable.
The lesson here is not about record sets and all that stuff. It is about writing code that is easy to read and easy to debug. If you do not have option explicit on all of your modules, go turn it on right now. Go watch this video if you do not know what that is. Colin, I see you do not have it on there. If you did, that is something up here, then it might have yelled at you. Go watch that video on one of your train rides.
Next up, we have Adrian, one of our Silver members. He has got a list with names like A, B, C, C, C1, C2, C3, and so on. He wants to know how to move something like C3 up to C2 while keeping the rest of the list in the right order.
This is one of those cases where the thing you display to the user does not have to be the thing that you sort on behind the scenes. I mentioned that down here. If your values are just text, A, B, C, C, C1, C2, they will sort alphanumerically just fine. But if you want buttons to move things up and down, text codes get annoying really fast. So a better design would be to add a numeric sort order field and you can hide it if you want to. You could still display A, B, C, C, C1, C2 and all that, but just sort it numerically behind the scenes.
What I would do is I would make that letter value equal to hundreds. So A would be 100, B would be 200, C would be 300, and so on, Z would be 2600. Then you could just add the numeric part, as 1, 2, 3, whatever, assuming you are not going to need more than 99 of them. It depends on what you need here. If you are talking about most normal outlines, you probably do not need more than 1000 items, but you might, in which case you would have to change the algorithm a little bit.
Now my fitness 77 video that is coming out public on Tuesday the 16th, so in a couple days, I make little buttons where you can go up and down, you can move your exercises up and down. Again, it is not just for fitness, it is not just for exercise, it is for any database you want, like this one. So if you want to move 2 up, just click the up button, and what you would have to do behind the scenes is just somehow keep track of what the letter and the number are separately or give it a numeric value. There are a lot of different ways you could do it.
If you want to see me put something like this together in a video, post a comment down below and say yes, you would like to see alphanumeric numbered lists reordering with buttons, etc. If enough of you are interested, I will put together a video that does this because I have had questions like this one before too. I would probably do two separate buttons because you can move something up and down in its alpha level. You want to just move 3 up to 2 and then they would renumber. But you could also say, I want to move this up to group A, so maybe hit the section up/down and that would go from C to B, B to A and so on.
There are a million ways you could do it. Let me know, post a comment. While you are posting a comment, do me a favor, hit that like and subscribe. That helps me out and it helps more people find my videos and I will give you a kiss.
Next up, we have Ken. He is getting an error when closing his form saying Access could not find a record in one table with a matching whatever ID from the other table.
Raymond, Lon, Donald, all pointed him toward the same general area. Something is probably trying to reference a record that either has not been saved yet or does not exist or was deleted. I see this one a lot. In fact, I have got a whole separate video coming out with this exact error message because I see this all the time. This usually means Access is trying to save or reference a related child record without a matching parent record. You have got what is called an orphan, or you are trying to create an orphan. It is classic one-to-many relationship stuff. You cannot have an order without a customer or an order detail without an order if you have got referential integrity set up.
So in Ken's case, his post mentions a hospital contact ID. Let us start looking. That is probably the foreign key, and it is missing whatever the primary key would happen to be.
Check any tables, subforms, combo boxes, or VBA code that refer to the hospital contact table and look at the form events like before update, after update, close, unload, all of those. If you are using multiple tables on one form, make sure the parent record gets saved before the related child records.
That is a weird one, but that is one of the reasons why I do not like mixing multiple tables in a query behind a form. It gets crazy fast. Raymond made a really good point. He said if it only happens on new records, Access may be trying to close or run code before the new record is saved. Lon mentioned checking dirty and possibly using undo. Of course, we have deleted records from Donald here.
The big takeaway is you probably have a relationship problem. That is why I recommended my widows and orphans video, this guy. I will put a link to this down below. That is basically widows and orphans. I think the example that I use in this one is orders and order details. In other words, you cannot create a detail for an order that does not exist. If this order ID is still new, that means there is no order up here yet. You will not be able to add a record down there. That is essentially what that problem is.
Next up, head over to Reddit. I posted my article on Reddit based on one of my videos about how you can use the MSysObjects table to get a list of what was modified recently - recently designed changes. Our GINs, one of the moderators over there, reminded me that Access already has a built-in way to see the modified dates right in the navigation pane. Again, I absolutely love it when people remind me of features that have been buried in the back of my brain for 20 years.
If you go to the navigation pane, right click on it and go View By, and then go to Details, it gives you all this information right in here, which I completely forgot about, to be honest. Then you can sort by modified date and it puts all the recently modified stuff up top. That is pretty cool. If you just want a quick way to see what you were working on on Friday, there you go. But if you like to do all the Accessing nerdy developer type stuff, the MSysObjects approach works, because you can query the data, filter, report on it, export it, and do all that kind of stuff with it. But if you just want a quick look, there you go, do it right in the navigation pane.
So thanks to our GINs for reminding me of that. It is a good reminder that even after working with Access for 30 plus years, if you do not use a feature for a decade or two, it can completely fall out of your brain hole. So, yeah, thanks for dusting a few cobwebs off the back of my brain.
Next up, we have Bruce from the UK, one of my Gold members. This one actually came in the comments on a survey. I ask all my students to do surveys after they watch my classes. And yes, I actually read all of them. For a while, I had a backlog of hundreds of them that I had not gotten to. One of my missions for last year for 2025 was to go through and finish doing all the surveys. Now I make sure I answer them when they come in.
Bruce said, maybe when you are writing code, leave it on the screen a little longer so the likes of me can follow on. I used to pause longer on the code segments on the screen so people could copy it, but then I started getting complaints that I was making the videos too long, I was padding out the videos to make them longer than necessary, so I stopped lingering so long. Now I try to keep the code up long enough so you can pause it and copy it, or at least hit that pause button fast. Plus, if you are a Gold member, you can always download the database, which has all the code in it. If it is a class that you are taking on my website, like one of my paid classes, you can get the databases from those too.
That being said, I do recommend that you type the code in yourself rather than just copying and pasting it. There is something about that eyeball to brain to fingers loop that helps things stick. When I learned to code, back in the 1980s with my TRS-80 CoCo, I spent hours typing out code, like typing out examples from magazines and books, and it just sticks better for some reason if you type it in yourself. I do not know what it is. Your brain has to process it instead of just selecting a bunch of text and hitting control-c control-v. That is one of the problems that I have with AI when people start having AI just write code for them and then they copy and paste it and do not even read it. You do not learn anything.
At least if I have AI generate something for me that I do not know, which is rare, I will take the time to at least read through it and understand it, or at least have AI explain the code to me - what did you just write? Instead of just copying, pasting, and going. That is my problem with vibe coding. Although I will be honest, I just vibe coded my first web app.
I had this idea while my wife and I were on our last vacation to make a drink timer. Just something simple. Because if you have more than three, me personally, if I have more than three drinks in an hour, I definitely get drunk. So this little guy is just a little drink timer. Let me slide it up.
I vibe coded this with ChatGPT. I told it what I wanted. I wanted to have this run on my phone. I wanted to be able to, every time I drink, say, add a drink, and it puts an hour-long timer on there. If I have a second drink, I add a second timer and so on. My goal is to have no more than three drinks in my system at once because the average person burns off about a drink an hour. So I wrote a little article about this and how I did it. You can read about it - it is in my Captain's Log. I will put a link down below.
When I do something like this, I always make sure that I am writing code that I understand. This was just a fun weekend project. This was not something that is business critical. If I was running my business on this, I would definitely make sure I understood every line of code. I know HTML. I know JavaScript. I was a C programmer for years, and JavaScript is pretty much the same as C in a lot of cases. So I told GPT, throw it together using HTML, CSS, and JavaScript. Here is what I wanted it to do. There it goes. Could I have written it myself? Probably in a weekend. But I had GPT do it for me in about 10 minutes. So yeah, I am coming around to the vibe code anything. I still do not think it is going to replace programmers for a long time to come yet.
Lots of Star Trek episodes are based on this - people where the Enterprise shows up and the people that built the technology have long since died, and now Geordi has to save the day.
Anyways, speaking of the YouTubes, we have Raphael who asked when will you resume development of Fitness, which was interrupted without any explanation. Well, yeah, it has been a few months. The explanation is that I have lots of different projects I am working on. I have Access developer lessons I am working on. I have got my SQL Server course I am working on. Fitness is a nice little fun side project. But I just finished six new lessons for it. 74 and 75 are already public - they are on my channel. Go watch those. They are on my website as well. Next week I have got four more coming out - 76, 78, 79, and an extended cut. So lots more coming out.
Fitness is one of those projects where I might put it on the shelf for a while, but when I do work on it, I like to record a whole bunch in a row so I do not have to figure out where I left off. So I have four more coming out. You are probably going to see a few more after that one, and then it might sit on the back burner for a couple more weeks or a month or two, but I have lots planned for it. It is one of those fun side projects that I work on when I get some spare time, so I am glad that you like it. Enjoy the new ones.
Next up, wait, hold on, we have Maurice Maricell. I do not know which one it is, Maricellard. Good day, I love the courses. Thank you very much. Thank you for sharing your knowledge. I watch them subtitled and dubbed in Spanish when available. I want to continue learning - what type of membership do I need to have access to all of your videos on YouTube? Purchasing them separately means I lose the advantage of the translation, which is essential for me.
A couple of things here. First of all, on YouTube you will find all of my free videos, my TechHelp videos, all that stuff, Quick Queries, my Beginner Level 1 classes, and for Access, I also posted Beginner Level 2, as well as my SQL Server Level 1 class. As far as my other paid courses go, they are only available on my website. Members on YouTube, like Gold, Silver, Platinum members, can watch all of the extended cut videos that come with the TechHelp videos. But all of the rest of my course library - my Beginner series, Expert, Developer lessons, all that stuff - those are only available on my website. The reason why I have not put them on YouTube is because YouTube does not allow creators to sell individual videos, and they are all priced differently because as you go up higher in the levels, they change. So I cannot just make a membership price for them. If they ever allow creators to sell individual videos, then I will start uploading them to YouTube.
Now, as far as the dubbing goes, I do not offer dubbing on my website. That is a really cool advanced feature that YouTube has. However, you can get them subtitled in Spanish if you use Live Captions. Live Captions is available in Chrome. I think it is available in Edge now too, but I am not sure. That allows you to turn on any captions in different languages right in your browser with whatever video happens to be playing. If you are happy with just reading the captions, then you can get that. But aside from that, I do not have any plans to add dubbing because that is some pretty advanced stuff that YouTube is doing. To be completely honest, I do not get very many requests for it. All the people that I know who speak other languages also understand English, so I do not know.
Next up, Jerome liked the auto log off video and suggested adding a warning message with a beep before logging someone out. He also wanted to know whether the beep would still work if Access was minimized while the user was working in another application. This is one of those things where I would say try it and see. Figure it out for yourself. That is always the best way to learn something. But generally yes, if Access plays a sound, you will still hear it even if Access is minimized. Minimized does not mean the program stops running. It just means it is not visible on your screen. So if you pop up a warning message and play a beep 10 seconds before it logs them off, the user should still hear it unless they have muted their system sounds or they are wearing noise cancelling headphones and blasting Rush. If that is the case, they are pretty cool even alone. I do like the idea of a warning prompt, something like, hey, are you still there with a no button? Maybe a countdown timer. Maybe even give them something like a message box with a timer on it, like I talked about earlier. That is usually friendlier than just kicking them out with no warning.
As for locking the workstation, yeah, that is definitely possible with the Windows API. It is a little more advanced than the Access-only solution. If you guys want to see how to do it, you know what to do - post a comment down below. Tell me that you want to see how to lock Windows from inside of Access. It is definitely possible. I have a PowerShell script that I wrote for my network, because I have three machines in my office that are always doing stuff - the one I work on and then two other machines sitting in the corner, my SQL Server, my Access server, quote unquote. I wrote a PowerShell script called Lock Fleet. What it does is, when I am leaving the house, if I am going out or whatever, I want to make sure all the machines in my office are locked. So I just run that and it locks all three workstations from one batch file. It is pretty cool. It keeps me from having to go to each machine and lock it individually.
Unlocking them is a different story. I use fingerprint readers though, so all I have to do is touch the side with my fingerprint and they all come back to life. But I still have to touch each one. There is no way to unlock the computers remotely like that. That would go against all of Windows security. You definitely want to have passwords on your machines.
Next up, Pollux is asking if there is a way around Access hitting the 65,000-row export limit when exporting formatted query output to Excel. Yeah, that limit usually shows up when you are exporting with formatting and layout, especially if you are using Access's output to style exports. Access is trying to preserve the look of the object and that is where you run into that old 65,000-ish row ceiling.
If you just need the data and you do not export formatted output, you can use a plain text export instead, like TransferText, or you can write the file yourself with VBA. If you loop through a recordset and write each row to a CSV or a text file, you are not limited to 65,000 records. At that point, basically the practical limit is your disk space. The answer is do not split the query into weird chunks unless you have to - export the raw data, not formatted data. There are lots of different ways you can do it. See this video for more information.
Next up, we have Brian, who says that I should show the SQL code too and then he noticed that when he formats SQL with line breaks and indentation, Access sometimes saves it all back into one giant line.
Two things here. That is a fair point, and I do strongly recommend people learn SQL, but this particular video was aimed at absolute beginners, and when someone is learning how to build their first query, I do not want to throw the query designer and SQL view and underlying code and all that stuff at them all at once. That is just information overload. This was a beginner, simple, how to build a basic query video. That is how I learned Access back in the 90s. I started with Access 2.0 in 1994. I used the query designer, the graphical designer, for years before I ever cared what the SQL looked like underneath. Once you are comfortable building the queries visually, then it is a lot easier to learn SQL because you already understand what the query is doing. That is how I teach it.
Now, as for the formatting issue, that is mostly just how Access has always traditionally worked. The old SQL editor would strip out your pretty formatting and your indenting and save everything as one long line. The newer Monaco SQL editor that Microsoft has been rolling out is much better. It has syntax highlighting and modern editing features, but even that has some issues with preserving line breaks and indentation in certain situations.
Microsoft has been gradually fixing those bugs, but it is still a work in progress. So no, it is not something you are doing wrong. It is just one of those long-standing Access quirks that the SQL people have been complaining about for years, but they are working on it. This is the newer Monaco editor, and this does a much better job at preserving your indents and keeping everything nice and easily readable. If you have got an older version of Access, you do not get that. It is all just one straight standard line of text. The first thing you could do is try updating your version of Access to the latest version, and maybe it will fix that for you.
Next up, a viewer is asking if I could build a sample cloud-based Microsoft Access project and show how Access works in the cloud. That is a great question, but it is one of the biggest misconceptions about Access. Access itself is not really a cloud database platform. It is designed as a desktop database that runs on a local PC or a local area network.
There are ways to get Access data into the cloud. You can move your tables to SQL Server or another cloud database and then build different front ends to connect to that data. There are also hosted solutions like Access Database Cloud that let you run Access applications remotely. Or you can use SharePoint, or you can leave a machine running in your office and remote desktop into it. That is what I do when I travel. There are all kinds of different ways you can get your Access database online.
I do plan on covering more cloud-based topics in the future because it is something a lot of people ask about. But if you are looking for how do I upload my Access database to a cloud solution, it is not really there. It is not how Access was designed to work.
I have actually got a tool I have been slowly working on over the past couple of years that lets you get your tables up to SQL Server, which is the easy part. Then you can export your forms as HTML files essentially, and those will connect to your database. It is something I am still working on, but is it possible? It is definitely possible. It needs a little love so far. If you are interested, post a comment down below. The more people that say yes, they are interested in something like that, the likelier I am to get off my butt and actually finish this thing.
I built it as a proof of concept like two or three years ago just to see if I could do it, and I did it. I got it working. Now it just needs polish. That is how I am. Oftentimes I will start something just to see if I can do it, and then once I know I can do it, I go back to my regular day job work like making videos like this one.
Next up, we have Abdullah. He says he would like to be able to control his Access database from his phone whether his PC is on or off. The short answer is, if your database is sitting on a PC and that PC is off, then no, there is no way to get to it. Somewhere along the line the computer has to be running. The data is not just magically floating around somewhere by itself and you can just get it. Now if your Access database is on a PC that is turned on, then yeah, you can remote desktop into it. You can get to that machine from your phone. I do that myself when I am traveling. I have remote desktop active. I leave my PC on, which costs a few dollars in electricity extra to leave it running, but if you want to be able to get remote access to it, then that is what you have to do.
If your data is sitting on a server and your Access database is on a different machine, you can turn that different machine off if you want to just get to the data on the server, but then you still need some way to read that data. You can build yourself a mobile-friendly web app to get to it. Access does not do that, but there are lots of ways to do it. I have got one for mine. That is one of the big differences between a desktop database like Access and a cloud-hosted system. With Access, the data has to exist on a computer that is powered on and available. No power, no database. It is that simple.
Finally, tonight we have Xavier who says another way to overcome the 255 field limit in the table is to create other tables with a 1-to-1 relationship. This is definitely one way to do it. In fact, I showed how to set up a 1-to-1 relationship in this association database part eight, where we did a demographics table. It is a separate table and it is linked 1-to-1 to your customer table. I am actually going to be putting out a separate video on 1-to-1 relationships very soon. It has been on my list for a while. I do cover it in several places throughout my regular course, but it could use an updated TechHelp video.
For customer demographics, for example, let us say you have 100,000 customers in your database which only have demographics for a small percentage of them, like 10 percent. It makes sense to move those fields into a separate table with a 1-to-1 relationship. That keeps your main customer table smaller and much cleaner, without all those extra fields with all those null values in it.
That said, I still think that if you are getting anywhere near a 255 field limit, I would really encourage you to stop and take a hard look at your database design. A lot of the time that is a sign that you are trying to store too much information in a single table. I have been building data - and you hear this from the I-Sunday Old Man - I have been doing this for 30 years, kid. But seriously, in my 30-year career of building databases, and I have built hundreds of them for other clients, I cannot really think of a time when I got over 100 fields. 60, 70, 80 maybe, but that is pushing it. I do not think I have ever built a database with more than 100 fields in it. Adding hundreds of columns - a better relational design is to move that information into related tables.
In some cases, you could use a 1-to-1 table, but in other cases, it might be something more like a name-value pair structure, where you have customer ID, attribute name, and attribute value. That is what I am going to be showing in this upcoming video. That can handle thousands of different attributes without needing hundreds of different fields.
For example - and I just mocked this up real quick in Excel - you have your customer table, you might have a demographics table. Okay, fine, that is 1-to-1 between customer and demographics. Maybe have a handful of fields in here. But for other stuff, off the wall stuff - favorite superhero, favorite captain, favorite food, first dog's name, that kind of stuff - I would not make a table with specific fields for that stuff. I would just have a name-value pair system. So you could put in favorite superhero, Superman; favorite captain, Captain Kirk; whatever. You could even put these in another table called Attribute Names if you want to have a list of available things to pick from. Then this would not be text in this table, this would be an ID - Attribute ID, for example. That could pull you - this would be a 1, a 2, a 3, and so on - that would pull from another table. So you could give your users a list of what attributes they can pick from or add their own, whatever. But now you are not dealing with hundreds of different fields. You just have multiple records for each customer. That is how I would set that up. If you are anywhere near 255 fields, you should probably be looking into something like this.
I am going to cover this in an upcoming video. So there you go. Hope that answers your question for you. Oh, I apologize. That was not a question. That was a statement. So Xavier, I agree with you. That is a way you could overcome the 255 field limit. I am just saying, if you have a need to overcome the 255 field limit, you really should examine why. Why do you need more than 255 fields?
So if you have not yet, hit that like and subscribe. Head over to my website and see what I have released recently. I see Alex released something called NuGet. I have no idea what that is, I am going to watch that later. You can see right down here, Fitness 74 and 75 came out last week. I have more coming. Make sure you check out my Captain's Log. You can find out more information about that vibe-coded app that I did for a drink timer, Should We Stop Using AI? and Why Outdated Does Not Have to Mean Obsolete. So check those out.
Hit up the merch store. Get your sweatshirts, your mouse pads, your whatever. Still waiting to hear back from them if they are going to add penguins or not. But there is the link. Grab a copy of my book on Amazon. Stop by the forums for lots of great discussion. If you need a tutor, a consultant, or a developer to work on your project with you, check out my Access Developer Network. Do not forget to get on my mailing list so you get notified whenever I release anything new. Lots of new stuff coming out all the time.
Today, we learned that the search key not found error is not always caused by your code. Sometimes the real culprit is database maintenance settings, file size limits, or something else happening behind the scenes. Turn off compact on close, people. Just turn it off.
We also learned why meaningful variable names matter, how custom sort orders are easier with numeric values, and why parent records have to exist before child records can reference them. Post a comment down below. Let me know how you liked today's video and if you have any questions for next week's Quick Queries. I would love to hear them.
That is going to do it for your TechHelp Quick Queries video for today, brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends. I will see you next time and enjoy your weekend.Quiz Q1. What is a common cause of the "Search Key Not Found" error in Microsoft Access, as discussed in the video?
A. Database corruption or reaching the file size limit
B. Typing incorrect SQL syntax
C. Having too many forms open at once
D. Using unsupported data types
Q2. Why is automatic "Compact on Close" not recommended for large databases?
A. It can cause accidental data deletion
B. It can lead to corruption and errors during shutdown
C. It removes all formatting from forms
D. It encrypts the data without warning
Q3. When cleaning up a large list of records and wanting to remember where you left off, what is a recommended solution?
A. Add a yes/no field like "reviewed" and filter unchecked items
B. Rename completed records alphabetically
C. Delete all old records immediately
D. Export reviewed records to Excel
Q4. What is the importance of meaningful variable naming and using "Option Explicit" in VBA programming for Access, as mentioned in the video?
A. Prevents undeclared variable mistakes and helps with debugging
B. Increases program speed
C. Automatically documents your code online
D. Hides your code from users
Q5. What is a good strategy for handling custom sort orders with alphanumeric values (like A, B, C, C1, C2) in Access?
A. Use a numeric "SortOrder" field and sort on that
B. Rely on the natural alphabetical sort in queries
C. Prefix every value with a zero
D. Only allow numeric values
Q6. What is a common reason for the error "could not find a record in one table with a matching whatever ID from the other table" when closing a form?
A. Referential integrity violation due to missing parent records
B. The table names are spelled incorrectly
C. Access has a limit on the number of tables per form
D. Too many subforms are loaded
Q7. What Access feature allows you to quickly see which objects have been recently modified?
A. View By - Details in the navigation pane
B. Query Wizard
C. Data Macro View
D. AutoCorrect Options
Q8. When exporting a large query result to Excel and running into a 65000-row limit, what is the recommended way to export all the data?
A. Export as plain text or use VBA to write CSV output
B. Split the data into separate queries manually
C. Print the query results to paper instead
D. Reformat the columns in Access to save space
Q9. What is a common misconception about running Microsoft Access databases "in the cloud"?
A. Access is designed to be a cloud database platform
B. Cloud storage always increases database speed
C. Access itself is not a cloud database - it must run on a desktop or as a front end to a cloud back end
D. Moving to cloud removes all database size limits
Q10. Which method is recommended to overcome the 255 field limit in an Access table when you need to store many different attributes?
A. Use a name-value pair table structure for flexible attributes
B. Add more fields beyond 255 by editing system files
C. Store extra fields in a hidden Excel sheet
D. Compress text in memo fields
Q11. What is a potential issue with copying and pasting code generated by AI, as discussed in the video?
A. Users may not learn or understand the code
B. It always introduces syntax errors
C. Access disables pasted code
D. It increases the risk of viruses
Q12. When minimizing Access, will system sounds like a beep still play for the user?
A. Yes, unless the user has muted their system sounds
B. No, minimized programs cannot trigger sounds
C. Only if the user has admin rights
D. Only if the computer is connected to the internet
Q13. What should you examine if your Access database is approaching the 2GB file size limit?
A. Split data into smaller files or archive tables
B. Add more indexes to speed up queries
C. Increase the RAM on your PC
D. Reduce the number of queries
Q14. For recording uncommon customer attributes like "first dog's name" or "favorite superhero", what is the best design approach?
A. Use a flexible attributes table with name-value pairs
B. Add individual columns for each possible attribute
C. Store all such data in a single memo field
D. Ignore those attributes altogether
Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-C; 10-A; 11-A; 12-A; 13-A; 14-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'm answering a variety of the most common Microsoft Access questions I've received from YouTube, my website forums, Reddit, email, and other sources. We'll touch on problems like the "Search Key Not Found" error, discuss best practices for tracking progress in large sets of records, examine issues surrounding exporting large data sets to Excel, talk about sorting custom lists, variable naming conventions, database cloud access, large table field limits, and more.
Starting off, let's address the "Search Key Not Found" error. This is an especially tricky issue in Access because while it may seem like your code is at fault, it often points to underlying database problems. These might include corruption, exceeding the database file size limit, or problems related to compact and repair options such as "compact on close." For instance, one user encountered this error in an ACCDE file that worked fine on their machine but failed on a client's computer, particularly around database export and shutdown processes. Ultimately, the problem was due to enabling "compact on close." Access struggles with automatic compaction when databases approach the 2 GB file size limit, and this can trigger errors or even corruption. My suggestion is to always compact your database manually and keep an eye on its size. Once you get above 1.5 GB, it is a good idea to split your data or archive older information into separate files. Never rely on "compact on close" since it can create problems, especially in multi-user environments.
Next, another common issue in long-term database projects is keeping track of where you left off when cleaning or processing thousands of records. The simplest solution here is to add a Yes/No field, such as "reviewed" or "processed," to your records. As you finish each one, check it off. Tomorrow, you can easily filter down to the ones that are not checked. Alternatively, some people prefer saving the current record ID to a separate settings table, allowing them to return directly to their last spot. Both approaches have merit, and it depends on your workflow. For my own recurring tasks, I use checkboxes to mark completed items, which greatly simplifies picking up where I left off.
We also discussed a problem related to variable naming conventions in VBA. One member ran into trouble when a loop used a variable named ZZ that was actually intended to be a number but had been declared (or defaulted) as a string instead. Since the name provided no clues, it was easy to overlook the mistake until debugging revealed it. This highlights the importance of meaningful variable names and using Option Explicit in all your VBA modules. Doing so keeps your code readable and reduces the chances of subtle type-related bugs.
Sorting custom lists, especially those that are alphanumeric (like A, B, C1, C2, C3, etc.), presents its own headaches in Access. Rather than relying on raw text sorting, I recommend adding a numeric "sort order" field. For example, assign multiples of 100 to the main categories (A=100, B=200, C=300, etc.) and use offsets for items within each group (so C1=301, C2=302, etc.). This allows users to move items up and down quickly by just reassigning numbers, with the displayed label remaining as you prefer. If there is interest, I can demonstrate a complete solution for building such a sorting system with up-and-down buttons.
Another question covered error messages about missing related records when closing forms. These errors almost always arise from relationship problems--usually when you are trying to add or reference a child record before its related parent record is saved. Make sure in any form that deals with parent-child relationships (like orders and order details), the parent record gets saved before any children are referenced or created. Avoid complex queries behind forms that join multiple tables unless you are confident about the save order. This advice will help prevent orphaned records and related errors.
I was also reminded, thanks to a Reddit commenter, that you can view the last modified dates of your Access objects directly in the navigation pane by choosing the Details view. This is handy for quickly finding objects you've recently worked on. For more advanced tracking like reporting, the MSysObjects system table is still valuable.
A suggestion from a survey asked that I leave code on the screen a little longer for easier copying. I try to balance this with not making videos unnecessarily long. I do encourage everyone to type code themselves instead of pasting--you learn better that way. But for Gold members, downloadable databases are available if you want to examine the code in full. When I use AI to assist with code, I always review and understand the output--blindly copying what you do not understand is not a good practice.
One question was about resuming development of my Access Fitness project. The explanation is simply prioritization; I have active developer lessons, SQL Server courses, and other obligations. Fitness is a passion side project, so while it may see gaps, I've recently completed several new lessons that are now being released in batches.
A viewer also asked about YouTube membership and access to all my training videos, particularly in subtitled or dubbed Spanish. Free videos, TechHelp, and some beginner classes are available on YouTube (with subtitles and auto-dubbing features provided by YouTube), but the full library, including paid courses, is only on my website due to YouTube's limitations for individual course selling. On my website, you can still use live captions for subtitles in different languages via Chrome or Edge.
Another question regarded auto logoff systems. If you want to add a beep or warning message before logging a user out, this is possible and generally will sound even when Access is minimized, unless the system sound is muted. Advanced options like locking the workstation with Windows API or PowerShell scripts are possible, and if there is interest, I can provide more details.
Regarding exporting large data sets, Access hits a 65,000-row limit when exporting formatted query output to Excel due to formatting restrictions. If you only need the data itself, exporting as plain text using TransferText, or writing your own export with VBA, lets you circumvent this limit entirely. Avoid unnecessarily splitting your queries into smaller pieces--just export the raw data without formatting to get everything in one go.
A viewer brought up SQL formatting in Access, pointing out that line breaks and indentation are often lost when saving queries, especially in older versions of Access. The new Monaco SQL editor does a better job preserving formatting, but not all versions support it yet. My advice is to update whenever possible and expect some quirks as Microsoft continues to improve things.
Several questions emerged about running an Access database "in the cloud." Access is fundamentally a desktop application, not a cloud-native database. That said, you can move tables to SQL Server or another hosted solution for cloud access, or use services like SharePoint or remote desktop for offsite usage. There are also tools and workarounds for publishing Access forms as HTML, but generally, Access was not designed for direct browser-based or offline cloud access. If there is interest, I have proof-of-concept tools for migrating data and forms that could be polished into a usable product.
On the subject of accessing an Access database from your phone, remember that if your PC is off, remote access is impossible. The database must live on a machine that is powered on and accessible. You can keep your PC running and use remote desktop apps from your phone, but if the computer is shut down, there is no way to access your data.
Finally, let's talk about the 255-field limit in Access tables. Creating 1-to-1 linked tables is one approach to extend the number of possible fields, but running into this limit often suggests your database design could use some refactoring. In most cases, using a name-value pair table (with customer ID, attribute name, and value) lets you track thousands of attributes without blowing up your table with hundreds of columns. This keeps your design cleaner, more flexible, and easier to manage. If you need to store only a handful of special attributes (like demographics), a 1-to-1 table can make sense, but avoid overwhelming your primary tables with excess fields.
All of these topics and more are part of regular questions I receive from the community. If you have a question or suggestion, be sure to post it. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Search Key Not Found error in Microsoft Access
Common causes of 'Search Key Not Found' error
Impact of database file size limits in Access
Issues with 'Compact on Close' feature
Resolving corruption and maintenance errors
Keeping track of reviewed records with Yes/No fields
Saving current record position for future work
Importance of meaningful variable names in VBA
Naming convention tips for variables
Using Option Explicit in VBA modules
Sorting custom alphanumeric lists
Implementing numeric sort order fields
Handling related records and referential integrity
Troubleshooting orphaned records in Access
Viewing modified dates in the Access navigation pane
Access query export limits to Excel
Alternative methods for exporting large result sets
Dealing with SQL formatting in Access SQL editor
Explaining Access limitations as a cloud database
Overcoming the 255-field table limit
Using one-to-one table relationships
Applying name-value pair structures for extensibilityArticle If you have ever seen the "Search Key Not Found" error in Microsoft Access, it can be confusing to figure out what is really going on. This error message is often misleading, making you think there is a problem with your VBA code or query. However, in reality, there are several different things that can cause it, and they are often unrelated to your code at all.
The "Search Key Not Found" error is usually a sign that something is wrong under the hood of your database. One of the most common causes is corruption, which can creep in over time, especially if your database is getting close to the maximum file size limit in Access, which is 2 gigabytes. If your database grows anywhere near that limit, I recommend splitting your data into smaller chunks or archiving older records. Another major culprit is having the "Compact on Close" option enabled for large databases. Compacting the database on close may sound like a good idea, but once the file size grows, Access often struggles to complete the process properly, sometimes causing corruption and errors like this one. I recommend turning off "Compact on Close" and instead compact your database manually as part of your regular maintenance routine. Back up your database, close all open objects, and run "Compact and Repair" yourself when there is less risk of a crash.
If you run into this error after months of working with a database and nothing has changed in your code or on your computer, check for recent Windows Updates, as those occasionally interfere with how Access works with files. But most of the time, the root cause turns out to be database file size, auto-compact settings, or a mild form of corruption that has crept in.
Beyond this particular error, it is important to remember that database design greatly impacts how smoothly things run. For example, if you have thousands of records and are working through them to review or clean up data, it is a good idea to add a "Reviewed" yes/no field to your table. As you process each record, you can check the box. Next time you return, just filter for unreviewed records, making it easy to pick up where you left off.
Another common situation is keeping track of position in a recordset. If you want to jump directly back to where you left off, consider saving the current record's ID in a settings table. That way, when you next open the database, you can automatically go to the last record you worked on.
Code quality and readability matter too. When writing VBA, always use explicit variable declarations. Place "Option Explicit" at the very top of every module. This forces you to declare variables, reducing errors from typos or incorrect types. For example, instead of using a generic variable name like "ZZ", choose meaningful names and identify their types, like "lngCounter" for a Long, or use naming conventions like "strName" for string variables, "blnProcessed" for booleans, etc. This makes your code easier to read and debug, especially when you revisit it later.
Sorting custom lists in Access can be tricky if you rely only on alphanumeric values. If you have a list where users need to reorder items like "A", "B", "C", "C1", "C2", "C3", and want to move items like "C3" up above "C2", handling this with just text values gets messy. The best solution is to add a numeric "SortOrder" field to each record. You can assign numbers like 100, 200, 300 for "A", "B", "C", and so on, then use these numbers to sort the list. When users move an item up or down, simply swap the sort order numbers, and the display order will update while the visible codes stay the same. This separation between display value and sort order keeps your lists flexible and manageable.
Access form design is another common cause of errors. If you are getting errors saying Access cannot find a record with a matching ID in another table, it generally points to a one-to-many relationship issue. For instance, if you have a main Orders table and a related OrderDetails table, and you try to create an OrderDetail before the parent Order record is saved or before it even exists, you create what is called an orphaned record. This violates referential integrity. Always ensure the parent record is saved before adding child records, and watch for new or unsaved records when closing or updating forms with related tables.
When it comes to keeping track of what database objects you have modified recently, Access does provide some simple tools. In the Navigation Pane, you can switch to "Details" view to see modification dates for tables, queries, forms, and reports. If you want more advanced tracking, the MSysObjects system table contains this data, and you can build queries to filter or report on recently changed objects.
If you find that your database tables are approaching or exceeding the field limit of 255 fields, it is a sign that your table design needs attention. Rather than packing every possible field into a single table, consider breaking large sets of data into related tables using either a true one-to-one or a one-to-many relationship. For example, you might split out demographics or optional attributes into a separate table linked to your main customer table. For highly flexible data, a name-value pair design works well. In this setup, you have one table for the attribute names (like "Favorite Color") and another table that stores each customer's ID, the attribute ID, and their value. This lets you store hundreds or even thousands of optional fields without running into any technical limits.
Exporting data from Access to Excel is another area where size limitations may surprise you. If you export formatted query results, Access might warn you about a 65,000-row limit because of the formatting features. However, if you export raw data using TransferText or VBA to write a CSV file, you can export as many rows as you want, limited only by file size or Excel's current row limits. For large exports, stick to plain text data if formatting is not essential.
Some users ask about making Access "cloud-based" or accessible from a phone. Access itself is a desktop application and not a true cloud database system. If you want online access, you should consider moving your data tables to SQL Server or another cloud database, and then connect to that data with Access or a custom web front end. There are third-party services that let you run Access applications remotely, or you can use remote desktop software to connect to a machine running Access. But remember, as long as your Access database sits on a powered-off PC, you cannot access it from the outside world. The data must live on a running server to be accessible remotely.
Another tip is about making your user interface as friendly as possible. For example, if you have a timer or countdown displayed in a form, and do not want users to interact with a textbox that just displays a countdown, simply disable it by setting its Enabled property to No. This prevents users from clicking or selecting it, improving your app's usability.
When working with SQL in Access, newer versions now feature improved editors that keep your code formatting and indentation. But in older versions, Access would flatten all your SQL code to a single line, making it harder to read. If you value code readability, keep your Access updated so you can benefit from the latest editor enhancements.
Finally, when you need to go beyond Access's built-in limitations, like exceeding 255 fields or wanting to add customizable attributes for users, thinking in terms of related tables and flexible design is key. Always look for ways to normalize and structure your data for future growth and clarity.
In summary, technical issues like the "Search Key Not Found" error are often less about your current code and more about database maintenance, size, structure, or environment issues. Good habits like regular manual compacting, thoughtful data design, meaningful variable names, and separating display values from sorting keys will keep your Access databases running smoothly and make your life as a developer much easier. If you have specific problems or want to learn more advanced techniques, keep exploring, ask questions, and try solutions out for yourself. Regular maintenance, attention to table design, and thorough understanding of how Access expects data and relationships to work will prevent nearly all of these common database headaches. |
