Building a SelfRefreshing MS Access Warehouse

1
Building a Self-Refreshing MS Access Warehouse
Presenter Laura Key, Taylor University Monday,
April 9th - 830 AM
2
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!

3
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.

4

• 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.

5
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-gtsql
  specific-gtpass through.
• Go to the querys properties and set the username
  and password. Save the password with the
  username.

6
Creating Views
7
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.

8
Creating Views
9
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).

10
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.

11
Building a Macro
12
Summary

• Summarize key points you want your audience to
  remember

13
Build a Batch File

• After the warehouse was complete and the macro
  was finished and tested. I created a .bat file.

14
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.

15
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 1230 A.M. on Monday
  through Friday. The computer must be on and
  logged into Windows, so you may want to be sure
  its in a secure environment.

16
Windows Scheduler
17
Questions?

• Its question time
• Or feel free to reach me later at
• Laura Key, Taylor University
• lrkey_at_tayloru.edu or [phone]