Data Admin Team home page | ODBC setup page | Suggestion Box | MSU Home Page | Database Info
Instructions for installing ODBC connections and creating Data Sources
This set of instructions will walk the user through the process of creating an ODBC connection to data stored on the AIS servers. It is designed for users running Windows XP and MS Access 2000.
1. Using Access, create or open a database.
2. Click the "Tables" button on the left side of the window, if you do not already see tables in the work pane.
3. select File menu, then "Get External Data", then "Link Tables".
4. A window pane titled "Link" will open. At the bottom of the pane is a pull down list box titled "Files of Type". Open this, and click on the last item, titled "ODBC Databases()".
5. The next window is titled "Select Data Source". Click the "Machine Data Source" tab on the top.
6. Scroll through the list of data sources shown. If one already exists for the database and server desired, click on it, then click the "OK" button, and click here to skip to step # 15. Otherwise, continue to step 7 to create a new data source.
7. To create a new data source, click the "New" button on the left side of the "Select Data Source" window. You will see a new dialog box titled "Create New Data Source". Select the "User Data Source" radio button if it is not already selected, and click "Next".
8. The next dialog box is also titled "Create New Data Source". Scroll down to the bottom of the list, and select "SQL Server", then click "Next".
9. A third dialog box titled "Create New Data Source" will appear. Do nothing but click "Finish" here.
10. On this screen, you will give the new data source a name, a description, and tell Access what server it resides upon.
While the name can be whatever is meaningful to you, AIS suggests you use a combination of the desired database and the server it resides upon. In this example below, the name is "SISFIN-MSUDATA. This makes it quickly obvious that this data source connects to the SISFin database on the MSUDATA server.
The Description field can be any freeform text to help you understand and recognize this on the next time you connect to it.
The server name will be the fully qualified name of the server. Typically this will be "msudata.ais.msu.edu". Once you complete these fields, click "next".
11. Next up is a window titles "Create a New Data Source to SQL Server". Make sure that the radio button "With SQL Server authentification using the network login ID" is checked. Enter your ID and password, and click the "Next".utton.
12. On the next window, you need to click the "Change the default database to:" box, and then select the database you want to access from the pull-down list. Leave the checkboxes "Use ANSI quoted identifiers" and "Use ANSI nulls, paddings and warnings" checked, as shown. Click "Next" button to continue.
13. All the check boxes on this window should be unchecked, except for the "Perform translation for character data" box. Click "Finish" when ready.
14. The window titled "ODBC Microsoft SQL Server Setup" will list the parameters you have selected in this process. Click on the "Test Data Source…" button at the bottom. It should come back with a message saying "TESTS COMPLETED SUCCESSFULLY". This indicates that all is well. Click OK to get back to the Server Setup window, and then click "OK" again.
15. Now that the machine data source is created, click once on it to highlight it, and click on "OK".
16. You must log in to the SQL Server to show that you are authorized to access the data you want. Enter your ID and password in the spaces shown, and press "OK".
17. We're getting close!!! The window labeled "Link Tables" contains all of the tables and views for which you have access. Click on each table you want to use, or click on the "Select All" button to get all of them. Click "OK" when done.
18. The system will now go through all the tables you selected, and create a link between the table on the server and your version of MS Access. These links will allow you to connect to each table directly, or let you create queries to select data from them.
19. For some tables, Access may ask you to identify the field or fields that act as a unique identifier for each record in the table. You may get a screen like this one:
If you know which fields are used to define the key, highlight them by clicking on them, and press "OK". If you accidentally click on the wrong fields, you can deselect them by clicking on them again.
If you don't know which fields make up the key, just click "OK" and the system will add the table without defining the key.
Note:
It is possible to create more than one link to the same table. When Access finds a link to a table that already has an established link, it will create another link to the table, and append a number to the end of the table name. In the example below, the tables SAMAWD2 and SAMBIO3 are linked to twice, and SAMAWD1 is linked to 3 separate times. While this will not hurt your queries, it can cause confusion about what data you are seeing. You can delete the extra linked tables by right-clicking on the extra table, selecting "Delete", and confirming the operation. Be aware that SOME table names do end with a number, though. For example, there are several sets of tables such as SAMADS1, SAMADS2, and SAMADS3, SAMBIO1, SAMBIO2, SAMBIO3, etc. If you have any questions about whether you have duplicate linked tables, feel free to contact AIS Help and Support at 353-4420 ext 311 or at ais311@ais.msu.edu.
If you already have tables linked to this database, but the links are not working, you may want to use the Linked Table Manager tool instead to restore the links. This utility will reset the links to the data tables much faster than by creating a new ODBC connection.
To use it, click on the "Tools" menu, then "Database Utilities", then "Linked Table Manager".
Select the tables to be relinked by checking the boxes to the left of the table name. If you want to relink all of the tables, click on the "Select All" button.
Click on "OK" to relink. Access should display a message saying that all selected tables were successfully refreshed. Click "OK", then "Close".
If you have comments or suggestions about this webpage Email us. (Please do not change the subject line)