In this video, learn how to link data from other databases into your database.
- [Instructor] It doesn't take a long reporting career to figure out that sometimes the tables we need are actually stored in other databases, maybe other Access databases or even Sequel databases. We can easily link tables into our solution for our dashboards. So let's review how to link a table and then how to work with a linked table manager. Again, I'm going to show you how to link an Access databased table to my Access database, and I'll show you where the ODBC connections are; if you have ODBC connections to your Sequel database you can easily use those.
And then we'll take a look at the linked table manager. Okay, let's go back to our LearnData database. Okay, so the connections are actually going to be stored on the external data tab, so I'll navigate to the external data tab. We're going to use an Access database for our example here, but if you have ODBC connections to a Sequel database then you can actually use ODBC connections to get there. Okay, let's click Access. And we're going to navigate to our exercise files.
I'm going to work with the manager's required videos. These are the videos that managers are required to watch. We want to keep up with those in our reporting so we're going to bring that table in. I'll double-click that; I'm going to do a link to the table so that as the manager required video set is updated my database will also be updated. I'll go ahead and click OK. I'll click the table and then I'll click OK. And then I can easily see my local tables from my linked tables.
Notice that my linked table has a little blue arrow and then it also shows where it's actually linked from when you hover over it. All my other tables are considered local because they're stored actually inside the Access database. If you're connecting to your data through other types of connections like ODBC then you'll use those options again when you link your data. If you need to modify your table link you have to use the linked table manager to do that. So any linked table, you can right click it, navigate to the linked table manager and left click.
It'll show you videos required for managers. I can check the box there, I can tell it to prompt for a new location, and click OK. This'll allow me to change the location or update the data. Alright, we're perfect, we'll go ahead and close that. For the purposes of this course we're working off local tables. But in real life I always link my tables into a brand new database to build my queries. This allows me to have my Access database open and my dashboards open at the same time.
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Understanding the software limitations
- Working with Access tables
- Analyzing table relationships
- Building queries
- Exporting data for Excel
- Linking to Access data from Excel
- Building charts and PivotCharts in Excel
- Creating macros