Week 7: August 11-15

TUES   THURS   TO DO

WEEK'S OBJECTIVE

Database management. MS Access 2013 Build-from-scratch. Book: Lesson 6. On-Your-Own Tasks: Database Creation.

NOTE : You need to attend ALL the classes during all of Week 7 and Tuesday of Week 8, since I will use class time to demonstrate how to start an example basic database from scratch.

Tuesday

GAMEPLAN

  • Database from Scratch
  • More Tables
  • Relationships
  • Queries

DISCUSSION

Info to use/import as desired: Names Excel spreadsheet

Table Steps/Tips:

  • Create 1 core table to at a time. Make sure you really design the heck out of it - all details, before moving on to the next.
    • Examples: HappyPaws Customers, HappyPaws Products
  • Name simply/effectively. Ex: Customers Table.
  • Create basic columns, either in datasheet or design view.
  • Decide on the ID number/method for each record, make UNIQUE so that it cannot be duplicated.
  • Set your Primary Key
  • Use simple, no-spaced Column names. Keep using these kind of name consistently throughout yourt Database.
  • Make sure to break up all appriopriate information into separate columns you might need to sort, like LastName, MiddleInit, FirstName; and ProdName, ProdCategory, UnitPrice, CasePrice
  • Use only columns in this table than cannot/should not be separately typed into another table.
  • Set every Data Type right away: type, field size, input mask format, etc.
    • IF you need a new field to start with a specific Default Value (like Reorder Number 5), add this.
    • IF you need a new field to be required (other than Autonumber), use the Validation Text to type something like (Required Info)
    • Make sure that Indexed is set to Yes, NO Duplicates.
  • Use a short description in EVERY table's field - as if someone else will need to see it later.
    • For instance, in ANY field that might become a Foreign key in another table, note this in the Description field.
  • HOT: In this table, create one (1) single Test Case record/row that is appropriate for the table. This is a keeper to help try out Data Types and Test Relationships BEFORE you import/create mass data.
  • NOTE: Once you have a well-functioning contact table, you can Copy and Paste it into the Database, then RENAME the copy to the appropriate name for the new use of the table.
    • Be 100% certain to go through the Reusable table to CHANGE all Column Headings, appropriate Data Type lengths and info, and Comments so they are ready for the New table as it WILL be used.
    • DO NOT try to "reuse" tables for every purpose - do Like with Like, like Customers, Vendors, Supplies.
  • When you COMPLETE creating your tables, Set and Test all relationships with your Single Test Case rows to verify the tables and data formats all work properly. DO NOT IMPORT or ADD actual data until you fully test the basic tables and relationships FIRST.

Special Note: LookUp Tables

A Lookup table is really good for common information you want to be selected from a dropdown choice box when someone inputs NEW records. However, keep in mind:

  • Create a LookUp table for ONLY brand new/unimported information in a database, like specific Category Names, etc.
  • DO NOT try to create a LookUp table for information that will be imported en masse - there will be horrid conflicts.
    • For instance, you might want a State Look-Up table, right? DO NOT create one to link to a Customer or Vendor Table IF YOU PLAN TO INPUT EXISTING addresses and states. Create one to use as a LookUp table for only empty tables you are inputting the info in for the first time.

Think of Table Relationships as you plan:

  • What tables will you need to ask questions about?
  • Are the Data Types/Formats in each of these tables the same format for when you try to relate the tables? Ex: (CustID in Customer Table, and CustID in Orders Table?)
  • What kind of Joins do you want your query results to show?
  • Be sure to ALWAYS choose Referential Integrity in your Relationships.
  • Choose Cascade Update Related and Cascade Delete Related when creating Relationships.
  • After you set Relationships, and as you create Queries, Forms, and Reports, check the Object Dependencies to see how the objects are related to each other.
  • Set up ALL POSSIBLE relationships before adding data to your tables.
  • DO NOT IMPORT or ADD actual data until you fully TEST the basic tables and relationships FIRST.

Query Tips

  • Use the Query Wizards as much as possible, so you can get as much built-in help from Access to set up the Queries properly.
  • For EACH query, keep in mind:
    • What to ask for.
    • How the tables needed in the Query are related.
    • How you want the information to appear.
    • Which tables to choose from that have the information.
    • Ensure that the tables have a defined relationship in the Access Relationships window.
    • If the tables do NOT have a defined relationship, you need to create one.

Query Steps

To work with records from more than one table, you need to create a query that joins the tables. The query works by matching the values in the primary key field of the first table with a foreign key field in the second table.

  • Check the Tables you think you need in DATABASE TOOLS Tab/Relationships. See the relationships, and consider the columns you will need.
  • Use CREATE Tab/Query Wizard/Simple Query.
  • Choose the table to start with.
  • Choose the needed columns.
  • Choose the second needed table.
  • Choose the needed columns.
  • Name the query.
  • Choose Open query to view info
  • See the NEW query table, and sort/filter it further as desired.
BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Thursday

GAMEPLAN

  • Importing
  • Exporting
  • Analyze Performance
  • Locking
  • Object Dependencies
  • Splitting
  • Database Security
  • Database Utilities
  • Building databases from scratch

DISCUSSION

Database Options
  • In your database, choose File/Options/Current Database
  • To Display a Form as the first thing your audience sees, choose the form you make for the Welcome/Navigation form.
Importing

