Access contains these basic object types...

• Tables
  • Used to define and store data
  • Consist of records where each record can have several fields
  • Contains parameters for default values, field type and other rules.
• Queries
  • Types
    • Select - Used to select data from a table or tables
    • Update - Updates fields in existing records
    • Append - Adds new records to tables
    • Delete - Deletes records from tables
    • Make - Creates a new table, commonly used for temporary tables
  • Options
    • Criteria values are use to select/filter specific data
    • Data can be sorted on a field or fields
    • Data can be summarized or 'grouped' using the Totals ption
    • Multiple tables can be used in the same query
• Forms
  • Used to enter, maintain, delete or simply display data
  • Data is accessed directly from a table, SQL statment or queries
  • A Form can contain subforms
  • Also used as a selection tool for other forms, reports, and processes
  • Controls can be added to ensure data integrity and validation of data
• Reports
  • Reports are used to display data in preview mode or print to a printer
  • Reports can contain subreports
• Macro
  • Used to perform calculations or flow control
  • Can also be used to open/close forms, reports, queries
• Program Modules
  • Used to perform calculations or flow control
  • Can also be used to open/close forms, reports, queries
  • Executes faster than macros
  • More capabilities than macros

top of page

The following is our recommended naming convention. It is important that you define a naming convention, whether you use our recommendations or create your own. It is much easier to keep track of what you have done, especially several months from now. It also makes it easier for someone else to understand and modify your work.

• Rule 1
  • For all objects, use only letters, numbers or the underscore. Access will allow you to use almost any character or space, but some notations can cause problems. Using only letters, numbers and underscores keeps everything safe.
  • For values that represent a percentage, it is tempting to use the % symbol. Example: Tax%. Don't get tempted. Instead name it TaxPerc
  • Same goes for Customer Number. Don't use Cust#. CustID or CustNum is the recommended choice
• Rule 2
  • Try to keep the names short, but still meaningful
  • Abbreviate if you can
  • If you have objects that could have similar abbreviations such as INV for Invoices or Inventory, pick a different abbreviation.
  • Example: Use ARINV in place of INV for Invoices
• Tables
  • Table Names
    • Only use all uppercase letters.
  • Example: CUSTOMERS, VENDORS, INVOICES
  • Reason: This will help program modules to be more readable. When you see a reference to an all upper case name, you will know it refers to a Table and not to a Query.
  • Field Names
    • Use all lower case, except for the first letter of each word in the name
    • Examples: CustNum, CName, DueDate, EmpID
  • Reserved Names
    • There are several reserved words in Access that should NEVER be used as field or object names. The most common are...
      • Name
      • Print
      • If
      • Else
      • Page
      • Date
      • Today
      • While
      • Basically any word that may be a common program command.
      • Access does not warn you when you use a reserved word. The problem will appear later when you discover a form or report doesn't work correctly.
  • Captions
    • Because you don't want the user viewing funky names, like CName, place a user friendly description in the caption property.
    • Example: Customer Name
    • Be careful of short fields like State, which is normally only 2 characters, that you don't use a long caption name. It will cause columns to be displayed wider than necessary on queries, forms and reports. In this case a caption of ST and a field name of State is acceptable and understood.
• Queries
  • First letter of the query name should be a single lowercase letter that represents the type of query...
    • q = select or standard query
    • a = append query
    • u = update query
    • d = delete query
    • c = crosstab query
    • m = make query
  • For the following characters, use lowercase, except for the first letter of each word, use uppercase
  • Examples: qCustAging
• Forms
  • First letter(s) of name should be in lowercase letter(s).
    • f = form
    • sf or sub = subform
  • For the following characters, use lowercase, except for the first letter of each word, use uppercase
  • Examples: fCustomer, sfCustAging
• Reports
  • First letter(s) of name should be lowercase letter(s).
    • r = report
    • sr or sub = subreport
  • For the following characters, use lowercase, except for the first letter of each word, use uppercase
  • Examples: rCustStmt, srCustAging
• Macros
  • First letter should be lower case m.
  • It is convenient to tie the name of the macro to the report or form.
  • Again using lowercase letters and uppercase only for the first letter of each word.
  • Examples: mCustomer, mCustAging
• Modules (Program Code)
  • Module name should be short but descriptive
  • Functions or SubRoutines: Use lowercase letters except for the first letter of each word
  • Examples:
    • module: CalcTax
    • functions: GetTaxRate(), CalcTaxRate()

top of page

The Startup options provide you with the ability to...

• Set a database name that appears on the Windows title line
• Set a form to automatically open, when the database is opened
• Show or hide the database window
• Change the database icon

To change startup options, click on Tools, Startup

top of page

