NOTE: to skip the intro, click here to go directly to the material on creating an ODBC connection.
These instructions are written for users who have Windows XP operating system, and are using MS Access 2000.
Instructions for MS Access 2007 are available here.
If you are not using MS Access, the instructions to make a connection via the Control Panel are here.
Instructions for Windows Vista will be available in the future.
The databases introduced in these training sessions are SISInfo and SISFrzn. The SISInfo database is a reporting database populated with Admissions, Enrollment and Academic History data from the Student Information System (SIS) database. The SISFrzn database also contains Academic History data from the SIS database, but the tables in this database represents data as it looked at various points in the semester. SISFrzn tables are loaded at the end of early enrollment, after the first day of class, at quarter of semester, mid semester, and end of the semester, and for fall semester only, at the end of the Academic Orientation Program (AOP).
Both Microsoft SQL Server databases are housed on a database server called MSUDATA that is administered by Administrative Information Services (AIS). AIS performs all database loading and updating, backups, software maintenance, etc. MSUDATA also houses several other databases that are available to various departments across campus on an as-needed basis. SISInfo and SISFrzn though, are the databases that provide the data that is used most heavily at MSU.
Security
Access to reporting databases is limited to those persons authorized by their academic deans or unit directors. All requests for access are processed through the Enterprise Information Stewardship office (EIS) after training is completed. Training in the meaning and use of SIS data is necessary due to its complexity and the many interrelationships between the tables within the databases: SISInfo contains 187 tables and the SISFrzn database contains 129 tables. Once the EIS has approved your request for access to the MSUDATA databases, AIS will assign you a LoginID and password.
Please note that depending on the functions you need to perform, you may be restricted to a subset of the tables that are available within any particular database.
Writing Queries
Data is read from the database through the use of queries written in Structured Query Language, or SQL. Most people use Microsoft Access to build these queries since Access provides a graphical, easy-to-understand way to generate SQL. Some people use Access to create the initial query and then edit the SQL directly to fine-tune it. Training in Microsoft Access is available through the Libraries, Computing and Technology Training Program. Though Microsoft Access will do a lot of the work for you in generating queries, you may find that for really complex queries, you’ll need to know more about how to write or modify SQL. The HELP feature in Microsoft Access can provide valuable information about writing your queries and local bookstores carry books on SQL. There are many resources on the Internet that may be of interest also.
Database Documentation
In addition to the table descriptions for SISInfo that you receive during training, there are two other ways to find this information as you’re building queries. Both methods will show you the same type of information that you find in the printed materials, but you may find it simpler to look up a table definition online as you’re working. The first method is to go to the database documentation web site. At the login screen, click on the “Guest” button. You will then be able to display a list of tables for the database you’re interested in. Then, for each table, you can display all of the column names and definitions that make up that table.
The second method is to directly query the data found in the AATABLES and AACOLUMNS tables on the database you’re writing queries against. As you may guess, AATABLES contains information about each table in the database and AACOLUMNS contains information about individual columns. These tables are used to populate the web pages described above and are available for all reporting databases on the MSUDATA server. Since you’ll be using SQL to display data from these tables, you may customize your selection criteria so that you see only the subset of data from each that you need to write your query. For instance, the query SELECT TABLE_NAME, COLUMN_NAME, SHORT_DESC FROM AACOLUMNS WHERE TABLE_NAME = 'SISPMJR' would yield this result:
| TABLE_NAME | COLUMN_NAME | SHORT_DESC |
|---|---|---|
| SISPMJR | Pid | Person Identification Number |
| SISPMJR | Term_Seq_Id | Term Sequence Code |
| SISPMJR | Student_Level_Code | Student Level |
| SISPMJR | Major_Code | Major Code |
| SISPMJR | Term_Code | Term |
| SISPMJR | Primary_Major_FlaG | Primary Major Flag |
| SISPMJR | Permit_Rgstn_Flag Major | Permits Registration Flag |
| SISPMJR | Primary_Lvl_Flag | Primary Level Flag |
| SISPMJR | Aplcn_Ref_Num | Application Reference Number |
| SISPMJR | Time_Stamp |
Notice that the last column in this table is called Time_Stamp. This column will tell you the last time that the table was updated. Most reporting databases are updated daily and will contain the information found on the mainframe at the end of the previous evening’s processing. During certain busy times, such as during the first few days of a new semester when there is a great deal of activity such as student course drops and adds, the updates may occur after 8:00am. If a report you need to create depends on having the most up-to-date data, you can look at the time_stamp to see if the database has been updated with yesterday’s adds, changes and deletes.
Where to Go for Help
If you have trouble downloading ODBC drivers or defining the data sources you need or if you’ve forgotten the LoginID and/or password assigned to you, please call the AIS Help and Support Center at 353-4420, ext. 311. The Help Desk will put you in touch with someone on the AIS Data Administration team who can assist you.
If you need help using Microsoft Access to write queries or to create forms or reports, please call the AIS Help and Support Center at the number above. They will have someone contact you who can answer your questions. This will likely be someone from the MSU Computer Lab and there may be a charge for a consultation, depending on the time spent helping you.
Finally, if you have questions about the meaning or proper usage of the data available in a particular reporting database, please contact the Client Advocacy Office at 353-4856.
Instructions for installing ODBC connections and creating Data Sources
ODBC stands for "Open Data Base Connectivity". This is a standardized way for any application to make a connection to any database, regardless of the vendors of the software. At MSU, it usually refers to a connection made from a tool like Microsoft's Access to a SQL database.
MS Access database can connect to one or more databases. At MSU, these databases can reside on any of several servers. In addition, the user could define tables within the Access database. This all means that Access can get data from a variety of sources, and make it all seamlessly available to the user. This page contains information about the databases that are available. Of course, access to the data itself is restricted to those who are authorized to see and use the information. See the page on security and requesting access for more information.
The connection to each external database is accomplished by using a Data Source. This is a collection of information about where the data resides, what database to access on that server, etc. You must set up a Data Source to connect to a database. A separate Data Source is needed for each combination of database and server that you want to access. Once set up, it can be reused to connect to the data from multiple MS Access databases.
If you have a Data Source created already, and you have problems connecting to the data, you should try to refresh the connection first. The instructions to do so are located here.
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. If you have different operating systems or versions of Access, contact AIS Help and Support at 353-4420 ext 311 or at ais311@ais.msu.edu .
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".
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".
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.
Multiple links to the same table
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.
Linked Table Manager
Refreshing links to an existing Data Source
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)