Computers on the Farm Conference
Using Access Databases - The "Nuts and Bolts"
January 5, 2000

green line

Stephen Martin

Stephen Martin from the Ag Electronic Bulletin Board office at MU discussed basic topics associated with Access Databases. He explained the terms and some common functions associated with creating databases.

green line

Access - Nuts and Bolts Topics


**** Go to the bottom of this page for a link to useful Access learning tools. ****


The MDB File - container for all of the objects that make up a MS Access database



Field Types - what general type of data are we storing (text, number, date/time, etc.)

Data Types - defines how data is stored, viewed, and used by the database system

Key Fields - unique identifiers for records

Indexes - speeds up access to specific rows or groups of rows

Relationships - define how data in one table is related to the data in other table/s

Finding, filtering, and sorting records - turn data into useful information

Validation Rules - use to ensure that data entered into tables meet your rules/requirements

Input Masks - data entry templates that provide for consistent data entry

Lookups - provide a list of values that are valid for a field - can limit entry to these values if desired

Import/Export - text files, tables, spreadsheets

Copying and Pasting Tables - create quick backups of your data before making scary changes

Linking to Data - link to external data like text files, spreadsheets, tables from other databases



Query by Example - user friendly way to define how you want to view your data

Query Types


Forms - create the user interface to your tables and queries

Form Types - general categories

Form Wizards - quick and easy way to generate generic forms for your data


Reports - present data as information in various formats

Report Types - general categories


Macros - automate many repetitive and/or complex tasks

Switchboard Manager - add-in to create a menu driven user interface using macros

Microsoft plans to eliminate macros from future versions of Access in favor of VBA

Macros are much easier than VBA


Modules - user programmable VBA code modules

VBA - Visual Basic, Application Edition or Visual Basic for Applications

Application programming language used for everything from simple program automation to building complex decision/logic code segments. Not as easy as macros.


Database Documenter - create a road map to your database (data dictionary)


Structured Query Language - SQL (pronounced "seequel")


Compacting/Repairing - sounds scary but is necessary to keep a happy healthy database


Field & Data Types - most of them

TextUp to 255 characters1 byte per character
MemoUp to 64,000 characters1 byte per character
ByteNumbers from 0 to 2551 byte
IntegerNumbers from -32768 to 327672 bytes
Long Integer (default)Numbers from -2,147,483,648 to 2,147,483,6474 bytes
SingleNumbers from -3.402823E38 to 3.402823E384 bytes
DoubleNumbers from -1.7976913486231E308 to 1.7976913486231E308 (real big)8 bytes
Date/TimeDates and times8 bytes
AutoNumberUnique sequential or random number automatically inserted in a new record4 or 16 bytes
Yes/NoBoolean values: yes/no, true/false, on/off1 bit
OLE ObjectCan store objects like pictures, sounds, or binary files like Word docsUp to 1 gigabyte

Operators - some examples

Arithmetic+ , - , - (unary) , * , / , \ , Mod , ^
Comparison< , <= , =, >= , > , < >
LogicalAnd , Or , Not , Xor (exclusive or )
OtherIs , Like , In , Between , & ( concatenation )

Functions - some examples

Date/TimeDate( ) , Now( ) , Day( ) , Month( ) , Year( ) , DateDiff( )
StringFormat( ) , InStr( ) , LCase( ) , Left( ) , Trim( ) , Val( )
ConversionCInt( ) , CSng( ) , CStr( ) , CCur( )
Math/TrigAbs( ) , Cos( ) , Log( ) , Rnd( ) , Sqr( )
FinancialFV( ) , NPV( ) , PV( ) , Rate( ) , Pmt( )
MiscIIf( ) , IsNull( ) , IsDate( )
AggregateAvg( ) , Count( ) , Max( ) , Min( ) , StDev( ) , Sum( ) , Var( )

Look for web links to useful database learning tools at: a few weeks after the conference.

The download information in the following section was added to this document on 01/11/2000.

Due to "EULAs" (End User License Agreements) we could not make the files directly downloadable from AgEBB. If you have problems finding the described files at the Microsoft site, please let us know. We will do whatever we can to help.

**** Microsoft's Access 97 Download Page ****
This page includes downloadable samples, service packs, wizards, accessories, and white papers for Access 97.
The files "QRYSMP97.EXE", "FRMSMP97.EXE", "RPTSMP97.EXE", and "NEATCD97.EXE", from the samples section, have quite a few hard to find "how to" examples. QRYSMP97.EXE, for example, has numerous example queries that show how to do tricky things like running totals, referencing a field in another record, or how to use the DLookup() function inside a query.

The other three files topics are:


Stephen Martin
AgEBB (573) 882-4827

[ Back to Computers on the Farm 2000 Program]