Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you move data in or out of your database on a regular basis you don't need to go through all the import and export steps every time. For example, if you receive data weekly from your company's larger database system, but you use Access for your day -to-day analysis, those weekly imports will always have the same settings. So you can expedite the process by saving your setup steps for reuse. Let's say for example every Monday I receive an Excel spreadsheet of new products from corporate, and I need to add them to my database to start the week. So I'll add these two new brands to my existing product's table.
I'll close my spreadsheet in Excel. Here is what my table looks like in Access. I'll close this as well. Go to your External Data tab and click on the Excel button. First, we need to navigate to our file. So I'll click on Browse and find the Productsimport file. Next, I have the option of creating a brand-new table from scratch, adding the file's data to an already existing table or linking the table. If you add the records to the current table, you'll need to make absolutely sure that the field names are identical, the datatypes, the number of columns, the spelling, the spacing, everything.
If you Link to the table you actually create a live connection between the two. Any changes made to your data in either program will be reflected in the other. We'll tell it to append and we'll add the records to the Products table, and I'll click OK. Here it shows me the data that's going to be imported and I'll click Next. It's going to add it to the Products table. So I'll click Finish. Now, here is a complex message that we need to go over; Microsoft Access was unable to append all the data to the table.
The contents of fields in 0 record(s) were deleted, and 0 record(s) were lost due to key violations. Well, this is actually good news that while something is obviously up, nothing was deleted or lost so the update did happen. If it says that records were deleted, it means that you have a mismatch in your data field types or your FieldSize properties. If it says that records were lost, take a look at your primary key values. You probably have some duplicates in your IDs. So I'll go ahead and say Yes. Now it wants to know if I want to save my import steps, so I'll put a check mark in this box.
It suggests the name and it's an Import and here is my file name Productsimport. So I'll leave those. I'll give it a description to remind me of what it is later; Weekly import from Corporate. Now I also have a wonderful option here to create an Outlook task. This will create a task that even has a Run Import button right in it. So when I click on the button it goes into Access and it runs this import for me. You can even set that task as Recurring. So it pops up every week. Right now though, I'm going to uncheck it. I'll click Save Import, and now let's take a look at our data.
I'll click on the Products table; there is my Alpharatz, and I'll scroll down, and there is Montoya. Now, let's take a look at our ImportErrors. I'll open this up, and it says something about the Profit field. Now, this actually makes sense, because if I go back to my Products table this Profit field is not actual data, this is a calculated field, and it's subtracting my Cost from my Price and giving me a dollar amount. So it actually makes sense that it had an ImportError, because there is no actual data in that field. So I'll close both tables, and then I'll go ahead and delete my ImportErrors.
I'll confirm the deletion. Now here is the cool part. The next time I want to do this import or an export, instead of clicking on the Import Excel or Export Excel buttons and going through all those steps again, I can go straight to my Saved Imports. Now before I take this step the first thing that I have to do is get my new file and place it in the exact same location and with the same name as the previous file. So I'm going to go to my Outlook, and here is the file that was sent to me from Corporate. So I'll right-click on it, and do a Save As, find the original file and save the new one over the old one.
If you like to try this for yourself at home, I've created a Productsimport2 file. You can delete this file, erase the 2 from that file, and continue on with these steps. I'll go ahead and click Save. It asks that the file already exists. So Yes, I want to replace it. Now I'll go back to Access, and I'll run this import. I get the same message as before which makes sense now and I'll click Yes. Here is another message about the error descriptions. I'll say OK, and I'll close.
Now, when I look at my Products table again I have my new products, and just as before I don't even have to bother with this ImportErrors. I can go ahead and delete it. Look how many steps we cut out by saving our import, and if you use the Outlook button it will go even faster.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74977 Viewers
80 Video lessons · 129943 Viewers
52 Video lessons · 64126 Viewers
59 Video lessons · 49912 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.