Import or link to data in an SQL Server database

You can link to or import data from an SQL Database, which is a high-performing managed database used for mission-critical applications. For more information, see SQL Server 2016.

Overview of connecting Access to SQL Server

Before you begin

Want things to go smoother? Then make the following preparations before you link or import:

Stage 1: Get started

  1. Select External Data >New Data Source >From Database >From SQL Server.
  2. In the Get External Data – ODBC Database dialog box, do one of the following:
  3. Select OK.

Stage 2: Create or reuse a DSN file

You can create a DSN file or reuse an existing one. Use a DSN file when you want to rely on the same connection information for different link and import operations or to share with a different application that also uses DSN files. You can create a DSN file directly by using the Data Connection Manager. For more information, see Administer ODBC data sources.

Although you can still use prior versions of the SQL ODBC driver, we recommend using version 13.1, which has many improvements, and supports new SQL Server 2016 features. For more information, see Microsoft ODBC Driver for SQL Server on Windows.

Select Data Source dialog box

    Do one of the following: If the DSN file you want to use already exists, select it from the list.

Create New Data Source dialog box

Depending on which authentication method you entered in the connection information, you may need to enter a password again. To create a new DSN file:

    Select New.

Stage 3: Use the Create a New Data Source to SQL Server wizard

In the Create a New Data Source to SQL Server wizard, do the following:

  1. On page one, enter identification information:
  2. On page two, select one of the following authentication methods:
  3. On pages three and four, select various options to customize your connection. For more information about these options, see Microsoft ODBC Driver for SQL Server.
  4. A screen appears to confirm your settings. Select Test Data Source to confirm your connection.
  5. You may need to login to the database. In the SQL Server Login dialog box, enter the login ID and password. To change additional settings, select Options.

Stage 4: Select tables and views to link to or import

List of tables to link or import

  1. In the Link Tables or Import Objects dialog box, under Tables, select each table or view that you want to link or import, and then click OK.

Stage 5: Create specifications and tasks (Import only)

Results

When a link or import operation completes, the tables appear in the Navigation Pane with the same name as the SQL Server table or view combined with the owner name. For example, if the SQL name is dbo.Product, the Access name is dbo_Product. If that name is already in use, Access appends "1" to the new table name — for example, dbo_Product1. If dbo_Product1 is also already in use, Access will create dbo_Product2, and so on. But you can rename the tables to something more meaningful.

In an import operation, Access never overwrites a table in the database. Although you cannot directly append SQL Server data to an existing table, you can create an append query to append data after you have imported data from similar tables.

In a link operation, if columns are read-only in an SQL Server table, they are also read-only in Access.

Tip To see the connection string, hover over the table in the Access navigation pane.

Update the linked table design

You can’t add, delete, or modify columns or change data types in a linked table. If you want to make design changes, do it in the SQL Server database. To see the design changes in Access, update the linked tables:

  1. Select External Data >Linked Table Manager.
  2. Select each linked table you want to update, select OK, and then select Close.

Compare data types

Access data types are differently named from SQL Server data types. For example, a SQL Server column of the bit data type is imported or linked into Access with the Yes/No data type. For more information, see Comparing Access and SQL Server data types.

What else should I know?