Introduction to Microsoft Access (Home | Back)

1. There is a sample database, Northwind Traders (Northwind.mdb), that comes with Office XP. This database should be in the folder C:\Program Files\Microsoft Office\Office10\Samples\. Copy any files you want to use from this folder to your I:\ drive. If the Northwind database is installed on your home computer, it can also be found in the folder C:\Program Files\Microsoft Office\Office10\Samples, where "C:\" is assumed to be your hard drive. If it is not installed, you can install it using the Microsoft Office XP setup disk. Access Help includes a number of examples that use the Northwind database.
2. You should be aware of one important difference between MS Access database files and other Office XP files (such as worksheets). A worksheet or a Word document can be opened and fiddled with and then closed without saving any changes. This is not the case for a database. Access saves the file as soon as you open it then resaves almost every change automatically. For this reason, if you want to mess around with a database that should not be tampered with, copy it to a new file before you open it.
3. What is a database?
1. A database is a collection of information:
• A book
• A file folder or a file cabinet
• An Excel workbook
2. A database program, such as Microsoft Access, allows you to organize information in a way that allows it to be easily retrieved or manipulated.
• You can keep your friends phone numbers and addresses on scraps of paper in you wallet, but its hard to find a particular name.
• With a computerized database, you could find any name in a flash. You could even find all friends whose names began with "L" or whatever letter.
• A database has at least one table, and may have other objects (queries, forms, reports, etc).
4. Access basics
1. Right click on the link and choose "Save Target As..." to copy the file bookstore.mdb (Access database files have the extension *.mdb) to your I:\ drive. Rename the file to bookstore2.mdb. Open the database by double clicking on it. You should see a screen that looks somewhat familiar:
• There is an application window with the title bar with minimize, maximize and close buttons
• The menu bar
• One or more toolbars
• Just as Word has a document window within the application window, Access has a database window within the application window.
• Although Word allows you to work on more than one document at a time, Access allows only one database to be open at once.
• You can have other windows, such as tables, forms, etc, but these are all in the same database.
• All the objects within a database are accessible from the database window.
2. Every Access database must have at least one table. Open the Books table by clicking the Open button. The table will display in Datasheet View. In this view, the table looks a lot like an Excel worksheet. In fact, you can very easily import an Excel worksheet into Access.
• Tables are the heart of any database. They are where the actual data is stored.
• Other objects can be used to enter or view this data (forms), answer questions about the data (queries), or print the data (reports).
• You can print a table to get a hardcopy of the data, but since your display and selection options are limited, we usually use more sophisticated ways of getting the information into print.
5. Fields and records
1. A field is a category of information that we want to keep track of.
• For a table in Datasheet View, the fields are the columns.
• Notice that each column has a title at the top. This should tells us about what's in that field. The column heading is often (but not always) the field name.
• To see the field names, switch to Design View (click the button with the drawing triangle or choose View, Design View). Design View also lets you view or change the other properties of a field (data type, display format, etc).
2. A record is a set of field values related to a particular entity.
• For a table in Datasheet View, the records are rows.
• A record might represent a particular book (as in the bookstore example) or something else (for example, a person).
• Every table should have a primary key, which is a field that contains a unique value for each record (for example, no two students at Three Rivers have the same ID number).
3. Data can be viewed, entered, altered or deleted directly in a table in Datasheet View.
• Click on a cell in the table and you can type in it. The current record is marked with a triangle (if saved) or a pencil (if not saved).
• Move to a new record and your changes are saved. It is important to note that you are limited in your ability to undo changes to records in Access. The last change can (almost always) be undone. You are usually stuck with earlier changes.
• The last row of the table is marked with an asterisk. You can add a record by typing in here.
6. Moving and sorting
1. You can sort by any field by clicking on the field heading and choosing Records, Sort or clicking the A-Z or Z-A button.
2. You can move columns by selecting them and then dragging the column heading to where you want it.
3. You can resize columns and rows by dragging or using the Format menu (similar to Excel).
7. Other objects:
1. Forms are a more convenient way to view or enter data. They can prompt for appropriate input or display calculated output.
2. Queries allow us to select and combine data from one or more tables in such a way that we can answer questions about the data or produce customized reports.
3. Reports allow us to output the data to a printer in an attractive format.
4. Macros are sets of commands that the database has memorized to automate things that are done repeatedly. Note that macros in Access are distinct from the Visual Basic routines. (In Excel, they are the same.)
5. Modules are Visual Basic programs that can make the database very user friendly. They can automate data entry, reporting, selection of data, etc.