Link to SQL Server data

You can work with data stored in SQL Server either by linking to it or importing the data into an Access database. Linking is a better option if you share the data with others because the data is stored in a centralized location and you can view the most current data, add or edit the data, and run queries or reports in Access.

Note:  This article doesn't apply to Access apps – the new kind of database you design with Access and publish online. See Create an Access app for more information.

Step 1: Preparation for linking

1. Locate the SQL Server database that you want to link to. If necessary, contact the database administrator for connection information.

2. Identify the tables and views you'll be linking to in the SQL database. You can link to multiple objects at a time.

Review the source data for the following considerations:

• Access supports up to 255 fields (columns) in a table, so the linked table will include only the first 255 fields of the object you link to.

• The columns that are read-only in a SQL Server table will also be read-only in Access.

1. To create the linked table in a new database: Click File > New >Blank desktop database. To create the linked tables in an existing Access database, make sure that you have the necessary permissions to add data to the database.

   Note:  A linked table created in an existing Access database, gets the same name as in the source object. So, if you already have another table with the same name, the new linked table name has a "1" added to it — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)

Step 2: Linking to data

When linking to a table or view in a SQL Server database, Access creates a new table (known as a linked table) that reflects the structure and contents of the source table. You can change the data either in SQL Server, or in Datasheet view or Form view from Access and the changes are reflected in both SQL and Access. Any structural changes to linked tables like removing or changing columns, have to be made from the SQL Server and not Access.

1. Open the destination Access database.

2. On the External Data tab, click ODBC Database.

3. Click Link to the data source by creating a linked table > OK and follow the steps in the wizard.In the Select Data Source box, if the .dsn file you want to use already exists, click the file in the list.

   To create a new .dsn file:

   In the Select Data Source box, click New> SQL Server > Next.

   1. Type a name for the .dsn file, or click Browse.

      Note:  You need write permissions to the folder to save the .dsn file.

   2. Click Next to review the summary information, and click Finish.

      Follow the steps in the Create a New Data Source to SQL Server Wizard.

4. Click OK and under Tables, click each table or view that you want to link to, and then click OK.

If you see the Select Unique Record Identifier, it means that Access was unable to determine which field or fields uniquely identify each row of the source data. Just select the field or combination of fields that is unique for each row, and if you are not sure, check with the SQL Server database administrator.

When the linking operation is complete, you can see the new linked table or tables in the Navigation Pane.

Apply the latest SQL Server object structure

When you open either a linked table or the source object, you see the latest data. However, if any structural changes are made to a SQL Server object, you'll need to update the linked table(s) to see those changes.

1. Right-click the table in the Navigation Pane, and then click Linked Table Manager on the shortcut menu.

2. Select the check box next to each linked table that you want to update, or click Select All to select all of the linked tables.

3. Click OK > Close.

Note:  Since Access data types differ from SQL Server data types, Access links to the most appropriate data type for each column. You can only review not change the assigned data types in Access.

For more information, see ways to share an Access desktop database.

Top of Page