Link Access to SQL Server

Instead of copying and pasting data from SQL Server into your Access tables; or instead of exporting tables from SQL Server to an Access database; or instead of importing data from SQL Server into Access; instead, why not link a table in Access to SQL Server?

Here’s how:
Step 01 – Create an ODBC connection for the SQL Server.
In Windows 2000, go to your control panel, in Administrative Tools go to Data Sources (ODBC).
Once the ODBC Data Source Administrator window opens, click on the System Tab.
Add / Select SQL Server (the very last one on the list)
On the next window, name the connection, describe it, and select the server.
Select next and configure how you will authenticate to the server.
Next, select the database you want to connect to.
Finish.
Test Data Source. If Test Successful, OK. If test not successful, go back and tweak your settings in the ODBC Data Source Administrator window.

Step 02 – Link the Access database to the SQL Server using the ODBC connection you just created.
Open Access, from the top menu, select Insert Tables, a pop up window appears, Select Link Table, on the next window, change the Files of Type to ODBC Databases ().
In the Select Data Source window select the Machine Data Source tab; select the name of the ODBC connection you created in Step 01, and finally select the table or view you want.

Trouble Shooting
If you do not see the table or view you are looking for, make sure that the username defined in SQL Server and which is being used by the ODBC connection has permissions to view the table.

If immediately after linking the table, Access wants to assign a unique identifier, you can select one, or simply ignore it by selecting OK.