Microsoft Access
Powerful database management system that functions in the Windows environment
Relational Database
Collection of data organized in a manner that allows access, retrieval, and use of that data
Collection of tables
Rows in tables are called records
Columns in tables are called fields
Unique identifier, Primary Key
Primary Key
A field that uniquely identifies each record
Most tables have at least one
Must be unique for each record
SS# is an example of a primary key because it is unique for each employee
Prevents duplicate records from being entered in the table
Used to control the order in which records display in the table
Makes it faster for databases to relocate records in the table and to process operations
Used to create a link between tables
Create a database on a 3.5 inch floppy
There are several different ways to do this
One way will follow
Place formatted floppy disk in drive A and click on the Start Button
Point and click New Office Document
Select General Tab if necessary, click Blank Database Icon and click OK
Point to Save and select 3 1/2 Floppy (A:)
Click in the File Name Box delete db1 and type in the name of your database
Click on create
Table
Structure
Each field must have a unique name
Each field must have a data type indicating the type of data the field will contain
Access allows you to enter a detailed description of the field
Rules for field names
Up to 64 characters in length
contain letters, digits, and spaces as well as most punctuation symbols
cannot contain periods, exclamation points, or square brackets
Same name cannot be used for two different fields in the same table
Field Properties
Field size: sets the max number of characters that can be entered in the field.
Format: Specifies how data displays in a table and prints
Input Mask: Simplifies data entry by controlling what data is required in a field and how the data is to be displayed.
Caption: specifies a field label other than the field name
Default value: automatically fills in a certain value for this field in new records as you add to the table. You can override a default value by typing a new value into the field
Validation Rule: limits data entered in a field to values that meet certain requirements.
Validation Text: specifies the message to be displayed when the associated validation rule is not satisfied.
Required: specifies whether or not a value must be entered in a field.
Allow zero length: specifies whether or not an entry containing no characters is valid
Indexed: sets a field as an index field (a field that controls the order of records). It speeds up searches on fields that are searched frequently.
Field Size
Default 50
Access only uses the amount of storage space actually needed, space not wasted
Setting field size to smallest possible size can decrease processing time
To change, click field size property text box and replace the entry
Data Types
Text
Number
Positive or negative
Used in arithmetic operations
Currency
Only dollar amounts
Displayed with dollar signs, commas, decimal points
Create a Table
Click New Button in the database, clock Design View then clock OK
Click Maximize
Fill in the field names and types and any appropriate description
Menu options given to click on for type
After entering a field name, you can select the primary key by clicking on the little key on the tool bar
Use the tab and backspace to correct errors
To delete a field, select the leftmost column on the row that contains the field to be deleted then press the delete key
To save a table, click the Save Button on the toolbar, give it a name, then click OK
Click the Close Button
Adding Records to a Table
Right-click the table in the database window then click Open on the shortcut menu
Table displays in Datasheet view
In Datasheet view, table is represented as a collection of rows and columns called a Datasheet
Use Tab Key
Click close button for the window when complete
Adding Additional Records
Navigation buttons lower left of window
First Record, Previous Record, Next record, Last Record, New Record buttons
New Record places insertion point in position to enter a new record
Correct Errors
Click incorrect entry and make appropriate corrections
Delete a record, Click on Record Selector that immediately precedes the record, then press the delete key
Printing
Portrait orientation: across the width of the page
Landscape orientation: across the length of the page
To preview the print out, right click on the table and select print preview
To print, right click on the table and select print
For page set up, click file menu bar then point to page setup and click
Form View
In datasheet view, you can see multiple records
If you have more than a few fields, you cannot see all fields at once
In form view, see only a single record
See all fields in the records
Personal preference
Create a Form
New Object: AutoForm button on the Toolbar
With database open and appropriate table selected, point and click on the New Object: AutoForm arrow and select AutoForm
To close he form, click the close button for the client window and answer yes to save then click OK.
Opening a form
Once you have created a form, you can use it any time by opening it
With database open, point and click on the Forms Tab
Select the form and click on Open
Once the form is open, use the navigation buttons to move through the records
Add new records or change existing ones
To delete a record, click on Record Selector then press delete
Switching between form view and datasheet view
From Form View to Datasheet view
Click View Button arrow on toolbar and point to Datasheet View and click
Creating a Report
Click Tables Tab and select the appropriate table. Click the New Object: AutoForm button arrow on the toolbar
Click Report then point to Report Wizard and click then click Ok
You are given several options, click next
You can assign a title to your report, finally click finish
Print the report
Click reports tab in database window, right-click the report then point to Print on the shortcut menu and click
Help
Use the Office Assistant
Question mark on the Toolbar brings the office assistant to the screen
Database Design
Arrangement of data into tables and fields
One simple principle to be kept in mind when designing databases is to design to remove redundancy
Redundancy: storing the same fact in more than one place
Query
Posing a question to a database
To find an answer to a question, create a corresponding query
Run the query to obtain an answer
Creating a Select Query Window
Make entries in a window called a Select Query Window
With the database open, Tables Tab selected, and the appropriate table selected, click the New Object: AutoForm button arrow on the Toolbar, click New Query
With Design View Selected, click OK
Maximize
Upper pane of window contains field list for the client
Lower pane of window contains design grid which is the area where you specify fields to be included, sort order, and the criteria records you are looking for must satisfy
Drag to change sizes
Assume you have a database named Technical Services with the following fields:
Client Number, Name, Address, City, State, Zip Code, Billed, Paid, Tech number
Where Client Number is the primary key
Create query to show client number, name, and technician number for all clients
Double click Client number field to include
Double click Name field to include
Double click Tech field to include
If you wanted to include all fields in the query, you could double click on each one individually, or simply double click on the asterisk
Run the Query
Point to the run button on the toolbar and click
Extra blank row marks the end of the table
To print, point to the Print button on the toolbar and click
To return to the Select Query Window, point to view button arrow Click, point to Design View and click
Close a Query
Click Close button for the Select Query Window
Answer yes or no regarding saving the query
Correcting Errors
Fix each one individually
Clear entire query, click Edit on the menu bar and click clear grid
Entering Criteria
Usually looking for those records that satisfy some criterion
Enter on the Criteria row in the design grid
Individually select all fields by double clicking
On the Show line check all those you want to appear in your result
On the Criteria line, fill in the appropriate criteria under the field
The following query will give the name, id number, and bank balance of all entries in Table1 whose bank balance is greater than $100:
Field
Name
ID Number
Bank Balance
Table
Table1
Table1
Table1
Sort
Show
Check Mark
Check Mark
Check Mark
>100
Wild Cards
Symbols that represent any combination of characters
The asterisk represents any collection of characters
Gr* represents the letters Gr, followed by any collection of charcters
The question mark represents any individual character
t?m represents the letter, t, followed by any single character, followed by the letter ,m. e.g. Tim or Tom
Criteria for a field not in the result
Include the field in the design grid, remove check mark from its show check box
Compound Criteria
More than one criteria for the data for which you are searching
AND criteria
To combine criteria with AND, place criteria on same line, find those clients whose billed amount is greater than $300 and whose technician is technician 12
field
Name
Billed
Paid
Tech Number
Table
Client
Client
Client
Client
Sort
Show
check
check
check
check
Criteria
>300
12
or
OR criteria either true
Those clients who were billed greater than 300 or their technician is technician 12
field
Name
Billed
Paid
Tech Number
Table
Client
Client
Client
Client
Sort
Show
check
check
check
check
Criteria
>300
or
12
Sort Data in a Query
Click the sort row below the field to be sorted
Click on the arrow and choose ascending or descending
Sort on Multiple Keys
Joining Tables
Tech Services database has two tables:
Client: Client Number, Name, Tech Number
Technician: Tech number, last name, first name
find records in two tables that have identical values in matching fields
Bring field lists for both tables to the upper pane of the select query window
Access will draw a join line
With Client in a select query window, right click any open area in the upper pane of the Query1: Select Query Window
Click Show table on he shortcut menu
Select Technician to select the Technician Table then click Add button and close the show table dialog box
Include the client number, name, and tech number fields from the client table and the last name and first name fields from the technician table
Computed Field
Field whose value is computed from other fields
In a query, enter name for the computed field, a colon, and then the expression in one of the columns in the field row
Outstanding Amount:[Billed]-[Paid]
Right click the field row in the design grid then click Zoom on the shortcut menu and type in the computation as described above
Statistics
Access supports the built-in statistics: COUNT, SUM, AVG, MAX, MIN, STDEV, VAR, FIRST, AND LAST
To use in Query, include it in the Total row in the design grid
Total row routinely does not appear in the grid
Right-click the grid, and then click Totals on the shortcut menu
Introduction
Microsoft Access (Access) is a relational database application. It allows you to store, retrieve, sort, analyze, and print information contained in your database. Data may be manipulated without data redundancy by defining relationships between sets of data. A collection of information is stored. Databases are often used for product inventory, supplies, invoices, employee data and many other things.
Advantages
1. It's free to use.
2. Information can be collected, stored and manipulated easily.
Disadvantages
1. Financial and statistical calculations are not calculated as quickly as if they
they were performed in a spreadsheet.
Tasks
1. Accessing Access
2. Understanding the terminology
3. Opening an existing database
4. Creating a new database
5. Creating a new table
6. Working in the table design view
7. Setting field properties
8. Setting table properties
9. Setting index properties
10. Modifying a table
11. Working in the datasheet view
12. Entering and editing data
13. Manipulating data
14. Importing and exporting data
15. Changing the datasheet layout
16. Creating forms
17. Modifying a form's design
18. Creating a new query
19. Exploring the query window
20. Designing a query
21. Creating a new report
22. Modifying a report
23. Creating a macro
24. Programming command buttons
Accessing Access
In order to begin Access, you must be seated at an IBM computer at one of the computer labs in either Pickler Memorial Library or Barnett Hall. After double clicking on the lab applications folder, double click on the Access icon. You are now in Access.
Understanding the terminology
The following chart provides a list of objects that can be contained in a database and their description.
Object Description
Table Stores data in a row-and-column format like a spreadsheet.
Query Extracts data from a table based on user-specified criteria. Also, they enable you to view fields from more than one table in the same record.
Form Displays data from a table or query based upon a user-defined format. Forms allow you to view, edit, and print data.
Report Displays and prints data from a table or query based on a user-defined format. You cannot edit data in a report.
Macro Automates common database actions based on user-selected commands and events
Opening an existing database
In order to open an existing database, begin by accessing Access. After this has been completed, choose the File Open Database command from the menu at the top of the screen. Choose the appropriate directory and database file name. Once this has been completed, click "OK".
Creating a new database
Once Access has been accessed, choose the File New Database command from the menu at the top of the screen in order to create a new database. The database window is displayed. Choose the appropriate directory and drive. Then enter an eight-character name for the new database file and click "OK".
Creating a new table
In order to create a new table, begin by displaying the database window, then clicking on the "Table" button and then the "New" button. Use the Table Wizard by clicking on the "Table Wizard" button. Select whether you want business or personal tables and then select a sample table. The corresponding predetermined sample fields appear in the box. The arrow buttons allow you to add or remove fields from the new table definition. The field names may also be edited by editing the text box below the field list. After selecting the fields, click the "Next" button and another box will appear. Enter the appropriate names in the corresponding places. Next, allow Access to set the primary key for you by clicking on the circle next to the "Let Microsoft Access set a primary key for me" statement and click "Next". You may now select whether you want to modify the table design, enter data in the table, enter data in a form that the wizard creates for you, or use Cue Cards for help with table design or data-entry tasks. Click in "Finish" to create the table and display it in the view that was selected. The datasheet view is similar to a spreadsheet and allows for data entry and editing. The design view allows the structure and appearance of the table to be changed but not the data.
Working in the table design view
Three possibilities exist for entering the design view. The first occurs when the table is currently being displayed in datasheet view. In this scenario, choose the View Table Design command from the menu at the top of the screen. In order to enter the design view for an existing database, open the database, select the desired table, and click the "Design" button. When entering the design view for a new table, open the database, select the desired table, and click on the "New" button. The design view contains the table design toolbar, the field grid pane, and the field properties pane.
The table design toolbar provides the following buttons:
Button Description
Design view Displays the table in design view
Datasheet view Displays the table in datasheet view
Save Saves the table design
Properties Opens or closes the property sheet for the currently selected object
Indexes Displays the index sheet for the currently selected object
Set Primary Key Enables the user to select a column or columns as the primary key
Insert Row Inserts a row above the current row
Delete Row Deletes the selected row
New Query Creates a new query based on this table
New Form Creates a new form based on this table
New Report Creates a new report based on this table
Database window Displays the database window
Build Displays the appropriate wizard or builder
Undo Undoes the most recent change
Cue Cards Displays the on-line tutorial help feature
The field grid pane enables you to define field names and data types. Field names can be up to 64 characters and must be unique within the table. Data types include text, memo (alphanumeric characters), number, date/time, currency, counter (sequential numbering), yes/no (yes/no, true/false, or on/off), and OLE Object (objects, graphics, or other binary data).
Setting field properties
The field properties that are set at the table level are automatically applied to the other database objects that use the table, such as forms, reports, and queries. Properties include the field size, format, amount of decimal places, input mask, captions, default values, validation rule, determination of required data entry, allowing of zero length, and indexing. In order to set these fields, select the field for which you want to set the property and click the specific property that you want to set. Enter the property value or select it form a drop-down list. Set all of the desired properties for the field and any other fields and save the table.
Setting table properties
Similar to fields, tables have properties. They include description, validation rules, and validation text. In order to set the table properties, choose the View Table Properties command from the menu at the top of the screen. Enter any desired table properties and close the table properties window.
Setting index properties
Indexes aid Access in finding values. Creating an index for specified fields is useful when you frequently search or sort certain fields. This will increase processing speed. All field types except OLE, Memo, and Yes/No may have indexes. You can create an index that includes duplicate field values or one based on unique field values. In order to establish an index, enter the field grid pane and select the field to be indexed. Select the indexed property and select a type of index.
Modifying a table
Access allows you to add, rename, delete, and move fields. In order to insert a field, position the point in the row before which you want to insert a row and click the "Insert Row" toolbar button. You can rename a field by selecting the field-name cell and typing the new name. Click the "Delete Row" toolbar button in order to delete a field. In order to move a field, click the field selector to choose the field row that you want to move and drag the field row to the new position. Be sure to carry through the changes to the appropriate forms, queries, and reports.
Working in the datasheet view
After you create the table, you are ready to begin entering data. Choose the View Datasheet command to enter the datasheet view. You can now enter and view data in a spreadsheet format. The following is a list and description of the datasheet toolbar buttons:
Button Description
Design view Displays the table in design view
Datasheet view Displays the table in datasheet view
Print Opens the print dialog box, in which you can set up the printer and print the current table in datasheet format
Print preview Displays the current table in page layout format
New Moves to a new record at the end of the datasheet
Cut Deletes selected data and copies it to the clipboard
Copy Copies selected data to the clipboard
Paste Inserts what has been cut or copied to the clipboard
Find Searches the current field for user-specified data
Sort ascending Sorts data in ascending order
Sort descending Sorts data in descending order
Edit filter/sort Enables you to view and edit the filter and sort criteria
Apply filter/sort Applies the filter and sort criteria
Show all records Removes the filter and sort criteria, displaying all records
New query Creates a new query based on this table
New form Creates a new form based on this table
New report Creates a new report based on this table
Database window Displays the database window
Autoform Creates a simple form
Autoreport Creates a simple report
Undo current
field/record Undoes the last change in the current field or record
Undo Undoes the last change
Cue cards Displays an on-screen tutorial
Entering and editing data
Adding, deleting, and editing table records are some of the most basic data-entry skills. Two options exist for adding records. Adding records can be done in the edit mode or in the data-entry mode. The edit mode allows you to add new records at the end of the table. Whenever you change data or enter new records, Access automatically puts you in the edit mode. The data-entry mode hides all existing records in the table and displays a blank table. To activate the data-entry mode, choose Records Data Entry. In order to deactivate it, choose Records Show All Records. In both cases, you may use the blank record at the bottom of the datasheet to enter new records in a table. Access automatically saves new records and any changes when you move off a record.
In order to delete an entire record, click the record selector (the first column on the left side) and choose Edit Select Record from the menu at the top of the screen. The row will then be highlighted. Choose the Edit Delete or press the delete key to delete the record.
To edit existing data table, select the field in the record that you want to edit. If there is no data in this field, begin typing. If data is contained in the field, Access will select the entire cell contents. If you start typing you will replace the cell contents. Press F2 to navigate and modify what already exists in the cell.
Manipulating data
In Access, the standard Windows cut, copy, and paste functions work the same way. Access allows you to cut, copy, and paste data from one cell to another or from one table to another. First, select the record to be cut and copied. Then choose the Edit Cut or Edit Copy command from the menu at the top of the screen. This places the record on the clipboard. After this has been done, select the records to be replaced in the target table and choose the Edit Paste command to replace the records or Edit Paste Ammend to add the records.
Access also allows you to sort and filter data. To do this, choose the Records Edit Filter/Sort from the menu at the top of the screen. Move to the field row in the filter window and select the field to be searched from the drop-down list. Once this has been accomplished, move to the sort row and select the desired sort order from the drop-down list. The criteria should then be selected in the criteria row. These steps should be repeated for all of the fields that are to be filtered or sorted. After all of the information has been entered, choose the Records Apply Filter/Sort . The desired subset of data is now displayed. Choose File Save As Query in order to save the filter.
Importing and exporting data
Data can be transferred in and out of Access. This allows you to use data from another computer system or application or transfer data to other computer systems or applications. You can import and export Text, Excel, Lotus 1-2-3, and dBASE III and IV files.
In order to import data into an Access table, open the database window and choose the File Import from the menu at the top of the screen. Select the file format and click "OK". Choose the file to be imported, and click the "Import" button. The data is now imported from the file and a new Access table is created that stores the data.
In order to export data, open the database window and choose the File Export from the menu at the top of the screen. Select the export format and click "OK". Choose the object to be exported and click "OK". Finally, select the name of the file to which the data is to be exported and click "OK". The data has now been exported to the specified file.
Changing the datasheet layout
Access allows you to customize the layout of your table's datasheet view by modifying the datasheet properties. The does not affect the data. The Format menu at the top of the screen can be used to change fonts, row heights and column width, hide or display columns, freeze or unfreeze columns (columns stay to the left stay on-screen while you scroll to the right), and turn grid lines off and on. After the changes have been made, Access allows you to save or not save the changes.
In order to change the location of a field, first enter the datasheet view. Click the field selector to the desired columns and click and hold down the mouse button in the field selector again. A vertical bar appears along the left side of the column. Drag the field to its desired location and click to insert the field. Click anywhere in the datasheet to deselect the field.
Creating forms
Forms provide a different way of viewing table data. Access enables you to create forms that can be used to enter, maintain, view, and print data. The Form Wizard is provided to assist you in the construction of forms. Four types of forms can be created. These include single-column (displaying one record at a time in a vertical format), tabular (displaying multiple records in a row-and-column format), main/subform (combining the single-form and tabular formats into one form), and graph (displaying a graph of the data).
To create a form, click on the "New Form" button and then identify the table or query for which you want to create a form. The choose Form Wizards from the menu at the top of the screen, select the desired Wizard, and click "OK". Click the arrow buttons to add (>) or remove (<) fields from your form design. Use (>>) to add all of the fields and (<<) to remove all of the fields. Click on "Next" to proceed. Select the appropriate data display style and click "Finish" to proceed. You are then prompted to name the form. If you desire to use the form immediately, click the "Open the Form With Data In It" button. If you desire to customize the form, click the "Modify the Form's Design" button. Once this has been accomplished, click "Finish" to proceed.
Modifying a form's design
Before modifying controls in the form, you must learn to select and adjust controls. By clicking on a control, it is selected. Access displays handles around the control to indicate that it has been selected. The smaller black handles are the resizing handles while the larger ones at the top of the control are called the move handles. Drag the resizing handles to change the size of a control and the move handles to move the control to a new location.
Bound, unbound, and calculated controls can be created. Bound controls are linked to a field in a table or query, while unbound controls are not. Calculated controls are unbound controls that use field data to perform calculations on-screen. The result of these calculations is not stored in any table or query.
In order to add a control, display the Toolbox by choosing View Toolbox from the menu. Select the desired control tool in the Form Design toolbox. For unbound controls, position the mouse pointer in the form where you want to add the control and click to create the control. For bound controls, display the field list by choosing the View Field List from the menu. Click and drag the appropriate field name to the desired position in the form. For a calculated control, create the unbound control for the calculated field. Enter the expression in the control, or set the control's ControlSource property to the expression. To set the control and form properties, select a control and then choose View Properties from the menu. After the Properties window is displayed, you may select different controls, sections of the form, or the form itself. Double-click the top-left corner of the form to select the form and display the form's properties. Use the window that appears to change the caption at the top of your form window.
Creating a new query
Four basic types of queries exist. They are the crosstab query (which summarizes query data in spreadsheet format), the find duplicates query, the find unmatched query, and the archive query (which copies specified records from one table into a new table and can eliminate those records from the original table. In order to create a new query, click the "New Query" button in the toolbar and then click on the "Query Wizard" button. Each wizard prompts you for specific information that is needed to create the specified type of query. In all cases, you are required to identify the table(s) or queries on which the new query will be based.
Exploring the query window
The query window allows you to see queries in three different views. The design view is used to define the query. When viewing or modifying the SQL query-language definition of your query, use the SQL view. This is very advanced and not covered here. To display the results of your query use the datasheet view. These views can be changed by clicking on their appropriate buttons on the toolbar.
Designing a query
The query design view is split into two main sections. A field list box for each table being used in the query definition is contained in the top section, while the bottom section houses the Query-by-Example (QBE) grid, where you define your query. Every column in the QBE grid is a field. You define parameters in the rows of the QBE grid for each field.
In order to add a field to the QBE grid, double-click the field in the field list box. The field name and the default selections to total by group are filled in and the Show check box is checked. By double-clicking the asterisk, all fields in the field list box will be selected. You may remove a field by selecting the field column and pressing the Delete key.
The dynaset is set of records that results from your query. The fields included can be controlled. By checking the box in the Show row of the QBE grid, the field will be included in the dynaset. Note that all of the fields used in the QBE grid do not have to be included in the query results. In order to view the table name and total, choose the View Table Names and View Totals command from the menu, respectively.
Calculated fields can be added by first moving to the appropriate column in the QBE grid. Then enter a new calculated field name in the Field row, followed by a colon. Continue to type in the field-name cell (in brackets), and enter the desired calculation expression (i.e. Total:[Unit Price]*Quantity). After this is completed, save and execute the query.
Sometimes, query results need to perform calculations for groups of records rather than for each individual record. Sophisticated calculations on groups of records are allowed in Access. In order to perform a group calculation, create a select or crosstab query and display the total row by choosing the View Totals command. Select a total type for the total cell. If the totals are for all records, no total cells should be the Group By type. If the totals are to be calculated by group, select the Group By type. Save and then run the query.
Access also allows you to specify the query criteria. The criteria row in the QBE grid enables you to specify the criteria. You can select records by entering any of the following criteria: exact match (use a literal value, i.e. MO or 100), wildcard pattern match (use a combination of literal and wild-card characters, i.e. N* or 2###), elimination match (use the not operator to eliminate records, i.e. not MO), date match (use an exact date), blank values (use null to see only blank values and not null to eliminate blank values), comparison operators (use any of the comparison operators, i.e. >, <, <=, <>, etc.), yes/no values (use yes, true, on, or -1 to specify yes values and no, false, off, or 0 to specify no values), and multiple criteria (use and, or, not, between, or in to establish multiple criteria within the same field).
To specify a sort order in a query, select the field and sort order from the drop-down list. You may also consider specifying query properties. In order to do this, choose the View Properties command to open the Properties window. Click on the object for which you want to set the properties and modify its properties.
Creating a new report
Creating a report is similar to creating a form. Click on the "Report" button, the "New" button, and then the "Report Wizard" button. The report wizard allows you to create single column, groups/totals, mailing label, summary, tabular, and autoreport reports. When using the Wizard, Access will present a series of dialog boxes that ask you for the report specifications. These differ for each type of report. Enter the appropriate specifications and click "Finish" when you are done.
Modifying a report
Modifying a report is exactly the same as modifying a form's design in regards to selecting and adjusting controls, creating new controls, and setting control and report properties. Access also enables you to group data in reports and use subreports.
Grouping segments data into separate groups and sorts records within the groups based on your determined specifications. In order to add grouping to a report, choose the View Sorting and Grouping command. A dialog box will appear. Select the field on which you want to group in the top section. Also, specify the ascending or descending sort order. In the bottom section, specify the properties for the grouping.
Subreports may be used to create a multitable report. When creating a subreport, first, create the detailed report that will be used as the subreport. Display the main report in the report design view and display the database window. Then, drag the detailed report object icon to the desired section of the main report. Finally, save the report design.
Creating a macro
Similar to Excel, Access allows you to create macros, sets of instructions that perform tasks for you. In creating a macro, you select the actions from a drop-down list and define arguments, which tell Access how to perform that action in the database. In order to create a macro, open the database window, click the "Macro" button, and then click the "New" button. A new macro window is opened. The following chart displays the buttons in the macro toolbar.
Button Description
Save Saves the macro
Macro names Shows or hides the macro name column
Conditions Shows or hides the condition column
Run Executes the macro
Single step Executes the macro one step at a time
Database window Switches to the database window
Build Starts the appropriate wizard or builder
Undo Undoes the most recent change
Cue cards Displays the on-screen tutorial
A new macro window shows only two columns in the top section (the action and comment columns) and nothing in the bottom section (action arguments). As actions are entered in the action column, the action arguments section displays any available arguments that can be set. You can use the mouse to move between the two sections.
In order to add actions and set action arguments, select an action from the drop-down action list or type the name of the action in the action column of the macro window. If needed, enter a comment in the comment column to describe what the action does. Use the mouse to switch to the action arguments section and complete the required action arguments. Repeat these steps for each step in the macro. Save the macro by choosing the File Save command.
You may execute the macro in the macro window by clicking the "Run" button in the toolbar and then selecting the macro to execute. In the database window, click the "Macro" button, select the appropriate macro, and click the "Run" button. In any window, you can execute the macro by choosing the File Run Macro command and then selecting the macro to execute.
Programming command buttons
Access allows you to create command buttons that are easy-to-use, easy-to-maintain methods for automating database tasks. The Command Button Wizard allows the following types of buttons:
Type of button Description
Record navigation Go to a record, create a new record, or find a record
Record operations Save, undo, delete, print, or duplicate the current record
Form operations Open, close, print, filter, edit filter, and refresh forms
Report operations Print reports, preview reports, send reports to a file, or mail reports
Application Run or quit applications
Miscellaneous Print tables, run queries, run macros, or dial the phone
In order to create a button in a form or report, open the form or report in the design view and display the toolbox. Select the Control Wizards and click the "Command Button" tool to select it. Position the crosshair (mouse pointer) in the form or report where the button is to be added. Click and drag the button to the desired shape and size. The Command Button Wizard will now be started. Select a button from the list of categories. Once the category has been selected, choose the desired button action and click the "Next" button to continue. Choose whether the button is to be text or picture and click the "Next" button. Enter the name for the button and click the "Finish" button to create the button in form view.