Show All

Hide All

Access provides several ways to use files that were created in other Microsoft Office 2003 programs — Word documents, Excel workbooks, PowerPoint presentations, Visio diagrams, and more. This article explains how to store those files and and display them on your forms and reports.

Table of contents

• Using files from other Office programs
  The concepts you must understand in order to use files correctly, such as whether to link or embed them, and whether to use bound or unbound files. It also briefly explains OLE (Object Linking and Embedding), the technology that some Access components use to store and display files.
• Storing files in a database
  How to add fields to your database tables and either link to or embed file data in those fields.
• Adding unbound files to forms and reports
  How to add files that remain fixed as you move through the records in a database or the pages in a report.
• Adding bound files to forms and reports
  How to add files that change as you move through the records in a database or the pages in a report.
• Updating and editing links and files
  How to update links to your files, display updated files automatically and manually, and edit files in Access.
• Solving common display problems
  What to do when you see file names instead of the files themselves, and "Package" in your table fields instead of a specific Office file type.

Using files from other Office programs

The process of using files from other Office programs in an Access database typically follows several broad steps. First, you decide how and where you want to store the files that you want to use. You can leave them on your network or hard disk or make them part of your database file.

Next, you add a control to your forms and reports and display your files by using that control. If you want the files to change as you move through your records, you use a bound object frame. Otherwise, you use an unbound object frame. As part of that process, you also choose to embed your files or link to them. Embedding makes the file a part of your database. In contrast, linking creates a reference to the original file. When you update the original file, you see any changes when you view that file in your database.

 Note   You cannot update or delete data in a table that is linked to a Microsoft Office Excel 2003 worksheet.

If you're new to using files from other programs with an Access database, you need to make informed choices about how you store and display those files. If you make the wrong storage choices, you can rapidly inflate the size of your database and cause it to run slowly. If you link to your files and move either your database or the files, your links break.

About storing files

You have several options for storing the files that you want to use with an Access database:

• Embed the file in an OLE Object field in a database table.

  What's an OLE Object field?

  OLE is the technology used to share files among the various Office programs. For example, when you insert an Excel worksheet into a Word document, or insert a PowerPoint presentation into a Visio drawing, you're using OLE. You use an OLE Object field when you need to store files (or links to them) directly in your database.

  ---

  This method is the easiest to implement because you use the screens and tools that Access provides. Also, the files become part of your database and travel with it, so you never need to to update the links to them.

  However, storing files this way can rapidly inflate the size of your database, because OLE creates an additional bitmap image for each original file. Remember that your Access databases have a 2 gigabyte size limit, and when they approach that limit they start to run slowly.

  Why does OLE create bitmap files?

  By design, OLE uses components called OLE servers to display files that were created in other Office programs. An OLE server is a component provided by the program that was used to create the original file. When you use a file from another Office program with your database, the OLE server on that originating program renders the file, not Access.

  For example, suppose you display a PowerPoint presentation in a bound object frame in Access. The object frame relies on PowerPoint to render the slides. Put another way, Access does not render the slides. PowerPoint does the rendering work.

  At this point, you need to keep several facts in mind.

  • To render a file, the program used to create that file must reside on the same computer as your Access database. If that program fails, or you move the database to a computer that does not contain the originating program, OLE cannot render the file.
  • OLE works around those possible problems by creating a .bmp file or Device Independent Bitmap (.dib) file that contains display data for each of your original files. If you move the database, or the parent program fails, OLE renders the bitmap image, and you at least see a version of the original file.
  • Those BMP and DIB files are often larger than your original file, and that is why your database can grow so rapidly.
  • Outside of Microsoft Office, you can't predict whether a given program will provide the OLE server that is needed to render a given type of file.

  ---

• Store your files on a hard disk or network and store links to them in an OLE Object field. This method is similar to embedding files in an OLE Object field, but it consumes less storage space. Also, if you update the original file, you see any changes when you view the file in your database.

  However, if you move your database or your files, you have to update your links. Also, your links break if your files become corrupted. In addition, the computer that contains your database must also contain the program that was used create the original file, or OLE cannot render the file.

• Store your files on a hard disk or network and either embed them or link to them directly from the control.

  This method bypasses table fields altogether and thus saves storage space. It also has the same advantages and disadvantages as storing links in a database table. Keep in mind that if you choose to embed the file, you make that file part of your database and potentially cause that database to grow.

About displaying files

As part of using files from other Office programs in Access, you need to learn some terminology and know the answers to some key questions:

