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

Note   You can link a table only in a Microsoft Access database, not a Microsoft Access project.

Important   You cannot import or link an SQL or other ODBC data source unless you have installed the appropriate driver.

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

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

   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. More information about data sources.

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

5. 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 databaseso 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. For information on how to disable the Save The Login ID And Password check box, click .
   
   
6. 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.

Notes

• After importing or linking an SQL database table, you can set field properties for the table. If you import a table, you might want to set a primary key for the table. For more information on setting field properties for the table, click .
  
  
• If you encounter an error while importing, linking, or using an SQL database table, there might be a problem with your account on the SQL database server or with the database itself. If you can't access an SQL database table, contact the SQL database administrator.
  
  
• To edit a linked SQL database table, usually the table must contain a unique index on the server. If you want to edit a table that doesn't have a unique index, or if you want to edit an SQL view, then you can create an index within Microsoft Access that the SQL database isn't aware of. You do that by creating a data-definition query, using the Create Index statement. Be aware, however, that the index must be created on a field or combination of fields in which each value is unique. If the field contains any duplicate values, all updates to the table will fail. To delete the index, use another data-definition query. For more information on creating such a data-definition query, click .
  
  
• If the structure of an SQL database table changes after you link it, use the Linked Table Manager to refresh the link. For more information, click .
  
  
• If you are importing from a Microsoft SQL Server database into an Access Project, consider using Data Transformation Services (DTS), which can be a faster and more flexible way to import and transform data. For more information, see the Microsoft SQL Server documentation.