More Pass-Through Queries! Evaluation Code 245

1. More Pass-Through Queries!Evaluation Code 245 Dan DeBower Technical Consultant SCT Tuesday, March 26th, 2002 8:00am – 9:30am

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: I’m 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 Object:Access

5. Review • Use the latest version of MS Access:if you’re using Office2000, make sure you’re 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: ODBC;DSN=???;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 ODBC;DSN=Banner;DBQ=PROD;

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 doesn’t 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, it’s 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 don’t want to destroy the table you’ve 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 doesn’t work if the Query doesn’t 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 you’ve got a single Macro that • Doesn’t destroy any changes you’ve 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 Object:Access • Problem:Oracle SQLNet (or Net8) isn’t set up on every workstation because we use web-enabled Banner • Problem:I like Object:Access, but it’s just too slow • Problem:The Banner and Network administrators don’t like me running reports during the day…

21. Easy Access to Object:Access • Solution:Create a simple Data Warehouse! • Create a Pass-Through Make Table that creates an unrestricted copy of Object:Access output Select * from as_student_data Where term_code_key = ‘200220’ • And don’t forget the Manditory Conditions, like Term Code Key…

22. Easy Access to Object:Access • 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 you’ll write better reports!

24. Final Comments • MS Access is a very broad, powerful application • But it’s quirky… • If a technique doesn’t work, try something similar • Maybe you’re running into a quirk… • And PLAY with Access! • Mess around! Try new things! Use techniques you haven’t 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@sct.com Please fill out the Evaluation Form Evaluation Code 245 Check out the MS Access Reporting (BOF) session Wed, 10:00am – 11:30am in Marquis NW (M)