Working with Databases
Project 2: Access Basics

 Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

Project Objectives

  • Know the parts of the MS Access window
  • Open and close MS Access.
  • Create a simple table manually
  • Add and edit records
  • Sort records
  • Filter records by selection, by form, and with Advanced/Sort
  • Create a simple query with Query Wizard
  • Edit a simple query to sort & filter results
  • Create a form with AutoForm
  • Create a report with AutoReport
  • Create a data access page with a wizard
  • Print a datasheet, a form, a report, a data access page
  • Create a table with Table Wizard
  • Change a table's design
  • Use the Lookup Wizard
  • Create a relationship in the Relationships window
  • Enforce referential integrity
  • View subdatasheets


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics
    Access ObjectsTo subtopics
    RelationshipsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Project 3: Tables & Queries

Project 4: Forms & Reports

Search  
Glossary  
Appendix

What you will do

In this project you will create a simple database with two tables, one query, one form, one report, and one data access page.  Not a typical database!

You will use some of the wizards that Microsoft Access includes to help you get started. The wizards are only a starting point! They save you a lot of time when you are first learning how to create the parts of a database. You will usually want to make changes, however, to get what you really want.

The example databases are in your resource files, if you would like to open them to see them at work.

Microsoft Access Limits

There have to be limits to the number and size of many things about MS Access. The limits are fairly generous, but, of course, there are always situations that exceed any limit you care to pick! Below is a table of some of the limits you might run into.

Total size of database 2 Gigabytes
including space needed for system objects that the database requires
Number of objects 32,768
Total of all tables, queries, forms, reports, indices, etc. It's hard to count up the number of indices!
Characters in name of table or field 64
Fields in a table 255
Open tables 2048
including internal tables opened by Microsoft Access
Characters in a text field 255
Characters in a memo field 65,535 when entered through user interface.
1 gigabyte if entered programmatically
Size of OLE object 1 gigabyte
Tables in a query 32
Fields in a recordset 255
Sort limit 255 characters in 1 or more fields
Levels of nested queries 50
Characters in a label in form or report 2048
Characters in text box 65535
Form or report width 22"
Section height in form or report 22"
Number of levels of nested forms or reports 7
Number of printed pages in report 65535

There are more limits than these! Look in the Help files for MS Access under Getting Started > Microsoft Access Specifications.

Real World Limits

Your database may suffer from serious sluggishness long before it reaches the maximum size. The larger the database, the more important good design and good indexing become. Otherwise, the simplest action can take a long time to perform.

While there is no limit to the number of records in a table, the whole database has to fit inside 2 Gigabytes of space. How many records you can squeeze in will depend on how much data goes with each record.

When your database gets close to the maximum for any of the limits, it's time to think about switching to a more advanced database program.

Access Project

As you look through the Help files, you will see references to Access projects, which sometimes have different features or methods that Access databases. An Access project is, according to the Access Help files: "An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views."