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.