The main reason to set-up table relationships is to control the integrity of the data. Let's assume you have two tables for Invoices: INVOICEHEADER and INVOICEDETAIL where there are several records in the detail table for each record in the header. Unless you add code in a form, a user could delete the header record and the detail would still remain.

By creating a relationship between the two tables and setting the integrity flags, Access will automatically delete (cascade) all detail records when a header record is deleted.

top of page

If you create any program modules, it is most important to compile and save all modules. Otherwise the program will execute slowly.

Compile

To select this option...

• Open any program module
• Click on Debug
• Click on Compile
• Click on Save All Module

Compact and Repair

In addition it is also recommended that after any changes are made and before allowing use of the database, you should compact and repair the database.

To do this...

• Click on Tools
• Click on Database
• Click on Compact or Repair

Not only will this improve speed, but Access can produce strange results unless this operation is performed.

Decompile

Often it is recommended to decompile a large database before compiling.

To do so...

• Click on Start, Run
• Enter msaccess.exe /decompile
• Select the database to decompile. The database will open and decompile
• Close Access and Open the database in the normal manner
• Now compile, compact and repair the database

The results should be a smaller database size and increased performance

top of page

One annoying default is Access sets the movement of the arrow key to select the next field.

To change this to next character, which I think is more convenient and what users are expecting,

• Click on Tools
• Click on Options
• Click on Keyboard tab
• Change to Arrow Key behavior to Next Character

Note: This change is for each copy of Access and not the individual databases.

top of page

The following represents the most common short cut key strokes. There are many more that can be found in the online help for Access.

• Help (F1) - Displays the Office Assistant, Access Contents and Index; context-sensitive help about the selected property, control, macro action, or Visual Basic keyword; or alerts.
• What's This help (SHIFT+F1) - First move the pointer to the menu command, toolbar button, dialog box option, or screen region.
• Save (CTRL+S) - Saves the current database object
• Open Combo Box  (F4 or ALT+DOWN ARROW)
• Refresh (F9) - Refreshes the contents of a Lookup field list box or combo box
• Find and/or Replace text
  • To open the Find dialog box  (CTRL+F)
  • To open the Replace dialog box (CTRL+H)
  • To find the next occurrence of the text specified in the Find or Replace dialog box when the dialog box is closed  (SHIFT+F4)
• Select Header, Detail or Totals (F6) - Also works for design view of tables, macros, and queries and the Advanced Filter/Sort window
• Spelling (F7)
• ESC - Abort current record changes in form view

top of page

In Access terminology a database can contain both data (tables only) and program objects (queries, forms, reports, macros, modules). Because most installations will require that more than one person can access the data at the same time there should be at least two databases: Programs and Data. Also referred to as the Frontend and Backend.

Data

The data is usually stored on a server which everyone has access to. Note: If any changes are required to the table structures, the data database must be open in exclusive mode. This type of database is sometimes referred to as backend.

Example data name: projectdata.mda

Programs

A copy of the programs is placed on each client. It is also recommended to keep a master copy on the server. After any changes are made (and tested!!!!) to the master copy, the client copies need to be updated. This type of database is sometimes referred to as frontend.

Each client should have a convenient means to update their copy of the programs from the master. A simple solution is to create a 'good ole' DOS batch file that simply copies the master database from the server to the client. This batch file can be placed as a shortcut on the Desktop.

Example program name: projectprog.mda

The program database has to be set to link to the tables in the data database.

top of page

If you get the error message that you cannot open a database file because it is located outisde your Intranet or is an untrusted site, you need to make the following entry in the Security section of Internet Explorer.

• Open Internet Explorer
• Click on Tools
• Click on Internet Options
• Click on Security Tab
• Click on the Local Intranet icon
• Click on Sites
• Click on Advanced
• In the "Add this web site to zone", enter *.yourdomainname.com - (replace yourdomainname with your actual domain name)
• Click on Add
• Click OK
• Click OK
• Click OK
• Close Internet Explorer
• Reboot the computer

top of page

• Max Database File Size: 2 gigabytes minus the space needed for system objects Note: data can be split between multiple databases, so the total amount of data that can be stored is virtually unlimited.
• Max Number of Fields in a table: 255
• Max Table Size: 2 gigabytes minus the space needed for the system objects
• Max Number of Characters in a Text field: 255
• Max Number of Characters in a Memo field: 65,535 when entering data through the user interface; 1 gigabyte of character storage when entering data programmatically
• Max Number of Characters in a Record: 2,000 (excluding Memo and OLE Object fields)
• Max Number of Characters in a Cell in the query design grid: 1,024
• Max Number of Characters in a Text box on a form or report: 65,535
• Max Number of Levels of nested forms or reports: 7
• Max Number of Users: 255
• Max Limit on Number of Records in a Table: No limit on number of records, other than the maximum size of a table

top of page