Docstoc

Introduction to MS Access - Office of Grant and Research

Document Sample
Introduction to MS Access - Office of Grant and Research Powered By Docstoc
					Introduction to MS Access
                   Steve Shapiro
            Computer Services Manager
  Office of Research Services and Administration
               University of Oregon
         Region VI/VII NCURA Conference
                    April, 2011



                                                   1
             What is a Database?
• a comprehensive collection of related data
  organized for convenient access, generally in a
  computer

  dictionary.reference.com/browse/database
• This definition was found via a search of Google’s database
• Databases are all around us, and all of us interact with them
  on a daily basis.
• Who said you can’t make your own?


                                                                  2
            Where does MS Access fit?
•   http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
    lists history for about 50 database programs

•   Popular Databases:

    Oracle, Microsoft SQL Server, IBM DB2, FileMaker, Ingres, MySQL, Corel Paradox, Dbase III,
    R:Base

•   MS Access is a low-end to mid-tier database application
     –   It runs independently on workstations for single users
     –   It runs on servers that can be accessed by multiple users at the same time on a network
     –   It provides a user friendlier front end to more powerful database applications such as Oracle and MS SQL Server




                                                                                                                           3
           What is MS Access?
• MS Access is a relational database, meaning
  that data is stored in multiple tables that are
  related to each other.
  – PI’s in one table, their awards in another table.
    The database maintains a connection between the
    tables using something called a ‘key’ – a number
    that is the same in both tables.




                                                    4
                      History
• Access is one of the few products originally developed
  by Microsoft
• Development began in the mid 1980’s
• Combined with other databases that Microsoft
  licensed such as R:Base and FoxPro
• Released in November 1992 as a single user application
  for very small (<10mb) files
• Became dominant database for windows when
  competitors failed to transition to Windows
  successfully.
• Now a very stable and robust application, scaling from
  1 to many users and up to 2g of data in each file
                                                       5
               Today’s Session
• Will cover < 1% of MS Access capability
• We won’t talk about
  – Security
  – Advanced anything
     • Forms, queries, front/back ends, modules, macros
  – Interfacing with other databases
  – Advanced Data Validation
  – Questions on these and other topics are welcome!

                                                          6
Relational Database?




                       7
Keys relate information
  in different tables




                          8
      5 Major Components of Access
              Access Database Objects

•   Tables
•   Queries
•   Forms
•   Macros
•   Modules




                                        9
                    Tables
Tables hold the information, called data




                                           10
                      Tables - Data Types
•   Text Use for text or combinations of text and numbers, such as addresses, or for numbers
    that do not require calculations, such as phone numbers or postal codes (255 characters)
•   Memo Use for lengthy text and numbers, such as notes. Stores up to 63,999 characters
•   Number Use for data to be included in mathematical calculations, except money
•   Date/Time Use for dates and times
•   Currency Use for currency values and to prevent rounding off during calculations.
•   AutoNumber Use for unique sequential that are automatically inserted with a new record
•   Yes/No Use for data that can be only one of two possible values, such as Yes/No, True/False,
    On/Off.
