When you link to a table or view in a SQL Server database, Access creates a new table (often referred to as a linked table) that reflects the structure and contents of the source object. You can make changes to data either in SQL Server, or in Datasheet view or Form view from within Access. The changes that you make to data in one place are reflected in the other. However, if you want to make structural changes, such as removing or changing a column, you must do so from within the SQL Server database, or from within an Access project that is connected to that database. You cannot add, delete, or modify the fields in a linked table while working in Access.

If the SQL Server database contains a large amount of data, you should choose linking over importing, because the maximum size of an Access database is 2 gigabytes (minus the space needed for system objects). Importing many large tables or views can cause you to exceed that limit, whereas linking to the data does not add much size to your Access database.

Common scenarios for linking to SQL Server data

Typically, you link to a SQL Server table or view from an Access database for these reasons:

1.   Your department or workgroup uses Access for reporting and querying and uses SQL Server for data storage. Individual teams can create SQL Server tables and views for centralized storage, but often this data must be brought into desktop programs for aggregation and reporting. Linking is the appropriate choice, because it allows users of both the SQL Server database and the Access database to add and update data, and to always view and work with the latest data.

2.   You are an Access user who recently started using SQL Server. You migrated several of your databases to SQL Server, and most of the tables in these databases are linked tables. From now on, instead of creating Access tables, you will create tables and views in SQL Server and then link to them from your Access databases.

3.   You want to continue storing your data in SQL Server, but you also want to work with the most recent data inside Access in order to run queries and print reports that you designed in Access.

Prepare to link to a SQL Server table

1.       Locate the SQL Server database that has the data to which you want to link. Contact the database administrator for connection information.

2.       Identify the tables and views to which you want to link. You can link to multiple objects in a single linking operation.

3.       Review the source data and keep the following considerations in mind:

   I.     Access does not support more than 255 fields in a table, so the linked table will include only the first 255 fields of the object you link to.

   II.    The columns that are read-only in a SQL Server object will continue to be read-only in Access.

    III.    You will not be able to add, delete, or modify columns in the linked table in Access.

4.       Identify the Access database in which you want to create the linked tables. Ensure that you have the necessary permissions to add data to the database. If you don't want to store the data in any of your existing databases, create a new, blank database by using the following command: Click the Microsoft Office Button, and then click New.

5.       Review the tables in the Access database. When you link to a SQL Server table or view, Access creates a linked table with the same name as the source object. If that name is already in use, Access will append "1" to the new linked table name — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)

Link to the data

1.       Open the destination database.

2.       On the External Data tab, in the Import group, click More.

3.       Click ODBC Database.

4.       Click Link to the data source by creating a linked table, and then click OK.

5.       In the Select Data Source dialog box, click the .dsn file that you want to use, or click New to create a new data source name (DSN).

6.       In the Select Data Source dialog box, if the .dsn file you want to use already exists, click the file in the list.

Note:-  The steps in this procedure might vary slightly for you, depending

on the software that is installed on your computer.

            I.        Click New to create a new data source name (DSN).

The Create New Data Source Wizard starts.

II.            In the wizard, select SQL Server in the list of drivers, and then click Next.

 III.            Type a name for the .dsn file, or click Browse to save the file to a different location.

Note:-  You must have write permissions to the folder to save the .dsn file.

    IV.            Click Next, review the summary information, and then click Finish to complete the Create New Data Source Wizard.
The Create a New Data Source to SQL Server Wizard starts.

    V.            In the wizard, type a description of the data source in the Description box. This step is optional.

    VI.            Under Which SQL Server do you want to connect to, in the Server box, type or select the name of the SQL Server computer to which you want to connect, and then click Next to continue.

  VII.            On this page of the wizard, you might need to get information from the SQL Server database administrator, such as whether to use Windows NT authentication or SQL Server authentication. Click Next to continue.

VIII.            On the next page of the wizard, you might need to get more information from the SQL Server database administrator. If you want to connect to a specific database, ensure that the Change the default database to check box is selected, select the SQL Server database that you want to work with, and then click Next.

    IX.            Click Finish. Review the summary information, and then click Test Data Source.

      X.            Review the test results, and then click OK to close the SQL Server ODBC Data Source Test dialog box.

If the test was successful, click OK again to complete the wizard, or click Cancel to return to the wizard and make changes to your settings.

7.       Click OK.
Access displays the Link Tables dialog box.

8.       Under Tables, click each table or view that you want to link to, and then click OK.

9.       If the Select Unique Record Identifier dialog box appears, Access was unable to determine which field or fields uniquely identify each row of the source data. In this case, select the field or combination of fields that is unique for each row, and then click OK. If you are not sure, check with the SQL Server database administrator.

Access completes the linking operation and displays the new linked table or tables in the Navigation Pane.

Important:- Each time you open either a linked table or the source object, you see the latest data displayed in it. However, structural changes made to a SQL Server object are not automatically reflected in a linked table.

To update a linked table by applying the latest SQL Server object structure:

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.

If the update is successful, Access displays a message to that effect. Otherwise, Access displays an error message.

4.       Click Close to close the Linked Table Manager.