|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Error Messages By Richard Rost 
5 years ago
Common Error Messages in Microsoft Access In this video we'll discuss the most common error messages you may get while using Microsoft Access, what they are, what causes them, and how to deal with them. Ethan from Portland, Maine (a Gold Member) asks: I keep getting randomly getting the error message "Couldn't save; currently locked by another user" when I try to edit a customer record. I'm the only user of this database. What could be the problem? MembersMembers will learn about a bunch of additional, more advanced error messages, including those caused while working with Access VBA programming. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Error Messages Covered
Error Messages Extended Cut
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, microsoft access error, not enough space on disk, not responding, not working, has stopped working, odbc connection failed, product activation failed, key violations, you must enter a valid in the field, your network access was interrupted, keeps shutting down, keeps crashing, keeps closing
IntroIn this video, we talk about the most common error messages you might encounter in Microsoft Access, what causes them, and the best ways to resolve them. We will cover topics such as lock file issues, database corruption, bit version compatibility, memory problems, record locking, validation rule errors, field size and input mask issues, action query warnings, and more. Learn what to do when error messages pop up, how to interpret their meanings, and practical troubleshooting steps you can take to keep your databases running smoothly.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we are going to talk about how to handle the most common error messages in Microsoft Access, what they are, what causes them, and how to deal with them. Today's question comes from Ethan in Portland, Maine, one of my Gold members. Ethan says: I keep randomly getting the error message 'Could not save; currently locked by another user' when I try to edit a customer record. I am the only user of this database. What could be the problem? Well, Ethan, this is a weird one. Even if you are on a standalone computer, not on a network, and not sharing your database, you might see this error message from time to time, especially if your database locks up and you have to kill it. You have to use the task manager or Ctrl-Alt-Del, and you have to kill Access, or your system just shuts down, maybe the power went out. I have seen this happen before. What happens is when Microsoft Access opens up a database, the ACCDB file, it creates a lock file, an LACCDB file, in the same folder. It is designed to work with multiple people in the database, even if you are the only person. But sometimes, what happens is that lock file might get corrupted if Access does not shut down properly. So, the first thing I would suggest you do is delete that lock file. Go to Access, delete the lock file, try opening it up again, and see if the problem persists. If not, you might need to compact and repair the database. If that still does not work, you might need to create a new blank database and just pull all the objects in; import all the objects from your old database. Your database file itself could be corrupted beyond what compact and repair can fix. I have seen that happen too. So with that in mind, I am going to recommend that you check out my troubleshooter page. I have a big long list of all the different types of things you can try whenever you are getting an error message that does not make sense. And some of them do not. I have seen Access throw up some weird messages and you are like, what? There are no other users in my database. What could this possibly mean? Well, take a look at my troubleshooter page. There is a video here that walks you through a lot of the different things that I mentioned. Back up your database, restart Access, restart all your databases, compact and repair, compile the database, just go down this list. I try to organize them from the easiest things to do that tend to fix the most problems, all the way down to the more difficult things; all the way down to reinstalling Office, updating Office, reinstalling Windows if you have to. Check out the troubleshooter page if the error message that you are getting does not make sense. Now, there are thousands of other error messages that you might get. Some of them do make sense. So let us run through a bunch of the common ones, and I have got some people that have sent me emails like, I do not understand what this means. Let me explain what a bunch of the common ones mean. Now, some of the errors that Access will give you are painfully obvious: out of disk space. Your hard drive is full. Either compact your database, make it smaller, remove some stuff you do not need, or get a bigger hard drive. Out of disk space is usually painfully obvious. Check the drive that your database is on. However, you might have multiple drives in your computer, one on the C drive, D drive, network drive, whatever. The drive that Access is running off of might be full. Check it out. Now, a related error message that you might get says 'Not enough space on temporary disk.' This could mean a couple of things. Sometimes, while Access is doing its crunching in the background, it might read and write information to your swap file, also called your page file. That is usually on your system disk. If your C drive is getting full, where Windows is installed, even if your database is on a different drive that has plenty of space, your local swap file, your page file might be full. So check that too. I have also seen this error message thrown when people are trying to do really, really complicated queries, especially action queries, append table queries, update queries, because Access is doing a whole bunch of temporary stuff in the background. If that temporary file gets over two gigabytes, you could see this error message. So if you have got a really, really complicated action query running and you get 'not enough space on temporary disk,' that could be the reason. Simplify the query. These ones are pretty obvious too: The database was created with the 64-bit version of Access. Please open it with the 64-bit version. Remember, there are two bit versions of Access. The default used to be 32-bit. Back in the day, I think Access 2016 changed the default to 64-bit. Usually, if you are running the newer versions of Access, which I strongly recommend, everyone get a 365 subscription, it is less than 10 bucks a month, you will have the most up-to-date version of Access. If you have older versions of Access and you are getting weird problems, no sympathy for you. Get the latest version of Access. But in the newest version, it does not matter which bit version you have. Access should open and be able to run it just fine. I have got a 32-bit machine in my office and a 64-bit machine. I can transfer database files between them without much change. This error message usually shows up in older versions. This will also show up if you are using an ACCDE file, the encrypted version, the version that you give to your end users. That has to match the bit version of the Access runtime or the version of Access that they are running if you give them the full version of Access. It is plainly obvious, but even if you have people in your office with different versions, it should work if it is the latest version of Access. But that is, again, an obvious error message. This one usually comes up, any one of these three errors, when you have got a corrupt version of Access on your machine: 'Cannot find the wizard', 'The wizard is not installed', 'There is an incorrect setting in the registry', 'The wizard has been disabled', 'You cannot find a dynamic link library (DLL)', or 'Cannot find the wizard, builder, or add-in.' Usually, something is missing. You could try to troubleshoot this until the cows come home. The easiest way to fix this is to uninstall Office and just reinstall it fresh. Do not try to install it over an old version and do not try to just reinstall it over a corrupt install. Completely remove all of Microsoft Office - Word, Excel, everything. Uninstall and then reinstall it fresh, and it should install the files that you are missing. This is one of those things you could spend two or three hours trying to troubleshoot and find the DLL that is missing. No, just wipe it and reinstall. Trust me. I have spent way too many hours of my life trying to troubleshoot bad installs of Office. Out of memory errors. If you see 'Out of memory. There is not enough memory available to perform this calculation.' that is usually because you have got a really, really complicated query. Or you get 'System resource exceeded.' Usually, Access just ran out of memory. If you have never done any VBA programming on your own, you are not a programmer, you have got no code in your database, something you built from scratch yourself, then that is pretty weird. You will see this if you do not reboot Access from time to time. I have got a database that runs in my office 24-7, and if I do not reboot it, at least once a day, I get the next error message. I get that 'Out of stack space'. I use my Access Updater program, which I use for updating versions across my network, so I have my database programmed to restart at least once an hour. But even so, if I do not restart the computer, meaning shut down Windows and reboot, I will get an 'Out of stack space' error message if I do not reboot at least once a week. You go on vacation before, four or five days go by, if I did not remember to reboot my computer before I left for vacation, 'Out of stack space.' Even if I shut Access down and restart it, out of stack space again. Why does this happen? If you have got any kind of code in your database from VBA code and you were not 100 percent perfect about freeing up your variables, like your objects that you declare and stuff, you could run out of memory. It is called a memory leak. Memory just slowly starts eating away, and the amount of free memory available for the system just slowly declines. So I usually recommend if you have got a database running 24-7 like I do, I suggest you reboot your computer, the whole thing. Shut down and restart Windows at least once a day to prevent these kind of memory problems. If you do freshly boot the computer and you still are getting these out of memory problems, then you might have an underlying issue with the code in your database. You should not see these error messages unless your database has been up and running for hours and hours and you are doing lots and lots of stuff with it. If it is a fresh boot and you see these error messages, it is a problem. I have also in the past seen this happen if you have got physically bad memory in the computer too. I had a guy who had a bad SIM chip in his computer years ago. We replaced the memory in his computer and everything ran fine. So it is possible for faulty hardware to cause these kinds of error messages. This was a weird one. 'Microsoft Access cannot open the Zoom box.' You know when you click on a field and you hit Shift-F2 to zoom in, I do it all the time in my classes so you can see the code better. One of the downsides of constantly making sure you have got the most recent updates of Office is that if the programmers at Microsoft break something, you get what they broke. Me personally, I usually wait to update Access. I do not update it right away when an update is available. I let some other people find the problem. I jumped on this one update and they broke the Zoom box, and I use the Zoom box a lot in my classes. Hit Shift-F2, Access would crash. Reboot, restart Windows, do it again, crash. After googling it, I found out that, yep, it is a problem. So in the next update, they fixed it. But just be aware that Access updates sometimes might break your database, so be careful. This is an interesting one. This usually only happens if you have got an older machine and less memory than you should. 'System error occurred' or 'There is not enough free memory to start Access' when you try to open up Access. Give your system a clean boot, try again. You might have to disable programs that are in your startup. If you have got too much stuff loading when Windows loads, you might not have enough memory to run Access. If you have got an older computer, upgrade your computer. Database is exclusively locked or is already opened exclusively by another user. If you are a single person and you do not have your database shared on a network, you should not get this error message. But you can open Access exclusively. Usually, I used to do this when I am trying to update a file, like a backend table file on the server and I did not want anybody else getting into the database. You go to File > Open, you pick 'Open Exclusively'. I have covered this in a couple of other videos, and that prevents other people from opening the database. So someone else may have done this to you. If not, if you are the only person on the database on the system, again, try deleting that lock file and see what happens. That is usually what happens with that error message. Database has reached the maximum size. I do not see this one often, but I have seen it a couple times. Remember, two gigabytes, folks. Two gigabytes is the maximum size for any one database file. Now you can split your database, and I have got a whole video on splitting. I will put a link to it down in the link section below. But essentially, if your database gets too big, split it up into multiple files. These ones are obvious too: 'Microsoft Access cannot start because there is no license for it on this machine' or 'Product activation failed'. Basically, you do not have a legitimate copy of Access. You did not buy a copy of Access from a legitimate site. I recommend either Amazon or go directly to Microsoft. Again, I strongly recommend the 365 subscription. Direct to Microsoft, you get a valid copy. It is less than 10 bucks a month. If you are going to be using Access for your business and it is important to you that it works and it is legitimate, go right to the source. I had one client of mine years ago, he bought like 10 copies of Office from some guy on eBay, I think it was, and it turned out not to be legit. The product key was not right, could not get a hold of this guy, spent like $1,000. You get what you pay for. These errors have to do with if you are using your database over a network. If you are not using your database over a network, you should never see these ones. But anything that has to do with an ODBC connection, usually you are trying to connect to an SQL Server or some other kind of server-based platform. Connection failed, call failed, 'Connection to' and then your database name failed. That means that Access cannot connect to the backend database. The next one: 'The network access was interrupted. To continue, close the database and open it again' or 'A network IO error'. That can also happen if you are just using a shared database on your network. The most popular setup is you have got the shared split database solution. Your tables are on your server, like I cover in a lot of my lessons. If for any reason your network goes down or that machine goes down, the workstations will get that error message. Even me in my little office here, I have got a little three-system network setup. If the power goes off for a second and the hub goes down, well, I have got everything on battery backups, but let us say hypothetically, if your switch goes down, if the server machine goes down for a minute and comes back up, the workstations will lose connection. You will have to restart Access. That is an easy one to fix. Let us talk about tables. This one comes up if you change the properties in your fields for your table. For example, if you take a text field, a short text field, and you make it from, let us say, 255 down to 50 characters. You might see this: 'Data integrity rules have been changed. The existing data may not be valid for the new rules. The process may take a long time. Do you want the existing data to be tested with the new rules?' If you say no, Access will not do any testing; it will just make whatever changes it needs to. So if you do the change I just said and you have got some value in your text file that is 60 characters long, it is just going to truncate it. It is going to chop off the invalid characters. If you switch from text to number, anything that does not fit in the new number field is gone. So be very careful when you are making changes to your table design. Now, this is not necessarily an error message in and of itself. This is just saying, be careful, you are changing the data integrity rules. You might have long text and you are changing it to short text. If it is too big, it is going to get wiped out. So just be aware of that if you see that guy. This one has to do with validation rules. If you have never done a validation rule before, I have got a video on that too. I will put it linked down below. Validation rules are where you can say what the valid entries are. For example, if you have a credit limit, you might have a maximum of 850 and a minimum of 500 or whatever. If they type in a value that is outside that range, then they do not get to type in that number and it yells with the validation rule. Now, this happens when you change the validation rule. If you have changed it from 200 down to 50 and it says 'existing data violates the new validation rule', do you want to keep testing? Do you want to revert to the old rule? Do you want to stop testing, whatever. If you stop testing, it will leave that data in the table, even though it is invalid. So be careful. This one happens if you have got a split database with linked tables. You have got tables linked to a backend file. It says 'The table is a linked table whose design cannot be modified.' If you try to open it in design view, you will get this error message. You cannot make changes unless you open up that other database file, or the server folder that the database is in and open up Access there. Open up that ACCDB file. Cannot make changes to the link. I used to get this one a lot back with Access 2007 or 2010: 'Records cannot be read; no read permission on' whatever the table name is, error 3112. This one I used to get a ton of. This is usually the sign of a corrupt database because they no longer have security permissions in Access. They are a throwback to the older versions where they did have built-in workgroup security. So if you are seeing this, compact your database, try doing a repair on it. Something is corrupt in the database. Cannot modify a table if someone else is using it. Again, if you have a linked database, and you have got someone else using it, you cannot make design changes while someone else has the table open if they are editing a record. You have to kick everybody out of the database. This is a fun one. I do not see this one a lot. I thought 'Access cannot insert the field. There are too many fields in the table.' 255 is the limit. Now, I will say I have been working with Access since 1994. That is 27 years. I have never, in that entire time, come anywhere near close to getting 255 fields in a table. If you have got that many fields in your table, your database is probably not built right. You should be breaking that up into multiple tables. Usually, it is when people do things like phone number 1, phone number 2, phone number 3, phone number 4. That should be a separate table. I have seen people try to do orders in an order table. You know, line item 1, line item 2, line item 3, like 50 of them. No. Related table. Go watch my relationships video. I have never, ever, ever seen a table with more than, I am going to say 100 fields in it that was legitimately built correctly. You should never see this error message. There is a lot of different error messages that have to do with multi-valued fields and attachment type fields. I am not going to go through all the different ones here, but you should not use either of these. They are both evil. In fact, the Access veterans and I on my website, my trusted developer students, we are trying to make a list of all the evil stuff in Access. Multi-valued fields and attachments are on the list. This is going to be an upcoming video: 'Stuff you should never, ever do.' Do not use multi-valued fields. Use a related table with a combo box, for example, and do not use attachments because Access is not designed to store attachments, documents, and pictures. Just do not do it. If you get an error message about that stuff, you will get no love from me. I will tell you: get rid of the multi-valued fields. Get rid of your attachments. This one is pretty self-explanatory. Not even going to read it. That is what it is. Let us talk about primary keys and indexes. You will see this guy every time you make a table and forget to add your autonumber as a primary key field. I almost never do this. If you have watched any of my other videos, when I make a table, I will type in my customer ID or order ID or whatever and I will set it as an autonumber. But I almost always forget to click the little key button, this little guy right there, primary key. I never do it. Access will yell at me and do it for me. If you have watched any of my classes, you will see that is what I do. I always forget. As long as Access sees that autonumber in there, it will ask you to make it the primary key and just let it do it. Not all error messages are bad. If you try to delete the primary key, you will get this warning: 'Deleting the field requires Access to delete the primary key. Are you sure?' Be careful. If you have got relationships between this table and some other tables, you are going to mess up those relationships. Be very careful. And of course, I have to pop this slide up once in a while: back up your data regularly, nightly. Watch my backup video. It is important. Before you do any deleting of anything, back up your data. You might also see this related message: 'Deleting field requires Microsoft to delete one or more indexes.' Not just the primary key. Remember, the primary key is always indexed (no duplicates). But if you index a field, like something you are going to search or sort on, like last name or phone number, you could index those and make them duplicates are okay. But Access still builds a separate index for just that field to speed up searches and sorts. So if you are going to delete an indexed field, you will also get that warning message. Now, sometimes Access cannot delete the indexed field: 'The table participates in one or more relationships. Delete relationships in the Relationships window first.' If you do not know what that is, it is under Database Tools > Relationships. Now, you might not see anything up in here. I never use it personally because I just do not. I like to set up my relationships in queries. If you are doing stuff between multiple backend database files, you cannot use global relationships in here. But if you have, like, Customers and Contacts, and you set up a relationship between CustomerID like this and you enforce referential integrity, do all this stuff, save that. Now, if I go into my Customer table here and try to delete it until its relationships with other tables have been deleted, you cannot delete stuff that has global relationships set up. So if you have got global relationships set up, you cannot delete fields or tables, usually, that are involved in those relationships. And there are related error messages that you cannot delete the table and you cannot delete this field. It is part of one or more relationships. 'Changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.' This will happen if you have got an index set up and you try to type in a duplicate one. For example, let us say you have got phone number or email in my database. I have got email address indexed, no duplicates, because all of my customer accounts are based on their email address. You change your email address, that is fine. I will change it for you. But if that email address already exists, I cannot add a second customer record with it. You can do the same thing with, you know, social security number or whatever. Here is another huge one: 'The recordset is not updateable.' This happens if you make a query that has got a bunch of different tables in it, and at a certain point your query becomes un-updateable. In other words, it is too complex. Access can no longer edit those records or add new ones. I have got a whole separate video on this one. I will put a link down below. I explain all the different reasons why this happens. Basically, your query is too complex. Simplify it. Break it down into multiple queries. 'Relationships that specify cascading deletes are about to cause records in this table, along with related records, to be deleted.' Be very careful if you have cascading deletes set up. If you do not know what cascading deletes are, someone else probably set them up in your database for you. That goes back to here. This relationship right there, cascade deletes. They are very dangerous. Be careful with them. That means if I delete a customer, all of the related contacts and orders and whatever else you have got set up also get deleted. So be very, very careful with those. 'Field cannot be modified because it is a read-only field.' This usually happens if you build a form off of a query and in the query you have got a calculated field. Like you have got quantity and price, and then you have got the line total, and then you bring that into a form, and someone is trying to edit the line total. You cannot change it. It is a read-only field. It is based on the calculation of two other fields. This is pretty self-explanatory: 'You will not be able to undo this replace operation.' If you are doing a global find and replace, you say you want to replace all instances of the word Joe with Joseph, hit go, it is going to warn you. Cannot undo it. Make sure you back up your table first just in case. This one is pretty self-explanatory too: numbers too large. Remember, if you make something 'Long Int', that is a default number type, long int. The value is going to go up to plus or minus about 2 billion. If you try to type in 6 billion, you cannot put that in a long int, you have to go up to double or something bigger. I had one customer that accidentally changed the number size to byte and could not put in any values over 255. She was like, why cannot I type in 500? Well, right down here, long integer is the default. You change that to byte. Now, it is 255. That was an accident. She did not mean to set it to byte, but that is why she could not do it. Integer is about plus or minus 32,000, long integer, and of course, these are all counting numbers, no fractions. Long int is the default type, plus or minus 2 billion. That is a good size for pretty much numbers you are going to run into on a daily basis. Now, if you are trying to calculate someone's net worth or something like that, and it is Bill Gates, singles and doubles, I almost always use doubles. I do not use singles at all. Do not worry about replication IDs. And I never use, do not use the decimal. Decimal has got some issues. Use double if you need floating points. Of course, if you are doing money, use currency. Currency is specifically designed for holding dollar values. 'The value you entered is not appropriate for the input mask.' Input masks are where you can control what format people type data into the table in. For example, a phone number format: 000-000-0000. If they try typing a Z or an A, it will say cannot do it, it does not fit this input mask. I have got a whole different video on input masks if you want to learn how to use those. I am old enough to remember, my grandma used to say that back in the day, their phone numbers had letters in them, like TA3-426 or something like that. It took them a while, they used just numbers, then area codes came along. Yes, I am old. Again, self-explanatory. We already talked about validation rules. If you get 'less than 0 or greater than 100' and someone types 150, does not meet the validation rule. Goodbye. We talked about these ones up at the top for Ethan. If you are the only user of the database and you are getting record lock problems, then chances are you have got a corrupt database. Delete the locking file, compact and repair, reboot the computer. If you are still getting it, post something in my forums and maybe we can help you out. That is a weird one if you are the only user on the database. 'Field is too small to accept the amount of data you attempted to add.' This usually happens when you are trying to cut and paste something. If you have got a big paragraph of text, you try cutting and pasting it into a short text field, you will get this error message. It is too much stuff. Cut it down. Or if you are trying to do an update query and you are updating from one table into another and the field is too big, you will get this error message too. I hate this one: 'The database engine stopped because you and another user are attempting to change the same data at the same time.' If you have got a customer database and you both try to edit the same customer record at the same time, sometimes you will get an error message. Sometimes you will not. Sometimes it will just save both of them, and the last person to save it wins. It is really weird. Access is a great database. It is my favorite product. I love Access. But it does have some minor issues when you are on a network with a lot of people. We are going to talk about this in a minute. It is one of the other error messages we have coming up. The maximum number of users that can connect to an Access database is 255. You will never get anywhere near that many though. If you have got more than 10 or 15 users on your database that are constantly working with it, you really should think about upgrading the backend to SQL Server, because this kind of stuff happens. SQL Server is much better at handling collisions than Access is. In fact, I have got another whole video on record locking and how to be careful with it and do your own custom record locking, and I will put a link to that down below in the link section too. This one is self-explanatory, but I actually had someone email me the other day that got this error message and could not figure out why: 'You must enter a value in the field, required.' She was like, I do not have this value. I am like, well, that is the table set as required. She said, oh, yes, it is. It is a property right down here in table design, required. The default is no, set it to yes. If you want to have it so that people do not have to type it in, I almost never use required, very, very sparingly. I talk about this in my Access Beginner 1 class. No data is better than bad data. If you have, let us say, a phone number field and you make it required because you are telling your employees, I want you to make sure you get phone numbers from every customer, and a customer does not want to give their phone number, what is going to happen? The employee cannot save the record without typing in some garbage number. No data is better than bad data. You do not want to have to type some number in just to get around the database rules. This way, it is easier to pull up a list of customers that are missing phone numbers. Just throw in 'is null' in a query and you have got it. You do not want to have to figure out which numbers are bogus because you have stupid validation rules and requirements in here. Circular reference. You see this a lot more in Excel than in Access. Circular reference is where you have got a cell depending on its own value to do a calculation. Some equals, let us say the field name is order total and it is equal to order total plus 1. It is just going to keep looping and it creates what is called a circular reference, an endless loop in VBA terms. That is where you have a cell like B5 equals B5 plus C5. You cannot do it. 'Expression too complex. The expression you entered is too complex.' Usually, this happens when you enter in a formula, in a query or a form or a report, and it is too complex. It is too long, variables, too many fields. Simplify it, break it down into multiple smaller steps if you have to. This one happens a lot: division by zero. When you have got a field that you are always assuming is not going to be zero but it is. If this is the case, make sure you use an IF function to get around that. If it is possible for X to be zero, use an IF: if X equals zero, then make it zero; otherwise, Y divided by X, you can do your math. Otherwise, you are going to be throwing up division by zero errors. If you do not know what IF is, I will have a video down below. Type mismatch errors occur when you try to put the wrong type of variable in a field. For example, your type is set to currency and you try to put a name in it. Or it is set to a date field and you try to put something that is not a date. That is a type mismatch. It is expecting a number and you gave it text or it is expecting a date and you gave it currency. You cannot sort on this field. You might think this is self-explanatory, but people do not realize there are some field types that you cannot sort on. You cannot sort on long text, attachment types, multi-valued fields. There are a lot of things you cannot sort on. You also cannot sort on the asterisk field in a query. If you bring in the asterisk, you cannot sort on it. You also cannot put criteria on the asterisk and you cannot calculate totals on the asterisk. Asterisk says all fields and that is just to make it easier so you do not have to bring them all down one at a time from the table into the query. But there is stuff that you cannot do with them. 'Query must have at least one destination field.' When you are creating a query, you have to make sure you have at least one output field. Or if you are doing an update or make table query or one of those types of queries, you have to make sure you have some data that you are setting. This one also goes back to the too complex one that we talked about earlier. If you can see that it is really long and crazy, break it up into smaller steps. You might even have to break it up into multiple queries. If you are trying to do too much in one query, it might not calculate properly. Break it down into simple steps. 'This operation must use an updateable query.' This also goes back to the not updateable thing I mentioned earlier, that if you are trying to do something, for example, an update query, you are trying to change some values, and the query that you are trying to make changes to is not itself updateable, you might get this error message. 'Specified field could refer to more than one table listed in the FROM clause of the SQL statement.' This happens if you have got a join between two tables and you referred to a field that could be in either one of them. Like earlier, I referenced Customers and Contacts. Both tables have a CustomerID in them. So if you have a query criteria down here that says CustomerID equals 4, it might not know which one you are talking about. It should be the same if they are joined, but you have to be specific, e.g., ContactT.CustomerID. One of my favorites: 'Enter parameter value.' If you set up a query that has got a parameter value in it, you will know that you did and you will know that you have to type it in. If you are not expecting to see this and it pops up a box for a parameter value, I guarantee you have got something spelled wrong. Nine times out of ten, something is spelled wrong. In fact, I did a whole video on just this error message. I will put a link to it down below. I guarantee you have got a field or a table name spelled wrong somewhere in your query. 'You are about to run an update query that will modify data in your table.' You will also see this with append queries, make table queries, delete queries - any kind of an action query. It is going to modify data in your table, and you are going to get this warning message. Can you turn it off? Yes. There is an option in the database to turn off action query messages. It is under File > Options > Client Settings, down here, Action Queries. I have mine off, but be careful. If you turn them off, you are not going to get any warnings. You are just going to do it. Do not say I did not warn you. Be careful. This guy usually pops up if you are trying to add records to an update query and you are violating any kind of a rule. For example, there are type conversion failures where you are trying to put values from a text field into a currency field, that is a type conversion. Key violations means you have got a duplicate index, for example, like we talked about earlier. Essentially, for any number of reasons, Access cannot add all of the records. You will see the second message: 'Unable to append all the data to the table.' So update table queries can do that, append queries can do that. Any kind of action query might run into problems if you are not putting the data in there right. Another popular one I see all the time: 'Section width is greater than the page width.' This is for reports, and you have got the section, the detail section is too big for the page that you have specified. You have got an 8.5 by 11 sheet of paper. You are trying to make a report that is bigger than that. You are going to get this error message. For example, here is my invoice report. I have got an 8.5 inch sheet of paper, so you can go out to 8.5, right there, but I have got quarter inch margins, so that is a half an inch total. So I want to be just shy of that 8 inch mark. If I go too wide and go to there, print preview, I get that error message: 'Section width is greater than the page width.' So you just have to make your detail section smaller so it fits in the page, or make your margins smaller, or change the page size. And finally: 'You cannot use aggregate functions in a page header or footer.' Sum, count, max, min - all those are aggregate functions. You cannot use them in a page header or footer. Where can you use them? In the form header and footer, or the report header and footer. You cannot put them in the page header and footer. You cannot put a sum in the bottom of the page. There is a trick to it. I do have an advanced lesson that I cover when I develop classes, but generally, no, you cannot use aggregate functions in page headers and footers. Sixty-some error messages down. I have got about forty-five more to cover, including a lot that have to do with VBA error messages. I cover those in the extended cut for the members. Silver members and up get access to all of my extended cut videos. How do you become a member? Click the Join button below the video. If you click the Join button, you will see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website, and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select 'All' to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it is also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page, and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the primary cause of the "Could not save; currently locked by another user" error in Access when you are the only user?A. Corrupted lock file (LACCDB) due to improper shutdown B. Outdated version of Access C. Incorrect query design D. Insufficient network permissions Q2. What does Microsoft Access create when opening an ACCDB file to manage multi-user access? A. A lock file (LACCDB) B. A backup file C. An encrypted file D. A temporary query Q3. What is the first recommended step if you suspect a corrupted lock file is causing issues in your database? A. Delete the lock file B. Reinstall Windows C. Change database permissions D. Switch to SQL Server Q4. What is "compact and repair" used for in Microsoft Access? A. To reduce file size and fix minor corruptions B. To upgrade the file to a newer version C. To split the database into tables D. To encrypt the database Q5. If "compact and repair" does not fix a suspected corruption, what is a suggested next step? A. Import all objects into a new blank database B. Reboot the computer C. Change database file extension D. Disable all macros Q6. What should you check if you receive an "out of disk space" error? A. The available space on the drive hosting the database B. The number of table relationships C. The printer settings D. The status of the primary key Q7. What does the "Not enough space on temporary disk" error often relate to? A. Insufficient swap (page) file space on the system drive B. Database with too many tables C. Too many queries in design view D. Invalid validation rules Q8. Which bit version mismatch can prevent an ACCDE file from opening? A. 32-bit database on 64-bit Access or vice versa B. Any version of Access on a Mac C. More than one concurrent user D. Network connectivity issues Q9. If you receive an error like "Cannot find the wizard" or "The wizard is not installed," what is the preferred method of repair? A. Completely uninstall and reinstall Microsoft Office B. Change the registry manually C. Download missing DLLs from the internet D. Reboot the computer and try again Q10. What is a "memory leak" in the context of an Access database? A. When code does not properly release variables and memory usage slowly increases over time B. When the database exceeds 2 GB in size C. When a query multiplies too many fields D. When too many users edit the same record Q11. What is a good maintenance habit for databases running 24/7 to prevent "out of memory" or "out of stack space" errors? A. Reboot the computer regularly B. Turn off antivirus software C. Delete all queries weekly D. Compact and repair daily Q12. What is a likely reason for the error "System error occurred or not enough free memory to start Access" on an older computer? A. Too many programs in startup consuming memory B. Incorrect field types C. Invalid input mask D. Database not indexed Q13. Why might you receive an error that the database is exclusively locked or already opened exclusively by another user when you are the only user? A. The database was opened using "Open Exclusively" or the lock file is stuck B. Too many tables are linked C. Incorrect field validation rule D. Outdated network drivers Q14. What is the file size limit for a single Microsoft Access database (ACCDB)? A. 2 gigabytes B. 10 gigabytes C. 100 megabytes D. Unlimited Q15. If you see "no license for it on this machine" or "Product activation failed," what is most likely the problem? A. Using an unlicensed or improperly activated copy of Access B. A network lock file is corrupted C. Database is too large D. Data types are mismatched Q16. What does an ODBC connection error usually indicate? A. Access cannot connect to the backend SQL Server or data source B. The primary key is missing C. The report width is too wide D. A backup is needed Q17. What will happen if you reduce the size of a text field in a table and choose to skip data validation? A. Data longer than the new size will be truncated B. Access will prevent the change C. Access will automatically convert text to numbers D. The field will be deleted Q18. What should you do before making design changes to a table that other users might be using on a network? A. Ensure all other users are out of the table or database B. Change the field data type to text C. Delete all relationships D. Turn off database security Q19. What is the maximum number of fields allowed in a single Access table? A. 255 B. 1,024 C. Unlimited D. 100 Q20. Why are multi-valued fields and attachment fields discouraged in Access? A. They lead to complicated error messages and poor database design B. They speed up performance C. They are compatible with SQL Server D. They prevent corruption Q21. What error may occur if you try to delete a field that is part of an index or relationship? A. Access will prompt you to delete the index or relationship first B. The field will be immediately deleted without issue C. All table data will be lost D. Only the primary key will remain Q22. What causes the "Changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" error? A. Attempting to insert a duplicate value into a unique index or primary key B. Too many tables in one query C. Using a calculated field in a form D. Typing text into a currency field Q23. Why might a query become "not updateable" in Access? A. The query is too complex, often involving multiple tables or certain joins B. The tables are not indexed C. There are too many forms open D. The database is read-only Q24. What should you be cautious about with cascading deletes in Access? A. Deleting a parent record may also delete all related child records B. All users will be logged out C. Queries will run slower D. Input masks will be disabled Q25. Why can you not edit a calculated field displayed in a form based on a query? A. Calculated fields are read-only B. Their value changes all records C. They are primary keys D. They only appear in reports Q26. What number limit applies when using the "Long Int" number type in Access? A. Approximately plus or minus 2 billion B. Plus or minus 32,000 C. Up to 255 characters D. No limit Q27. What does an input mask do in an Access field? A. Controls the format of data entered, like phone numbers B. Limits the field size to 2 GB C. Ensures referential integrity D. Creates action queries automatically Q28. What is a "circular reference" in Excel or Access? A. A calculation in which a value depends on itself, causing an endless loop B. A relationship between two unrelated tables C. An input mask using all zeros D. A join with no criteria Q29. What leads to a "division by zero" error in a query or report? A. The formula divides a number by a field that sometimes contains zero B. Using too many primary keys C. Setting validation below zero D. Making a field required Q30. What happens if you try to sort on a field type such as long text or attachment in Access? A. You will receive an error because these field types cannot be sorted B. The table will be deleted C. Access will automatically convert to short text D. You can sort with no issue Q31. What does "Query must have at least one destination field" mean? A. You must include at least one output field in the query design B. You need to specify a backup location C. You must have a primary key on every table D. Every query must use aggregate functions Q32. What typically causes an "Enter parameter value" prompt when running a query? A. A misspelled field or table name in the query B. Having no relationships setup C. Attempting to delete a primary key D. Running out of disk space Q33. What is the danger of turning off action query warning messages in Access? A. You will not receive any warning before data is modified or deleted by those queries B. The database will run slower C. Users will need to log in every time D. Data will be encrypted Q34. What does the error "Section width is greater than the page width" generally affect? A. Reports that are wider than the selected paper size and margins B. Query results with too many records C. Data imported from Excel D. Validation rules in tables Q35. Where are aggregate functions like SUM and COUNT not allowed in Access reports? A. Page headers or footers B. Form detail sections C. Form header/footer D. Report header/footer 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; 21-A; 22-A; 23-A; 24-A; 25-A; 26-A; 27-A; 28-A; 29-A; 30-A; 31-A; 32-A; 33-A; 34-A; 35-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. SummaryToday's video from Access Learning Zone focuses on understanding and managing the most common error messages in Microsoft Access. As your instructor, I want to walk you through what these messages mean, why they appear, and what you should do when you encounter them.Let me start by responding to a specific question about the error message "Could not save; currently locked by another user," which can be particularly confusing when you're the only person using your database. This error can pop up even if your system is not shared or networked. It often occurs if Access fails to close properly, such as during a system crash, power outage, or if you end up terminating Access through Task Manager. In these cases, Access leaves behind a lock file, called an LACCDB file, in the same folder as your database file. If this file gets corrupted, you can encounter locking errors. To resolve this, the first step is to delete the LACCDB lock file. Try reopening your database afterward. If the problem remains, use the compact and repair feature. If that doesn't solve the problem, you may need to create a new blank database and import all the objects from your existing file. Sometimes, file corruption can be severe enough that compact and repair isn't sufficient; importing everything into a new database is the only way to fix it. I recommend referring to my troubleshooter page, where you'll find a comprehensive list of solutions for Access errors that might not make much sense. There are step-by-step instructions to guide you through methods, starting from simple fixes like restarting Access and compacting the database, to more advanced steps such as reinstalling Office or even Windows if needed. Now, let me go through some of the most common error messages you might face: A very direct message is "out of disk space," which simply means your hard drive is full. Either free up space or switch to a larger drive. Sometimes your database might reside on a secondary drive or a network location, so always check where your file is stored. A related message, "not enough space on temporary disk," usually means your system's swap, or page file, is full. Even if your database is on a drive with plenty of room, your C drive (where Windows manages the swap file) could be the problem, especially with large, complex queries. Another common message involves bit versions: "The database was created with the 64-bit version of Access. Please open it with the 64-bit version." There are both 32-bit and 64-bit versions of Access, and while recent versions handle compatibility better, older versions may require you to run the correct bit version, especially with ACCDE files. You may encounter errors such as "Cannot find the wizard," "The wizard is not installed," or "There is an incorrect setting in the registry." If you start seeing errors about missing DLL files or missing wizards, the quickest and most reliable fix is a complete uninstall and fresh reinstall of Office, rather than trying to fix one component at a time. I have personally spent far too much time troubleshooting these problems manually — just reinstall, and save yourself the headache. "Out of memory" and "system resource exceeded" are messages tied to complex queries or code that isn't managing system resources properly — for example, memory leaks caused by objects not being released in VBA code. For databases running around the clock, I suggest rebooting the system regularly. If problems persist even after a fresh reboot, hardware failure, such as faulty RAM, could be the culprit. If you see "Microsoft Access cannot open the Zoom box," it might be a bug from a recent Office update. I always advise waiting on new updates until bugs are confirmed and fixed — sometimes Microsoft does break features, and you'll have to wait for a patch. Older or underpowered computers might display messages like "System error occurred" or "There is not enough free memory to start Access." A fresh reboot often helps, but if these persist, you might need to upgrade your hardware. "Database is exclusively locked or is already opened exclusively by another user" can appear if someone else has opened the database in exclusive mode, or, as before, a corrupted lock file is left behind. Again, deleting the lock file is your first course of action. The message "Database has reached the maximum size" is straightforward. Access databases are limited to 2 gigabytes. If you get close to this limit, split your database into multiple files. "If you see 'Microsoft Access cannot start because there is no license for it on this machine' or 'Product activation failed'," this means you don't have a valid installation of Access. Avoid buying Office from questionable sources; stick to reputable sellers or Microsoft directly. There are specific network-related messages like "connection failed," or "network access was interrupted." These are common in split database setups and result from network or server instability. Lose the network connection, and Access workstations lose access to data until the connection is restored and Access is restarted. Access will occasionally warn you when you change table structures, for example, by shortening a text field's length. You'll be prompted about possible data truncation or integrity issues. Always be careful when changing data types or validation rules; invalid or out-of-range values might be deleted silently or left in an inconsistent state. Likewise, changing validation rules may flag existing data as invalid; Access gives you the option to test or bypass these checks. If you use linked tables, remember that you cannot modify their design from the front-end database. You must open the backend file directly. In older versions or corrupted databases, you might see errors like "Records cannot be read; no read permission." Typically, this points to database or permissions corruption. Design changes to a table cannot be performed if someone else is editing it. Make sure all users are out of the database before making structural changes. You may see "Access cannot insert the field. There are too many fields in the table:" the limit is 255. Hitting this limit is often a sign of poor database design. If you think you need more than 255 fields, break things up using related tables. Multi-valued fields and attachment fields also generate their own error messages. In general, avoid these features as they often cause more trouble than they're worth. Messages about primary keys and indexes are quite common. "No primary key" or "are you sure you want to delete this primary key" are warnings for table integrity. Adding a primary key is essential for relational design and database performance. Backing up your data should become a regular practice, especially before making any significant changes or running bulk operations. Be aware that deleting indexed fields produces a warning, and if that field is part of a relationship, Access will insist you first delete the corresponding relationship. If you try to enter a duplicate value in a field marked as indexed with no duplicates, Access blocks the input and notifies you. Queries that are too complex or that involve too many tables sometimes become non-updateable, meaning you can't edit or add records through them. Simplify the query structure or break it down into steps if you encounter this. Cascading deletes are powerful but can be dangerous. If enabled, deleting a parent record automatically deletes all related child records. Use this feature judiciously to avoid accidentally wiping out important data. Fields calculated in a query (for example, totals) often become read-only in forms and cannot be directly edited. "Numbers too large" or messages about input masks and validation rules are mostly self-explanatory. Always make sure your fields are the right type and size for your data. If you're only user and still experiencing record lock errors, you're most likely dealing with corruption. Delete the lock file, compact and repair, and reboot the system. If nothing changes, seek advice in my forums. If you paste more data into a field than it can accept, you'll get a "field is too small" error. Trim the data or change the field's type. The message "The database engine stopped because you and another user are attempting to change the same data at the same time" is a classic example of record locking troubles, which become more likely as more users work with the database. For setups with more than 10 or 15 users, consider migrating your backend to SQL Server for better concurrency and reliability. Sometimes, fields are marked as required in the table design, resulting in errors if you leave them blank. Unless there's a compelling reason to make a field required, consider leaving it optional to prevent users from entering bogus data just to satisfy a constraint. Circular references and overly complex expressions can cause calculation errors or endless loops. Always break up calculations into manageable steps and check for references to the same field within its own formula. Division by zero errors are frequent if you forget to guard against zero denominators. Use conditional logic in your calculations. Type mismatches happen when you attempt to store the wrong data type, such as putting text in a currency or date field. Certain field types cannot be sorted or filtered, including long text, attachments, and multi-valued fields. "Query must have at least one destination field" and errors concerning overly complex queries or updateable queries both point back to needing a simple, logical query structure. If a field name is present in more than one table in a query, and you reference it without the table name, you'll get "specified field could refer to more than one table." Make your references explicit. "Enter parameter value" usually signals a misspelled field or table name in a query. This is a common problem and I have a whole lesson dedicated to it. Warnings pop up when running action queries, such as update, append, or delete. These notifications can be switched off in Access settings, but do so with caution—you may not be alerted to accidental mass changes. Type conversion failures and key violations during append or update queries mean that your data is incompatible with the target fields, or that you're breaking an index constraint. Design-related warnings also occur in reports, such as "section width is greater than the page width." Adjust your report layout to fit your paper size and margins. Aggregate functions like sum and count can't be used in page headers or footers, only in form or report headers and footers. I've covered over sixty of the most common Access error messages here. In the Extended Cut for members, I cover an additional forty-five messages, especially those related to VBA errors. Silver members and above have access to all extended cut videos, plus live sessions and more. Gold members also get access to downloadable databases and my vault of VBA functions. Platinum members receive all these benefits along with my full-length course library, which includes not only Access but also Word, Excel, Visual Basic, ASP, and more. Whether you're a member or not, I'll keep releasing these free TechHelp videos. Your engagement makes it possible—please like, comment, subscribe, and enable notifications so you never miss new content. For more information and an organized list of additional resources, check the links below the video description. If you'd like to get email updates when I post a new video, you can join my mailing list as well. If you're new to Access, give my free Access Level 1 course a try—it covers all the basics and runs over three hours. Level 2 is just one dollar, or free for all YouTube members. If you have a question you'd like answered in a future TechHelp video, visit my TechHelp page. You can also explore my other videos, beginner courses, and subscription options below. You'll 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 ListCauses of the 'Could not save; currently locked by another user' errorAccess lock files and how they work Deleting corrupted LACCDB lock files Using Compact and Repair to fix database issues Importing objects into a new blank database to fix corruption Access error message troubleshooting steps Backing up and restarting Access and the database Understanding 'Out of disk space' and related errors Dealing with 'Not enough space on temporary disk' Action queries causing temporary file overflow Handling 32-bit vs 64-bit Access error messages Solving missing wizards or registry errors Uninstalling and reinstalling Office to fix missing components Out of memory and system resource exceeded errors Memory leaks and rebooting best practices for Access Understanding and fixing 'Out of stack space' Zoom box error caused by Office updates Fixing 'System error occurred' and startup memory issues Exclusive lock error and exclusive Access mode Access database maximum file size limits and file splitting Product licensing and activation error messages ODBC and network connection errors Handling network IO interruption and connection failures Data integrity rule change prompts and implications Validation rule errors and managing existing invalid data Linked table design restriction error in split databases Error 3112: records cannot be read due to permissions issues Cannot modify a table if another user is editing it Access table field limit exceeded error Drawbacks of multi-valued fields and attachment fields in Access Primary key and index related error messages Deleting fields involved in indexes or relationships Deleting fields or tables involved in relationships Duplicate value errors in indexes or primary keys The 'Recordset is not updateable' query error Cascading deletes warning and potential data loss Read-only field errors in forms and queries with calculated fields Warning about undo in global find and replace operations Number size and type limits in Access fields Currency, single, double, and decimal field usage advice Input mask errors when entering invalid data formats Field required property and related error message Circular reference errors in calculated fields 'Expression too complex' in queries and expressions Division by zero error and using IF conditionals Type mismatch errors with fields and queries Field types that cannot be sorted or filtered in queries 'Query must have at least one destination field' error 'This operation must use an updateable query' error Ambiguous field reference errors in multi-table queries 'Enter parameter value' prompt and causes in queries Action query warning prompts and turning off warnings Update and append query error summaries 'Section width is greater than the page width' in Access reports Aggregate function usage limitations in page headers and footers |
||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access microsoft access error, not enough space on disk, not responding, not working, has stopped working, odbc connection failed, product activation failed, key violations, you must enter a valid in the field, your network access was interrupted PermaLink Error Messages in Microsoft Access |
