Microsoft Access 2010 Tutorials
External Data / Linking to Data
Visitors to VTC.com will be able to view all introductory videos for each training course.
Free Trial Members will gain access to first three chapters for each training course.
Full Access Members have full access to VTC.com�s entire library of video tutorials.
Learn More
Subtitles of the Movie (Microsoft Access 2010 / External Data / Linking to Data)
Now let's take a look at something you're going to hear a lot about, as you get out there and read about Access and play with Access, and that is the concept of Linking to Data, and you'll hear them called Linking to a Table, and that sort of thing. Now this is quite popular with Access, and what it's going to do is allow you to link to data that's stored in another format, or in another database, and work with it, as if it is an Access database object. And I'm going to show you a very simple demo here. The first thing I want to do is, export our students table out to Excel OK? And so I'm just going to choose the Export to Excel, after having highlighted the students table, the file format is SXLSX, and I will just take the default, send it out there, and then if I go look in my documents folder, and as you can see here is my students Excel file, and if I open it up, you will notice how it exported. There are my field names, and these just all become columns in Excel. Now here's the cool part, I can now link to this. Let's say that this is an Excel file that we have out there, that's really not part of our database, but we want to get to this information and include it in a query, on a report, that sort of thing. I will close Excel, I will close this, and what I'll do now is, in the Import and Links Section in Excel. Notice I'm on the External Data Tab, in Access 2010, and I just click on Import and Link on Excel. Now notice my choices, I can import this data into the database, I can append the copy of whatever's out there to a table, or here's the one I want, Link to the data source, by creating a linked table. And so I choose that, and I have to tell it where is the data, and I click on Browse, and I point to an Excel file, students, that's the one I just created, and normally I wouldn't create it, but there would be a file out there, an Excel file, I want to get in there, and so I choose that, and now I hit OK. It shows me the data, and notice I can look at worksheets in the file, if there were multiple worksheets, I'd see them here. I can do a named range, and if I have named ranges, I would see them here, and then I can click Next. And I can tell it the first row contains column headings, and it does. If I turn that off, notice I lose those. And I'll click Next, and my Linked table name, I'm going to call this Students Excel. Click Finish, and notice it tells me that it has completed the process, and now over here in my Tables Section, I now have a Students Excel. And if I double click that, notice it looks like an Access Table. Alright now, can I change this data? No, notice when I try to edit it, I don't know if you can hear the warning, there it is, every time I hit a key, I can only view this data, alright? But notice something I can do. I can click on this, and then go to Create and Report, and I can report on that data. Notice there is my data in a report. Now I did the Fast Report, and it's kind of ugly, but you can go look at some of the videos on reporting, and see how we can use this alright? And so, this becomes usable data in Access. Now there's a lot of ifs, ands, and buts. In Excel, since that's not a database application, I can't update the data, or add data, or delete data, but I can view it, so if you have Excel spreadsheet data out there, that you want to include in reports, or even in queries, notice I can create a query, based on that students Excel data alright? And I can query data from that. Notice I just point at it, and boom, there's a query come in from that Excel spreadsheet. Cool stuff, and so you can link to other Access locations, other Access tables, you can link to SQL, you can link to Excel, XML, all that stuff, even to our comma delimited text files. So that's Table Linking. Now you need to read up on that a little bit, but it's very cool, very neat, you just have to make sure that this Excel file never moves from the location it is. If it does, you'll have to re-link it alright? But that's Table Linking in Access 2010.
Tutorial Information
| Course: | Microsoft Access 2010 |
| Author: | Mark Long |
| SKU: | 34224 |
| ISBN: | 1-936334-91-7 |
| Release Date: | 2011-05-12 |
| Duration: | 9 hrs / 121 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
