Append Mistake #2
By Richard Rost
4 years ago
Can't Append All the Records in the Append Query
Continuing on with common errors that people receive when performing append queries, we come to "Microsoft Access can't append all the records in the append query." This is either due to a type conversion failure, key violation, lock violation, or validation rule violation." We'll discuss what each of these are and how to fix them in this video.
Pre-Requisites
Recommended Courses
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, Common errors when you run an append query, How to Deal, How to fix, type conversion failure, key violations, lock violations, validation rule violations, Why can't I append some records, dealing with common errors
Intro In this video, we will talk about some of the most common mistakes you can encounter when using Append Queries in Microsoft Access, specifically the error message saying Access can't append all the records. I'll show you examples of what causes errors like key violations, type conversion failures, lock violations, and validation rule violations. You'll also see how importing data from Excel into Access can lead to these problems, and what you can do to fix them.Transcript Welcome to another Fast Tips video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.
In today's video, we're talking about Append Query mistake number two. This actually consists of a couple of different things you can do to get the error message that says that Access can't append all the records in the Append Query. You're either doing this because of a type conversion failure, a key violation, a lock violation, or a validation rule violation. We're going to talk about all these right now.
But first, if you haven't watched my Append Query video, go watch that right now. I put a link down below. It's absolutely free of charge. It's on my website and it's on my YouTube channel. Go do that, then come back.
One more time, just like I showed in the last mistake video and in the original Append Query video, let's go through importing data from Excel again, so I can show you another common mistake that happens to people.
So one more time, let's go to External Data, New Data Source, From File, Excel. We're going to browse to it. It's on my desktop and it's the Append right there. Hit open, Import, first row contains column headings. Yes. Let's fix our column names so they match the ones in the destination. Again, you can do this here, or you can do it after you import. It doesn't matter. We talked about this in the last video.
Now, let's say at this point, most people just leave the default and let Access add the primary key. That's okay. Next, but this causes the problem, or at least part of the problem. Finish and then close.
So here we have our data. Now, this ID here doesn't match the customer table because it's got customer ID there. Just like we did in mistake number one, we have to make sure all the fields match. I'm going to rename this customer ID. Save that, close it.
Let's make our query, design sheet one. Append to customer table. Hit OK. Bring down the star, and then run it.
We get the message: Can't append all the records in the Append queries. There are four different reasons it could be.
Zero fields due to a type conversion failure - we'll talk about that one next. Here's our problem: five records due to key violations.
I mentioned this briefly in the original Append Query video because we had a key violation when we indexed the email address field for Will Riker. We made it so that you couldn't append the same customer, and we're keeping the email addresses unique.
The problem here is that we've got the customer ID, which is an autonumber, and we're trying to put the same autonumbers in the table. Do I run the append query anyway? No, because nothing will really happen.
This isn't going to work with the star because sheet one has these customer IDs that already match the customer IDs that are in here. If you're importing more than just these records - let's say you've got customers and their orders, and you have to keep this number the same - you're going to have to change it to something that's not in this table.
If you're bringing in customers, like this is customer number five, and you've already got a customer number five, you're going to have to take this customer number five, reassign his customer ID to something else, make it not an autonumber, and also update any related table. It becomes a mess.
I do cover this in my more advanced classes, but essentially the problem here is that you've got a number here that's matching a number in here and you're getting a key violation.
If this is just a standalone set of people and this customer ID doesn't really matter, you can just delete the field and then you'll be able to import the data. So delete that and just get rid of customer ID. Delete the field, confirm you're sure. That will also delete the primary key. You don't need it. There you go. Now you can import the data.
You'll also get a key violation if, like for example in the first video, email exists in here and you've got email indexed. That'll also give you a key violation. That's what that error is.
Now, what are the other ones? So we've covered key violations. Let's go back to the first one: type conversion failure.
I see this all the time. A type conversion failure is basically putting data into a field that doesn't support that type. You're trying to jam text into a number, for example, or a date that's not properly formatted.
Let me give you an example. I see this all the time. Let's say you come over here - we've got a family size field in our database. In the customer table, we've got family size. And family size is a number.
Let's come over here and put family size in here. I'm going to fix these while I'm in here so I don't have to fix them again in Access. I want them all to import properly. We know how to do this: last name, and then email address.
I see people do this all the time. They get a sheet that's got, for example, two, four, six, then someone puts in "A" in here, and then three. You're working just fine in Excel like this. Let's see what happens when Access gets a hold of it.
Save this, Control-S, close that down. Let's do our import again and see what we get. Delete sheet one. Go to External Data, Import from File, Excel. We're masters of this by now. Browse, bring this in, hit open. First row contains column headings. Here's our fields.
You can come over here at this point and change the data type to Long Integer or whatever you want, but most people don't catch this at this step. When you import the data, Access is actually going to pull it in as text. No primary key. Next, then finish.
So now you have a sheet one that's got family size as text. Watch what happens when we try to do the query: sheet one, append, customer table, hit OK, bring in the star, and run.
You get: one field due to a type conversion failure. That means you're trying to jam an "A" into a number and the append query just won't do it. If you want to run the query anyway, you just won't get that record. But I would say no, don't run the query. Go fix that, figure out what the problem is, and then do it again.
As a side note, you can try to fix this problem during the import itself. Watch this: if you import from file again, and at this point click on this field and change it to Long Integer, which is what family size is in the table, when you run the import you're going to get a table called import errors. It's saying type conversion error: field is family size, row is five. So it's telling you all the rows that have errors on your import.
If you look in sheet one, you still do get your row five, it just doesn't import that field. So that's kind of nice. This might be better for you, because at least now you're bringing in family sizes and all values. Let's see what happens now if we do this with a null family size.
Let's run that query again: query design, sheet one, append (oh, wrong one). I've been clicking on the same things for an hour and a half - it's driving me nuts. Do this and then run it.
Everything seems okay. Let's check the customer table. There we go, Harry Mudd is right there, and he's got a null value for family size. It doesn't bring in the zero; it gives you a null. That's better than having an error, because if you have the error at the append query, it doesn't bring the whole row in at all. At least if you fix it on the import, it just doesn't bring in that field. That's up to you.
Another common way that I see type conversion failures is with dates. If someone puts a date, for example, into the Excel spreadsheet and it's not a valid Access date, then you go to import it, do the import, and you get the error.
Some types, like 20220123, they're storing it in a weird format in Excel, and Access can't handle that as a date. You get a type conversion failure. You have to find some other way to massage that text field into a valid date using left, right, mid, and all kinds of functions. That will be a topic for another video if you want to see that.
You'll want to know string functions to fix that. Bring it in as a string. If you have something like 20221023, for example, my birthday, you have to break that up into an actual Access date. You can use some string functions and then convert that to a date.
Moving on. The other two, lock violations and validation rule violations, aren't as common, but they do come up from time to time.
A lock violation usually is when you have a multi-user database and someone else is editing a record in that table. That can cause the lock violation. If you're the only user of the database and you get that, check to make sure you don't have the table open in design view in the background. If that still doesn't work, shut down the database, compact and repair it. Reboot your computer if necessary, and then try it again.
Sometimes the Access lock file, that LACCDB file, gets kind of messed up and Access thinks that a record is locked when it really isn't, and that will generally fix that problem. If that still doesn't fix it, run down my troubleshooter. I have a nice big long troubleshooter on my website. It will walk you through all the steps to try to fix your problem if it's something weird. Back up, restart, option explicit, reboot the PC - all this different stuff you can try.
Finally, validation rule violations are exactly what the name implies. You have a validation rule in the table or in a field in the table that the incoming data violates. For example, if you've got a maximum credit limit of $1,000 and one of the records you're trying to append has a credit limit of $5,000, that violates the validation rule. If you don't know what a validation rule is, go watch this video. Like I said, I have a video for everything.
If start date has to be greater than a certain date and you've got data coming in that isn't, it's going to be a validation rule violation. Remember, in addition to field validation rules for a single field, you can have table-level validation rules as well that are usually a combination of fields.
For example, total tips have to be more than this and your total income has to be less than that. So check out this video if you're not sure what that is.
If you're still getting a validation rule violation, also keep in mind you may also get a validation rule violation if you've got the required property set to Yes and you don't have data in a field, like just happened a minute ago when we tried to pull in that NA and it came in null. If that field is required, you'll get a validation rule violation.
Same thing if you've got Allow Zero Length Strings set to No, and you pull in a field that's got nothing in it, like last name. That will also cause a problem.
Those are the most popular reasons why you get this error message: Can't append all the records. If you have any questions, post them in the comments down below. I hope you learned something and we'll see you next time.
If you want to learn more about action queries in general, I have a five class series that covers everything you want to know about them. It's over eight hours of lessons just on queries: mostly update queries, append queries, delete queries, make table queries, tons of different examples. I even go over some things that aren't action queries like union queries for putting two tables together, and crosstab queries, which are basically pivot tables in Excel.
My Microsoft Expert 13 through 17 classes - I'll put a link below that you can click on to find out more information.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, a free Beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you've finished the Beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you've finished the Expert classes. These are the full length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is one of the most common reasons why an Append Query in Access fails to append all records?
A. Sequence error
B. Type conversion failure
C. Disk space issue
D. Access version incompatibility
Q2. What is a key violation in the context of an Append Query?
A. Attempting to import a field with the wrong data type
B. Trying to insert a record with a primary key value that already exists in the destination table
C. Failing to connect to the data source
D. Importing too many records at one time
Q3. What should you do if you are importing customers and the customer ID already exists in the destination table?
A. Leave the customer ID as is
B. Change the customer ID to a unique value not present in the table
C. Ignore all errors
D. Change the table design
Q4. If the customer ID does not matter for your new records, what is a recommended solution before importing?
A. Double the customer ID value
B. Delete the customer ID field from the import dataset
C. Change the data type
D. Import as a new table
Q5. What typically causes a type conversion failure during an import or append process?
A. Importing too many columns at once
B. Trying to append data of one type (like text) into a field designed for another type (like number)
C. Having duplicate column names
D. Case sensitivity issues
Q6. What happens if you include a text value such as "A" in a number field like Family Size during import?
A. It converts "A" to 0 automatically
B. It results in a type conversion failure and the record is not imported
C. It imports as text
D. It is ignored and the record is still imported
Q7. If you change the field data type manually to Long Integer during the import and have non-numeric data, what does Access do?
A. Imports everything, ignoring errors
B. Shows an import errors table listing which rows failed
C. Stops the import altogether
D. Converts non-numeric values to zero
Q8. What is a lock violation during an Append Query?
A. When a table is set to read-only
B. When another user or process has the table or record locked during import
C. When a validation rule is violated
D. When field names do not match
Q9. Which of the following is NOT a potential cause of the "Can't append all the records" error message?
A. Key violation
B. Type conversion failure
C. Field name mismatch
D. Lock violation
Q10. How can you address a validation rule violation during an append operation?
A. Remove all validation rules from the table
B. Ensure imported data follows all field and table validation rules
C. Import each record individually
D. Ignore all warning messages
Q11. Which of the following may result in a validation rule violation?
A. Setting a required property to Yes but importing null into that field
B. Duplicating email addresses when indexed as unique
C. Importing data from an unsupported file type
D. Using outdated Access version
Q12. What should you do if the "Allow Zero Length Strings" property is set to No and you import a blank value?
A. Access will import it as empty without error
B. Access will throw a validation rule violation error
C. The field will default to "NA"
D. The record will be automatically deleted
Q13. What does Access do if there is a type conversion error during the import process and you explicitly set the data type during import?
A. Stops the import process and imports nothing
B. Adds a new column for the problematic field
C. Imports only the valid fields and logs the errors in an "import errors" table
D. Appends the entire row but leaves invalid fields blank
Q14. Why might dates like 20220123 cause a type conversion error during import from Excel to Access?
A. Access cannot process date values with hyphens
B. The date is stored in a non-standard string format not recognized as a date
C. The date is too far in the future
D. Access does not accept numeric dates
Q15. What is a recommended approach if you need to convert oddly formatted date strings (like 20220123) during an import?
A. Re-enter all values manually
B. Use string functions like left, right, and mid to parse and format the date before import
C. Import as is and fix later in Access
D. Set the field data type to text in Access
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 10-B; 11-A; 12-B; 13-C; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone explores one of the most common mistakes people encounter when working with Append Queries in Microsoft Access. We're focusing on the error that tells you Access cannot append all the records from your query. This situation typically arises due to issues like type conversion failures, key violations, lock violations, or validation rule violations.
If you have not already watched my foundational Append Query video, I recommend checking that out first. It covers all the basics and is available for free both on my website and YouTube channel.
Let me walk you through the process using an example. Suppose you're importing data from Excel into Access. After choosing the file from your computer, Access guides you through mapping fields, ensuring the column names in your Excel sheet match those in your destination table. You can standardize these names during or after import; Access accepts either approach.
Most users leave the default setting so Access creates a primary key for the table. While this often works, it can also be the source of the problem. For example, after importing, you might find a field like "ID" in your new table, which may not align with your main customer table where the ID is also an AutoNumber field. If you attempt to run your append query with these directly, Access will produce an error explaining it cannot append all the records. The error message often refers to key violations.
Key violations occur when there is a conflict with unique fields, such as attempting to insert duplicate values into a field set to allow only unique values. For instance, in a previous video, we saw an issue where Access would not allow duplicate email addresses due to an indexed field. In our example now, the issue is that you are attempting to manually append specific customer IDs that already exist or conflict with AutoNumbers in the target table. If you attempt to import records containing these IDs, Access cannot process them because they would not be unique. A workaround, if you do not actually need the imported customer IDs, is to simply remove that field from your Excel data before importing. Doing so lets Access assign new, unique AutoNumbers on import, preventing any conflict.
Key violations may also come up for other fields that are forced to be unique, such as email addresses or other indexed fields.
Next, let's discuss type conversion failures, another frequent problem. This occurs when the data type in your source does not match the data type expected in Access. A typical scenario might involve a numeric field in Access, such as "Family Size," but your Excel file contains an entry like the letter "A" among other numbers. Access cannot convert "A" to a number, so it flags this as a type conversion failure.
If you try to append the data as-is, Access excludes any records that violate the data type—so that one record will not be added. It is best to catch and correct these errors before importing. One way to address this during the import is to specify the data type for each field, changing the relevant fields to the correct numeric or other types. If there are errors, Access will create an "Import Errors" table so you can review the records and fix them as needed.
Date fields present their own challenges. If you're importing a date from Excel that is not in a format Access recognizes (such as "20220123"), you'll see conversion failures as well. You may need to preprocess such values, splitting the string and converting it to a valid Access date using string manipulation functions. This is a topic worth its own dedicated lesson, so let me know if you'd like to see it.
Now, consider lock violations and validation rule violations. Lock violations are less common and usually arise when another user is editing a table at the same time as you're attempting the append. If you are the only user and still encounter a lock violation, check if the table is open in design or datasheet view. If that does not clear the problem, try closing and reopening Access, compacting and repairing the database, or even rebooting your computer. Sometimes, Access creates a lock file that must be cleared if it thinks a record remains locked.
Validation rule violations occur when your appended data breaks a rule set within the Access table or one of its fields. For example, if there's a maximum credit limit of $1,000 and you try to append a record with a limit of $5,000, Access will reject it. There are also table-level validation rules, which might involve checking combinations of fields. Additionally, if a field is set as required and your imported data leaves it blank or null, you'll incur this violation as well. Likewise, if zero-length strings are not allowed and you attempt to append a blank string, you will see an error.
Those are the primary reasons you might see the "Can't append all the records" error in Access. If you find yourself facing a strange or stubborn issue, I have a full troubleshooting guide on my website that walks you through further solutions.
For more comprehensive coverage, I offer an extensive series on action queries that spans over eight hours. My Expert-level classes address all the nuances of queries, including append, update, delete, make-table, union, and crosstab queries. More details are available through the link below.
If you'd like access to extra resources such as extended cut TechHelp videos, downloadable sample databases, or my code vault, consider becoming a member. Silver members and above receive additional classes and perks, Gold members gain download privileges and priority on questions, and Platinum members also unlock all beginner courses for every topic I teach and receive advanced classes monthly.
Rest assured, my free TechHelp videos will always remain accessible to everyone who needs them.
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 Importing data from Excel into Access
Matching field names during import
Setting primary key options during import
Appending records from imported tables
Understanding "Can't append all the records" error
Troubleshooting type conversion failures
Identifying and fixing key violations in append queries
Fixing autonumber and duplicate ID issues
Handling unique indexed fields during import
Correcting data type mismatches (numbers, text, dates)
Using the import wizard to set field data types
Interpreting and using the import errors table
Dealing with null values during import
Converting non-standard date formats for Access
Preventing lock violations during append queries
Resolving table lock and LACCDB file issues
Understanding and fixing validation rule violations
Checking required property and zero-length string settings
Distinguishing field-level and table-level validation rules |
