Here’s a short tutorial on how to link your Microsoft Access database to your Microsoft SQL Server database. (Note: Screenshots from Microsoft Access 2010)
1. First, you will need to create an Access database or open up an existing one.
2. Next, you’ll want to click on the External Data tab and then ODBC Database icon which will start a wizard.
3. Select Link to the data source by creating a linked table and click on OK.
4. In the Select Data Source window, type in the DSN Name and click on the New button.
5. In the Create New Data Source window, select SQL Server and click on the Next button.
6. In the Create New Data Source window, type in the file name and click on the Next button.
7. In the Create New Data Source window, click on the Finish button.
8. In the Create a New Data Source to SQL Server window, type in the name of the database for the Description and enter the fully qualified domain name for the Server.
9. In the Create a New Data Source to SQL Server window, check With SQL authentication, type in your Login ID and Password and click on the Next button.
10. In the Create a New Data Source to SQL Server window, you can leave the options as is or change them. Click on the Next button.
11. In the Create a New Data Source to SQL Server window, you can leave the options as is or change them. Click on the Finish button.
12. In the ODBC Microsoft SQL Server Setup window, click on the OK button.
13. In the Select Data Source window, click on the OK button.
14. In the SQL Server Login window, enter the Login ID and Password.
15. In the Link Tables window, select the table(s) you want to link and click on the OK button.
16. In the Select Unique Record Identifier window, choose the fields that will uniquely identify the record and click on the OK button.
17. You have now created a linked table in Microsoft Access!
