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) 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

Import or link tables from another Microsoft Access file

Open a database, or switch to the Database window for the open database.
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.
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.
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.
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

Import or link a delimited or fixed-width text file

Important Before you import or link data from a delimited text file or fixed-width text file, make sure that the file has the same type of data in each field and the same fields in every row.

Open a database, or switch to the Database window for the open database.
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.
In the Import (or Link) dialog box, in the Files of type box, select Text Files (*.txt; *.csv; *.tab; *asc).
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.
Follow the directions in the Import Text Wizard dialog boxes. Click Advanced to create or use an import/export specification.
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

Import 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.

Open a database, or switch to the Database window for the open database.
Do one of the following:
- To import a spreadsheet, on the File menu, point to Get External Data, and then click Import.
- To link a spreadsheet, on the File menu, point to Get External Data, and then click Link Tables.
In the Import (or Link) dialog box, in the Files of type box, select Microsoft Excel (*.xls).
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.
Follow the directions in the Import Spreadsheet Wizard dialog boxes. 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 SQL or another ODBC data source

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

Note You can only import or link an SQL or other ODBC data source if you have installed the appropriate driver.

Open an Access file, or switch to the Database window for the open Access file.
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.
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 that are installed on your computer.
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.
If the ODBC data source that you selected requires you to log on, enter 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.
If you're linking a table, select the Save The Login ID And Password check box to save the information for the table in the current Access database so that users won't have to enter it. If you leave the check box cleared, all users must enter the logon ID and password every time they open the table in each new session with Microsoft Access. Your SQL database administrator can also choose to disable this check box, requiring all users to enter the logon ID and password each time they connect to the SQL database.
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

Import or link data from Microsoft Outlook or Microsoft Exchange

You can use the Exchange/Outlook Wizard to 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 the Exchange/Outlook Wizard.

Open a database, or switch to the Database window for the open database.
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.
In the Import (or Link) dialog box, in the Files of type box, select either Outlook() or Exchange().
Follow the directions in the Exchange/Outlook Wizard dialog boxes.

From another database

Import a Microsoft Works database

You can't import 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.

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

Import 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.

Open a database, or switch to the Database window for the open database.
Do one of the following:
- To import files, on the File menu, point to Get External Data, and then click Import.
- To link files, on the File menu, point to Get External Data, and then click Link Tables.
In the Import (or Link) dialog box, in the Files of type box, select one of the dBASE file types.
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.
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 for the table.

Import 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.

Open a database, or switch to the Database window for the open database.
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.
In the Import (or Link) dialog box, in the Files of type box, select Paradox (*.db*).
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.
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.
If you want to import or link another Paradox table, repeat steps 2 through 5.

A data access page

Import a data access page from a Microsoft Access file

When you import a data access page, 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), enter 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.

Open a database, or switch to the Database window for the open database.
On the File menu, point to Get External Data, and then click Import.
In the Import dialog box, in the Files of type box, make sure that Microsoft Access (*.mdb; *.adp; *.mda; *.mde; *.ade) is selected.
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.
Under the Pages tab in the Import Objects dialog box, click each data access page that you want to import.

Import data and schema from XML

Open a database, or switch to the Database window for the open database.
On the File menu, point to Get External Data, and then click Import.
In the Import dialog box, in the Files Of Type box, select XML Documents (*.xml).
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.
A list of tables contained within the XML document is displayed.
Note All of the tables shown in the list will be imported. You cannot select a subset of the XML document for importing.
In the Import XML dialog box, do one of the following:
- To start importing the file, click OK.
  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.
- To cancel importing, click CANCEL.
- To set options for importing, do one of the following:
  To import just the structure of the table, click Structure Only under Import Options.
  
  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).