• Do you want to use bound or unbound files? You use bound files when you want to display a different file as you move among the records in a database or the pages in a report. For example, if you have a Sales report, and you want to display workbooks from a set of regional offices, you use bound files.

  In contrast, unbound files don't change as you move among records and pages. If you want to display a copyright notice or a legal disclaimer, you can add an unbound file.

• Do you want to link to the file or embed it? Linking saves space, because Access stores just the link information, while an embedded file becomes part of the database itself. Embedded files can rapidly increase the size of your database file and possibly cause it to run slowly, but embedding also ensures that a file is always available.

The following sections explain those options and the best ways to use them.

Understand bound and unbound files

Understand embedding and linking files

 Back to top

Storing files in a database

The following sections explain the most common ways to store files in an Access database. The storage methods work for forms and reports. The steps use the sample Northwind Traders database, but you can adapt the steps to suit your own databases.

The process of embedding or linking to files in a table follows these broad steps:

• If you haven't already, add an OLE Object field to an existing table, or create a new table and add an OLE Object field.
• Insert your files into the field. During that process, you choose whether you embed or link to the file. You can also start the parent program for a given file type and create a new file without leaving Access. When you take that action, Access embeds the file in your table, and the file becomes part of the database.

The following sections explain how to perform those tasks.

Add an OLE Object field to an existing table

Create a new table containing an OLE Object field

Add existing files to the OLE Object field (embedded or linked)

Start a parent program and create a new file in Access

 Back to top

Adding unbound files to forms and reports

You use the unbound object frame to display files that don't change as you move among database records or the pages in a report. These steps explain how to add the unbound object frame to forms and reports and how to add existing files to the frame or create new files in the frame. The steps use the sample Northwind Traders database, but you can apply them to your own database.

1. Start Access, open the Northwind Traders sample database, and then display the Database window.

   How?

   1. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
   2. If a Security Warning dialog box appears, click Open.
   3. Click OK to close the Northwind Traders startup screen.
   4. On the Main Switchboard screen, click Display Database Window.