•   OLE Object Use for OLE objects (such as Microsoft Word documents, Microsoft Excel
    spreadsheets, pictures, sounds,
•   Hyperlink Use for hyperlinks (hyperlink: Colored and underlined text or a graphic that you
    click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on
    an intranet. Stores up to 2048 characters.
•   Lookup Wizard Use to create a field that allows you to choose a value from another table or
    from a list of values using a combo box


                                                                                                 11
                        Table Encounter
• The prospect of creating multiple tables almost always
  intimidates beginning database users. Most often, beginners
  will want to create one huge table that contains all of the
  information they need, similar to an Excel spreadsheet.

• When thinking about which fields to add to a table, a good
  first guess is: What piece of information will only occur once?
   – Such as
       • A person will probably only have one first name, though it may change
            – First_Name is a good candidate for the “PI” table
       • A table needs at least on field that never duplicates in the same table
            – Two or more people can have the same first, last and middle names
       • A person may have more than one award (per name)
            – The award title is not a good candidate for the PI table, since we don’t know how many awards a person
              may have



                                                                                                                 12
                                  Queries
• Queries select and modify
  specific data

• “Queries convert data to
  information”

• They are used to populate forms
  and reports

• MS Access uses a visual query
  wizard to help novice (and
  advanced!) users construct
  queries




                                            13
Simple Queries From a Single Table
• Select Award_Title from
  Awards where (Award_Title
  Like “Exploring*”) and
  (Closed = False) Order By
  Award_Date;




                                 14
 Simple Queries from Multiple Tables
• Set up relationships
  (Access may make you do this and if it does, will help you with
  a wizard)




                                                                15
     Simple Queries from Multiple Tables
• SELECT
  PIs.[First Name],
  Awards.Award_Title,
  Awards.Award_Date,
  Awards.Closed
• FROM
  PIs
  INNER JOIN Awards
  ON
  PIs.ID = Awards.PI_ID;




                                           16
                 Action Queries
• Queries can update, add or delete records
  from a table




• DELETE * FROM Pis WHERE (PIs.[First Name])="No Research";




                                                          17
                    Forms
• Forms let you enter and display specific data
  in a customized format.




                                                  18
         Basic Types of Forms
• Single Record




• Datasheet



                                19
               Form Controls
• Bound Controls
  – Are directly ‘attached’ to the data and will update
    as you leave the field on the form
• UnBound Controls
  – Have to be manipulated with program code
• Calculated Controls
  – Do not exist in the data tables. They are derived
    based on other controls or fields in the database

                                                          20
                  Types of Controls
•   Text Box: Displays and allows user to enter data
•   Label: Displays static text
•   Button: Does something by runnning macros or VBA Code
•   Combo Box: A drop down list of values
•   List Box: A list of values
•   SubForm: a form of related data within a form
•   Shapes: boxes, lines, images
•   Check Boxes: Yes/No or True/False
•   Option Groups: choose one option from a group
•   Toggle Buttons: enabled or not enabled
•   Tabs: for forms with lots of data, multiple tabbed pages
•   Charts: Display data in graphical format
•   More…

                                                               21
               Properties
What can a Control look like and how can it act?




                                                   22
Events – Making Access Do Something




                                  23
       Visual Basic Code in a Form
• Behind a button




  Private Sub btn_Close_Click()
    DoCmd.Close acForm, frm_PI_Awards
  End Sub




                                        24
Data Validation and formatting
          In the Table




                                 25
Data Validation
   On the Form




                  26
                  Macros
• Wizard driven tool to automate repetitive
  tasks
• Can be very simple or very complex




                                              27
                      Reports
• Reports display and print formatted data
  – Text
     • Form Letters, columnar reports, grouped reports
  – Graphics
  – Sub Reports
  – Export to other formats, such as spreadsheet,
    word processing
  – Wizard driven or drive yourself


                                                         28
Designing a Report




                     29
                   Modules
• Modules contain Visual Basic for Applications
  program code as subroutines or functions
• Visible from anywhere in the Application:
  – tables, queries, forms, macros and reports
• Subroutines typically do something
• Functions do something and return a result



                                                  30
                             VBA Code
• Wizard and context assistants help write code
• Almost, but not quite understandable
  Public Function Activate_Detail_Form(My_Form As Form)
  Dim db As Database
  Dim rec As Recordset
  Set db = CurrentDb()
  Set rec = db.OpenRecordset("tbl_sys_Color_Scheme", dbOpenSnapshot)
  rec.MoveFirst
  My_Form.FormHeader.BackColor = rec!Detail_Header_BackGround_Color
  My_Form.Label1.ForeColor = rec!Detail_Header_Font_Color
  rec.Close
  Set rec = Nothing
  Set db = Nothing
  My_Form.Repaint
  End Function
                                                                       31
      Getting Started with Access
• Database development is quite unlike most other
  programs used to create information in a computer,
  such as word processing or spreadsheet.

• Database development requires prior knowledge

• A beginning user opening Access for the first time
  likely has no idea where to start. Unlike Word or
  Excel, you can’t just ‘start typing’

                                                       32
               Create Ribbon
• Starting point for all new objects in the
  database




                                              33
                        Conventions
• Application developers like to label objects in their
  applications in such a manner that when they go back to look
  at it several years later, they can figure out what they’ve done.
• We use prefixes and suffixes when we name things:
   –   Tables: tbl_Awards
   –   Forms: frm_Awards
   –   Buttons on forms: btn_Form_Close
   –   Reports: rpt_Reports
   –   Text fields within a table: PI_Name_txt
   –   Integer Fields within a table: Award_Number_txt
• Spaces in object names are allowed, but dangerous.
  Use underscores “_” or enclose object names in brackets
  [tbl Awards]                                                   34
         Educational Materials
• Office.microsoft.com
  – Templates  Databases

• My favorite Book:
  – Microsoft Access 2010 Bible
     • By Michael Groh


• Search Engine: [Access 2010 and your question]

                                                   35
36

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:12/5/2012
language:Unknown
pages:36
Lingjuan Ma Lingjuan Ma
About