Edit Linked Excel Data
By Richard Rost
2 years ago
Edit Linked Excel Data Directly in MS Access
In this Microsoft Access tutorial, I will show you how to edit linked Excel data directly within Access, allowing you to make changes without constantly re-importing data. We'll cover linking Excel spreadsheets to Access, modifying the connection string, and the limitations of this method, such as not being able to delete records.
Keith from Germantown, Wisconsin (a Platinum Member) asks: In my office, everyone uses Excel for data entry and managing spreadsheets. I handle most of the reporting and financial statements, and I frequently import their data into my Access database. I noticed that linking an Excel spreadsheet as a table in Access makes it non-editable. Is there a way to avoid constantly importing data while still being able to edit the linked spreadsheet?
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, editing linked Excel data in Access, editable Excel table in Access, linked Excel sheets in Access, modify Excel from Access, Access table linked to Excel sheet, IMEX setting Access, change IMEX value Access, Access 2021 linked Excel, delete records linked Excel Access, Access linked table manager, Excel data import in Access, Access 365 Excel editing
Intro
In this video, we'll talk about how to edit linked Excel data directly in Microsoft Access. You'll see how to link an Excel spreadsheet to Access, update records, and add new data without the hassle of constantly importing the spreadsheet. We'll cover using the Linked Table Manager, editing the connection string to enable editing, and explain important limitations, like why you still can't delete records. If you've ever needed to make changes to Excel data from within Access, this tutorial will show you a useful trick to make it happen.
Transcript
Today we're going to talk about editing linked Excel data. You've got an Excel spreadsheet. You can link to it, but you can't edit any of the records in it. Well, in this video, we'll show you how. Today's question comes from Keith in Germantown with Skaampton, one of my platinum members. This is one of those questions that I get every so often. The more times I get a question, the further up the list I have to bump it. Someone else just asked me this morning, sent me an email saying that this is possible. I'm like, yeah, yeah, I've been meaning to do a video about it. So here we go.
Keith says, in my office, everyone uses Excel for data entry and managing spreadsheets. I handle most of the reporting and financial statements. I frequently import their data into my Access database. I noticed that linking an Excel spreadsheet as a table in Access makes it non-editable. Is there a way to avoid constantly importing data while still being able to edit the linked spreadsheets?
Yes, Keith, it's possible with a trick. The default method of linking to an Excel spreadsheet even tells you in the instructions, hey, you can't edit this stuff. But as a couple of people have discovered online, there's a little trick you can do to be able to edit it. You can edit records and you can add records to the spreadsheet, but you still can't delete records. So let me show you how to do it.
First off, a couple of prerequisites. This is an expert-level video, so it's a little bit beyond the basics, but it's not quite developer level. We don't need any programming for this. I recommend if you don't know how to import data from Excel, go watch this. And in this video, I teach you how to link to an Excel spreadsheet read-only. If you don't know the difference between importing and linking, definitely go watch these two videos. These are both free. They're on my website. They're on my YouTube channel. Go watch them and come on back.
Alright, so I've got a spreadsheet here with student grades in it. Real simple sheet. Student, test 1, 2, quiz, 1, 2, 3. I think I used this for another video, in fact. But what we want to be able to do is link to this from our Access database. And some people can manipulate this data or even add records.
Alright, so here I am in the TechHelp free template. This is a free database you can grab from my website if you want to. We're going to link to that sheet. So external data, new data source from file, Excel. We're going to link. I'm going to browse. Go to my spreadsheets folder, find student grades. There it is. I'll have a little preview. They add it over here and then hit open. Then hit OK. You'll get this little preview here. First, it contains column headings as it should. That looks about right. Hit next. Give the linked table a name. I'll call it gradeT for grade table. And it says finish linking.
Alright, we're in here. There it is right there. If we double-click on it, open it up. You can see the data. Notice there's no extra row on the bottom. If you click up here, you can't make any changes. How do we get it so this thing is editable? It's a simple fix, but it only works in later versions of Access. I think 2021 and later. So if you've got 365 and you're up to date, it'll work.
What you do is right-click on the table. You're going to go into the linked table manager. Open this guy up here and find your sheet. Right-click on the box and then click edit. This thing comes up. This is the connection string to that spreadsheet. All you got to do is change this value here, IMEX. Not IMAX like when I go to the movies, it's IMEX. Import export. We're going to change that right there to a zero. That's it. Save it. Close.
Now, when you open up your table, look at that. I've got an add new row on the bottom there. I can come in here and make changes. 65, 100, and so on. I can add someone new down here. Record 99, whatever. Still can't delete though. Once you've done editing, if you try to hit delete, it says deleting data in a linked table is not supported by this ISAM. That's Indexed Sequential Access Method. I had to look it up myself. I don't know what that is. Basically, you can't do it. You can add records. You can edit existing records. But you can't delete records.
Also, be careful if you've got multiple people trying to edit the spreadsheet at the same time, that can cause problems. But if you just want to get in there once in a while and make a few changes, do stuff from Access. That's great. Notice if I go back to the original sheet, there's my additions right there. You can see it. It's not perfect with the formatting folks. So your data will go in there. But obviously, the formatting, well, you get what you get. But at least it works.
If you want to learn more, Allen Browne's website has a great article on this with a lot more information. You can also go in and edit the connection string directly in a query. He's got a lot more data on there. He's definitely got all these things detailed. He's got a little video. Go watch it. Give him some likes and subscribes and all that good stuff. I'll put a link to this page down below.
If you want to learn more cool stuff with modifying Excel Sheets from Access, I've got a video on Excel Automation. We can actually construct an Excel Sheet from Access VBA. This one's pretty cool. I cover a lot more about importing, linking, and all that stuff between Access and Excel in my Access Expert level 20 course.
There you go. That's going to do it for today. That's your TechHelp video. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Editing linked Excel data in Access
Linking an Excel spreadsheet as a table in Access
Prerequisites for linking and editing Excel data
Creating a link to an Excel spreadsheet in Access
Using external data source to link Excel in Access
Previewing and setting up Excel data link in Access
Renaming linked table in Access database
Using the Linked Table Manager in Access
Editing the connection string in Access
Changing IMEX value to enable editing
Adding new records to linked Excel table in Access
Editing existing records in linked Excel table
Limitations of deleting records in linked Excel data
Handling multiple users editing linked Excel data
Viewing changes made in linked Excel data
Allen Browne's article on editing linked Excel data
COMMERCIAL:
In today's video, we're going to tackle editing linked Excel data directly from Access. You'll learn how to link your Excel spreadsheet to Access, making it editable without constantly re-importing data. We'll walk you through linking the sheet, changing the connection string, and finally, editing and adding records. Note, you still can't delete records, but you can edit and add with ease. Also, watch out for multiple users editing at once! You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.
Quiz
Q1. What type of queries did Keith frequently make regarding Excel spreadsheets?
A. How to link UN-editable tables
B. How to delete records from imported data
C. How to avoid importing data constantly
D. How to use Excel for data analysis
Q2. Which version of Access is required to edit linked Excel spreadsheets?
A. Access 2010 and later
B. Access 2016 and later
C. Access 2019 and later
D. Access 2021 and later
Q3. What is the primary limitation when editing linked Excel data in Access?
A. You cannot add new records
B. You cannot edit any records
C. You cannot delete records
D. You cannot insert new columns
Q4. What is the name of the property in the connection string that needs to be changed to allow editing?
A. IMAX
B. IMEX
C. IMXP
D. IMXT
Q5. What should the IMEX property value be changed to in order to enable editing linked Excel data?
A. 1
B. 3
C. 2
D. 0
Q6. What does ISAM stand for?
A. Indexed Styled Access Method
B. Importing Sequential Archive Manager
C. Indexed Sequential Access Method
D. Interactive Sequential Access Manager
Q7. What is one major caveat when editing linked Excel spreadsheets with multiple users?
A. Data may be overwritten
B. Spreadsheets can become locked
C. Formatting is perfectly maintained
D. Simultaneous edits may cause errors
Q8. Which external resource was mentioned for further reading on the topic of linking Excel and Access?
A. Microsoft's official Access documentation
B. Allen Browne's website
C. Excel's built-in help feature
D. Access user community forums
Answers: 1-C; 2-D; 3-C; 4-B; 5-D; 6-C; 7-D; 8-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 TechHelp tutorial from Access Learning Zone covers editing linked Excel data. Imagine you have an Excel spreadsheet linked to your Access database, but you can't edit it. We'll explore a workaround to make it editable.
Keith from Germantown, one of my platinum members, uses Excel for data entry and managing spreadsheets at his office. He handles reporting and financial statements, frequently importing data into Access. However, he noticed linking an Excel spreadsheet as a table in Access makes it non-editable and wondered if there's a way to avoid constantly importing data while still being able to edit the linked spreadsheets.
Keith, you can indeed edit linked Excel data with a trick. Normally, the default method of linking to an Excel spreadsheet mentions you can't edit the data. However, there's a workaround that allows you to edit and add records to the spreadsheet, though you still can't delete records.
Before we proceed, note that this tutorial is for expert-level users and doesn't require programming. If you aren't familiar with importing data from Excel or linking to an Excel spreadsheet read-only, I recommend watching my introductory videos on these topics available on my website and YouTube channel.
Let's start. I've got a simple spreadsheet with student grades. To link it to our Access database, use the TechHelp free template, which you can download from my website. Link to the sheet by navigating to external data, new data source from file, and then choosing Excel. Select to link it, and open the spreadsheet. The wizard will guide you through the steps: make sure the sheet contains column headings, name the linked table, and finish linking.
Once linked, open the table. Initially, you can't make any changes to the data. Here's the fix—it only works in Access 2021 and later versions. Right-click on the table and select the linked table manager. Find your sheet, right-click, and choose to edit the connection string. Modify the IMEX value from one to zero and save it.
Now, when you open the table, you'll see the option to add a new row and make changes to existing records. You can't delete records, but you can add and edit them. Be aware that multiple users editing simultaneously might cause issues. For single-use edits, this method works well. Changes will reflect back in the original sheet, though the formatting might not be perfect.
If you want more information, Allen Browne's website has an excellent article on this topic, including editing the connection string directly in a query. He provides detailed explanations and a video. I'll link to his page below.
For more advanced techniques, including Excel Automation with Access VBA, check out my other tutorials. I cover importing, linking, and more in my Access Expert level 20 course.
That's it for today's TechHelp tutorial. Visit my website for the complete video with step-by-step instructions on everything discussed. Live long and prosper, my friends.
Topic List
Editing linked Excel data in Access
Linking an Excel spreadsheet as a table in Access
Prerequisites for linking and editing Excel data
Creating a link to an Excel spreadsheet in Access
Using external data source to link Excel in Access
Previewing and setting up Excel data link in Access
Renaming linked table in Access database
Using the Linked Table Manager in Access
Editing the connection string in Access
Changing IMEX value to enable editing
Adding new records to linked Excel table in Access
Editing existing records in linked Excel table
Limitations of deleting records in linked Excel data
Handling multiple users editing linked Excel data
Viewing changes made in linked Excel data
Allen Browne's article on editing linked Excel data
|
