Creating an ODBC Connection
In order to create an ODBC connection, it is assumed that an administrator or power user must be used to create the connection. Typical end users will not have the rights to make such a connection.
In order to create the connection (Microsoft Access 2007), open Microsoft Access. Select create a new database. Name the database so that you will know it is the connection to the server.
When Access opens, click on the External Data on the Ribbon (MS Access 2007). Select More and ODBC Database.
The next screen is critical. You must choose LINK to the datasource. This will provide a connection to the SQL Server tables. The tables that are linked to the server will have read only access if the database administrator sets the permissions correctly (this is a preferred method). This also ensures data is not changed in Access but in the application that was written for SQL.
The next window will begin to add the data source, you should select new, select SQL driver, put in the SQL description, enter the server, the user name and password. At this time an option will come up to select the tables to link to. Select all of the tables and click OK. Access is now LINKED to your SQL server. This now allows you to write queries that can pull up data for special queries.