You can import data into a database from other sources. This is done so that existing data does not need to be completely recreated. Note that any table you import from other sources will have the same records and columns in Access, so if you plan to create a lean relational database, you would separate the data into appropriate table before importing.

  • Access: You can import tables from other Access databases.
  • Excel: This is a common format to import from. You might be panning to translate Excel tables/spreadsheets into a new database, or you might use Excel as an intermediate format between Access and another hard-to-export-from database program.
  • Text files: You might acquire tabluar data from the web or other lists that have been saved as comma delineated files. These can include contact lists from webmail, like Gmail.
  • There are additional database programs that you can import from.
  • You would use External Data /Import & Link area and the appropriate program button (Access, or Excel, or Text File) to use the Get External Data Wizard.
Exporting

You can also export data from an Access database to other programs. Usually you would export only some of the information, such as a table from a query response.

  • You can export single items, like one table, form, or query.
  • Only the main table or main form of a set that includes a subtable or subform will be exported.
  • You can also export to a PDF and similar files that allow the viewing but not editing of report or table information in a database.
  • You would use External Data / Export area, and the appropriate program button (Access, or Excel, or Text File) to use the Export Wizard.
Analyze Database

You can analyze the performance of a database. The idea behind this is to give you tips that will help the database be more efficient. You might NOT want to use the tip, but it wil be there.

  • Use Database Tools/ Analyze Performance, and in the pop-up Selections Window, choose All Object Types
  • Review the output report to see which tips make sense for you.
    • It is good to follow-through on the relationships suggestions.
    • You can take the suggested changes for datatypes with a grain of salt - especialy if you seriously considered which data type you wanted to use in the first place.
    • When your database feels complete, you can try the MDE tip.

Object Dependencies

Object Dependencies are extremely important in databases. This tool tells you how every table, query, form and report is interdependent on each other. If you EVER consider breaking any table relationships because of problems, check the Object Dependencies first:

  • Select the table, query, form, or report you want to evaluate in the Navigation Pane
  • Choose Database Tools / Object Dependencies
  • View the information in the Object Dependencies output sheet
  • Tp get a fuller visual of all relationships, be dure to check the Database Tools / Relationships and be sure that ALL tables are showing.
Splitting

Splitting a database is something you would do if several people are using the same database, and processing gets slow/clunky. The idea is to keep the database data (tables of core data) on a central server while users can use a copy of the rest of the database to do their work.

  • Front-end: This contains the forms, queries, and reports that people use to work with database data.
  • Back-end: This contains the tables that store all the data in the database.
  • Access can move data required by a database object over the network much faster than it can move the entire object.
  • Splitting can also help protect the core data from problems that might affect its reliability and usability.
  • Use the Database Tools / Move Data, and choose the Access Database button in order to start the Database Splitter wizard.
Database Security

A database can be the most important asset a company or institution has. It contains all the information needed for business and information productivity, like financial records for an entire payroll department, products/services records and orders for an entire product line, medical records for a hospital group, etc. Imagine if s company like Google, Amazon, or Boeing lost its core databases?

  • Splitting databases is one way to protect the security of the core data (see above info).
  • Assigning secure passwords is another way to protect databases from being entered and updated/mishandled by unauthorized personnel. This is so that the database can be set to view only, but it is not useful for daily database work.
    • Close the database.
    • On a PC, choose File / Open / Computer / Browse, and find the database at its location.
    • THEN, Select the database, AND click the little down arrow by the Open button, then choose Open Exclusive.
    • Choose File / Info, and choose the Ecrypt with Password option.
    • Keep in mind, this encrypst the whole database for editing AND even for viewing.
  • Databases that may be moved or distributed in some part can be protected by distributing as an executable file rather than a database file. Saving a database as an ACCDE file compiles and compacts the resulting database.
    • With the database open, choose File / Save As / Make ACCDE file.
    • When the Save As dialog box open, note how the database will be named with an .accde extension.
  • Databases always need a secure backup. Companies often back-up databases on a daily basis.
    • With the database open, choose File / Save As.
    • When the Save As dialog box open, note how the database will be named by its name AND the date added to its name.
Database Utilities

Normal database use can affect the internal structure of a database so that it becomes fragmented, resulting in a bloated file and poor use of disk space. This can in turn lead to a corrupted database. Access has several utilities to help keep a database healthy and running well.

  • Compact and Repair Database: This utility first optimizes performance by
    rearranging how the file is stored on your hard disk, and attempts to
    repair any corruption in tables, forms, & reports.
  • Database Documenter: This utility produces a detailed report containing enough
    information to rebuild the database structure if that is ever necessary.
  • Analyze Performance: This utility analyzes the objects in your database and offers
    three types of feedback: ideas, suggestions, and recommendations. and you can then optimize the file by following through on any of the suggestions or recommendations.
  • Analyze Table: This wizard tests database tables for compliance with standard
    database design principles, suggests solutions to problems, and implements
    solutions at your request.
Securing your Forms

You can make your forms read-only, using the Design View and Properties sheet:

  • Open the Form you want to secure.
  • View the Form in Design View.
  • Open the Form's Property Sheet.
  • In the Selection Dropdown box, choose "Form"
  • Choose the DATA tab.
    • Set the property for Allow Deletions: NO.
  • Choose the FORMAT tab in the Property Sheet, and choose as shown below:

Tab

Property

Value

FORMAT

Caption

Type in Customer Orders (Read Only)

Default View

Change to Continuous Forms

Allow Form View

Yes

Allow DataSheet View

Change to NO

Record Selectors

Yes

Navigation Buttons

Change to NO

Control Box

Change to NO

 

DEMO

Database Building Basics

EXAMPLE of HappyPaws Database Layout - PDF

We'll look over queries, forms and reports info as needed in class today.

BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

TO DO

  • MIC FINAL: Full Database, due BEFORE 12:30pm Thursday, August 21. 120 Points. I will not accept late uploads or assignments for any reason. NO!
Resources
BACK TO TOP