Introduction to ms access
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

Introduction to MS Access PowerPoint PPT Presentation


  • 56 Views
  • Uploaded on
  • Presentation posted in: General

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. What is a Database?.

Download Presentation

Introduction to MS Access

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Introduction to ms access

Introduction to MS Access

Steve Shapiro

Computer Services ManagerOffice of Research Services and Administration

University of Oregon

Region VI/VII NCURA ConferenceApril, 2011


What is a database

What is a Database?

  • a comprehensive collection of related data organized for convenient access, generally in a computerdictionary.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?


Where does ms access fit

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


What is ms access

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.


History

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


Today s session

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!


Relational database

Relational Database?


Keys relate information in different tables

Keys relate information in different tables


5 major components of access access database objects

5 Major Components of AccessAccess Database Objects

  • Tables

  • Queries

  • Forms

  • Macros

  • Modules


Tables

Tables

Tables hold the information, called data


Tables data types

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


Table encounter

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


Queries

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


Simple queries from a single table

Simple Queries From a Single Table

  • SelectAward_Titlefrom Awards where (Award_TitleLike“Exploring*”) and(Closed = False) Order By Award_Date;


Simple queries from multiple tables

Simple Queries from Multiple Tables

  • Set up relationships (Access may make you do this and if it does, will help you with a wizard)


Simple queries from multiple tables1

Simple Queries from Multiple Tables

  • SELECTPIs.[First Name], Awards.Award_Title, Awards.Award_Date, Awards.Closed

  • FROMPIs INNER JOIN Awards ONPIs.ID = Awards.PI_ID;


Action queries

Action Queries

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

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


Forms

Forms

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


Basic types of forms

Basic Types of Forms

  • Single Record

  • Datasheet


Form controls

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


Types of controls

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…


Properties what can a control look like and how can it act

PropertiesWhat can a Control look like and how can it act?


Events making access do something

Events – Making Access Do Something


Visual basic code in a form

Visual Basic Code in a Form

  • Behind a button

Private Sub btn_Close_Click()

DoCmd.CloseacForm, frm_PI_Awards

End Sub


Data validation and formatting in the table

Data Validation and formattingIn the Table


Data validation on the form

Data ValidationOn the Form


Macros

Macros

  • Wizard driven tool to automate repetitive tasks

  • Can be very simple or very complex


Reports

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


Designing a report

Designing a Report


Modules

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


Vba code

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


Getting started with access

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’


Create ribbon

Create Ribbon

  • Starting point for all new objects in the database


Conventions

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]


Educational materials

Educational Materials

  • Office.microsoft.com

    • Templates  Databases

  • My favorite Book:

    • Microsoft Access 2010 Bible

      • By Michael Groh

  • Search Engine: [Access 2010 and your question]


  • Login