Learn how to import or link to data

Sometimes, the data you want to use in your Microsoft Office Access 2007 database already exists in some other format or in a separate Office Access 2007 database. For example, you might have information stored in a Microsoft Office Excel 2007 workbook or in a Windows SharePoint Services 3.0 list. To use that data in Office Access 2007, you can either import it or link to it. When you import data, a copy of the data is placed in a table in your database. When you link to data, the data remains in the original format and is accessed directly by Office Access 2007 as if it were a native table, with some exceptions noted in the section Link to data in another format.

What do you want to do?


Import data

When you import data, a copy of that data is placed in a new or existing table without altering the source file. You can import data from an Office Excel 2007 worksheet, from a table in another Access database, from a Windows SharePoint Services 3.0 list, or from a number of other sources. To import data, you usually select from the commands available in the Get External Data group on the External Data tab.

If you are working with a template that you opened from Access or downloaded from the Microsoft Office Online site, you might see a button in the template that you can use to import data. For example, some templates include a Contact List form with an Add from Outlook button that you can use to import contacts directly from Office Outlook 2007.

Import data into an existing table

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select and open the database.
  2. On the External Data tab, in the Import group, click the command for the type of file that you are importing. For example, if you are importing data from an Excel worksheet, click Excel. If you don't see the program type that you need, click More.

The Get External Data dialog box appears.

 Note   If you can't find the correct format type under Import, you might need to start the program in which you originally created the data, and then use that program to save the data in a common file format (such as a delimited text file (delimited text file: A file containing data where individual field values are separated by a character, such as a comma or a tab.)) before you can import that data into Access.

  1. In the Get External Data dialog box, click Browse to find the source data file, or type the full path of the source data file in the File name box.
  2. Click the option that you want under Specify how and where you want to store the data in the current database. You can create a new table that uses the imported data, append the data to an existing table, or create a linked table that maintains a link to the data source.

For most file formats, a wizard starts. However, if you are importing from an Access database, the Import Object dialog box appears — make your choices in the dialog box, and then continue to step 8. Otherwise, continue to step 6.

  1. Follow the instructions on the next few pages of the wizard. The exact process will depend on the import options that you choose.
  2. On the last page of the wizard, click Finish.
  3. On the last page of the wizard, you might be prompted to save the details of these import steps. If you think that you will be performing this same import operation again in the future, select the Save Import Steps check box, and then enter the details and click Save Import. You can then easily repeat the operation at a later time by clicking Saved Imports in the Import group on the External Data tab. If you don't want to save the details of the operation, click Close.

For more detailed information about how to import data into Access, see the articles Import or link to data in an Excel workbook, Import from or link to a SharePoint list, Import or link to data in another Access database,or click the links to the articles in the See also section.

Top of Page Top of Page

Import address book data from Outlook

Some templates include a Contact List form with an Add from Outlook button. You can use the Add from Outlook button to import contacts directly from the address book in Outlook.

  1. On the form, click the Add From Outlook button.
  2. If the Choose Profile dialog box appears, select a Profile Name, and then click OK.

 Note   You might see the following message: A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it might be a virus and you should choose "No". If you see this message, and you are going to choose more than one contact, you might want to select the Allow access for check box and then choose a short period of time, such as 5 minutes. Doing this allows access to your stored e-mail addresses in Outlook only for the period of time you specify. When you are finished, click Yes.

  1. In the Select Names to Add dialog box, double click the names that you want to add, and then click OK.

The names are added to the form.

Top of Page Top of Page

Link to data in another format or database

When you link to data in another format, the data continues to be stored in the original format and appears as a linked table in your database. For most file formats, this means you can view and modify the data by using either Office Access 2007 or the original program that created the data, because they are both working with the same physical data. However, you cannot modify linked data that is stored in Excel by using Office Access 2007 — you can only view the data. To modify the data, you must use Excel.

Create a linked table

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select and open the database.
  2. On the External Data tab, in the Import group, click the command for the type of file to which you want to link. For example, if you want to link to data in an Excel worksheet, click Excel. If you don't see the program type that you need, click More.

The Get External Data dialog box appears.

 Note   If you can't find the correct format type under Import, you might need to start the program in which you originally created the data, and then use that program to save the data in a common file format (such as a delimited text file (delimited text file: A file containing data where individual field values are separated by a character, such as a comma or a tab.)) before you can link to that data in Access.

  1. In the Get External Data dialog box, click Browse to find the source data file, or type the full path of the source data file in the File name box.

For most file formats, a wizard starts. However, if you are linking to data in an Access database, the Link Tables dialog box appears — make your choices in the dialog box, and then continue to step 8. Otherwise, continue to step 5.

  1. Under Specify how and where you want to store the data in the current database, click Link to the data source by creating a linked table. Note that Access can link only to certain file formats, so this option might not be available for your file format.
  2. Follow the instructions on the next few pages of the wizard. The exact process will depend on the link option that you choose.
  3. On the last page of the wizard, click Finish.

Top of Page Top of Page

 
 
Applies to:
Access 2007