Institutional Computing Services
Frequently Asked Questions
Microsoft Access
This original document was prepared by
- John Gallagher, Center for Instruction, Research, and
Technology
- David Pifer, Technical Support
as a means of giving IT recommendations about use of MS Access, particularly in connection with Banner and
the Banner datamarts.
This document will be revised as necessary to include additional questions and more information, with content
provided by other contributors as well.
MS Access
Using MS Access with Banner and Datamarts
MS Access
Q: How do I obtain MS Access software?
A: See information about Indiana State University's
Microsoft campus agreement licensing.
Q: What version(s) of MS Access are currently supported by IT on
campus?
A: MS Access 97, MS Access 2000 and MS Access 2002.
Q: What are the hardware requirements for running MS Access on my
PC?
A: See information about
regular PC requirements here at Indiana State University.
Q: Where can I get training for MS Access (in general)?
A: See information about available faculty/staff training on
The Center for
Instruction, Research, and Technology: Training Web site.
Also, Ivy Tech Workforce Development Center and CT Consulting Group
& Learning Center both conduct MS Access classes designed to prepare
students for Microsoft Office User Specialist (MOUS) certification exams.
There is a fee associated with their classes.
Q: Where can I get help for MS Access questions (in general, not
Banner-related)?
A: Contact the Help
Desk. They will create a work order and assign it to an IT
representative who will work with you to help resolve the issues involved.
Q: What resources are available for learning and using MS
Access?
A: Here is a small sampling of the numerous resources available to users
of MS Access.
Books
Course Learning System: Microsoft Access 2000, (each with
3.5-inch software disk) [Thomson Learning] - Microsoft Office User
Specialist (MOUS)-approved courseware designed as preparation for MOUS
certifcation exams in MS Access
Illustrated Basic Edition, ISBN 0-7600-6392-3
Illustrated Intermediate Edition, ISBN 0-7600-6393-1
Illustrated Advanced Edition, ISBN 0-7600-6394-X
Microsoft Access 2000 Bible (with software CD), by Cary N. Prague, Michael R. Irwin [1999,
Hungry Minds, Inc; ISBN: 0-7645-3286-3]
Access 2000 For Windows(C) For Dummies(C) Quick Reference by Alison Barrows [1999, Hungry Minds, Inc;
ISBN: 0764504452 Spiral edition]
Mastering Microsoft Access 2000 Development (with software CD)by Alison Balter [1999, SAMS; ISBN 0-672-31484-3]
Special Edition: Using Microsoft Access 2000 (with software CD)by Roger Jennings [1999, Que; ISBN: 0-7897-1606-2]
Access 2000 Developer's Handbook Volume 1: Desktop Edition [Sybex; ISBN 0-7821-2370- 8; User level - Intermediate/advanced;
Category - Application Development]
Also see various web sites such as
http://www.computerhelpbooks.com/m/
Microsoft_Access/
http://cbd.booksonline.com/
User groups and e-mail lists or electronic discussion groups
(e-groups)
Banner-Access e-group (Using MS Access with an emphasis on SCT
Banner)
For more information or to join, point your browser to:
http://groups.yahoo.com/group/BannerAccess/
ISU Access e-group
Here at Indiana State University, IT has created a local mailing list for
the discussion of reporting of Banner data using MS Access.
To subscribe, send e-mail to:
listproc@lists.indstate.edu
with the message: SUBSCRIBE ICSACCESS-L firstname lastname
Post messages to:
icsaccess-l@lists.indstate.edu
ISU Administrative Computing User Group (ACUG)
For more information, contact the
group coordinator.
Online documentation and web sites
CIRT Tutorial Library
Microsoft corporate site
(includes support articles and other helpful resources)
Bruce Knox from University of Arkansas
(specializing in Banner setup)
http://www.fmsinc.com/free/links/index.html
http://www.vb123.com/toolshed/tips-fx.htm
http://www.mvps.org/access/
http://programmingmsaccess.com/
Q: I want to save my MS Access programs for future use. Where should
I store them?
Q: How can I share my MS Access programs with other users?
Q: I want to save some data for use and analysis in my area. Where should I
store it?
Q: How can I share my data with other users?
Q: What steps should I take to backup my MS Access programs or data
files?
A: There are several options for storing MS Access programs and data,
depending on such factors as the amount of data, the duration of the
storage, how easily the data could be reproduced, with whom the programs
and/or data would be shared, backup and security issues.
For MS Access programs and data to be shared, storing them on the Novell
network is a natural choice: Sharing can be accomplished by granting
appropriate rights or permissions to desired users; owners should be aware
of which rights may or may not be inherited on other directory levels. Files
stored on the network would be backed up automatically by IT Operations.
The drawback to storing an MS Access database on the network is that
overall disk space is limited, and personal accounts normally have only
about 5MB of total space, which may not be enough for purposes of the
database.
Because of security issues, IT discourages the storing of data on
the local hard drive of any PC, with the possible exception of larger data
files (10MB or more) that need to be stored on a temporary basis. If the
data is to be stored for extended periods of time, the use of CD-writeable
media is desirable; CD "burners" are readily available, and are often
"standard equipment" on PC nowadays.
Another alternative for larger data files, but not as "clean" as the
Novell network solution, is to use the GroupWise library to store the
database. By doing so, one can assign different rights to that database to
individuals or groups of users. The data is encrypted, and is automatically
backed up when the GroupWise server is backed up by Operations.
In any case, MS Access should be configured to use the local hard
drive for temporary work files, as this creates less traffic on the network,
less load on the server, and makes better use of server space.
Because of the impact that storing and hsaring MS Access databases may
have on other campus users, IT requests that you
contact the Help Desk.
At the beginning of the request description, please include the following
key phrase:
storing and sharing MS Access database
The Help Desk will create a work order and assign it to an IT
representative who will work with you to find a solution suitable to your
needs.
Q: I want all my MS Access queries to run in Snapshot mode, but when I
create a query, it automatically assigns it to Dynaset instead. Is there a
way I can change the default to Snapshot?
A: In our experience, running queries in Snapshot mode instead of Dynaset
will usually result in faster run times, so we agree with your desire to use
Snapshot mode when possible.
It is possible to change the default on a PC running Windows so that MS
Access queries default to Snapshot mode. But this requires the editing of
the machine registry, a critical piece of software that affects all PC
operations, not just MS Access. In other words, if the editing is not done
correctly, the entire machine could be rendered inoperable.
Because of the importance of the registry to the operation of the PC, IT
requests that users not try to edit the registry themselves; rather,
contact the Help Desk.
At the beginning of the request description, please include the following
key phrase:
change MS Access query default to Snapshot
The Help Desk will create a work order and assign it to an IT
representative who can edit the PC registray at a minimum risk to you.
Q: How can I schedule an MS Access job to run at a specific time?
Q: How can I run an MS Access job in "batch mode"?
Q: How can I set up a series of several MS Access (only) jobs, so one runs
after another without user intervention?
Q: How can I set up a sequence of MS Access (only) jobs in a chain of
dependencies, so that the next job runs only if the prior job completed
successfully?
Q: How can I use user-supplied parameters for MS Access jobs when running in
"batch mode", in a series or in a sequence?
A: To do any of the tasks (or combinations) mentioned in the questions
above will require some combination of Windows Task Scheduler, MS Access
macros and Visual Basic programming, depending on the complexity of the
desired setup.
For assistance, you may
contact the Help Desk. They will create a work order and assign it to
an IT representative who will work with you to help create a solution that
will meet your needs.
Q: What if I want to set up a combination series of MS Access, Banner
and/or FOCUS jobs?
Q: What if I want to set up a combination sequence of MS Access, Banner
and/or FOCUS jobs in a chain of dependencies, so that the next job runs only
if the prior job completed successfully?
A: Sorry, but at this time we do not have software available to
accomodate the automated combining of MS Access jobs with Banner and/or
FOCUS jobs.
Q: I understand that data in administrative systems is confidential.
What security measures should I take when using MS Access?
A: Administrative data is indeed confidential, and is often subject to
governmental regulations or involves other legal issues, thereby should be
protected. Here are some recommendations:
- Know the governmental regulations governing use of the data you want to
use, and the legal aspects pertaining to it use and misuse.
- Do not store data on your PC.
- Set passwords for data files.
- Limit the number of persons who share the data and know the
passwords.
- Never share your user-ID or logon password with anyone.
- Do not leave your PC or any logged-in window in a state
whereby anyone else could use your account in your absence. For example:
- Log out when leaving your office for any length of time
- Use a password-protected screensaver
- Shut down your PC at the end of the workday and lock your office
(where possible).
Using MS Access with Banner and
Datamarts
Q: What training is available for using MS Access with Banner or the
Banner datamarts (specifically)?
A: Datamart documentation and vendor-sourced training
materials are available on the
"Datamarts" page of the ICS web site.
In addition, ICS will offer a series of workshops on using MS Access
with the Banner datamarts. Watch for future announcements.
Q: How can I connect to Banner or the Banner datamarts (specifically)
when using MS Access?
A: Information about Open DataBase Connectivity (ODBC) is available in
the
"MS Access and ODBC" section at the bottom of the "Reporting" page of
the ICS web site.
Q: Where can I get help for questions about reporting from Banner or
the Banner datamarts (specifically) using MS Access?
A: Contact the ICS Banner coordinator for the area you are working
with.
Added 27 Feb 2002
Q: Do I need a user id and password to access BANNER data with MS
Access?
A: A user id and password is required to access either data from BANNER
base tables or data from a BANNER datamart using MS Access. For BANNER base
tables (PROD, CLNT, or DEVL), the user id and password required is the same
as your BANNER logon id and password for the specified BANNER database.
However, the user id must be validated for one or more reporting reporting
roles. Reporting roles allow the user id to use specific BANNER base tables
and specific BANNER views. These views may be referred to as Object:Access
views.
There are two BANNER datamarts. PRODDMRT, which reflects the BANNER
PROD database, and DMRT, which is based on the CLNT database but may not
always totally reflect the CLNT database. PRODDMRT is refreshed each night.
Each morning PRODDMRT will reflect PROD database as of about 2:00 am. To
access either of these two datamarts requires a user id and password for the
datamart being used. The datamart user id is usually the same as your
BANNER login id. However, the password will likely be different.
To change your datamart password logon to the desired
datamart using sqlplus:
- log onto jade
- sqlplus
- Give user id and password for the desired datamart (Examples of a user
user id logon for PRODDMRT and DMRT)
cctest@proddmrt
cctest@dmrt
- alter user YYYYYYYidentified by XXXXXXX;
(where YYYYYYY is your user id and XXXXXXX is the password you want to
change to)
Datamart user ids must be validated for the datamart desired composite
tables or desired Object:Access views. This is accomplished by granting
reporting roles to the id. To request a datamart user id and access to
reporting roles or to request access to additional reporting roles for an
existing datamart id, contact the area that manages the desired data.
- Student area data = Stacey Thomas or Connie Shattuck
- Finance type data = Stacy Hammond
- Alumni type data = Jeff Pohlen
- Financial Aid type data = Jim Bell
- Human Resources type data = Roy Boissy
A description of the type of data desired will be needed along with
justification for using the data.
- Vicki Winn
|
