I just finished a series of blogs on using MS Access as a tool to clean your Excel data. That series presumed that you want to keep your data in Excel, but you’d also like to take advantage of some of the tools available in Access to analyze and clean your data. We imported the Excel data into Access, performed a series of manipulations on it, and then exported it back to Excel.
Today I’m starting a new series to talk about linking Microsoft Access to other data sources, including Excel and SQL Server. I’ll talk about the reasons you might want to do this, the methodologies, and some restrictions that you’ll face in Access when you do this.
I’m planning to cover the following topics in this series:
Linking to Excel data
Linking to SQL Server tables and views
Linking to data in other Access databases, also known as a "Split Database"
Linking to other data sources
Let's start by exploring the reasons you might want to link your Access databases to data that resides elsewhere. In a perfect world, all of your data would reside in a single location - be that SQL Server, a software package, Access, or another database tool. But that's not a typical scenario. I've worked with dozens of companies over the years, and they all have data scattered over several different sources. They might have financial information in one database, inventory in another, and manufacturing in a third. In addition, there are often little data addendums - snippets of data that are obtained from customers, vendors, partners, or cloud-based sources in a variety of different formats, including Excel.
I've worked with companies that try to get all of that data into a single source, but I've never seen it done successfully. Not even close, in fact. A more realistic solution is to use tools that allow you to consolidate data that resides in different sources into a single repository. You'd then use that repository for querying, reporting and analyzing your data.
There are many tools you could use as that repository, including Access. I won't get into the pros and cons here, since that's a big topic by itself. Instead, let's say that you already have a license to Access, and you're somewhat familiar with it. In that scenario, it might make sense to employ the powerful query and report tools in Access to pull together your data.
As previously stated, I'll be covering several different data sources in this series, starting with Excel. I'm starting here, because Excel is a tool that many people are already familiar with it, and because it's a common data repository, especially among small businesses.
In a previous post, we looked at importing Excel data into Access. Today we'll be linking it instead. When you link, you'll have a live connection to the data source. If the Excel sheet gets changed, Access will be aware of that nearly immediately. You'll never have to worry about refreshing the data, or importing new data. It'll always be live and up-to-date.
To keep things simple, I'm going to work with a very small Excel database. Here's what it looks like:
As you can see, this Excel sheet has a list of my vendors. Notice that there is a second worksheet called "Students". We won't be using that one today. I'm only going to link to the Vendor data.
Setting up the link in Access is simple. I'll follow these steps:
Create an empty Access database
Use the "External Data" ribbon to create a new data source:
On the Import screen that pops up, I'll select my Excel file, and choose the "Link" option:
After clicking "OK", I get a security prompt. You may or may not get this, depending on how things are set up on your computer:
Next I see the Linked Spreadsheet Wizard, where I choose which sheet in the Excel file I want to link:
I check off the "First Row Contains Column Headings" checkbox, and click Next:
I type in a table name (I used tblVendorList), and click Finish:
A message will pop up telling you that the table is linked, and you'll see it in your database window:
Note the icon to the left of the table. You'll see the familiar Excel logo, along with a tiny blue arrow. The blue arrow indicates that this is a linked table.
When you open the new table in Access, you'll see the same data that was in Excel. If you try to change a record or add a new one, you'll find that you can't. Access creates a one-way link to Excel. The data can only be changed on the Excel side of the fence. This is not true with other data sources. In a future blog, we'll see that you can edit other types of data in Access. With Excel, we're stuck with read-only, but that enough for reports, queries and analysis.
There's another important limitation with linked Excel data. You can only have that data opened in one place - either Access or Excel - but not both at the same time. Once again, this is not true for other data sources. But with Excel, you'll have to worry about timing. This can often be acceptable if your dataset changes infrequently (maybe once a week), and your querying and reporting needs are also infrequent (maybe once a month). I have clients who update their Excel sheets every Monday, and then run their reports every Tuesday. A similar scenario might work for you.
Refreshing the Data
As I mentioned above, I can't have the Access table opened at the same time that I'm editing the Excel sheet. So I'll close the table, but I'll keep my Access database open. Now I can edit my Excel file. Here are my changes:
As you can see, I added a new row, and I changed the vendor name on row 8. After I saved it, I went back to Access and re-opened the table. As you can see, the changes are reflected here:
Note that I did not have to click a Refresh button or run any process. The link is live, which means it always reflects the current changes.
Don't Move Your File
One of the downsides of this link is that it is dependent on the Excel file staying in the same location and having the same name. If you rename the file or move it to another folder, the Access link will no longer work. To demonstrate, I renamed my Excel file. When I try to open it in Access, I get this error message:
To correct this, I right-click the file and choose "Linked Table Manager". On the screen that pops up, I check off the Excel table, and click OK. Excel automatically prompts me to locate the file:
The confusing thing about this is that it looks like the original file is still in the folder (in my screen shot, the file to the right of the highlighted file). It's not really there. That's just a ghost of the file, being kept alive by Access. If you were to close Access, that would disappear.
Ignoring the ghost, I select the renamed file, and click "Open". Access then updates the link to the table, and I can continue working with it.
It's easy to link your Excel file to Access, so that you can use Access tools to query and report on the data. Your capabilities are somewhat limited, but its still a great feature. In my next Access blog, I'll look at linking to SQL Server data, and you'll see that it's a much richer interface. We'll also be able to link together the SQL data and the Excel data, allowing us to integrate the two sources. If you'd like to be notified when that post goes live, subscribe using the form below.
As always, reach out if you'd like to discuss your Access project. I'm currently looking to take on one or two new clients in 2023, so this is a great time to open a conversation. I expect these spots to fill up fast! You can reach me using the Contact page, by phone (978-582-9550) or by email.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at email@example.com.
© 2023 North Port Solutions, LLC. All right reserved.