Transcript A Typical Application

Using Microsoft ACCESS to develop
small to medium applications on
campus
Agenda
1. Review various applications used on campus
2. Discuss briefly a typical application
3. Review the TAMS (Time and Materials System) used in
IST
4. Review the pros and cons to building an application
versus buying one
5. Build a simple application using ACCESS 2010
6. Questions?
Systems used on campus
• Over 200 applications on campus (according to 2007 IST
survey)
• Some applications are for storing stats, inventory,
tracking expenses, maintaining lists
• Approximately 30 applications using some form of
ACCESS
A Typical Application
•
•
•
•
•
•
•
Logon screen
Menus
Data Entry and Validation
Data lookup, edit and delete
Reports
Batch Interface (Macros, VisualBasic for Access)
Advanced features:
– Data Import/Export from multiple sources
– Integrate to other systems (i.e. email, Sharepoint)
TAMS demo
•
•
•
•
•
•
Login
Create a request
Show lists
Show reports
Display an invoice
Show documentation
Buy versus Build ?
• Not simple
• Many factors to consider besides price and time
– Important to understand short term versus long term benefits and
challenges
– Training, customization, documentation, upgrades, cost
Buying software
Pros
•
•
•
•
‘Faster’ and ‘easier’ to deploy
The vendor is an ‘expert’
May belong to user groups to share information
Vendor assumes most responsibility. No need to employ
technical staff
• Vendor stays on top of changes such as new taxes or
government requirements (where applicable)
Buying software
Cons
•
•
•
•
•
•
•
Cost, initially, ongoing maintenance, additional licenses and requested
changes
Dependent on vendor’s expertise
Sometimes must customize software to fit your needs. This introduces other
issues with the vendor as you become ‘unique’ and forces you to become
really dependent on vendor support
May need to spend time up front to help the vendor ‘understand’ your
system and requirements
Software may be ‘proprietary’
Software versions must be in sync with your environment. (i.e. you may be
forced to upgrade your environment in order to keep using software)
You may be forced to continually upgrade or lose support from the vendor
Building software
Pros
• You can build an application exactly how you want it
• You are in complete control of the application and future
changes
• Cheaper solution as there are no license or maintenance
fees.
• Local support of the environment from end-to-end
(hardware, operating system, data warehouse).
• Changes based upon all users input.
Building software
Cons
•
•
•
•
May take longer to develop and deploy
May initially cost more to build than to buy
You are responsible for all problems
You may need to staff some technical expertise and
skills to support
Microsoft ACCESS
Pros
•
•
•
•
•
•
•
•
•
Cheaper. The university has an enterprise license. No extra fees.
It’s easy and fast to learn, use and deploy new applications. Manual is
online at:
http://testtube.uwaterloo.ca/trellis/safari.cfm
You can use existing applications to build new ones
You can integrate to other Microsoft applications such as Outlook and
Sharepoint
You can use a SQL database (unlimited space)
Supported by Microsoft. Less chance that they will discontinue support or
go out of business
Potentially, we can share information on campus and help each other
The technical staff just need to understand ACCESS. It doesn’t really matter
what the application is for.
Supports batch processing
Microsoft ACCESS
Cons
• Does not support more than 20 users very well
• 2Gb limit on ACCESS table sizes
• Some ACCESS queries do not work with SQL
databases. Only with ACCESS databases.
• Microsoft could change or discontinue the product
(similar to VB 6 to VB .Net)
• Requires a technical person to support
Typical Access Architecture
Single Access Database
Split Access Database
- 1 File
- 2 Files
Forms/Reports
Data
Forms
Reports
Access
File
Data File
Typical Access Architecture
Single Access Database
• 2 GB file size limitation
• Data and Interface stored in 1
file
• Good for simple databases
with non-mission critical data
• Updates require no one to be
in the database at that time
Split Access Database
• 2 GB file size limitation
• Data and Interface are split
into two Access files and are
linked together
• Provides data security so
users cannot manipulate data
file without proper access
• Easier to make updates to
interface without impacting the
data structure
Reliable Access Architecture
SQL Server
Data
Access Files
Forms
Reports
Custom
Interface
- Single data source that can be
utilized by multiple interfaces
- Provides better data security
- SQL managed by IST
- No limitations with storage
- Updates are easier as each
user is required to run their
own copy of the interface, but
does make it difficult to keep
track of versions being used
Setting up an application in ACCESS
• Templates
• Charitable donations
• Blank database
Questions??