Import or link data and objects

 Important   If you link to a file on a local area network, make sure to use a universal naming convention (UNC) (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) path, instead of relying on the drive letter of a mapped network drive in Microsoft Windows Explorer. A drive letter can vary on a computer or may not always be defined, whereas a UNC path is a reliable and consistent way for Microsoft Access to locate the data source that contains the linked table.

From another Access file

ShowImport or link tables from another Microsoft Access file

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. Do one of the following:
  3. In the Import (or Link) dialog box, in the Files of type box, make sure that Microsoft Access (*.mdb; *.adp; *.mda; *.mde; *.ade) is selected.
  4. Click the arrow to the right of the Look in box, select the drive and folder where the Access file that you want to import or link is located, and then double-click the database's icon.
  5. In the Import Objects (or Link Tables) dialog box, click each table that you want to import or link.

If you're importing, and you want to import just the selected tables' definitions (not the data they contain), click Options, and then under Import Tables, click Definition Only.

 Note   If you import a table that is already linked, then Access does not import the data; instead, it links the table to its data source (in effect, copies the link information).

From a text file

ShowImport or link a delimited or fixed-width text file

 Important   Before you import (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.) or link data from 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.) or fixed-width text file (fixed-width text file: A file containing data in which each field has a fixed width.), make sure that the file has the same type of data in each field and the same fields in every row.

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. Do one of the following:
    • To import data, on the File menu, point to Get External Data, and then click Import.
    • To link data, on the File menu, point to Get External Data, and then click Link Tables.
  3. In the Import (or Link) dialog box, in the Files of type box, select Text Files (*.txt; *.csv; *.tab; *asc).
  4. Click the arrow to the right of the Look in box, select the drive and folder where the file is located, and then double-click its icon.
  5. Follow the directions in the Import Text Wizard dialog boxes. Click Advanced to create or use an import/export specification (import/export specification: A specification that stores the information that Access needs to run an import or export operation on a fixed-width or delimited text file.).

To cancel importing, press CTRL+BREAK.

Notes

  • You can append the data to an existing table as long as the first row of your text file matches the table's field names.
  • If all the records in a fixed-width text file are the same length, an embedded row separator (such as a carriage return and a linefeed) in the middle of a record will not cause unexpected results. However, if the records aren't all the same length, Microsoft Access will treat the embedded row separator as the end of the record.

From a spreadsheet

ShowImport or link data from a spreadsheet

Before you proceed, make sure that the data in the spreadsheet is arranged in an appropriate tabular format, and the spreadsheet has the same type of data in each field (column) and the same fields in every row.

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. Do one of the following:
  3. In the Import (or Link) dialog box, in the Files of type box, select Microsoft Excel (*.xls).
  4. Click the arrow to the right of the Look in box, select the drive and folder where the spreadsheet file is located, and then double-click its icon.
  5. Follow the directions in the Import Spreadsheet Wizard dialog boxes.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

If you are importing from a Microsoft Excel version 5.0 or later workbook, then you can import from one worksheet within a workbook. You cannot import from any other multiple-spreadsheet files, such as Microsoft Excel version 4.0 workbooks. To import from these files, you must first save each spreadsheet as an individual file.

Notes

  • You can import or link all the data from a spreadsheet, or just the data from a named range of cells. Although you normally create a new table in Microsoft Access for the data, you can also append the data to an existing table as long as your spreadsheet column headings match the table's field names.
  • Access attempts to assign the appropriate data type to imported fields, but you should check your fields to make sure that they are set to the data type you want. For example, in an Access database, a phone number or postal code field might be imported as a Number field, but should be changed to a Text field in Microsoft Access because it is unlikely that you will perform any calculations on these types of fields. You should also check and set field properties, such as formatting, as necessary.

From a Web page

ShowImport or link (read-only) HTML tables and lists

  1. Open a database, or switch to the Database window for the open database.
  2. To import HTML tables or lists, on the File menu, point to Get External Data, and then click Import.

