Data Admin Team home page  | ODBC setup page |  Suggestion Box  |  MSU Home Page  |  Database Info

Microsoft Access 2007 uses a slightly different way to start the process to create ODBC connections. But after the first 4-5 steps, the process is the same. This page will show you the new steps, and provide a link into the original methods shown for Access 2000 once we are back to familiar territory.

1. Using Access 2007, create or open a database.

2. Click the "External Data" menu or tab at the top. You should see the word "More" appear underneath this tab. If it doesn't, expand the Access 2007 window to the full size of the screen. Click on "More" when you see it.

3. You should see a window labeled "Get External Data - ODBC Database". Click on the button labeled "Link to data source", then click the "OK" button at the bottom.

4. A window titled "Select Data Source" will appear. Click on the "Machine Data Source" tab at the top. A list of the data sources already defined will appear. If one of the sources on this list is for the data you need, click on it and click "OK". You will be taken to a screen where you will enter your user name and password. Click here to jump to step 14 of this document for instructions on how to continue. Otherwise, continue to step 5.

5. If no data source is found that meets your needs, click on the "New" button to create one.

6. On this window, click on the "System Data Source" radio button, and click "Next".

7. On this window, you will select the driver needed to set up the data source. Scroll to the bottom, and select "SQL Server". Click "Finish" when done. Click "Finish" on the next screen, also.

8. The next screen is where you will give your data source a name, a description, and indicate what server it will connect to.

The name of the data source can be anything you want that has not already been used. However, AIS Data Admin team recommends that you use a name that describes the database and the server where the data resides. This will make it easier to recognize and use the data source on later dates.

The description can be any free-form text that will help you recognize the data source at a later date.

The server needs to be a fully qualified server name. At MSU, that will probably be "msudata.ais.msu.edu", as shown. Click "Next" when finished.

9. On the next screen, click on the radio button as shown so that SQL Server will prompt for a password. Also, enter your password in the box at the bottom, and click "Next".

11. Make sure the check boxes on your screen match those shown here, and click "Finish".

12. This window is a summary of all the selections and parameters associated with this data source. Click on the "Test Data Source" button shown. You should see a window saying "Tests completed successfully!" Click "OK" there, and then "OK" again.

13. Now you should see a list of the data sources, with the one just created highlighted. Click OK. Access will next prompt you to enter your password. Click "OK" when you have done so.

14. This screen shows a list of the tables in the database that you are authorized to access. Click on the individual tables to highlight them and select them, or click on the "Select All" button on the left. If you get some tables by mistake, click them again to de-select them. Click the "OK" button when done.

16. Keep in mind that it is possible to have more than one link to the same table that resides on a SQL Server. In that case, Access will append a number at the end of the table name. The example below shows duplicate entries for the tables ACADEVNT, ACADLVL, and ACADSTAT. While there are certain times when having multiple links to the same table are useful, this can also cause confusion. Unless you need those multiple links, it's best to avoid this. To get rid of the duplicat table links, right click on the extra ones, and select "delete".

  If you have comments or suggestions about this webpage Email us.    (Please do not change the subject line)