Database management. MS Access 2013 Build-from-scratch. Book:
Lesson 6. On-Your-Own Tasks: Database Creation.
: 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.
- Database from Scratch
- More Tables
Info to use/import as desired: Names Excel spreadsheet
- 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.
- 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.
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.
- Analyze Performance
- Object Dependencies
- Database Security
- Database Utilities
- Building databases from scratch
- 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.
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
- 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.
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
- 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
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 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 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
- 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.
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
- 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.
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
information to rebuild the database structure if that is ever necessary.
- Analyze Performance: This utility analyzes the objects in your database
three types of feedback: ideas, suggestions, and recommendations. and you
can then optimize the file by following through on any of the suggestions
- Analyze Table: This wizard tests database tables for compliance
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:
Type in Customer Orders (Read Only)
Change to Continuous Forms
Allow Form View
Allow DataSheet View
Change to NO
Change to NO
Change to NO
Database Building Basics
EXAMPLE of HappyPaws Database Layout - PDF
We'll look over queries, forms and reports info as needed in class today.
- 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!