Creating the ODBC Connection
1) Enter a name and location to save the data source file. Click Next. On the summary page, click Finish.
2) The “Create a new data source to SQL Server" window will open. Enter a brief description in the description field. Enter the name (and instance if applicable) of the SQL server you are connecting to (Figure 4). Click Next.
3) Select the proper authentication method – Windows NT or SQL Server credentials. Click Next to continue.
4) Use the “Change the default database to:" dropdown to select the database you want to connect to. Keep the remaining items as default. Click Next.
5) This last page can remain default. Click Finish.
6) The summary page gives you the opportunity to test the connection before finishing (Figure 5). Click the “Test data source…" button. You should receive a success message. If you ran into an error, there may be an issue with the credentials you provided or the SQL server may not be configured for remote connections. Consult with your Database Administrator for more assistance. Click OK on the test windows and the summary window. You should be back to the “Select Data Source" window we started with back in step 5 (Figure 2).
7) Make sure the new ODBC file you created is selected and click OK.
8) A “Link Tables" window will open. Select the tables you wish to link and click OK. Note: you can hold down the CTRL button to select multiple tables (Figure 6).
9) For each linked table you selected, you will need to choose the primary (unique) key. Select one or more keys and click OK. Repeat for all tables you are linking.
10) You will now see the linked tables show up in Access. If you double click on them, you will see the data in the tables populate (Figure 7).
That’s all – the rest is up to you. Take a look at BrightHub for some other great Microsoft Access articles such as how to create a lookup field in Microsoft Access or how to create a form manually in design view.