2. At this point, you can create a new form or report and add a file to it, or you can add files to existing forms or reports. You can also create new files as you add the object frame to your form. The steps in the following sections explain how to take those actions.

   Create a new form and add unbound files

   When you add an unbound object frame to a form, you can embed or link to existing files, or you can create a new, embedded file in the frame. The following sections explain how to take both actions.

   To add an existing file to a new form  

   1. In the Database window, under Objects, click Forms, and then double-click Create form in Design view. Access opens a new, blank form and displays the toolbox. If the toolbox does not appear, on the View menu, click Toolbox.
   2. In the toolbox, click Unbound Object Frame .
   3. Point to the location on the form where you want to display your file, and click once to place the control on the form. The location that you select marks the upper-left corner of the object frame. The Microsoft Office Access dialog box appears.
   4. Click Create from file, and then click Browse. The Browse dialog box appears.
   5. Use the Browse dialog box to locate the file that you want to display, and then click OK. If you want to save storage space by linking to the file instead of embedding it in your database, click Link before you click OK.
   6. Optionally, you can set properties for the object frame. For example, you can allow or prevent users from editing a file by enabling or disabling the Locked property. To set and change properties, right-click the object frame and click Properties. For a description of each property and help on how to use it, select a property and press F1.
   7. Add any remaining controls to the form, such as lists, text boxes, and subforms, and save your changes . You can type a name for the form or accept the name that Access provides.

   To add a new file to a new form  

   1. Repeat steps 1 through 3 from the previous section to create a new form and display the Microsoft Office Access dialog box.
   2. Click Create New, select a file type from the Object Type list, and then click OK.

      Depending on the type of file that you choose to create, a program control appears, and you use that control to create the file. For example, if you choose to create an Excel worksheet, a cell grid appears on your form, and the Excel toolbars temporarily replace the Access toolbars. You see the same behavior when you create a PowerPoint presentation, a Word document, and so on. Use the control and toolbars to enter and format your data.

      If you need to resize the program control, click the surrounding form and drag the handles on the program control as needed. To return to editing data in the program control, double-click the control.

   3. When you finish entering data, on the File menu, click Save As. The Save As dialog box appears.
   4. In the upper text field, type a name. This is a name for the new form, not the file in the object frame. You cannot assign a name to the new file because the file becomes an embedded part of the larger database (.mdb) file.
   5. From the As list, select Form, and then click OK.
   6. Add any remaining controls to the form, such as lists, text boxes, and subforms, and save your changes . To view the form, close and reopen it.

   ---

   Create a new report and add unbound files

   When you add an unbound object frame to a report, you can embed or link to existing files, or you can create a new, embedded file in the frame. The following sections explain how to take both actions.

   To add an existing file to a new report  

   1. In the Database window, under Objects, click Reports, and then double-click Create report in Design view. Access opens a new, blank report.
   2. On the View menu, click Toolbox.
   3. In the toolbox, click Unbound Object Frame .
   4. Point to the location on the report where you want to display your file, and click once to place the control on the report. You can place the frame in the Page Header, Detail, or Page Footer section of the report. The location that you select marks the upper-left corner of the object frame. The Microsoft Office Access dialog box appears.
   5. Click Create from file, and then click Browse. The Browse dialog box appears.
   6. Use the Browse dialog box to locate the file that you want to display, and then click OK. If you want to save storage space by linking to the file instead of embedding it in your database, click Link before you click OK.
   7. Optionally, you can set properties for the object frame. For example, you can allow or prevent users from editing a file by enabling or disabling the Locked property. To set and change properties, right-click the object frame and click Properties. For a description of each property and help on how to use it, select a property and press F1.
   8. Add any remaining fields and save your changes. You can type a name for the report or accept the name that Access provides.
   9. To view the report, either close and reopen it, or click Preview .

   To add a new file to a new report  

   1. Repeat steps 1 through 3 from the previous section to create a new report and display the Microsoft Office Access dialog box.
   2. Click Create New, select a file type from the Object Type list, and then click OK.

      Depending the type of file that you choose to create, a program control appears, and you use that control to create the file. For example, if you choose to create an Excel worksheet, a cell grid appears on your form, and the Excel toolbars temporarily replace the Access toolbars. You see the same behavior when you create a PowerPoint presentation, a Word document, and so on. Use the control and toolbars to enter and format your data.

      If you need to resize the program control, click the surrounding report and drag the handles on the program control as needed. To return to editing data in the program control, double-click the control.

   3. When you finish entering data, on the File menu, click Save As. The Save As dialog box appears.
   4. In the upper text field, type a name. This is a name for the new report, not the file in the object frame. You cannot assign a name to the file in the frame because you're embedding the file, which makes it part of the larger database.
   5. From the As list, select Form, and then click OK.
   6. Add any remaining fields and save your changes. You can type a name for the report or accept the name that Access provides.
   7. To view the report, either close and reopen it, or click Preview .

   ---

   Add unbound files to an existing form

   When you add an unbound object frame to a form, you can embed or link to existing files, or you can create a new, embedded file in the frame. The following sections explain how to take both actions.

   To add an existing file to an existing form  

   1. In the Database window, under Objects, click Forms, select the form that you want to change, and then click Design. Access opens the form in Design view and displays the toolbox. If the toolbox does not appear, on the View menu, click Toolbox.
   2. In the toolbox, click Unbound Object Frame.
   3. Point to the location on the form where you want to display your file, and click once to place the control on the form. The location that you select marks the upper-left corner of the object frame. The Microsoft Office Access dialog box appears.
   4. Click Create from file, and then click Browse. The Browse dialog box appears.
   5. Use the Browse dialog box to locate the file that you want to display, and then click OK. If you want to save storage space by linking to the file instead of embedding it in your database, click Link before you click OK.
   6. Optionally, you can set properties for the object frame. For example, you can allow or prevent users from editing a file by enabling or disabling the Locked property. To set and change properties, right-click the object frame and click Properties. For a description of each property and help on how to use it, select a property and press F1.
   7. Save your changes .

   To add a new file to an existing form  

   1. Repeat steps 1 through 3 from the previous section to open a form in Design view and display the Microsoft Office Access dialog box.
   2. Click Create New, select a file type from the Object Type list, and then click OK.

      Depending the type of file that you choose to create, a program control appears, and you use that control to create the file. For example, if you choose to create an Excel worksheet, a cell grid appears on your form, and the Excel toolbars temporarily replace the Access toolbars. You see the same behavior when you create a PowerPoint presentation, a Word document, and so on. Use the control and toolbars to enter and format your data.

      If you need to resize the program control, click the surrounding form and drag the handles on the program control as needed. To return to editing data in the program control, double-click the control.

   3. When you finish entering data, click Save to save your changes.

       Note   You cannot assign a name to the new file because the file becomes an embedded part of the larger database (.mdb) file.

   ---

   Add unbound files to existing reports

   When you add an unbound object frame to a report, you can embed or link to existing files, or you can create a new, embedded file in the frame. The following sections explain how to take both actions.

   To add an existing file to an existing report  

   1. In the Database window, under Objects, click Reports, select the report that you want to change, and click Design. Access opens the report in Design view and displays the toolbox. If the toolbox does not appear, on the View menu, click Toolbox.
   2. As needed, move the existing fields in your report to make room for the control that will render your file.
   3. In the toolbox, click Unbound Object Frame .
   4. Point to the location on the report where you want to display your file, and click once to place the object frame on the report. The location that you select marks the upper-left corner of the object frame. The Microsoft Office Access dialog box appears.
   5. Click Create from file, and then click Browse. The Browse dialog box appears.
   6. Use the Browse dialog box to locate the file that you want to display, and then click OK. If you want to save storage space by linking to the file instead of embedding it in your database, click Link before you click OK.
   7. Optionally, you can set properties for the object frame. For example, you can allow or prevent users from editing a file by enabling or disabling the Locked property. To set and change properties, right-click the object frame and click Properties. For a description of each property and help on how to use it, select a property and press F1.
   8. Save your changes .
   9. To view the report, close and reopen it, or click Preview .

   To add a new file to an existing report  

   1. Repeat steps 1 through 4 from the previous section to open a report and display the Microsoft Office Access dialog box.
   2. Click Create New, select a file type from the Object Type list, and click OK.

      Depending the type of file that you choose to create, a program control appears, and you use that control to create the file. For example, if you choose to create an Excel worksheet, a cell grid appears on your form, and the Excel toolbars temporarily replace the Access toolbars. You see the same behavior when you create a PowerPoint presentation, a Word document, and so on. Use the control and toolbars to enter and format your data.

      If you need to resize the program control, click the surrounding report and drag the handles on the program control as needed. To return to editing data in the program control, double-click the control.

   3. Save your changes .
   4. To view the report, close and reopen it, or click Preview .

 Back to top

