Viewers: in countries Watching now:
Discover how to manage data entry and reporting tasks more efficiently using Access 2010. Author Adam Wilbert presents lessons on designing forms, organizing and displaying data with form controls, creating flexible queries, and building a form-based navigation system. The course also shows how to build reports from wizards and queries, highlight important data with conditional formatting, and automate reporting processes with macros.
There is plenty of debate on whether you should actually attach files such as images or Word documents to records within your database. Some people claim that it leads to database bloat and slowdowns. The Hyperlink option is definitely a good way to include links to additional resources without actually embedding everything within the Access Database file itself. But when you do have tables such as our Employees table here if I right click on that and go into Design View that have the Attachment and OLE objects field, let's scroll down here, I have got fields here, one of Attachment and one of the OLE Object.
If you do use these two data types you will need to know how to actually make use of them in your forms. Now in this table we are using Attachment data type to store photos of our employees and I've also got this other field here called OLE that's using the OLE object attachment. Now the OLE object is kind of an older and somewhat more obsolete data type and it's basically just kept around for backwards compatibility. Now if you are not already using it, the OLE object data type is kind of an older and somewhat obsolete format. If you are going to attach files to your database, you should probably be using the attachment data type.
But, since we have them both here, let's take a look at how we can work with them in our forms. I will go ahead and close this table. I am going to create a new form in Design View by going to Create tab and Form Design. Now I am going to attach those forms to my Employees table by going to the Data tab, the Record Source property, I will use the drop-down menu here and select tbl_Employees. Then I will go to my Add Existing Fields panel, I will click on the button here to open that. This shows me all of the fields that are in my Employees table. I am going to go and double click on the Photo field here and that will add it to my form, and I will also double click on the OLE field here to add that to my form as well.
Now these two objects are bound to my table here, I can also add these fields manually up here in the Control section, let me go ahead and open this up here. The Attachment field is this one right here, it's the paperclip. If I click on that and I click down here in my form, it'll add an unbound attachment. At this point I can go into the Property sheet and attach it to a field if I wanted to. I could also go up here to the More button and I actually have two different ways that I can embed in OLE object. I have got this one here with a cactus and the XYZ; this will add a bound reference to something in my tables. So for instance this OLE field in my Employees table. I can also have an unbound OLE object, which will be this icon right here.
Let me go ahead and add one of those to my form as well. I will click on it and then I'll click down here to edit. When I do that, since it's unbound to a data table, it wants to store the object right here inside of the form. Access displays a little pop-up window that asks me, how do want to create a file. I can either create a new file right here inside of the form, or I can select a file by selecting the Create From file, and browse to something on my hard drive. For instance, I will go to Browse and I will go to my Exercise Folder on the Desktop, Chapter 3. I will scroll to the bottom here and I'll add this to TwoTreesSalesPresentation, which is a PowerPoint file.
I will go ahead and press OK and then OK again. That will add that into my form, and you will see an image of that pop right up. Let's go ahead and take a look at our form and we will see what these objects look like there. I will switch into Form view. Now these objects are a little bit haphazard on the screen here, but this one right here, this is our photo attachment that's pulling the photo directly from our employees table. If I double-click on it, I can bring up the Attachments window or I can play with the attachments that are attached to it. Now the attachments field allows you to attach multiple files, so right now I just have this one image, but I can add multiple documents here, I can say Add, back into my Exercise Files, Chapter3 folder and I will scroll down and may be I want to attach a catalog to this record.
I will click on the TwoTreesCatalog file, which is a Word document, and say Open. That adds it to the Attachments panel, and I can say OK. Now that I have attached this file, I will press this Pencil button right up here to finalize that into the table and I will double check my tbl_Employees here, I will double-click on it, and if I scroll to the right, we will see that I have now two attachments with the specific employee. The Word document is tied directly to this employee; let me go ahead and close this table. We will go back to our form here. Now I can also attach OLE objects, it's kind of a similar operation, double-click on the box Access tells me that the OLE object is empty, and I can't edit it until I add one, and it tells me how to add one as well.
It says I need to right-click on the field and then click Insert object. We will go ahead and say OK. This time I will right-click, go down to Insert Object and Access brings up a dialog box that we have seen already. I can create a new object or I can select one from a file. Now creating a new object is kind of a strange procedure here, for instance, I will select this bitmap image and I will go ahead and say OK. The OLE object opens it inside of its own window, I can do whatever I would like in here, for instance, I will just scribble something. And I will go ahead and close it. Access saves that into my OLE field. If I wanted to get rid of that now, I could right-click on it and say Delete.
If I right-click and say Insert object, I can add different objects, I have a whole list here, some of these work better than others. If I scroll down here for instance to Microsoft Excel worksheet, I could say OK. Access opens up a Microsoft Excel editing session, the problem with it is, it's right here, it's very tiny it's the size of the box that I drew out on my form, I can't really resize it, it looks like I can, but it doesn't really resize. Also it says I am still here inside of Access, but now I've got windows here that are from Excel, So it's kind of a strange editing environment. If I wanted to get out of here, I will just click off this box anywhere.
I will click over here on the side and that will take me back to my form, and you can see I have got these really tiny, little teeny Excel file kind of embedded right in here. Now I don't know of any way that I could actually make use of that. The attachment field is a much better option, in fact, if you click on it once you will get a little menu up here that allows you to scroll through the different attachments. So for instance, for my first record I have a photo, but I also attach this Word document, so if I press the arrow over to the right, I can get access to the Word document. If I double click on it now, it brings up this attachments window again, and I can say TwoTreesCatalogDoc open, and it will open up that file right in Word.
I can make whatever changes I want, for instance, if I put Olive Oil and change the font size, save my changes here, and then close the window. Now when I come back to Access, I say OK, and Access recognizes that I made changes to that file. It asks me if I want to update the version that's inside the database. I go and said Yes and that will update those changes into this Word document that's stored inside of the database file. The PowerPoint presentation that we added to the OLE field works similarly, but by default its ability to edit it directly here is disabled.
If I go into the View menu and go into Design View, I will take a look at the Properties for this down here. I will go to the Property sheet and right down here, where it says Enabled, No, that means that I can't double click on it to edit it. If I change its property to Yes, and then switch back to Form view, now I can double-click on it to open it up in PowerPoint, I will press Esc to get out of that. So the OLE data type really does kind of duplicate functionality that we have with the attachment data type, and because OLE objects are kind of at this point a legacy feature that's really only kept around for backwards compatibility reasons, I really wouldn't recommend using it if you don't have to.
If you must save files into your database, then use the newer attach file type and will be placed on a form, images display with the rest of your data, just like this photo here, and you can scroll through to get additional documents that are attached in a single field.
There are currently no FAQs about Access 2010: Forms and Reports in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.