How to link SQL Server tables in Access 2007
If your business uses SQL Server to store data, sometimes it is easier for users to have access to that data through an Access database. Access is easy to use and the interface can be made simple by providing links to only those tables that the users need. Users can create simple queries to look for data they need, locate data using the Find function and update the data in tables. Following are the steps to link SQL Server tables in an Access database.
Step 1: Select Source
Open an existing Access database or create new a Access database by selecting blank database and entering a File Name for it. Click on the External Data tab and then click on More. From the dropdown list of options, select ODBC Database.
Step 2: Select the option to link tables
From the Select Source and Destination dialog select Link to the datasource by creating a linked table.
Step 3: Select ODBC Datasource
The Select Datasource dialog opens. If you have already created a data source for the SQL Server database you want to link to select it. Otherwise, create a datasource.
Step 4: Login to database
The SQL Server Login dialog opens. The username you enterd when you created the ODBC connection will be in the Login ID box. Enter your password and click OK.
Step 5: Select tables to link
The Link Tables dialog opens. Click to highlight on all the tables you want to link. Check the Save password checkbox and click OK.
Step 6: Tables have been linked
The linked tables will appear in the the Access 2007 navigation pane under Tables. You can use these tables like you use any other tables in Access. The only the exception is that the the design of tables cannot be changed.