Adding bound files to forms and reports

The process of adding bound files to a form or report typically follows several steps. First, either you embed files in an OLE Object field, or you insert links to your files in that field. Next, you bind the form or report to the table that contains the OLE Object field and use the bound object frame to render the files.

The following sections explain how to add bound files manually and how to add them by using the various form wizard or report wizards.

 Note   To complete the steps in these sections, you must have a database table with an OLE Object field, and that field must contain one or more embedded or linked files. For information on configuring a table for use with bound files, see Storing files in a database.

Use the Form Wizard to create a new form containing bound files

Use the Report Wizard to create a new report containing bound files

Manually create a form containing bound files

Manually create a report containing bound files

Add bound files to an existing form or report

 Back to top

Updating and editing links and files

If you link to files and then change the location of your database or the location of your files, you must update those links. The following sections explain how to update links to files in bound and unbound object frames.

In addition, these sections explain how to edit files in Access.

Edit files in Access

If the computer that runs your database has the Office programs installed that were used to create your files, you can edit files directly from your form.

 Note   If you're editing embedded files, these steps change only one instance of the file. These steps do not change the original file. Also, if you embed a file more than once, you must change each instance of the embedded file.

Make files read-only

To prevent users from editing your files, you can:

• Lock a bound or unbound object frame. This prevents users from starting the program that was used to create the file.
• Convert an unbound object frame to an image control, which also prevents users from starting the program that was used to create a file.

Update changed files

When you link to a file and later change that file, under certain conditions you can specify whether the changed file appears automatically or only when you request it. The following table lists the default behaviors for the object frames and describes how to change those behaviors.

Delete files

If you can add a file to a form or control, you can remove that file. You follow a different procedure for deleting unbound and bound files.

To delete unbound files  

1. Open the form that contains the unbound object frame in Design view by clicking Design .
2. Click the frame and press DELETE.

To delete bound files  

1. Open the form or report that contains the bound object frame in Form view (or open the underlying table as a datasheet).
2. Find the record that you want to change, click the file (or the frame, if it is visible), and press DELETE.

3. Access removes the file from the frame and the file data from underlying record source, but the object frame remains on the form. To delete the frame, open the form or report in Design view, click the control, and press DELETE.

   If you delete the object frame before you delete a file, that file still resides in the underlying table. If you want to remove the file data, open the table in Datasheet view and either delete the individual file or delete the OLE Object field that contains the file data.

Solving common display problems

Access may fail to render files from other programs for several reasons. The following table lists the most common reasons and describes how to fix the problem.

 Back to top