Using the ODBC Administrator to establish your connection to data sources

In order for you to either link to live data tables or import tables, you need to tell your computer where those data sources are and what type of driver is needed to connect to them. So to do this you need to know:

1.  Where your data sources are (make friends with your DBA!)

2.  Which driver you need to use (again, info your friendly DBA knows)

See- simple and straightforward so far!

STEP 1:

Go to your programs and enter ‘Data Sources’. Open the program that is found. If you cannot type in a program name in your start menu, then go to your control panel and in the ‘Systems and Security’ area you will find ‘Administrative Tools’. On the Administrative Tools menu is ‘Data Sources (ODBC)’. Open this program.

What you see in your window will be VERY different than what you see in my window. Yours may be blank or yours may already have connections made. To be able to know what you need to see, go to your (now best-friend) DBA and tell them you want to use MS Access as a data query tool to access your Jenzabar/Datatel/Banner/PowerFaids data. As long as you already have permissions to access these databases (meaning you can log-in to the front end and see stuff), then you can do this. Your DBA then will/ should tell you the name of the data source, the name or alias of the server it is on, and the type of driver you need. At our school we use Jenzabar, PowerFaids and Raiser’s Edge. All three of those use the same driver. You can use either the User DSN or the System DSN- User DSN’s are only visible to you on your computer. System DSN’s are visible to others. Ask DBA/IT if they have a preference for which you use, if they do not care, use the system DSN.

If you do not have a SQL Server Driver configured to play with your database, then have no fear... Just follow these steps!

Click ‘Add…’

Scroll all the way to the bottom and select ‘SQL Server Native Client 10.0’ OR whichever driver your IT/DBA told you to use, then click ‘Finish’.

You will get a window that asks for the name, the description, and the server. The name and description are up to you. I advise using the same or near the same for both, description can be less specific. Then click Next.

Which radio button you select above depends on which permissions method your DBA/IT has set up. If you log in to a front end with different credentials than what you log in to your computer with, then you likely will chose the options I have checked above and you will enter your username and password for the application here. If you do not have to enter credentials to get into the front-end of your application, then your machine is using your Windows credentials for authentication. If one method does not work, you can always come back to this step and try it the other way. If both fail, you need to go see your friend in IT. Then click Next!

Yes- another screen, hang in there we are almost done.

OK- now is when we need to know the data source name. You want to check the box to ‘Change the default database to:’ and you want to select whatever it is your DBA/IT person told you was the data source name for the application you are trying to reach. Example from our school, our application is Jenzabar but the data source name for the database on the server is ‘tmseprd’. Keep the same checkboxes checked as I have and click ‘Next’.

There should just be the one checkbox checked above and then click Finish!

You can click on ‘test data source’ to go ahead and check that everything is working, you should get a little pop-up that verifies your connection (this step is also the first one that will verify your log-in username and password).

NOW- you are ready to actually begin linking and importing tables

WHAT LINKING TABLES LOOKS LIKE IN ACCESS:

From your Access menu, click on the External Data Tab and then select ‘ODBC Database’. If you do not see this icon, it could be hidden under ‘More’:

Choose to either Import the data tables (I choose this option on census day for Fall reporting) or choose to link to the tables. I have a census database for each Fall that tables are imported into and then one database I call “Live” that has links to the tables. NOTE: Importing tables takes up a lot of space!

Now this window looks familiar doesn’t it? You should see your data source info on the left in the window below. The description you chose to use for your data source shows on the right. Click on the name of the data source name to select it and then click OK.

At this point if you are using a password to log in, you will be prompted for it.

Then you get a list of tables that are in the database you selected to link or import tables.

This is where the fun really starts because you get to see just how much structure there really is to your database applications. For example, Jenzabar probably has 200 or more tables. I can run all the data I need from just 35 of them!

There is a learning curve to discovering where your data is for each system you use. It is well worth the adventure!

Same situation for PowerFaids, probably 100 tables in there- I can get all the financial aid data I need to do all my reporting from just 9 or 10 of them!

After you are all done you can see which tables are linked and which are imported, Access gives them unique icons (important to know if you mix use of linked and imported tables- that way you can see what is static and what is live):

Other COOL reasons to use Access:
You can link to/import from multiple data sources! You are not limited to just what the front-end or application specific program will let you run.
The ability to have all the data that will be used for a year’s worth of reporting all in one spot! Importing all your financial aid and registration data as of your fall census makes reporting consistent data year-round so much easier.
Once you get the hang of it, you can gather data quickly and get to the analysis step faster.
Respect and Admiration of your Colleagues (of course!)