Transcript Summit 2001 - Banner Reporting

S
the
power
Building a SelfRefreshing MS Access
Warehouse
of partnership
Presenter:
Laura Key, Taylor University
Monday, April 9th - 8:30 AM
Evaluation Code 03
S
Session Rules of Etiquette
• Please turn off your cell phone/beeper
• If you must leave the session early, please
do so as discreetly as possible
• Please avoid side conversation during the
presentation
Thank you for your cooperation!
2
Evaluation Code 030
S
Introduction
• Pass-through queries can be built in
Access that will get Banner tables in
order to create views using ODBC
connectivity.
®
• This can be refreshed automatically each
night by using Windows.
• Your warehouse can be shared out to
other users for them to link into your
views in order to build reports.
3
Evaluation Code 030
S
• After this session you will be able to:
• Build an MS-Access warehouse (may
need the help of your IS/IT Dept. to
set up ODBC connectivity)
• Set your warehouse up to
automatically refresh as often as
desired.
• Share your warehouse out to other
users.
4
Evaluation Code 030
S
Creating Views
• Create a blank database to hold your
warehouse in MS-Access. Make sure it
has ODBC Connectivity.
• Create your pass-through queries using
SQL. When the SQL is written, click on
queries->sql specific->pass through.
• Go to the query’s properties and set the
username and password. Save the
password with the username.
5
Evaluation Code 030
S
Creating Views
6
Evaluation Code 030
S
Creating views
• The first query you create is just a select
query to get the data that is desired for the
view. Save this query.
• Next do a “make table” query that calls the
select query in order to create your actual
view.
• Notice that you can use multiple queries
to create your view.
7
Evaluation Code 030
S
Creating Views
8
Evaluation Code 030
S
Building a Macro
• After you have your select and make-table
queries built and tested, you need to
create a macro that will fire them off.
Name the macro “autoexec” so that when
the database is opened, the macro
automatically starts.
• (To open the database without the macro
starting, you have to hold down the “shift”
key while opening the file).
9
Evaluation Code 030
S
Steps within the Macro
• First I turned of the warning messages.
• Then I opened the make-table queries.
• Then I exported the finished tables to
another database that I shared out (did not
share the actual warehouse so no one
could access my passwords, queries, etc).
This can be on a shared drive, etc.
10
Evaluation Code 030
S
Building a Macro
11
Evaluation Code 030
S
Summary
• Summarize key points you want your
audience to remember
12
Evaluation Code 030
S
Build a Batch File
• After the warehouse was complete and the
macro was finished and tested. I created a
.bat file.
13
Evaluation Code 030
S
Batch File
• This batch file contains the path to
Microsoft Access and then requests
Access to open your warehouse database.
When the database is opened, that in turn
fires off the autoexec macro which sets
the queries in motion and copies the
tables out to the shared database.
14
Evaluation Code 030
S
Starting the Batch File
• The batch file is started using the task
scheduler utility in Windows. This comes
with Windows 98 and can be added to
Windows 95 using the latest version of
Internet Explorer.
• Add the Batch File as a task and set the
time. We have it running at 12:30 A.M. on
Monday through Friday. The computer
must be on and logged into Windows, so
you may want to be sure it’s in a secure
15
Evaluation Code 030
S
Windows Scheduler
16
Evaluation Code 030
S
Questions?
• It’s question time…
• Or feel free to reach me later at:
Laura Key, Taylor University
[email protected] or (765)998-4683
17
Evaluation Code 030