BannerAccess - MS Access Reporting for Banner

 by Bruce Knox
  bknox @t     Oracle Database Programmer/Analyst                                                                   date last updated 02/13/2014

Copyright 2002 Cision Studios  Mad Tea Party


Oracle 11g Instant Client
We installed the Oracle 11g Instant Client on one PC and copied that install to additional PCs.  This saved a lot of installation time and reduced errors.  If you have the Oracle 9i Client installed on the PC, it must be removed using the Oracle Universal Installer before installing either 10g or 11g.  This Oracle 11g Instant Client install for ODBC is the most successful Oracle Client setup instructions that we have ever passed on to our technical support team for installing new machines.  The instructions including screen shots is just over two pages.   (Works fine with Oracle ODAC 12c too, but you will need to run the full Universal Installer to get just what you need.)

MS Access 2007
By default, the Enhanced Security on MS Office 2007 wants you to OK the running of your MS Access DB each time you start it. From some old work-around notes:

Setting up the MS Access db as trusted is done in Office 2007 under the "Office Button",

Select "Office Button" (the button in the far left top corner), "Word Option", "Trust Center", "Trust Center Settings", and "Trusted Locations".

Now, you can add the location.

MS Access Migration:
There has been a lot of interest in converting MS Access Applications to Oracle using APEX' MS Access Migration Tool.  I am collecting applications done in APEX by Banner clients for a BannerAPEX page.  APEX is Oracle's Application Express which is a free web based rapid development system.

SQL Developer has a very similar set of MS Access conversion tools.

Here are the 2007 Summit sessions I found listed with some reference to MS Access:

**Accessorize Your Reports**
Tuesday 10:00 AM 11:00 AM
Eton Chin The University of the West Indies

Finance and budget units can add value to their customers by taking the standard delivered banner finance reports through access into more user-friendly formats. Two approaches can be used. The first is to take the FGRBDSC, FGRODTA, FGRTBAL and FGRGLTA report outputs through MS Excel and into MS Access tables. Queries, forms, macros and reports are then used to package and deliver user relevant reports. Email from within access or deploy it through a front-end/back-end multi-user environment across your LAN. Deliver P&L's, I&E's, budget variance and other section reports or forms through switchboard(s) with one-click buttons. Access is fast and skilled users can add new reports or forms. Once the data is captured, the information output is virtually limited only to your imagination and manipulation of Access features. The second approach would use ODBC connections to the banner base tables such as FGBGENL and FGBOPAL to get closer to real-time data.

This one is mine, but be warned that it is more about converting from MS Access than using it. It might hold some interest if you want to move MS Access developed Reports to SQL*Plus.
**Argos - Moving into the Community**
Tuesday 10:00 AM
Bruce Knox University of Arkansas - Division of Agriculture - Cooperative Extension Service

Searching for THE Banner Reporting Tool your obsession? Mine too! After building my own Banner reporting tools for years including BannerAccess a MS Access based Banner reporting tool, I found Argos, the Evisions product for Ad Hoc Banner Reporting. I wanted it from the moment I saw it. Argos was my BannerAccess with all the missing pieces that I never found time to develop. Learn about our experience installing Argos, joining the Argos User Community, acquiring free community written software, and converting from MS Access.

**Event Attendance Tracking in Access**
Tuesday 2:00 PM 3:00 PM
Lisa Mahan Franklin College of Indiana

Franklin College Alumni Services has kept manual lists of event attendance for years. Using MS Access we have developed an event attendance tracking system integrated with PowerCAMPUS to keep a detailed history of alumni events and the attendees of those events. The system also integrates with the Admissions module to track inquiry/applicant attendees at co-hosted events for alumni and prospective students. The system could be easily customized to meet the needs of any PowerCAMPUS institution.

**MS Access Forms and Banner Reports**
Tuesday 3:30 PM 4:30 PM
Dennis Barnes Wharton County Junior College

When we implemented Banner in 2002, we chose to use Microsoft Access as our reporting tool. I will illustrate how to incorporate Forms in Access 2003 to overcome some of the difficulties in using Access as a reporting tool. This will include creating drop-down selection boxes for prompts and using Visual Basic for Applications (VBA) to create csv files from Banner Student 7.3 data. I've been a Sungard client since 2002, when we began the implementation of Banner, replacing an in-house legacy system. I was the primary systems analyst in the conversion of the student system. This will be my first presentation for Summit.

**Banner Event Management and MS Access**
Wednesday 8:30 AM 9:30 AM
Laura Brandenburg Saint Mary's College

Learn how to use the Banner Event Management Module and Microsoft Access add-on tracking for the end user. This session also uses Banner Advancement forms but is definitely not limited to Banner Advancement professionals. Benefits of Attending 1. Get a brief overview of strategic event planning. What information is needed to track an event? How and where can this information be stored? 2. See examples of add-on fields created by Saint Mary's College for successfully planning an event. 3. Learn how to set up MS Access forms. End users will be able to enter and save data in these forms, then run queries and reports needed for the event. 4. See examples of MS Access queries and reports which use both Banner data and the end user's data saved in MS Access.

**Reporting Using MS Access - Inter.**
Wednesday 8:30 AM 9:30 AM
Jyoti Srivastava Central State University