To link HTML tables or lists, on the File menu, point to Get External Data, and then click Link Tables.

  1. In the Import or Link dialog box, in the Files of type box, click HTML Documents (*.html; *.htm).
  2. Click the arrow to the right of the Look in box.
  3. Select the drive and folder where the HTML file you want to import or link is located, and then double-click the file name.
  4. Follow the instructions for the Import HTML Wizard or the Link HTML Wizard. Click the Advanced button if you want to edit an import/export specification (import/export specification: A specification that stores the information that Access needs to run an import or export operation on a fixed-width or delimited text file.) or specify different file and field formats.
  5. If your HTML file contains more than one table or list, repeat steps 1 through 6 for each table or list you want to import or link.

 Notes 

  • By default, Access converts a hyperlink address to a Hyperlink data type column, but only if all values in a table column or list contain a hyperlink address defined by an <A HREF> HTML tag. You can change the data type when using the Import HTML Wizard or the Link HTML Wizard.
  • A table embedded within a table cell in an HTML file is treated as a separate table when you import or link. A list embedded in a table cell is treated as the contents of a cell, and each item in the list is delimited with the carriage return/line feed characters.
  • For data that spans rows or columns in an HTML table, Microsoft Access 2000 (and later versions) duplicates the data in each cell, whereas Microsoft Excel 2000 (and later versions) stores the data in the first or upper-left cell, and then leaves other cells blank.

From Windows SharePoint Services

ShowImport or link data from Microsoft Windows SharePoint Services lists

You can use the Import from Windows SharePoint Services Wizard to import tables from Windows SharePoint Services lists, or use the Link to Windows SharePoint Services Wizard to create and link tables to Windows SharePoint Services lists.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. Do one of the following:
  3. Follow the directions in the Import from Windows SharePoint Services Wizard or the Link to Windows SharePoint Services Wizard.

From SQL or another ODBC data source

ShowImport or link SQL database tables or data from other ODBC data sources

 Note    You can only import (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.) or link an SQL (Structured Query Language (SQL): A database query and programming language widely used for accessing, querying, updating, and managing data in relational database systems.) or other ODBC data source (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.) if you have installed the appropriate driver.

  1. Open an Access file, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open Access file.
  2. Do one of the following:
    • To import tables, on the File menu, point to Get External Data, and then click Import.
    • To link tables, on the File menu, point to Get External Data, and then click Link Tables.
  3. In the Import (or Link) dialog box, in the Files of type box, select ODBC Databases().

The Select Data Source dialog box lists the defined data sources for any ODBC drivers (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.) that are installed on your computer.

  1. Click either the File Data Source or Machine Data Source tab, and then double-click the ODBC data source that you want to import.

To define a new data source for any installed ODBC driver, click New, and then follow the instructions in the Create New Data Source dialog box and the dialog boxes that follow it before continuing.

  1. If the ODBC data source that you selected requires you to log on, type your logon ID and password (additional information might also be required), and then click OK.

Microsoft Access connects to the ODBC data source and displays the list of tables that you can import or link.

  1. If you're linking a table, do not select the Save password check box if you want to keep the database secure. This will force users to type the logon ID and password every time they open the table in each new session with Microsoft Access. Your SQL database (SQL database: A database that is based on Structured Query Language (SQL).) administrator can also choose to disable this check box, requiring all users to type the logon ID and password each time they connect to the SQL database.
  2. Click each table that you want to import or link, and then click OK. If you're linking a table and it doesn't have an index that uniquely identifies each record, then Microsoft Access displays a list of the fields in the linked table. Click a field or a combination of fields that will uniquely identify each record, and then click OK.

From a mail program

ShowImport or link data from Microsoft Exchange or Microsoft Outlook

You can use the Import from Exchange/Outlook Wizard or the Link to Exchange/Outlook Wizard to import or link to your Microsoft Outlook Contacts folder and then create form letters and mailing labels by merging the data using the Microsoft Word Mail Merge Wizard. You must have Microsoft Outlook, Outlook Express, or Microsoft Exchange installed on your computer to run these wizards.

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. Do one of the following:
  3. In the Import (or Link) dialog box, in the Files of type box, select either Outlook() or Exchange().
  4. Follow the directions in the Import from Exchange/Outlook Wizard or the Link to Exchange/Outlook Wizard.

From another database

ShowImport a Microsoft Works database

You can't import (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.) a Microsoft Works database (.wdb) directly into Microsoft Access. You must first use Microsoft Works to save the file to one of the file formats that Microsoft Access can import.

  1. Open the database by using Microsoft Works.
  2. On the File menu, click Save as.
  3. In the Save as type box, select dBASE IV (*.dbf), and then click OK.
  4. Import the dBASE IV .dbf file that you created in step 3 into Microsoft Access.

ShowImport or link dBASE files

