Banner > MS Access

Using MS Access with Banner

In this article we will learn about the advantages of using Access with Banner as well as requirements and some basic how to.


Why use Access with Banner?

  • Banner reports are lacking the data you are looking for.
  • Access gives you greater flexibility to manipulate your data set.
  • You can link external data with data derived from Banner.
  • You can export information from Access to Excel.
  • Removes the need to use SQL (Structured Query Language) to obtain data sets from Banner.


Queries

A query is just that - a question. It is asked in a way that will return a particular set of data to you, based on criteria you set in the question. Access allows you to ask these questions visually, using a GUI, rather than having to know how to write the question out in SQL, with all of the correct punctuation.

Access allows you to select data from not only Banner tables, but from views generated in access of those tables, and queries that you have generated in Access and saved for later use - and all visually.

In addition, all Banner data is in a read-only state when being used by Access. This means that you can't change Banner data by using Access, which protects the Banner data you are using from any.... issues.... you may have while manipulating it!

Queries can be created using two methods - a built-in Query Wizard, or in the Query Design mode of Access.


Requirements for Using Access with Banner

You need the following information and resources to use Access in conjunction with Banner:
  • Standard Banner credentials
  • Additional Banner privileges that allow for the direct access of Banner data/tables
  • A special driver to be installed on you computer. This is an ODBC Driver (Open Data Base Connectivity) that has been customized for CU. For assistance with setting up the ODBC driver please email support@cu-portland.edu to schedule a time.


Exporting Derived Data to Excel

Once you've ran your query and obtained the data you want, you can then export it to Excel for further manipulation by using the Export to Excel spreadsheet button found on the External Data tab in Access. If you are more familiar with Excel, this might be an option for you if you feel your data requires further manipulation, or you are planning to use it in a merge document as a data source.
UsingAccessExcel1.jpg


Using External Data

When connecting to a Banner Table from Excel, you have two options - Linking and Importing. Always link to Banner data! Linking allows for the constant updating of your information from the linked data, and prevents you from attempting to load what can be a very large amount of data from banner locally onto your machine. This not only can create potential network issues, but also can present privacy and security concerns if the data you are acquiring contains sensitive information. So always link to your data!


Getting Support

If you having issues connecting to Banner or Access please email support@cu-portland.edu



Updated by Steven Quirk 10/2/13