More Pass-Through Queries! Evaluation Code 245

1
More Pass-Through Queries!Evaluation Code 245

• Dan DeBower
• Technical Consultant
• SCT
• Tuesday, March 26th, 2002
• 800am 930am

2
Session Rules of Etiquette

• Please hold all questions until the session ends
• 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 session
• But if you have a burning on-topic question, ask!
• Thank you for your cooperation!

3
Introduction

• I presented MS Access Pass-Through Queries at
  Summit last year, in Toronto
• This presentation grew out of the feedback I
  received from that session
• I will not cover the setup of ODBC or other
  technical issues during this presentation. (Feel
  free to ask questions at the end, though!)
• Remember Im assuming you know some SQL

4
Agenda

• Review Pass-Throughs
• Rename your fields
• The Make Table Query
• Delete and Append (an alternative to Make Table)
• The TimeStamp
• Easy access to ObjectAccess

5
Review

• Use the latest version of MS Accessif youre
  using Office2000, make sure youre on Service
  Pack Two (SP2)
• Create and Prepare your query
• Create a Pass-Through query
• Prepare your connection
• Enter your SQL

6
Create a Pass-Through Query

• The NEW button, on the database Queries tab.
• Or from the menu Insert - Query
• From the wizard, select Design View
• And Close the show table window
• And Query - SQL Specific - Pass-Through

7
Prepare Your Connection

• Create an ODBC Connection String
• In the Properties window enter aconnection
  string
• ODBCDSN???DBQ???UID???PWD???
• DSN - your ODBC Data Source Name
• DBQ - your Oracle database instance
• UID - your Oracle UserID
• PWD - your Oracle Password

8
Prepare Your Connection

• Create an ODBC Connection String
• If you leave out the Username and Password,
  Access will display a connection window
• ODBCDSNBannerDBQPROD

9
Enter Your SQL

• Remember - you can write and test your SQL
  queries in SQLPlus and then Copy-Paste from the
  SQL editor to the Pass-Through window!
• And SQLPlus gives better error messages!

10
The Make Table Query

• Make Table Queries allow you to save the output
  of your Pass-Throughs
• Without a Make Table, your Pass-Through will run
  every time you update your reports or queries
  that are based upon your Pass-Through
• With a Make Table, your saved data doesnt change
  unless you want it to. No more mismatched
  reports because someone updated Banner

11
Create a Make Table Query

• Create a query in Design View
• Add your Pass-Through with the Show Table window
  and close Show Table
• Select Query Make-Table Query
• Enter a Table Name
• Double click in your Pass-Through to select
  all the fields
• And Save your Query with a unique name

12
Rename Your Fields

• Seeing Banner field names in your Access tables
  can be hard to read and confusing
• When you enter a field name in your Pass-Through
  select statement, add a space, and then a unique
  (short) name
• Fromselect spriden_last_name,
• Toselect spriden_last_name LNAME,

13
Hint

• If you receive an error that your query was
  Cancelled by the user, your ODBC connection
  probably Timed Out. In other words, its just
  taking too long
• In the Query Properties window, set the ODBC
  Timeout property to 0
• Make sure your Pass-Through and Make Table
  queries both get changed

14
Delete and Append

• A Make Table Query completely deletes and
  re-creates your table every time you run it
• But what if you dont want to destroy the table
  youve made? Have you formatted the Datasheet
  view or added an index?
• Delete and Append allows you to keep your
  existing table, remove all the records, and
  repopulate with an Append Query

15
Delete and Append

• Delete
• Select the Macro tab
• Select New, creating a new Macro
• Select the action RunSQL
• At the bottom of the Macro page on theSQL
  Statement line, enterdelete from tablename
• And save your Macro with a unique name

16
Delete and Append

• Append
• Open your Make-Table Query
• Select Query Append Query
• Append Querys add records to an existing table
• Remember -- If you change your Pass-Through,
  you must re-Make your table. Append doesnt work
  if the Query doesnt match the Table

17
Delete and Append

• By adding a second command to your Macro, you can
  perform your Delete and Append in one step
• Open your Delete Macro
• Add the Action OpenQuery after RunSQL
• Choose your Append Query for Query Name
• And set Data Mode to Read Only (faster)
• Save your Macro
• Insert a SetWarnings to No Action at the top

18
Delete and Append

• Now youve got a single Macro that
• Doesnt destroy any changes youve made to your
  Table design
• Automatically updates your table without asking
  any questions
• Can be added to a Button on a Form or as an Event
  on a report (or even called from another Macro)

19
The TimeStamp

• Always add a field to the end of theselect
  statement in your Pass-Through
• Sysdate TimeStamp
• This TimeStamp field will record the date and
  time that your Pass-Through was last run
• Every record in your Table will have the same
  TimeStamp (even if it takes a while to run)
• Use an Append Query, and keep a history
• Valuable for statistical analysis!

20
Easy Access to ObjectAccess

• ProblemOracle SQLNet (or Net8) isnt set up on
  every workstation because we use web-enabled
  Banner
• ProblemI like ObjectAccess, but its just too
  slow
• ProblemThe Banner and Network administrators
  dont like me running reports during the day

21
Easy Access to ObjectAccess

• SolutionCreate a simple Data Warehouse!
• Create a Pass-Through Make Table that creates an
  unrestricted copy of ObjectAccess output
• Select from as_student_data
• Where term_code_key 200220
• And dont forget the Manditory Conditions, like
  Term Code Key

22
Easy Access to ObjectAccess

• Create a Macro to Delete and Append
• Put this new database in a shared directory on a
  Windows server (that has ODBC set up)
• Use the windows Task Scheduler to start this
  process daily (after hours) with the command line
  qualifier /x MacroName
• Use a LINK to this database from your
  workstation, and you have a simple warehouse!

23
Final Comments

• Use Macros!
• You can write entire applications without
  programming a single line of Visual Basic
• Learn more SQL!
• An understanding of SQL gives you an
  understanding of the inner workings of Oracle and
  Banner
• And youll write better reports!

24
Final Comments

• MS Access is a very broad, powerful application
• But its quirky
• If a technique doesnt work, try something
  similar
• Maybe youre running into a quirk
• And PLAY with Access!
• Mess around! Try new things! Use techniques you
  havent used before! Get an Access book!

25
Questions?

• Questions and (hopefully!) Answers
• Would you like to see something demonstrated?
• Any Tips or Tricks you'd like to share?
• Suggesting a topic for next year?

26
Thank You!

• Dan DeBower
• ddebower_at_sct.com
• Please fill out the Evaluation Form
• Evaluation Code 245
• Check out the MS Access Reporting (BOF) session
• Wed, 1000am 1130am in Marquis NW (M)