Access: Linking Tables from SQL Server

Need to use data from SQL Server in your Microsoft Access database? You can do this using linked tables.

Go to the 'External Data' tab, and then click on 'ODBC Database' in the 'Import & Link' group. Select the 'Link to the data source by creating a linked table' option. Select your file data source, or click on the 'New' button to create a new one. You also have the option of going to the 'Machine Data Source' tab and using a data source that is specific to your machine. Everyone's network is going to be a bit different, so I'll leave that choice to you. Click on the 'OK' button when you've made your selection.

Next, you're going to see the 'Link Tables' list. Here you can click to select (or deselect) one or more tables. Choose the tables that you would like to link, and then click the 'OK' button.

You might have to select unique record identifiers for the tables as they are imported. If so, a box will come up with a list of the fields to choose from. This is just an extra step to ensure data integrity and to allow you to update records. You can choose up to ten fields.

When you're done, you will see your linked tables appear in the Navigation Pane. They will have a little globe icon, instead of the usual table icon.

Keep in mind that, by linking these tables, any data you change in Access will be updated in SQL Server, and vice versa. One nice thing is that you can rename the tables in Access without affecting the tables that they're linked to. This is especially handy if you're using naming conventions within Access that don't necessarily match what was done in SQL Server.


Post a Comment