Linking SQL Tables

To link a table from another database system that supports ODBC SQL, you must have the ODBC driver for that database installed on your computer. Your computer must also be linked to the network that connects to the computer running SQL Server from which you want to link a table, and you must have an account on that server. Check with your system administrator for information about correctly connecting to the computer running SQL Server.

If you have SQL Server 2008 installed or have downloaded and installed SQL Server 2008 Express Edition, you already have a server running SQL Server at your disposal. See Appendix A for instructions about how to install SQL Server 2008 Express Edition. One of the best ways to be sure SQL Server is running on your computer is to use the SQL Server Configuration Manager. You can start the Configuration Manager from the Windows Start menu in the Configuration Tools folder under Microsoft SQL Server 2008. You can also start the Configuration Manager by running C:\Windows\System32\SQLServerManager.msc. In the Configuration Manager, choose SQL Server 2008 Services and be sure the SQL Server (MSSQLSERVER) service is marked as Running. If it is not running, right-click the service name and click Start on the shortcut menu.

To link an SQL table, do the following:

1. Open the Access database to which you want to link the SQL table. If that database is already open, close all open objects so that you see only the Navigation pane.
2. On the External Data tab, in the Import & Link group, click the ODBC Database command. Access opens the Get External Data - ODBC Database dialog box. Make sure the Link To The Data Source By Creating A Linked Table option is selected and then click OK.
3. Access opens the Select Data Source dialog box, in which you can select the data source that maps to the computer running SQL Server containing the table you want to link. Select a data source, and click OK. If you don't see the data source you need, see "Creating a Data Source to Link to an ODBC Database", for instructions. The ODBC driver displays the SQL Server Login dialog box for the SQL data source that you selected if the server is not set up to accept your Windows login.
4. When you are required to enter a login ID and password, and if you are authorized to connect to more than one database on the server and you want to connect to a database other than your default database, enter your login ID and password. Then click the Options button to open the lower part of the dialog box. When you click in the Database box, Access logs on to the server and returns a list of available database names. Select the one you want, and click OK. If you don't specify a database name and if multiple databases exist on the server, Access will connect you to the default database for your login ID.
   Troubleshooting You can't connect to a specific database using trusted authentication because you use more than one data source.
   When you connect to a server using trusted authentication (your Windows user ID), you automatically connect to the database specified in the data source. You might need to create more than one data source if you need to connect to more than one database on that server.
5. From the list of tables, select the ones you want to link. If you select a table name in error, you can click it again to deselect it, or you can click the Deselect All button to start over. Click OK to link to the tables you selected.
6. If the link procedure is successful, the new table will have the name of the SQL table (without the file name extension). If Access finds a duplicate name, it will generate a new name by adding a unique integer to the end of the name.