Transcript Using ODBC with Access - psug-mi

Using ODBC with
Microsoft Access®
- Bob Elenbaas
Lake Orion HS
Using ODBC with
Microsoft Access
Who am I?
- teacher of computer programming and
computer applications
- PowerSchool support for secondary buildings
(attendance, grading, scheduling)
- assist with data collection and analysis
- high school tech support
Using ODBC with
Microsoft Access
Lake Orion?
-
Oakland County – just north of Pontiac
8300 students / 650 teachers
7 elementaries, 3 middle schools, 1 high school
on PowerSchool for 2 years (after CIMS and
Centerpoint)
Using ODBC with
Microsoft Access
Goals:
• To examine PowerSchool’s data structure
• To see an ODBC connection to
PowerSchool in action
• Discuss advantages and disadvantages to
using ODBC
• To look at how to use ODBC with Access
• To look at accessing PowerViews
Using ODBC with
Microsoft Access
ODBC = Open Database Connectivity
provides a standard software method for
using database management systems.
The designers of ODBC aimed to make it
independent of programming languages,
database systems, and operating systems.
Using ODBC with
Microsoft Access
Why use ODBC?
• Making direct connection to the database
– fast; nothing inbetween
• Allows use of existing tools (like Excel,
MSQuery, Access) to look at the data
• Allows use of SQL (structured query
language
Using ODBC with
Microsoft Access
• Gives you the control
Using ODBC with
Microsoft Access
Data structure of PowerSchool
• To see a quick view of data structure, type
?ac=structure after the URL on any
PowerSchool screen
• Full data dictionary available on
Powersource
Using ODBC with
Microsoft Access
Install ODBC
• Get instructions from PowerSource and
PowerDataSolutions.org and follow closely
Using ODBC with
Microsoft Access
Quick Export, DDE, or ODBC?
• Just a few student fields? QuickExport
• Data from other tables or fields not on
QuickExport field list?
DDE
• More complicated needs? ODBC
Using ODBC with
Microsoft Access
Examples
1) Get student name, gender, and grade
level for one building
- using SQL in Access
Using ODBC with
Microsoft Access
2) Find all students with a D or E.
Find all seniors with an E.
Find all students with a D or E in math.
Using ODBC with
Microsoft Access
3) Access PowerView tables to see
distribution of students by grade level
and ethnicity.
Using ODBC with
Microsoft Access
4) Gather attendance data for select date
range.
Using ODBC with
Microsoft Access