This session will build on the basics of MS Access for your reporting needs. Focus on complex queries using multiple tables or views. Explain the different types of join properties to use in creating queries. Create reports to show data in a more presentable manner. Demonstrate the use of macros and forms to develop a switchboard (Switchboard is an easy to use self-reporting tool that can be implemented in MS Access for those who do not need to learn MS Access but still need data, reports etc.). We will show examples from the Admissions and Financial Aid areas; however the target audience for this session is anyone who wants to use MS Access as a reporting tool for their area.

**Profile Reports--It Can Be Done!**
Thursday 10:45 AM 11:45 AM
Teresa Goddard Rose-Hulman Institute of Technology

Building a comprehensive profile report from all the data in Banner can be overwhelming to a user with limited technical expertise. This session will demonstrate how to pull all that data together in a viewable and printable format using MS Access as a report writer. This session will give you a map on where to begin or maybe directions on where to go from where you are at. If you're a beginner and need to identify where the data is, or an experienced user that needs to pull it all together, this session should be beneficial for all.

**Open BOF - Topic TBD**
Thursday 10:45 AM 11:45 AM
Jan Levsen SunGard Higher Education This is an open slot for a Birds of a Feather. The topic will be determined early in the conference and communicated to everyone to allow participation.

Summit 2006 Sessions related to MS Access Reporting:

Advancement's Parent Load #801 by Candy Hamblin, Rollins College
Bank Reconciliation Utility #695 by Vincent Gaines, University of Dallas
Creating Employment Letters #479
Fixed Assets A-B-C's: An Overview #690
Fun with Oracle Discoverer Reports #152 by Laurianne Olcott (about moving from MS Access)
Online Awards and FFELP Loans #635 by Joel Wenger, Indiana-Purdue University - Fort Wayne
Self Reporting Using MS Access #365 by Jyoti Srivastava, Central State University
Still Printing & Mailing Letters? #330 by Crystal Finefrock, Plymouth State University
The Wizard of Ahhhs or Access to Go #448 by Yvonne Mann, Texas Christian University

I am currently working on the migration from BannerAccess to BannerArgos.  BannerAccess will continue.
Bruce Knox April 13, 2006

BannerAccess (MS Access Reporting) BOF

Our session is a moderated Q & A event on using MS Access as a Reporting Solution for Banner.  The emphasis is on sharing of solution approaches, problem solving, and the exchange of ideas, and be sure to bring your ideas with you. 

 Bruce Loving

 2003 Session PowerPoint   Bruce Knox     bknox @t        Bruce Loving   bruce_loving @t

Using SQL Pass-Though Queries for Banner Reporting  the Annotated Screen Shots from Summit 2003      +  Logon with Separate DNS and DBQ

Brad Dennis' Demo Site Pass-Through Parameter Form  +  Oracle Instant Client ODBC Install 

More links from Summit 2003
  Sarah Lawrence and Malinda Richbourg UNCG - Introduction to MS Access for Banner Users 
    Session 607 PowerPoint  
    Reporting with MS Access and MS Excel

Summit 2002 - MS Access BOF
      Bruce Knox

Saved Objects Database   
Saved Objects Examples   
SQL*Plus from BannerAccess?

     Bruce Loving
links page from the session

Byron "Buzz" Merrick's  MS Office with Banner links 
see: The User Guide (link holds all our end user documentation, tips N tricks, and Action Alerts
(1 page notices that tell users they have to do something different)).

More links from Summit 2002

 Jane Frounfelker' links (including SET PATH for Win2000/XP)
 Dan DeBower's Pass-Through PowerPoint Presentations Summit 2001 Summit 2002
 Laura Key's MS Access "Self Refreshing Data Warehouse" PowerPoint Presentation Summit 2001
 Teri VanDever's "Creating a Reports Menu with Access" Presentation is available from the file section of the BannerAccess ListServ 

Using MS Access connected via ODBC to Oracle as an ad hoc reporting tool for  SCT 's Banner.

A Building Block Approach to Ad Hoc Queries  

MS Access Now  

MS Access References                                                                          MS Office  VBA  DataAccess 
MS Access
SQL Pass-Through
MS Access/Oracle Generalized Logon Form      DSN vs. DBQ
Windows 2000 ODBC for Oracle8  
MS Access 2000 Oracle ODBC LONG datatype error fix

Indiana State's FAQ - John Gallagher

SCT Action Web    
SCT Listserv Archives    BORACLE    BGENERAL    BHUMRES    BFINANCE    BREPORT   BannerAccess    

IBM RS/6000 AIX (see Commands)      AIX Permissions

SQL*Plus             Oracle Dates        Oracle Documentation 

DBA Stuff    

Oracle Programming with Visual Basic by Nick Snowdon 

(published by SYBEX, 1999, ISBN 0-7821-2322-8).

For MS Access ODBC, Chapter 14 covers Data Access Objects.  One of very few books that adequately covers using MS desktop apps with Oracle databases.

Microsoft Access 2002 Inside Out by Helen Feddema 

(published by MS Press, 2002, ISBN 0-7356-1283-8).


Microsoft Access 2000 at a Glance

(published by MS Press, 1999, ISBN: 1-5723-1946-1).

A quick visual reference with only the user information you need.







Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences.  The scripts and information are provided "as is" without warranty, implied or otherwise.  Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)

Any materials posted here from other authors, all logos, images, and trademarks in this site are property of their respective owners


This site is oriented to Reporting from Ellucian's Banner product. (I maintain a number of special topic pages with links from this primary site.) is oriented to MS Access Reporting product as used with Banner.  See for related links.

This page was before depreciating that site.







An Open Source Project