Microsoft has created dBASE ISAM drivers for Microsoft Jet 4.0 that do not require the installation of the Borland Database Engine (BDE) to provide full read/write access to dBASE files. The default Jet 4.0-based dBase ISAM drivers that ship with Microsoft Data Access Component (MDAC) 2.1 and later, allow read-only access to dBASE files unless the BDE is installed. To obtain these ISAM drivers, you must contact Microsoft Technical Support and request that they be sent to you.

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. Do one of the following:
  3. In the Import (or Link) dialog box, in the Files of type box, select one of the dBASE file types.
  4. Click the arrow to the right of the Look in box, select the drive and folder where the .dbf file is located, and then double-click its icon.
    • If importing, Microsoft Access creates a table named after the file that you selected, and then imports the data from the .dbf file.
    • If linking, Microsoft Access displays a dialog box where you can associate dBASE index files. Do the following:
      1. Double-click each dBASE index (.ndx or .mdx) file that you want to use, and then click Close. If there are no indexes, click Cancel to continue.
      2. In the Select Unique Record Identifier dialog box, select an index that uniquely identifies each record. This index must contain no duplicate values, or Microsoft Access might not be able to properly update data in queries with joins.
  5. To import or link another dBASE file, repeat step 4.

 Note   After importing or linking a .dbf or .dbc file, you can set field properties for the table. If you import a .dbf or .dbc file, you might also want to set a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) for the table.

ShowImport or link Paradox tables

Microsoft has created Paradox ISAM drivers for Microsoft Jet 4.0 that do not require the installation of the Borland Database Engine (BDE) to provide full read/write access to Paradox files. The default Jet 4.0-based Paradox ISAM drivers that ship with Microsoft Data Access Component (MDAC) 2.1 and later, allow read-only access to Paradox files unless the BDE is installed. To obtain these ISAM drivers, you must contact Microsoft Technical Support, and request that they be sent to you.

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. Do one of the following:
  3. In the Import (or Link) dialog box, in the Files of type box, select Paradox (*.db*).
  4. Click the arrow to the right of the Look in box, select the drive and folder where the .db file is located, and then double-click its icon.
  5. If the Paradox table you select is encrypted, Microsoft Access prompts you for the password. Type the password for the Paradox table, and then click OK.
  6. If you want to import or link another Paradox table, repeat steps 2 through 5.

From a data access page

ShowImport a data access page from a Microsoft Access file

When you import (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.) a data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), you are only importing the link to the corresponding HTML file. The HTML file remains in the same location, which means that you have two links pointing to the same corresponding HTML file. If you don't want this, save one of the linked pages under a different name (using the Save As command on the File menu), type a different file location for the new corresponding HTML file in the Save As dialog box, and then delete the linked page that you just saved.

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. On the File menu, point to Get External Data, and then click Import.
  3. In the Import dialog box, in the Files of type box, make sure that Microsoft Access (*.mdb; *.adp; *.mda; *.mde; *.ade) is selected.
  4. Click the arrow to the right of the Look in box, select the drive and folder where the Access database (.mdb) or Access project (.adp) is located, and then double-click the Access database's or Access project's icon.
  5. Under the Pages tab in the Import Objects dialog box, click each data access page that you want to import.

From data in XML

ShowImport data and schema from XML

  1. Open a database, or switch to the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) for the open database.
  2. On the File menu, point to Get External Data, and then click Import.
  3. In the Import dialog box, in the Files Of Type box, select XML Documents (*.xml;*.xsd).
  4. Click the arrow to the right of the Look In box, select the drive and folder where the file is located, and then double-click its icon.
  5. The Import XML dialog box displays a list of tables contained within the XML document.

 Note   All of the tables shown in the list will be imported. You cannot select a subset of the XML document for importing.

  1. To start importing the file, click OK . This completes the import procedure.

 Note   For most records that cause an error, Microsoft Access creates and adds a row to a table called Import Errors. To view the list of errors that were encountered, open the Import Errors table from the Database window.

  1. To set options for importing, select one of the following under Import Options:
    • To import just the structure of the table, click Structure Only.
    • To import the structure of the table and the data, click Structure and Data.
    • To append the data to an existing table, click Append Data To Existing Table(s).

Click OK to start importing, or proceed to the next step.

 Note   For the first two options above, if a table already exists with the same name, a new table is created with a number appended to the name. Otherwise, a new table is created.

  1. ShowTo select a transform to apply to the data being imported, click Transform.

In the Import Transforms dialog box, do one of the following.

  • In the displayed list of transforms, select a transform and click OK.

 Note    Selecting (none) and clicking OK is the same as not applying any transform.

  • If the displayed list does not contain the necessary transform, add the transform file by clicking Add. This opens the Add New Transforms dialog box which allows you to add a file with the extension xsl or xslt. After adding the transform, select it and click OK.

 Note   If the XML data references any transforms, the Import Transforms dialog box also lists these transforms. These transforms and the item (none) cannot be renamed or removed.

 
 
Applies to:
Access 2003