In my Access blog series, I’m currently covering the integration of Access with other data sources, like Excel and SQL Server. I thought I’d follow suit in today’s Excel blog. This is a great follow-up to my last Excel blog, where I looked at importing your data into Excel. This time, we're going to link the data, so that you'll always have up-to-date information.
Why Link Your Data?
Excel has some fabulous tools for analyzing your data, so it makes sense that you’d want to use them against your mission-critical data sources. Those data sources are probably not all in Excel, though, so today we’re going to see how you can link your Excel sheet to data in another source.
We'll start by looking at linking to an Access database, just to keep things simple. Don't worry, if you've got your data in another source, like SQL Server, we'll work up to that.
Sample Database
Let's start by looking at the Microsoft Access database that I'll be using. I've downloaded the ubiquitous Northwind Traders database, which comes with a wealth of sample data. We're going to work with order data in this example, which looks like this:
As you can see, we've got a list of orders, and we've also got details for each order. A few of the important fields we'll be using are:
Product name
Quantity
Total price
Status
Establishing the Link
To link this data to Excel, I'll create a new Excel worksheet, and then use the "From Access" option on the "Data" tab:
When prompted, I'll browse to my Access file, and I'll then be presented with the Data Link Properties dialog box:
You can see that the name of my Access database appears under #1 as the Data Source.
#2 might be confusing. It's asking for your login information. This is not your login to your computer or your server (as the prompt implies). Rather, it's the login to the Access database. You might believe your Access database doesn't have logins, and you'd be right-ish. Once upon a time, Microsoft Access had user-level security that you could utilize to control access to your database. That feature has been eliminated, but instead of truly stripping it out of Access, Microsoft simply made it so that "Admin" is the one and only user for your database. So leave "Admin" as the user name, and make sure the "Blank password" is checked off.
There's lot of other information on the tabs of this dialog box, but I'm going to ignore them for now, and click the "OK" button. When I do, I'm presented with a list of tables and queries (a.k.a. "Views") in my Access database.
Let's start with a table, and then we'll move on to trying this with a query. I'll selecting the Order Details table, and then click OK.
The last prompt is the Import Data dialog box:
You can choose to create a table, and PivotTable or a PivotChart report. I'm going to stick with Table, so that I can easily view the entire dataset in Excel. I'll leave all of the other default options, and click OK. My Excel sheet looks like this:
Excel has gone ahead and formatted the data as a table. You can now filter and sort the data, as well as creating charts and PivotTables.
What Happens When Your Data Changes?
There's a Refresh button on the "Table Design" ribbon:
But let's go one step further. We can tell Excel to refresh this data automatically. To do so, click the "Connections" button on the "Data" ribbon. A dialog box will open showing the connections in your Excel file. Click the connection and then click Properties:
On the Properties dialog box, you'll see check boxes under "Refresh control". These options allow you to refresh at regular intervals (every 60 minute, for example), or refresh every time the Excel file is opened.
I usually choose the last option, so that the data is fresh every time the user opens the Excel sheet.
Access Queries
Now let's talk about queries, which are sometimes referred to as Views.
In Access, a query allows you to pull together data from more than one table. The Product ID is a good example in our dataset. Rather than seeing the ID number, wouldn't it be nicer to see the product name? The ID number is stored in the Order Details table, but the product name is stored in the Products table. To get both of them to appear in my Excel sheet, I'll create a query that brings them together. Here's my query design in Access:
I've named my query Order Details for Excel.
Now I'll create an Excel link to this query. The steps are exactly the same as before, except, of course, that I choose "Order Details for Excel" instead of "Order Details". You'll see that the data looks very similar, but this time I've got the Product Name instead of the ID number:
Now that the data is in Excel, you've got the full breadth of Excel tools at your disposal. Create PivotTables, charts and graphs to your heart's content!
What about SQL Server?
This was a great introduction to linking in Excel, but you're likely to need to connect to data in SQL Server, Oracle, or another data source. In my next Excel blog, I'll show you how to connect to SQL Server. To be notified when that post goes live, subscribe using the form below.
Did this technique work for you! Let me know in the comments below, or reach out to me for help with your project.
Kim
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 kmartin@northportsoftware.com.
#northportsolutions#learningneverstops#smallbusiness #excel #microsoftexcel#msexcel #excelformulas #dataanalysis
© North Port Solutions, LLC, 2023. All rights reserved.