Ms Access
Ms Access
DBMS(Database Management System) A database-management system (DBMS) consists of a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the database, contains information about one particular enterprise. The Primary goal of a DBMS is to provide an environment that is both convenient and efficient to use in retrieving and storing database information.
A database is a collection of data organized in a manner, which allows retrieval and use of that data, by anyone needing it. A database is organized and designed to allow a large number of users to draw information from it for many different purposes in many different formats.
Database systems are designed to manage large bodies of information. The management of data involves both the definition of structures fro the storage of information and the provision of mechanisms for the manipulation of information. In addition, the database system must provide for the safety of the information stored, despite system crashes or attempts at unauthorized access. If data are to be shared among several users, the system must avoid possible anomalous results. The importance of information in most organizations - which determines the value of the database - has led to the development of a large body of concepts and techniques for the efficient management of data.
Some popular DBMS are FoxPro, Clipper, Sybase etc.
RDBMS(Relaltional Database Management System):
Relational Database Management System is a specialized computer program, where the data are split in tables to reduce data redundancy and get consistency in data. Relational database is based on the relational database model, which was introduced in 1970 by E.F. Code. The model is based on mathematical theory, especially on the disciplines of Set Theory and Predicate Logic. The basic idea behind the relational model is that a database consists of a series of unordered tables (or, relations) that can be manipulated using nonprocedural operations. This model was in vast contrast to the more traditional database theories of the time, which were more complicated and less flexible and were dependent on the physical methods used to store the data. Relational approach uses the computer to search the database for the desired data rather than accessing data through series of indices and physical addresses. In RDBMS the data structure or relationship between data are defined in logical rather than physical terms.
Note: It is commonly thought that the word 'Relational' in the Relational Model comes from the fact that you relate tables to each other in a relational database. Although this is a convenient way to think of the term, it's not accurate.
Some of the popular RDBMS are MS-Access, MS-SQL Server, and ORACLE etc.
Microsoft Access 2000/XP
Microsoft Access 2000 is a powerful multi-user RDBMS (Relational Database Management system) developed by Microsoft Corporation and can be used to store and manipulate large amounts of information and perform repetitive tasks. Access creates desktop and client/server database applications that run under the Windows operating system and stores an entire database application within a single file with a .mdb extension, which can contain data objects, like tables, indexes and queries, as well as application objects like forms, reports, macros, and visual basic code. Although the term “database” typically refers to a collection of related data tables, an Access database includes more than just data. In addition to tables, an Access database file contains several different types of database objects:
- Saved queries for organizing data,
- Forms for interacting with the data on screen,
- Reports for printing results,
- Personal Applications
- Small Business Applications
- Departmental Applications
- Corporation-wide Applications
- As a front end for enterprise wide client/server Applications
- Intranet / Internet Applications
Sometimes more than one field has to be used to uniquely identify a record, i.e. Primary Key may contain more than one field. Such type of Primary Key is called Composite Key.
Starting Microsoft Access 2000/XP
Steps:
- Click on the Start button in the Task bar.
- Click on the Programs option and then on the Microsoft Access option.
- Select the Blank Access Database option if you want to create a blank database, Access Database wizards if you want to create database using wizard and Open an Existing file if you want to open an existing database.
- Select the location where you want to create the Access Database.
- Give the meaningful name for the Database and click on the Create button.
- The Access Database window will appears as follows:
Access database is made up of different Objects like tables, queries, forms, reports, data access pages, macros and modules. Each of the above Objects has their own special function different from one another. While working with Access you will include several objects like relationships, command bars, database properties etc.
Tables
Tables are grids that store information in a database similar to the way an Excel worksheet stores information in a workbook. Table is the first object that you add to your database is used to store all the data in the database. Each row of a table contains one record. Records are made up of fields that contain a particular piece of data in the table. Every table in Access 2000 must have a field that uniquely identifies each record in the table. This field is called the primary key. You can recognize the primary key in a table by the ID appended to it. The primary key ensures that you can retrieve data accurately and efficiently when you pull information from the database.
Queries
Queries in Access are Powerful and versatile. Using select queries you can view, summarize and perform calculations on the data of your table (single or multiple). And Action queries lets you add, update and delete data from table. When a select query is run, a datasheet appears, with the specified fields in the query and the records meeting the query’s criteria. When an action query is run, the specified action is run such as appending data to an existing table, deleting data from table or updating data of an existing table. The query created is stored as a separate object and listed in the database window.
The datasheet returned by a query is a dynamic set of data, called dynaset, and can be updated based on the tables data.
Forms
Forms are used to view, enter, edit and print table data in a custom format. Form can be created form the data of a query result also. It provides more structured view of the data as compared to datasheet view and can be used to add, change, delete or view database records, one record at a time. Using Forms, user can navigate through the records, access to certain fields can be restricted, validation rule can also be set and custom dialog box can be created that can be used to display or retrieve information from the users.
Forms are comprised of following three sections:
- Header
- Detail
- Footer
Reports
Report is a comprehensive tool of Access that is used to create and maintain report of facts about the data in a clear and concise form. Reporting is one of the primary purposes of a database programme and can contain data from multiple tables. With Access you can create Detail report, Summary report, Cross-tabulation report, Report containing graphics, charts, forms, labels etc.
Similar to Forms Reports is also comprised of three sections:
- Header
- Detail
- Footer
Pages
Pages allow you to view and update the data in your database from within a browser. Although they are stored outside the Access database (.mdb) file, they are created and maintained in a manner similar to that of forms. Although the data access pages are targeted toward browser, they can also be previewed within the Access application environment. Data access Pages can also be viewed and modified in Design View. Design View of a Data access page is similar to that of a form, which makes working with the data access Pages and deployment of your application over an Intranet, very easy.
Macros
Macros in Access differ from the macros of the other languages. They can't be recorded as in Word or Excel and are not saved as VBA code. With Access macros, you can perform most of the task that you perform manually from keyboard, menus and toolbars. Generally VBA (Visual Basic for Applications) code contained in module are used rather than macros to do the task that your application must perform. VBA code gives more flexibility and power than macro.
Modules
Modules are the foundation of any application and let you create libraries of functions that can be used throughout your application. Modules contains the VBA code and you can do the following using it:
· Perform error handling
· Declare and use variables
· Loop through and manipulate record sets
· Call Windows API and other library functions
· Create and modify system objects, like tables and queries
· Perform transaction processing
· Perform many functions not available with macros
· Test and debug complex process
· Create library database
Creating Tables
Tables can be created in Access using Design view, Datasheet view and table wizard and can be imported from another database or linked to the external table. Importing and linking a table is not appropriate for the most of the business solutions.
Creating Table using Table Wizard
Table Wizard Guides you through a series of steps that are required to create a table. The wizard creates the table from a list of ample tables and the created table can be modified using the design view.
Steps:
1. Click on the Table option in the database window.
2. Click on the New option, select Table wizard option and then click on the OK button. Or, Double click on the Create table by using wizard.
3. Select the table type (Business or personal) that you want to create.
4. Select a table from the Sample tables list and select the fields for the table from the sample fields’ list by clicking the single right arrow next to the Sample Fields list. If you want to include all of the Sample Fields in one table, simply click the double right arrow. When all the fields are selected then click the Next button and the fields in the table can be modified by using table Design view.
5. Give the name for your table and click on the Yes, set a primary key for me option if you want Access to assign Primary Key for you if not click on the No, I will set the Primary key option and then click the Next button.
6. If you Clicked the second option you will see the following dialog box.
7. Select a field that you want to make the primary key and select the Type of data the field will contain and click on the Next button.
8. Click on the Finish button.
Creating Table using Datasheet View
Table in datasheet view is created by directly entering the data into a spreadsheet-like format and the data type and size of the field is set automatically according to the type of data you enter.
Steps:
1. Click on the New option in the database window or select the table option form the insert menu.
2. Select Datasheet View in the New Table dialog box.
3. You can also create table in datasheet view by double clicking the Create table in datasheet view.
4. Give the field name by double clicking on the Field name (which appears Field1, Field2 etc.)
5. Enter the data for the table in the datasheet and save the table. The other Fields that haven’t renamed will automatically disappear.
6. After you save the table, you will be prompted to assign a primary key. Assign a primary key on a field that stores Unique value as you did in creating table using Table wizard.
Creating Tables in Design View
Design View is the best way to create table when creating a custom business solution. It allows you to define the fields in the table before adding any data to the datasheet.
Steps:
1. Click on the Tables option in the Database Objects list and click on the New option or, click on the create table in design view icon in the database window.
2. You can also create table by choosing Insert menu and then table option.
3. Select Design View option in the New Table dialog box and click on the OK button.
Design View database window is displayed.
4. Type the Field name, Select a data type for the field and the appropriate Description for the Field.
5. Change the Properties of the Field and click on the Save button in the toolbar to save the table.
6. Give the Table a name and click on the OK button. A dialog box prompting to create a primary key is displayed.
7. Click on the YES button if you want Access to create a primary key for you if not click on the NO button. When clicked on YES button access creates a new Field name ID and creates a primary key on the field.
8. If you clicked on the NO button, go to design view and select the Field in which you want to create a primary key and then click on the Primary Key button in the Table Design Toolbar.
9. Primary Key can also be created by clicking on the Primary key option of the Edit menu.
Data Types
The data type you have selected for a particular field greatly affects the performance and the functionality of your application. The data types supported by Access are discussed below:
Text Fields:
Stores all valid printable characters and is Text values and numbers that are not used for calculation like: phone numbers, zip codes, fax etc. The default size of the text Field is 50 but up to 255 characters can be stored.
Memo Fields:
Can store of up to 64,000 characters and if the size of the field data varies widely then it is suitable to use memo data type because space equal to the actual size of the data value is allocated.
Number Fields:
Stores numeric values that can be used for calculation. Numeric values that do not need to perform calculations with data should be given text data type.
Data Type
Description
Storage Size
Text
Alphanumeric characters
0 - 255 characters
Memo
Alphanumeric characters
0 - 64,000 characters
Number
Numeric values
1, 2, 4 or 8 bytes
Date/Time
Date and Time data
8 bytes
Currency
Monetary data
8 bytes
AutoNumber
Automatic number increments
4 bytes
Yes/No
Logical values: Yes/No, True/False
1 bit ( 0 or – 1 )
OLE Object
Pictures, graphs, sound, video
up to 1 GB
Hyperlink
Link for other documents
Up to 64,000 characters
Lookup Wizard
Displays data from another table
Generally 4 bytes
Date/Time Fields:
Is used to store valid date and time values. Access allows performing calculation for the values stored in Date/Time Fields.
Currency Fields:
Is used to store monetary data. You may choose number data type to store monetary data but choosing Currency data type is the best because it prevents rounding off data during calculation, so the performed calculation is more accurate than on number data type.
AutoNumber Fields:
Is used when you have to store an integer value that is incremented automatically when you add record and the generated number is unique. When a user deletes a record from a table, its unique number is lost forever and similarly when the user cancel the record adding process, then the unique counter value for the record is lost forever.
Yes/No Fields:
Is used to store Logical True or False Values. What actually stored in this field is -1 for Yes and 0 for No.
OLE Object:
Stores any types of object like picture, video clip, Sound, word document etc. It is better to use Hyperlinks rather than to use OLE Object if you want to store documents.
Hyperlink Fields:
Are used to store URL address, which are linked to World Wide Web pages on Internet, or the path of a file.
Lookup Wizard:
Is used when you have to select a limited number of values. You can take the value from a table or query or instead type yourself. When you select Lookup wizard from the data type option, a wizard is invoked and it guides you through the process.
If the values are taken from the table or query then any update to the table or query is reflected in the List.
Assigning Primary Key to a Field
Primary key uniquely identifies your record in a table. A table can contain only one Primary Key. A Combination of two or more fields can be used as the Primary Key and such a combination is called Composite Key.
To assign Primary Key,
Select the Field in which you want to assign Primary Key and then click on the Edit menu and then click on the Primary Key Option.
Or, You can also assign the Primary Key by selecting the Field and then clicking on the Primary Key button on the Toolbar.
Or, Select the field in which you want to assign the primary key, click the right button of your mouse and then select the Primary key option from the shortcut menu.
Field Properties
Field Properties pane allows you make more control over the way the data in your table is stored. Field Properties Pane of the design view has different properties that help you to have control over the data, which are as:
Field Size:
This property is available for the text and number fields only and determines the amount of data that can be stored in a field. For text data, only the number of characters specified in the field width will be stored. For numeric data types, the field size enables you to further define the type of number and which in turn determines the storage size for the data in the field.
Setting
Description
Decimal precision
Storage size
Byte
Stores numbers from 0 to 255 (no fractions).
None
1 byte
Decimal
Stores numbers from –10^38–1 through 10^38–1 (.adp)
Stores numbers from –10^28–1 through 10^28–1 (.mdb)
28
12bytes
Integer
Stores numbers from –32,768 to 32,767 (no fractions).
None
2 bytes
Long Integer
(Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).
None
4 bytes
Single
Stores numbers from –3.402823E38 to –1.401298E–45
for negative values and from 1.401298E–45 to 3.402823E38 for positive values.
7
4 bytes
Double
Stores numbers from –1.79769313486231E308 to
–4.94065645841247E–324 for negative values and from
4.94065645841247E–324 to 1.79769313486231E308 for positive values.
15
8 bytes
Format:
This property is available for all properties except OLE Object Fields. It allows you to specify how the data access displays your data which is different from the actual keystrokes used to enter the data. It only affects only the way data is displayed and not the actual data that is stored. The available format differs depending upon the Field’s data type.
Following Symbols are used to change the format of text and memo data types:
> : Converts all the characters entered in a field to Uppercase.
< : Converts all the characters entered in a field to Lowercase.
Following table displays the format supported by Number and Currency data type:
Format Type
Number entered
Number displayed
General
987654.321
987654.321
Currency
987654.321
$ 987654.32
Memo
987654.321
€987654.32
Fixed
987654.321
987654.321
Standard
987654.321
987654.321
Percent
.321
32.1%
Scientific
987654.321
9.87654321E+05
Following table displays the format supported by Date/Time data type:
Format
Display
General Date
01/16/2002 2:25 pm
Long Date
Wednesday, January 16, 2002
Medium Date
16-January-02
Short Date
01/16/02
Long Time
02:30:35 pm
Medium Time
2:30
Short Time
14:30
Decimal Places:
Is valid for only numeric and currency data. The decimal places can be from o to 15 depending upon the field size.
Input Mask:
Is available for text, Number, Date/Time and Currency fields. The format property affects how data is displayed but the Input Mask property controls how data is stored in a field. You can assign Input Mask for a field by using wizard or just typing in the Input Mask field.
If you give the Input Mask for PhoneNumber Field with Text data type as \(999") "009\-000009;;_, the first backslash causes the character that follows it ( parenthesis) to be displayed as literal. The three nines allow optional numbers or spaces to be entered. The Parenthesis and space within the quotation marks are displayed as literal. The two zeros require values 0 through 9 and the single nine require optional value or space. The dash that follows the backslash is displayed as literal. Five additional numbers and an optional number is required. There is nothing between the two semicolons, which indicates that the literals are not stored in the field. The second semicolon is followed by an underscore, so an underscore is displayed to indicate the space where the user types the characters.
Input Mask can be given using wizard, which appears as:
Caption:
Is used as an alternate name for the fields on tables, forms and reports, which makes the field name more explanatory in forms and reports.
Default Value:
Value given in the Default Value is displayed automatically for the field when add a new record to the table. Default value is only the initial value of a field that can be changed during the data entry and is created for the values, which you have to type more, so it saves your data-entry time.
Validation Rule:
Validation enables you to limit the values that can be entered into a field. Validation rule entered in the table are automatically applied to the forms and queries built from the table. You can give user-friendly message if the validation rule is violated using the validation text.
Take a look at some validations.
Validation
Description
="Sales"
Only the value "sales" can be given.
<100
Values less than 100 can be entered.
<=100
Values less than 100 or equal to 100 can be entered.
>100
Values greater then 100 can be entered.
>=100
Values greater then 100 or equal to 100 can be entered.
<>“Sales”
All the values except sales can be entered.
"CA" or "NY"
Values either CA or NY can be entered.
Like “pec*”
Values starting with "pec" and followed by any character can be entered.
Like "pec###"
Values starting with "pec" and followed by any 3 numbers can be entered.
Between 5000 And 10000
Integer Values between 5000 and 10000 can be entered.
In ("manager","faculty", "director") or Is Null
Accepts values like manager, faculty, director or null value only.
Not "Sales"
Accepts all the values except "sales".
<Date ( )
Accepts all the dates less than the current date.
Between #1/1/95# And
#12/31/95#
Accepts all the date values of the year 1995.
Validation Text:
Is used to give the user-friendly error message if the user enters the data that violates the validation rule. In validation rule you must use only the text values. When validation text is not entered, Access automatically generates standard error message whenever the validation rule is violated.
Required:
Determines whether the field requires a value or not. If the Required property is set to yes then the field value should be entered. This property is useful for foreign key fields.
Allow Zero Length:
Is similar to Required property and determines whether you can allow the user to enter a zero-length string (" "). A zero-length string is not the same as null (absence of an entry). When Required property is set to Yes and Allow zero length property is set to no, data must be entered in the field.
Indexed:
Are used to improve the performance when the user searches a field and are generally used for the fields which are regularly used for searching, sorting and as a criteria for queries.
Unicode Compression:
Is applied for text and memo fields only and is used to designate whether or not you want the data in the field to be compressed using Unicode compression. In Access 2000, data is stored in the Unicode 2-byte representation format. Although this format requires more space for each character (2 bytes rather than 1), the Unicode compression property allows the data to be compressed if possible. If the Unicode compression property is set to Yes, the data in the column is stored in a compressed format.
Importing Table From Another Database
Steps:
- Click on the Newbutton in the database window, Select the Import table option and click on the Ok button.
- Select the database from where you are importing the table and click on the Import button.
- Select the table that you want to import and click on the Ok button.
Linking Tables Steps:
- Click on the New button in the database window, Select the Link Table option and click on the OK button.
- Select the database from where you are linking a table and click on the Link button.
- Select the table to be linked and click on the OK button.
Modifying the Table Design
Adding Records
New records can be added to the table in datasheet view by typing in the record beside the asterisk (*) that marks the new record or by clicking the new record button at the bottom of the datasheet, or new record button in the table datasheet toolbar, to skip to the last empty record.
Editing Records
To edit records, place the cursor in the record that is to be edited and make the necessary changes. Use the arrow keys to move through the record grid. The previous, next, first, and last record buttons at the bottom of the datasheet are helpful in moving through the datasheet.
Deleting Records
To delete a record from a datasheet, place the cursor in any field of the record row and select Delete Record option from the Edit menu or click the Delete Record button on the table datasheet toolbar.
Adding and Deleting Columns
If you need to delete any fields (displayed as columns in the datasheet) from a table or add a new field to table always use the design view because more options are available but they can also be quickly added in datasheet view.
To add a column,
Select a column in front of which you want the column to appear and Click on the Columnoption in the Insert menu.
To Delete a column,
Place the cursor in the column and select the Delete Columnfrom the Edit menu.
Resizing Rows and Columns
The height of a row on a datasheet can be changed by dragging the gray sizing line between row labels up and down with the mouse. When you change the height of one row, the height of all rows in the datasheet will be changed to the new value.
The width of the column on a datasheet can be changed in a similar way by dragging the sizing line between columns or you can double click on the line to make the column automatically fit to the longest value of the column. Unlike rows, columns on a datasheet can be different widths.
The exact values can be assigned to the rows by selecting Row Heightoption from the Format menu and exact values to column can be assigned to column by selecting Column Width from Format menu.
Freezing Columns
Similar to freezing panes in Excel, columns on an Access table can be frozen. Freezing is helpful if the datasheet has many columns and relevant data would otherwise not appear on the screen at the same time.
To freeze columns,
Select the columns that you want to freeze and select Freeze Columns option from the Format menu.
To Unfreeze columns,
Select the columns to unfreeze and select Unfreeze All Columns option from the Format menu.
Hiding Columns
Columns can also be hidden from view on the datasheet though they will not be deleted from the database.
To hide columns,
Place the cursor in any record in the column or highlight multiple adjacent columns by clicking and dragging the mouse along the column headers, and select Hide Columns options from the Format menu.
To unhide columns,
Select Unhide Columns option from the Format menu.
A window displaying all of the fields in the table is listed with check boxes beside each field name. Check the boxes beside all fields that should be visible on the data table and click the Close button.
Soring and Filtering Sorting and Filtering allow you to view records in a table in a different way either by reordering all of the records in the table or view only those records in a table that meet certain criteria that you specify.
Sorting
You may want to view the records in a table in a different order than they appear such as sorting by a date or in alphabetical order. In table view, place the cursor in the column that you want to sort by and Click on the Records menu and then on the Sort Ascendingoption of the sort submenu to sort the record in Ascending order. Click on the Records menu and then on the Sort Descendingoption of the sort submenu. The records can also be sorted by clicking on the Sort Ascendingor Sort Descendingbuttons on the toolbar.
To sort by more than one column, select the Columns and select one of the sort methods stated above.
Filter by Selection
Filter by selection is used when you have to filter records that contain identical data values in a field such as filtering out all of the records that have the value "maharajgunj" in address field.
To Filter by Selection,
Place the cursor in the address field and make sure that your cursor is pointing to the value you want to sort by. Then Click on the Filter by Selection button on the toolbar or select Filter By Selectionoption from the Filter submenu of the Records menu.
Filter by Form
If the table is large, it may be difficult to find the record that contains the value you would like to filter by so using Filter by Form may be advantageous than using Filter by Selection. This method creates a blank version of the table with dropdown menus for each field that each contain the values found in the records of that field.
To Filter By Form,
Click on the Filter by Form button in the toolbar or select Filter by Form from the Filter submenu of the Records menu. Filter by Form window is displayed as below:
In the Look for tab at the bottom of the Filter by Form window, click in the field to enter the filter criteria. To specify an alternate criteria if records may contain one of two specified values, click the Or tab at the bottom of the window and select another criteria from the drop-down menu. More Or tabs will appear after one criteria is set to allow you to add more alternate criteria for the filter. After you have selected all of the criteria you want to filter, click the Apply Filter button on the toolbar then the output will be as follows:
The following methods can be used to select records based on the record selected by that do not have exactly the same value. Type these formats into the field where the drop-down menu appears instead of selecting an absolute value.
Format
Description
Like "*Street"
Selects all records that end with "Street"
<="G"
Selects all records that begin with the letters A through G
>1/1/00
Selects all dates since 1/1/00
<> 0
Selects all records not equal to zero
Relationships
A relationship exists between two tables when a key field(s) from one table is matched to a key field(s) in another table. The fields in both tables usually have same name though not compulsory but the data type and field size of the fields must be same. The fields that participate in relationship are called key fields. The Key field in the master table is called Primary Key and the Key field in another field is called Foreign Key. The right type of relationship between two tables ensures
· Data integrity
· Optimal performance
· Ease of use in designing system objects
Three types of relationship can exist between tables in a database, which are: One-to-Many, One-to-One, Many-to-Many.
One-to-Many Realationship
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in one table can have many related records in another table. A common example of one-to-many relationship is set up between a Customer table and an Order table. For each customer in Customer table, you may have many orders in the Orders table and on the other hand, for each order in the Order table you can have only one customer in the Customer table. The Customer table is on the one side of the relationship and the Order table is on the many side of the relationship. In order for this relationship, the field joining the two tables on the one side of the relationship must be unique. So, the field that joins the two tables on the one side of the one-to-many relationship must be Primary Key or must have a unique index.
One-to-One Relationship In a one-to-one relationship, each record in the table on the one side of the relationship can have only one matching record in the table on the many side of the relationship. This relationship is not common and is used only in special circumstances. The following are the most common reasons why one-to-one relationship is created:
· The amount of the fields required for a table exceeds the number of the fields allowed in an Access table (Access allows only 255 fields per table).
· Certain fields that are included in a table need to be much more secure than other fiel;ds included in the same table.
· Several fields in a table are required for only a subset of records in the table.
Many-to Many Relationship In a many-to-many relationship, records in both tables have matching records in the other table. A many-to-many relationship cannot be directly defined in Access, you must develop this kind of relationship by adding a table called a junction table. The junction table is related to each of the both tables as one-to-many relationships. Example of many-to-many relationship is the Orders table and the Products table. Each order provably will contain multiple products and each product is found in many different orders. The solution is to create a third table called OrderDetails, which is related to Orders table in a one-to-many relationship based on OrderID field and related to Products table in a one-to-many relationship based on ProductID field.
Establishing Relationships
Steps:
1. Click on the Tools menu and then on the Relationships option. Or, you can also click on the Relationship button in the toolbar.
2. Select the table or query that take part in the relationship and click on the add button. Click on the close button to close the show table dialog box.
3. Click and drag the field from one table to the matching field in the other table. The edit relationship dialog box appears.
4. Determine whether you want to establish referential integrity, and whether you want to cascade update related fields or cascade delete related records by enabling the appropriate checkboxes.
5. Click on the create button when finished.
Referential Integrity Establishing relationship is quite easy but establishing right kind of relationship is little more difficult. Referential integrity cannot be established when neither of the related field is a primary key and when neither has a unique index. Referential integrity consists of a series of rules that are applied by the jet engine to ensure that the relationships between tables are maintained properly.
At the most basic level, referential integrity rules prevent the creation of orphan records in the table on the many side of the one-to-many relationship. After establishing relationship between Customers and the Orders table, all the records in the Orders table must be related to a particular record in the Customers table. Following conditions must be met to establish referential integrity between two tables:
· The matching field on the one side of the relationship must be Primary key or must have unique index.
· The matching fields must have same data types (for linking purpose, AutoNumber fields match Long integer fields). With the exception of text fields, they also must have the same size. Numbers fields on the both sides must have the same size.
· Both tables must be part of the same Access database.
· Existing data within the two tables should not violate any referential integrity rules. All orders in the Orders table must relate to existing customers in the Customers table.
After the referential integrity is established between the two tables, the following rules are set:
· You cannot enter a value in the foreign key of the related table that does not exist in the primary key of the primary table.
· You cannot delete a record from the primary table if corresponding records exist in the related table.
· You cannot change the value of a primary key on the one side of a relationship if corresponding records exist in the related table.
If any of the above three rules are violated while entering the data on a table in which referential integrity is enforced between the tables, an appropriate message is displayed according to the rule violated.
While working on the data, you may, in some case, need to override the above last two rules of referential integrity. i.e. you may need to delete record or change the value on a primary table. This can be done by checking the two checkboxes below the Enforce Referential Integrity option of the Edit Relationships dialog box.
Cascade Update Related fields
The Cascade Update Related Fields option is available only if referential integrity has been established between the tables. With this option selected, the user is not prohibited from changing the primary key value of the record on the one side of the relationship. Instead, when an attempt is made to modify the field joining the two tables on the one side of the relationship, the change is cascaded down to the Foreign Key field on the many side of the relationship.
Cascade Delete Related Records The Cascade Delete Records option is available only if referential integrity has been established between the tables. With this option selected, the user can delete a record on the one side of a one-to-many relationship. A Customer can be deleted even if the customer has existing orders, for example. Referential integrity is maintained between the tables because Access automatically deletes all related records in the child table.
Enforcing Referential Integrity
In the Edit Relationships dialog box, check the Enforce Referential Integrity option. Check the both the Cascade Update Related Fields option and the Cascade Delete Related Records or only one according to your need and click on the Create button.
Creating Queries Queries can be created by using Design View or by using Wizard. Using Wizard will be easy as it guides through the process and using design view you can customize your queries.
Creating Query using Wizard:
Creating Query using the wizard is the easiest way of creating query because wizard guides you through the process of creating the query. Simple query wizard creates a basic query by guiding through all the steps of creating query. The other wizards help you create three specific types of queries: Crosstab, Find Duplicate, Find Unmatched. The Query created from wizard can be modified using the design view option. Using Design view you can create a new customized query.
Creating Query using Simple query Wizard:
Using simple query Wizard is the easiest way of creating a basic query as the wizard will guide you through the process.
Steps:
- Click on the New button Query page on the Database window, or click on the insert button and then on the Query option.
- Select Simple query wizard and click on the OK button.
- Alternately you can double click on the Create query by using the wizard option of the database window.
- Select a table of which you want to create a query and Select the fields by clicking the > button to move the field from the Available Fields list to Selected Fields. Click the double arrow button ( >>) to move all of the fields to Selected Fields. Select another table or query to choose from more fields and repeat the process of moving them to the Selected Fields box. Click Next > when all of the fields have been selected.
- Give the name for the query and click on the Finish button.
Is used to display records with duplicate values for one or more of the specified fields of a table.
Steps:
- Click the New button on the Queries database window, Select the Find Duplicates Query option from the new query dialog box and click on the OK button.
- Find Duplicates query wizard is invoked and this wizard guides you through the process of creating query.
- Select the table or query from the list provided that you want to use in the query and click on the Next button.
- Select the fields that may contain duplicate values by highlighting the names in the Available fields list and clicking the > button to individually move the fields to the Duplicate-value fields list or clicking the >> button to move all of the fields. Click on the Next button when the fields are selected.
- Select the fields that should appear in the new query along with the fields selected on the previous screen and click on the Next button.
- Give the name for the new query and click on the Finish button.
Is used to create a query that gives the records from one table that do not have corresponding values in a second table. You can use this query to find the records of the mailing list table, which do not have the related record in the contacts table.
Steps:
- Click on New button in the Query database window, Select Find Unmatched Query option in the new query dialog box and click on the Okbutton.
- Select the Table or Query that contain records you want in the query results and then click on the Next button.
- Select the table or query that contains the related records.
- Select the matching field names of tables and click on the <=> button. When the Matching Fields are selected then click on the Nextbutton.
- Select the fields that you want in the query result by highlighting the names in the Available fields list and clicking the > button to individually move the fields to the Duplicate-value fields list or clicking the >> button to move all of the fields. Click on the Next button when the fields are selected.
- Give the name for the Query and click on the Finish button.
Steps:
- Select the new button in the database window, Select the Design View option in the new query dialog box and click on the Ok button.
- Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query. Click on the Close button when the tables and queries needed have been selected.
- Add fields from the tables to the new query by double-clicking the field name in the table boxes or selecting the field from the Field: and Table: drop-down menus on the query form. You can also add the fields by dragging the field name from the field list box to the Filed: row.
- Sort the records if required by selecting an option from the Sort: field and enter the criteria for the query in the Criteria: field.
- Save the Query with a meaningful name.
Operator
Meaning
Example
Result
=
Equal to
=“Sales”
Finds only those records with “sales” as the field value.
<
Less than
<100
Finds all records with values less than 100 in that field.
<=
Less than
or equal to
<=100
Finds all records with values with values less than or equal to 100 in that fields.
>
Greater than
>100
Finds all records with values greater than 100 in that field.
>=
Greater than
or equal to
>=100
Finds all records with values greater than or equal to 100 in that field.
<>
Not equal to
<>“Sales”
Finds all records with values other than Sales in the fields.
And
Both condition
must be true
Created by adding criteria on the same line of the query grid to more than one fields.
Finds all records where the conditions in both fields are true.
Or
Either condition can be true
“CA” or “NY” or “UT”
Finds all records with the value of “CA”, “NY” or “UT” in the fields.
Like
Compares a string expression to a pattern
Like “sales*”
Finds all records with the values of “Sales” at the beginning of the field.
Between
Finds a range of values
Between 5 and 10
Finds all records with the values of 5-10
(inclusive) in the field.
In
Same as Or
In (“CA”, “NY”, “UT”)
Finds all records with the value of “CA”, “NY” or “UT” in the fields.
Not
Same as not equal
Not “Sales”
Finds all records with values other than Sales in the field.
Is Null
Finds nulls
Is Null
Finds all records where no data has been entered in the field.
Is Not Null
Finds all records not null
Is not Null
Finds all records where data has been entered in the field.
Date ( )
Current date
Date ( )
Records with the current date within a field.
Between Date And Date
A range of dates
Between #1/1/95# and #12/31/95#
All records in 1995.
Building Query Based on Multiple Table
If you have properly normalized your table data, you have to bring the data from your tables back together by using queries. When you build query a multitable query, you can combine data from related tables.
Steps:
- Click the New button in the database window, Select Design view and Click on the OKbutton.
- From the show table dialog box, add the tables needed for the query by selecting the query and clicking on the OK button. Click on the Close button when finished.
- Select the fields that you need and save the query.
One of the rules of data normalization is that the results of calculations should not be included in your database. You can output the results of calculations by building those calculations into queries, and you can display the results of the calculations on forms and reports by making the queries the foundation for a form or report.
The columns of your query results can hold the result of any valid expression, including the results of a user-defined function. This makes your queries extremely powerful. For example, the following expression could be entered:
Left(FirstName],1) & "." & Left(LastName],1) & "."
This expression would give you the first character of the first name followed by a period, the first of the last name, and another period. And even simpler expression would be this one:
[UnitPrice]*[Quantity]
This calculation would simply take the UnitPrice field and multiply it by the Quantity field. In both cases, Access would automatically name the resulting expression. To give the expression a name, such as Initials, you must enter it as follows:
Initials: Left([FirstName] ,1) & "." & Left ([LastName],1) & "."
The text preceding the colon is the name of the expression-in this case, Initials. If you don’t explicitly give your expression a name, it defaults to Exprl.
Crosstab query
Crosstab query is a kind of query operation that summarizes data from the fields in one or more tables and displays the dynaset in a datasheet format. Crosstab Query can be created by using a wizard, which guides you through the process.
Steps:
1. Click on the New button in the database window, select the Crosstab Query wizard and click on the OK button.
2. Select the table or queries that contains the fields for the Crosstab Query results and click on the Next button.
3. Select the fields that you want as the row headings and click on the Next button.
4. Select the field whose value you want as the column Headings and click on the Next button.
5. Select the field that will be used for calculation and choose a function to make the calculation. Click on the Next button.
6. Give the name for the query and click on the Finish button.
Action Queries
Using Action queries, you can easily modify data without writing any code. In fact, action queries are often a more efficient method than using code. Action queries are of four types: Update, Delete, Append and Make Table.
Update Queries
Are used to modify all records or any records meeting the specific criteria. An Update query can be used to modify the data in one field or several fields at one time (for example, a query that increases the salary of everyone in sales department by 10 percent).
Steps:
1. Click Queries in the objects list from the database window. Double click the Create query in Design view icon.
2. In the Show Table dialog box, select the tables or queries that will participate in the Update query and click on the Add button.
3. Select the Update Query option from the Query type dropdown list in the toolbar or, select Update Query option from the Query menu.
4. Add the fields to the query that will either be used for criteria or be updated as a result of a query.
5. Give the appropriate criteria in the criteria field and update expression in the Update To field.
6. Save the query and run it to view the result. Click the Yes button on the confirmation message box to finish the query process.
Delete Queries
Delete Queries permanently removes records from the table that meets the criteria specified; they are often used to remove the older records.
Steps:
1. In the Query's Design view, Select the Delete Query option from the Query type drop-down list in the toolbar or Select the Delete Query option from the Query menu.
2. Add the fields to the query grid and give the appropriate criteria.
3. Save the query and run it, a message box is displayed. Click on the Yes button to finish the query process.
Append Queries
Using Append queries, you can add records to an existing table. This is often used during an archive process. First, the records to be archived are appended to the history table and then they are removed from the master table using the delete query.
Steps:
1. In the Query's Design View, Select the Append Query option from the Query type drop-down list in the toolbar or Select the Append Query option from the Query menu.
2. Select the table to which you want the data to be added and click on the OK button.
3. Add the fields whose data you want to include in the second table to the query grid. If the field names in the two tables match, access automatically matches the field names in the destination table. If the field names do not match, you need to explicitly designate which fields in the source match which fields in the destination table.
4. Give the criteria in the query grid, save the query design and run it. Click on the Yes button to finish the query process.
Make Table Query
Append query adds records to an existing table but a Make Table Query creates a new table which is often a temporary table used for intermediary process. A temporary table is often created to freeze data while a report is being run. Another common use of a Make Table Query is to supply a subset o fields or records to another user.
Steps:
1. In the Query’s Design View, Select the Make Table Query option from the Query type drop-down list in the toolbar or Select the Make Table Query option from the Query menu.
2. Give the name for the new table that will be created when the query is run and click on the OK button.
3. Add the fields that you want to include in the new table in the Query grid. Give the criteria in the query grid if any.
4. Save the Query and run it. Click on the Yes button to finish the process.
Creating Forms
Form in Access 2000 can be created using Wizard or Design View. As in Query Wizard, you are guided through the process of creating a Form if you use wizard to create form. Form can be also created using another feature, AutoForm, Which is further of three types: Columnar, tabular and datasheet.
Create Form using AutoForm
Click the New button in the database window, select the appropriate AutoForm type from the New Form dialog box, select the table of which you want to create a form and click the OK button. An AutoForm is created for you of the type, which you have selected.
Creating Form Using Wizard
Access 2000 provides three types of wizard that you can use to create form:
Creating Forms using Form Wizard
Steps:
- Click the Create form by using wizard option on the database window. Or, Select the form wizard option from the new Form dialog box and click on the OK button.
- Select the table or query that you are using to create a form. Then, select the fields that will be included on the form by highlighting each one the Available Fields window and clicking the single right arrow button > to move the field to the Selected Fields window. To move all of the fields to Select Fields, click the double right arrow button >>. If you make a mistake and would like to remove a field or all of the fields from the Selected Fields window, click the left arrow < or left double arrow << buttons. After the proper fields have been selected, click the Next button.
- Select a layout for the form and click on the Next button.
- Columnar - A single record is displayed at one time with labels and form fields listed side-by-side in columns
- Justified - A single record is displayed with labels and form fields are listed across the screen
- Tabular - Multiple records are listed on the page at a time with fields in columns and records in rows
- Datasheet - Multiple records are displayed in Datasheet View
- Select a visual style for the form and click on the Next button.
- Give the name for the Form and click on the Finish button.
Steps:
1. Click on the New button in the Database window, select the Chart Wizard and click on the OK button.
2. Select the fields by clicking the > button to move the field from the Available Fields list to Selected Fields. Click the double arrow button >> to move all of the fields to Selected Fields. Select another table or query to choose from more fields and repeat the process of moving them to the Selected Fields box. Click on the Next button when all of the fields have been selected.
3. Select the type of chart and click on the Next button.
4. Modify the layout of the chart, Click on the Preview Chart button if you want to look how the chart will appear. Click on the Next button when layout of the form is ready.
5. Give the name for the form and click on the Finish button.
Creating Form Using Pivot Table Wizard
Pivot table wizard creates an Access form with a Microsoft Excel Pivot Table Object and the form created is useful when large volume of data is to be summarized.
Steps:
1. Select the Pivot Table Wizard option in the New Form dialog box, Select a table or query, which contains the data for the form, and click on the OK button.
2. Click on the Next button to start creating Form using Pivot Table Wizard.
- Select the fields that you use in the form by highlighting the names in the Available fields list and clicking the > button to individually move the fields to the Fields Chosen for Pivoting list or clicking the >> button to move all of the fields. Click on the Next button when the fields are selected.
5. Design the layout for the Pivot Table Form by dragging the field buttons on the right to the appropriate area of the Pivot table Form layout. Click on the OK button when you designed the layout for the Form.
6. Click on the Finish button to complete the process and you will get the output as:
7. Click on the Edit Pivot Table Object button if you want to make any change on the Pivot Table Form.
Creating Form in Design View
Steps:
1. Click the New button on the form database window. Select "Design View" and from the drop-down menu, choose the table or query the form will be associated with.
2. Select Toolbox option from the View menu to view the floating toolbar with additional options.
3. Add controls to the form by clicking and dragging the field names from the Field List floating window. Click on the View menu and then on the Field List option if the Field List do not appear by default. Access creates a text box for the value and label for the field name when this action is accomplished. To add controls for all of the fields in the Field List, double-click the Field List window's title bar and drag all of the highlighted fields to the form.
4. Save the form with an appropriate name.
Modifying Form Desing
Resizing Objects Form objects can be resized by clicking and dragging the handles on the edges and corners of the element with the mouse.
Moving Objects
To move a single control with its attached label, place your mouse over the object and click and drag. To move a control independent of its attached label, place your mouse pointer over the larger handler in the upper-left corner of the object, the mouse pointer appears as a hand with only the index finger pointing upward. Click and drag to move the control independently.
Aligning Objects
Access makes easy to align objects. To align objects, select the objects first then click on the Format menu. Select align option and then appropriate alignment according to your need.
Controlling Objects Spacing
Access gives you excellent tools for spacing the objects on your form an equal distance from one another. Select the objects you want to arrange at equal distances from each other, click on the Format menu and then on the Distribute Horizontally or Distribute Vertically. Select an appropriate option from the submenu.
Tab Order The tab order of the objects on the form can be altered by selecting Tab Order... option from the View menu. Click the gray box before the row you would like to change in the tab order, drag it to a new location, and release the mouse button.
Form Appearance Change the background color of the form by clicking the Fill/Back Color button on the formatting toolbar and click one of the color swatches on the palette. Change the color of individual form objects by highlighting one and selecting a color from the Font/Fore Color palette on the formatting toolbar. The font and size, font effect, font alignment, border around each object, the border width, and a special effect can also be modified using the formatting toolbar:
Page Header and Footer
Headers and footers added to a form will only appear when it is printed. Access these sections by selecting Page Header/Footer from the View menu. Page numbers can also be added to these sections by selecting Page Numbers option from the Insert menu. A date and time can be added by selecting Date and Time.... option from the Insert menu. Select Page Header/Footer from the View menu again to hide these sections from view in Design View.
Form Controls
Creating Application in Access is quite easy but making your application successful is much difficult. Your ability to design a user-friendly interface can make your application successful. Access and Windows programming environment offer a variety of controls that makes your application user-friendly and each one is appropriate in different situations.
Labels:
Are used to display information to your users. Labels are automatically added to your form when you add controls like text boxes, combo boxes and so on. These labels can be modified and their default caption is based on the caption property of the field. If nothing has been entered into the field's caption property, the field name is used for the label's caption. Labels can also be added using the label tool of the toolbox by clicking and then dragging it to place it on the form.
Text Boxes:
Are used to get information from the user. Bound text boxes display and retrieve field information stored in a table and Unbound text boxes gather information from the user that's not related to a specific field in specific record.
To add text box in form, Click on the text box tool in the toolbox then click and drag to place the text box on the form. The text box added is an unbound text box, if you want to bind the text box to data you have to set its Control Source property.
Combo boxes:
If there are small, finite number of values for a certain field on a form, using combo box may be a quicker and easier way of entering data. The combo box values are not displayed until the arrow button is clicked to open it.
To add Combo box in a form,
- Click on the View menuand then on the Toolbox to view the toolbox and make sure the "Control Wizards" button is pressed in.
- Click the combo box tool button and draw the outline on the form.
- Select the source type for the combo box values and click on the Next button.
- Depending on your choice in the first dialog box, the next options will vary. If you chose to look up values from a table or query, the above box will be displayed. Select the table or query from which the values of the combo box will come from. Click on the Next button and choose field that contains the values for the combo box from the table or query that was selected. Click on the Next button to proceed.
- On the next dialog box, set the width of the combo box by clicking and dragging the right edge of the column. Click on the Next button.
- Choose "Remember the value for later use" to use the value in a macro or procedure (the value is discarded when the form is closed), or select the field that the value should be stored in. Click on the Next button.
- Type the name that will appear on the box's label and click on the Finish button.
List boxes are similar to combo box, but consume more screen space. They allow you to select only from the list that's displayed. You can't type new values into a list box (as you can with a combo box).
A list box is added to the form by clicking and dragging the list box control on the toolbox, which gives you a list box wizard. List box wizard is identical to the combo box wizard. After running the list box wizard, the list box properties affected by the wizard are the same as the combo box properties.
Check boxes:
Check boxes are used when you want to limit your user to entering one of the two values. The values entered can be limited to Yes/No, True/False, or On/Off.
To add a checkbox, Click the check box tool in the toolbox, then click and drag to add a checkbox to the form. The checkbox you have added will be unbound and can be bound to data by setting the control's Control source property.
Options buttons:
Options buttons can be used alone or as a part of an option group. An option button alone can be used to display a True/False value, but this isn't a standard use of an option button (Check boxes are standard for this purpose). As a set of Option group, option button force user to select from a set of alternatives.
Toggle buttons:
Toggle buttons can be used alone or as part of an option group. A toggle button by itself can display a True/False value, but this isn't a standard use.
Option Groups:
Option groups allow the user to select from a mutually exclusive set of options. They can include check boxes, option buttons or toggle buttons but the most common implementation of an option groups is option buttons.
To add an option group,
- Click the Option Group tool on the toolbox and draw the area where the group will be placed on the form with the mouse.
- On the first window, enter labels for the options and click the tab key to enter additional labels. Click on the Next button when finished typing labels.
- On the next window, select a default value if there is any and click on the Next button.
- Select values for the options and click on the Next button.
- Choose what should be done with the value and click on the Next button.
7. Type the caption for the option group and click on the Finish button.
Command Buttons:
Command button used to perform action when clicked, in your application. Command button wizard makes you easy to create it.
To create a command button,
- Open the form in Design View and ensure that the Control Wizard button on the toolbox is pressed in.
- Click the command button icon on the toolbox and draw the button on the form.
- On the first dialog window, action categories are displayed in the left list while the right list displays the actions in each category. Select an action for the command button and click on the Next button.
- The next few pages of options will vary based on the action you selected. Continue selecting options for the command button.
- Choose the appearance of the button by entering caption text or selecting a picture. Check the Show All Pictures box to view the full list of available images. Click on the Next button when you have selected the appearance for your button.
Multiple-Page Form Using Tabs
Tab controls allow you to easily create multi-page forms. To create a form using Tabs,
1. Click the Tab Control icon on the toolbox and draw the control on the form.
2. Add new controls to each tab page the same way that controls are added to regular form pages and click the tabs to change pages.
Add new tabs or delete tabs:
Right-click in the tab area and choose Insert Page or Delete Page from the shortcut menu.
Reorder the tabs:
Right-click on the tab control and select the Page Order. In the Page Order dialog box, select the tab page and click on the Move Up or Move Down button according to the need.
Rename tabs:
Double-click on a tab and change the Name property under the Other tab of the Properties dialog box.
Password Text Fields:
To modify a text box so each character appears as an asterisk as the user types in the information, select the text field in Design View and click Properties. Under the Data tab, click in the Input Mask field and then click the button [...] that appears. Choose "Password" from the list of input masks and click Finish. Although the user will only see asterisks for each character that is typed, the actual characters will be saved in the database.
Multiple Primary Keys:
To select two fields for the composite primary key, move the mouse over the gray column next to the field names and note that it becomes an arrow. Click the mouse, hold it down, and drag it over all fields that should be primary keys and release the button. With the multiple fields highlighted, click the primary key button.
Subforms A Subform is a form that is placed in a parent form, called the main form. Subforms are particularly useful to display data from tables and queries that have one-to-many relationships. For example, in the subform below, data on the main form is drawn from an item information table while the subform contains all of the orders for that item. The item record is the "one" part of this one-to-many relationship while the orders are the "many" side of the relationship since many orders can be placed for the one item.
Creating Subform using wizard
Creating Subform using wizard is appropriate when none of the form is created. A main form and Subform can be created automatically using the form wizard if table relationships are set properly or if a query involving multiple tables is selected.
Steps:
- Double-click Create form by using wizard on the database window.
- From the Tables/Queries drop-down menu, select the first table or query from which the main form will display its data. Select the fields that should appear on the form by highlighting the field names in the Available Fields list on the left and clicking the single arrow > button or click the double arrows >> to choose all of the fields.
- From the same window, select another table or query from the Tables/Queries drop-down menu and choose the fields that should appear on the form. Click on the Next button after all fields have been selected.
- Choose an arrangement for the forms by selecting form with subform(s) if the forms should appear on the same page or Linked forms if there are many controls on the main form and a subform will not fit. Click on the Next button.
- Select a tabular or datasheet layout for the form and click on the Next button.
- Select a style for the form and click on the Next button.
- Enter the names for the main form and Subform. Click on the Finish button to create the forms.
If the main form or both forms already exist, the Subform Wizard can be used to combine the forms.
Steps:
- Open the main form in Design View and make sure the Control Wizard button on the toolbox is pressed in.
- Click the Subform/Subreport icon on the toolbox and draw the outline of the Subform on the main form.
- If the Subform has not been created yet, select "Use existing Tables and Queries". Else, select the existing form that will become the Subform. Click on the Next button.
- The next dialog window will display table relationships assumed by Access.
- Select one of these relationships or define your own and click on the Next button.
On the final dialog box, enter the name of the Subform and click on the Finish button.
Forms have many properties that can be used to affect their look and behavior. These properties are broken down into categories like: Format, Data, Event
and Other. To open the properties dialog box, Select the form and click on the properties button in the toolbar.
Format Properties of Form
The Format properties of form affect its physical appearance. Forms have 26 Format properties.
Caption:
The Caption property sets the text that appears on the form’s title bar. This property can be customized at runtime. For example, you could include the name of the current user or specify the name of the client for whom an invoice has been generated.
Default:
The Default View property allows you to select three options:
- SINGLE FORM—Only one record can be viewed at a time.
- CONTINUOUS FORM—As many records as will fit within the form window at displayed at one time, each displayed as the detail section of the single form.
- DATASHEET—Displays the records in a spreadsheet—like format, with the rows representing records and the columns representing the fields.
Views Allowed:
The Views Allowed property determines whether the user is allowed to switch from Form view to Datasheet view of vice versa. The default view property determines the default display mode for the form, but Views Allowed determines whether the user is permitted to switch out of the default view.
Scroll Bars:
The Scroll Bars property determines whether scrollbars appear if the controls on the form don’t fit within the form’s display area. You can select from vertical and horizontal, neither vertical nor horizontal, just vertical, or just horizontal.
Record Selector:
A record selector is the gray bar to the left of the record of the Form view or the gray box to the left of each record in the Datasheet view. It’s used to select a record to be copied or deleted. The Record Selectors property determines whether the records selectors appear. If you give a user the custom menu, you can opt to remove the record selector to make sure the user copies or deletes records using only the features specifically built into your applications.
Navigation Buttons:
Navigation buttons are the controls that appear at the bottom of a form; they allow to user to move from record to record within the form. The Navigation Buttons property determines whether the navigation buttons are visible. You should set it to No for any dialog forms, and you might want to set it to No for data-entry forms, too, and ad your own tool bar and command buttons that enhance or limit the standard buttons functionality. For example, in a client/server environment, you might not want to give users the ability to move to the first or last record because that type of record movement can be inefficient in a client/server architecture.
Dividing Lines:
The Dividing Lines property indicates whether you want a line to appear between records when the default view of the form is set to Continues Forms.
Auto Resize:
The Auto Resize property determines whether the form is automatically sized to display a complete record.
Auto Center:
The Auto Center property specifies, whether you want the form to automatically to be centered within the Application window whenever it is opened.
Border Style:
The Border Style property is far more powerful than its name implies. The options for the Border Style property are None, Thin, Sizable, and Dialog. The border style is often set to None or splash screens, which means the form has no border. A Thin Border is not resizable; the Size command is not available in the Control menu. This setting is good choice for pop-up forms, which remain on top even when other forms are given the focus. A Sizable Dialog border looks like a Thin Border. A form with a border style of Dialog can’t be maximized, minimized, or resized. Once the border style form is set to Dialog, the Maximize, Minimize, and Resize options aren’t available in the form’s Control menu. The Dialog border is often used along with the Pop Up and Modal properties to create custom dialog box.
Control Box:
The Control Box property determines whether a form has a Control menu. You should use this option carefully. One of your responsibilities as an Access programmer is to make sure applications comply with Windows standards. If you look at the Windows Programs you use, you’ll find very few forms without Control menu boxes. This should tell you something about how to design your own applications.
Min Max Buttons:
The Min Max Buttons property indicates whether the form has maximize and minimize buttons. The available options are None, Min Enable, Max Enable and Both Enabled. If you remove one or both the buttons, the appropriate options also become unavailable in the Control menu. The Min Max property is ignored for forms with a border style of None and Dialog. As with the Control Box property, people rarely use this property. To make applications comply with Windows standards, set the Border Style property, and then inherit the standard attributes for each border style.
Close Button:
The Close Button property determines whether the user can close the form using the Control menu of double-clicking the Control icon. If you set the value of this property to no, you must give your user to another way to close the form, otherwise, the user might have to reboot his or her computer to close your application.
Whats This Button:
The Whats This Button property specifies whether you want the Whats This Button add to the Forms’ title bar. This features works only when the form’s Min Max button is set to No. When the Whats This Button property is set to Yes, the user can click on the Whats This Button and then click on an object on the form to display Help for that object. If the selected object has no Help associated with it, Help for the form is displayed, and if the form has no Help associated with it, Microsoft Access Help is denied.
Width:
The Width property is used to specify the form’s width. This options is most often set graphically by clicking and dragging to select an appropriate size for the form. You might want to set this property manually when you want more than one form to be the exact same size.
Picture, Picture Type, Picture Size, Picture Alignment, and Picture Tiling:
The Picture property let you select and customize the attributes of a bitmap used as the background for a form.
Grid X, Grid Y:
The Grid X and Grid Y properties can be used to modify the spacing of the horizontal and vertical lines the form when in Design View. By setting these properties, you can affect how precisely you place objects on the form when Snap to grid is active.
Layout for print:
The Layout for Print property specifies whether screen or printer fonts are used on the form. If you want to optimize the form for printing rather than display, set this property to Yes.
SubdatasheetHeight:
The SubdatasheetHeight property us used to designate the maximum height for a subdatasheet.
SubdatasheetExpanded:
The SubdatasheetExpanded property allows you to designate whether a subdatasheet is initially displayed in an expanded format. When set to False, the subdatasheet initially appears collapsed. When set to True, the subdatasheet appears in an expanded format.
Palette Source:
The Palette Source property determines the source for selecting colors for a form.
Data Properties of a Form
The Data properties of a form are used to control the source for the form’s data, what sort of actions the user can take on the data in the form, and how the data in the form is locked in a multi-user environment. There are 10 data properties of the form.
Record Source:
The Record Source property indicates the Table, Stored Query, or SQL statement on which the form’s record are based. After you have selected a record source for a form, the controls on the form can be bound to the fields in the record source.
Filter:
The Filter property is used to automatically load a stored filter along with the form. I prefer to base a form on a query that limits the data displayed on the form. The query can be passed parameters at runtime to customize exactly what data is displayed.
Order By:
The Order By property specifies in what order the records on a form appears. This property can be modified at runtime to change the order in which the records appear.
Allow Filters:
The Allow Filters property allows you to control whether the records can be filtered during the runtime. When this option is set to No, all filtering options become disabled to the user.
Allow Edits, Allow Deletions, Allow Additions:
These properties let you specify whether the user can edit data, delete records, or add records from within the form. These options can’t be override any permissions that have been set for the forms’ underlying table or queries.
Data Entry:
The Data Entry property determines whether your users can only add records within a form. Set this property to Yes if you don’t want your users to view or modify existing records but want them to be able to add new records.
Recordset Type:
The Recordset Type property gives you three options Dyanset, Dynaset (Incosistent Updates), and Snapshot. Each offers different performance and updating capability. The dynaset option creates a fully updateable recordset. The only exceptions to this rule involve records or fields that can’t be updated for some other reason. An example is a form based on query involving a one-to-many relationship. The join field on the “one” side of the relationship can be updated only if the Cascade Update Related Records feature has been enabled. The Dynaset (Inconsistent Updates) options allow all the tables involved in the query. The Snapshot option doesn’t allow any updating.
Record Locks:
The Record Locks property specifies the locking mechanism to be used for the data underlying the forms recordset. Three options are available. The No Locks option—the least restrictive locking mechanism—provides optimistic locking; that is, Access doesn’t try to lock the record until the user moves it off. This option can lead to potential conflicts when two users simultaneously make changes to the same record. The all records options locks all records underlying the form the entire time the form is open. This is the most restrictive option and should be used only when it’s necessary for the forms’ user to make sure other users can view, but not modify, the forms’ underlying recordset. The Edited Record option locks a 2KB page of records as soon as a user starts editing the data in the form. This option provides pessimistic locking. Although it averts conflicts by prohibiting two users from modifying a record at the same time, it can lead to potential locking conflicts.
Other Properties of the Form
Pop Up:
The Pop Up property indicates whether the form always remains on top of the other windows. This property is often set to Yes, along with the Modal property, for custom dialog boxes.
Modal:
The Modal property indicates whether focus can be removed from a form while it’s open. When the Modal property is set to Yes, the form must be closed before the user can continue working with the applications. As mentioned, this property is used with the Pop Up property to create custom dialog boxes.
Cycle:
The Cycle property controls the behavior of the Tab key in the Form. The options are All Records, Current Record, and Current Page. When the Cycle Property is set to All Records, the user is placed on the next record when he or she presses Tab from the last control on the Form. With Current Record, the user is moved from the last control on the form to the first control on the same record. The current page options refers only to the multipage forms; when the Cycle property is set to Current Page, the user tabs from the last control on the page to the first control on the same page All three options are affected by the tab order of the objects on the form.
Menu Bar:
The Menu Bar property specifies a menu bar associated with the form. The menu bar, sometimes referred to as a command bar in Access 2000, is created by using the Customize dialog box, available by choosing Toolbars from the View menu and then selecting Customize.
Toolbar:
The Toolbar property designates a toolbar associated with the form. The toolbar, sometimes referred to as a command bar in Access 2000, is created by using the Customize dialog box. The toolbar you select is displayed whenever the form has the focus.
Shortcut Menu, Shortcut Menu Bar:
The Shortcut Menu property indicates whether a short- cut menu is displayed when the user clicks with the right mouse button over an object on the form. The Shortcut Menu Bar property lets you associate a custom menu with a control on the form or with the form itself. As with a standard menu bar, a shortcut menu bar is created by choosing Toolbars from the View menu and then selecting Customize.
Fast Laser Printing:
The Fast Laser Printing property determines whether lines and rectangles print along with the form. When this property is set to Yes, you'll notice a definite improvement when printing the form to a laser printer.
Help File, Help Context ID:
The Help File and Help Context ID properties are used to associate a specific Help file and topic with a form.
Tag:
The Tag property is an extra property used to store miscellaneous information about the form. This property is often set and monitored at runtime to store necessary information about the form. An example would be to add a tag to each of several forms that should be unloaded as a group.
Has Module:
The Has Module property determines whether the form has a class module. If no code is associated with your form, setting this property to No can noticeably decrease load time and improve your form's performance while decreasing the database's size.
Allow Design Changes:
The Allow Design Changes property determines whether changes can be made to the design of the form while viewing form data. If this property is set to All Views, the Properties window is available in Form view, and changes made to form properties while in Form view are permanent if the form is saved.
Control Properties
Format Properties of a Control
Format:
The Format property of a control determines how the data in the control is displayed. A control's format is automatically inherited from underlying data source. This property is used in three situations.
- When the Format property is not set for the underlying field.
- When you want to override the Format setting set for the field.
- When you want to apply a format to an unbound control
Decimal Places:
The Decimal Places property specifies how many decimal places you want to appear in the control. This property is used with the Format property to determine the control's appearance.
Caption:
The Caption property is used to specify information helpful to the user. It's available for labels, command buttons, and toggle buttons.
Hyperlink Address:
The Hyperlink Address property is available only for command buttons, images, and unattached labels. It contains a string used to specify the UNC (path to a file) or URL (Web page address) associated with the control. When the form is active and the cursor is placed over the control, clicking the control displays the specified object or Web page.
Hyperlink SubAddress:
Like the Hyperlink Address property, the Hyperlink SubAddress property is available only for command buttons, images, and unattached labels. The Hyperlink, SubAddress property is a string representing a location in the document specified in the Hyperlink Address property.
Visible:
The Visible property indicates whether a control is visible. This property can be toggled at runtime, depending on specific circumstances. For example, a question on the form might apply only to records in which the gender is set to Female; if the gender is set to Male, the question shouldn't be visible.
Display When:
The Display When property is used when you want certain controls on the form to be sent only to the screen or only to the printer. The three options are Always, Print Only, or Screen Only. An example of the use of the Display When property is a label containing instructions. You might want the instructions to appear on the screen but not on the printout.
Scroll Bars:
The Scroll Bars property determines whether scrollbars appear when the data in the control doesn't fit within the control's size. The options are None and Vertical. I often set the Scroll Bars property to Vertical when the control is used to display data from a Memo field. The scrollbar makes it easier for the user to work with a potentially large volume of data in the Memo field.
Can Grow, Can Shrink:
The Can Grow and Can Shrink properties apply only to the form's printed version. The Can Grow property, when set to Yes, expands the control when printing so that all the data in the control fits on the printout. The Can Shrink property applies when no data has been entered into the control. When this property is set to Yes, the control shrinks when no data has been entered so that blank lines won't be printed.
Left, Top, Width, Height:
These properties are used to set the control's position and size.
Back Style, Back Color:
The Back Style property can be set to Normal or Transparent. When set to Transparent, the form's background color shows through the control. This is often the preferred setting for an option group. The control's Back Color property specifies the background color (as opposed to text color) for the control.
Special effect:
The Special Effect property adds 3D effects to a control. The options for this property are flat, raised, sunken, etched, shadowed and chiseled. Each of these effects gives the control a different look.
Border Style, Border Color, Border Width:
These properties affect the look, color, and thickness of a control’s border, The border style options are Transparent, Solid, Dashes, Short, Dashes, Dots, Sparse Dots, Dash Dot and Dash Dot Dot. The Border Color Property specifies the color of the border; you can select from a variety of colors. The Border Width property can be set to one of several point sizes.
Fore Color, Font Name, Font Size, Font Weight, Font Italic, Font Underline:
These properties control the appearance of the text in a control. As their names imply, they let you select a color, font, size, and thickness for the text and determine whether the text is italicized or underlined. These properties can be modified in response to a runtime event, such as modifying a control's text color if the value in that control exceeds a certain amount. The font weight selections generally exceed what is actually available for a particular font and printer—normally, you have a choice of only Regular and Bold, whatever value you select for this property.
Text Align:
The Text Align property is often confused with the ability to align controls. The Text Align property affects how the data is aligned within a control.
Left Margin, Top Margin, Right Margin, Bottom Margin:
These properties determine how far the text appears from the left, top, right, and bottom of the control. They are particularly useful with controls such as text boxes based on memo fields, where the size of the control is large.
Line Spacing:
The Line Spacing property is used to determine the spacing between lines of text in a multiline control. This property is most commonly used with a text box based on a memo field.
Is Hyperlink:
This property, when set to Yes, formats the data in the control as a hyperlink. If the data in the control is a relevant link (that is, http://www.sudguru.com) the data will function as hyperlink.
Data Properties of a Control
Control Source:
The Control Source property specifies the field from the record source that's associated with a particular control. A control source can also be any valid Access expression.
Input Mask:
The Format and Decimal Places properties affect the appearance of the control, but the Input Mask property affects what data can be entered into the control. The input mask of the field underlying the control is automatically inherited into the control. If no input mask is entered as a field property, the input mask can be entered directly in the form. If the input mask of the field is entered, the input mask of the associated control on a form can be used to further restrict what is entered into that field via the form.
Default Value:
The Default Value property of a control determines the value assigned to new records entered in the form. This property can be set within the field properties. A default value set at the field level is automatically inherited into the form. The default value set for the control overrides the default value set at the field level.
Validation Rule, Validation Text:
The validation rule and validation text of a control perform the same function as they do for a field.
Enabled:
The Enabled property determines whether you allow a control to get focus. If set to No, the control appears dimmed.
Locked:
The Locked property determines whether the data in the control can be modified. When the Locked property is set to Yes, the control can get focus but can't be edited. The Enabled and Locked properties of a control interact with one another.
Filter Lookup:
The Filter Lookup property indicates whether you want the values associated with a bound text box to appear in the Filter By Form window.
Other Properties of a Control
Name:
The Name property allows you to name the control. This name is used when you refer to the control in code and is also displayed in various drop-down lists that show all the controls on a form. It's important to name your controls because named controls improve your code's readability and make working with Access forms and other objects easier.
Status Bar Text:
The Status Bar Text property specifies the text that appears in the status bar when the control gets focus. This property setting overrides the Description property that can be set in a table's design.
Enter Key Behavior:
The Enter Key Behavior property determines whether the Enter key, causes the cursor to move to the next control or add a new line in the current control. This setting is often changed for text boxes used to display the contents of Memo fields.
Allow AutoCorrect:
The Allow AutoCorrect property specifies whether the AutoCorrect feature is available in the control. The AutoCorrect feature automatically corrects common spelling errors.
Vertical:
The Vertical property is used to control whether the text in the control is displayed horizontally or vertically. The default is No, or horizontal. When vertical is selected, the text within the control is rotated 90 degrees.
Auto Tab:
The Auto Tab property, when set to Yes, automatically advances the cursor to the next control when the last character of an input mask has been entered. Some users like this option and others find it annoying, especially if they must tab out of some fields but not others.
Default:
The Default property applies to a command button or ActiveX control and specifies whether the control is the default button on a form.
Cancel:
The Cancel property applies to a command button or ActiveX control. It indicates you want the control's code to execute when the Esc key is pressed while the form is active.
Auto Repeat:
The Auto Repeat property specifies whether you want an event procedure or macro to execute repeatedly while the command button is being pressed.
Status Bar Text:
The Status Bar Text property specifies the message that appears in the bar when the control has the focus.
Tab Stop:
The Tab Stop property determines whether the Tab key can be used to enter a control. It's appropriate to set this property to No for controls whose values rarely get modified. The user can always opt to click in the control when necessary.
Tab Index:
The Tab Index property sets the tab order for the control. Set the Tab Index property by using View, Tab Order, rather than by setting the value directly in the Control's Tab Index property.
Shortcut Menu Bar:
The Shortcut Menu Bar attaches a specific menu to a control. The menu bar appears when the user right-clicks the control.
ControlTip Text:
The ControlTip Text property specifies the tooltip associated with a control. The Tooltip automatically appears when the user places the mouse pointer over the control and leaves it there for a moment.
Help Context ID:
The Help Context ID property designates the Help topic associated with a particular control.
Tag:
The Tag property is an extra property you can use to store information about a control. Your imagination determines how you use this property. The Tag property can be read and modified at the runtime.
Creating Switchboard Form
Steps:
- Click on the Tools menu and then on the Database Utilities option. Select Switchboard manager from the submenu. A dialog box is displayed that prompts you to create a new switchboard, click on the Yes button.
- In the Switchboard manager, Select existing switchboard page or if you wish to add a new switch board page, click on the New button and type the name of the switchboard and click on the Ok button.
- When the name of the switchboard is selected, click on the Edit button.
- Type the text for the button label, choose a command and option according to the command. Click on the OK button when every option are entered in the Edit switchboard Item dialog box.
- Similarly add Switchboard and the Items for the switchboard.
Reports in Access 2000 can be created using Wizard or Design View. As in Query and Form Wizard, you are guided through the process of creating a Report if you use wizard. Report can be also created using AutoReport feature as Form can be, which is further of two types: Columnar and tabular.
Create Report Using AutoReport Feature
Click the New button in the database window, select the appropriate AutoReport type from the New Report dialog box, select the table of which you want to create a Report and click the OK button. An AutoReport is created for you of the type, which you have selected.
Create Report using Wizard:
Access 2000 provides three types of wizard that you can use to create Report:
Creating Report using Report Wizard
1. Double-click the "Create report by using wizard" option on the Reports Database Window.
2. Select the information source for the report by selecting a table or query from the Tables/Queries drop-down menu. Then, select the fields that should be displayed in the report by transferring them from the Available Fields menu to the Selected Fields window using the single right arrow button > to move fields one at a time or the double arrow button >> to move all of the fields at once. Click on the Next button to move to the next screen.
3. Select fields from the list that the records should be grouped by and click the right arrow button > to add those fields to the diagram. Use the Priority buttons to change the order of the grouped fields if more than one field is selected then Click on the Next button.
4. If the records should be sorted, identify a sort order here. Select the first field that records should be sorted by and click the A-Z sort button to choose from ascending or descending order then Click on the Next button.
5. Select a layout and page orientation for the report and then click on the Next button.
6. Select a color and graphics style for the report and then click on the Next button.
7. Type the name for the report and select to open it in either Print Preview or Design View mode. Click on the Finish button to create the report.
Creating Report Using Chart Wizard: Steps:
- Click on the Chart wizard option in the new Report dialog box, select the table or query where the data for the report come from and click on the OK button.
- Select the fields by clicking the > button to move the field from the Available Fields list to Fields for Chart. Click the double arrow button >> to move all of the fields to Selected Fields. Click on the Next button when all of the fields have been selected.
- Select the type of chart you would like to create and click on the Next button.
- Design the Layout for the chart report and click on the Next button.
- Give the name for the report and click on the Finish button.
Steps:
1. Select Label Wizard option from the New report dialog box, select a table or query where the data for the report come from and click on the OK button.
2. Select a Label size for your label in the report and click on the Next button.
3. Select a font and color for the text in the report and click on the Next button.
4. Select the fields that contains the data for the label in the report by selecting the field and clicking on the single right arrow > button. Click on the Next button when the fields are selected.
5. Select the fields in the Available fields list, which you would like to sort by and click on the single arrow > button to select a single field and click on the double arrow >> button to select all the fields. Click on the Next button when the fields are selected.
6. Give the name for the Report and click on the Finish button.
Creating Report in Design View
Steps:
- Double click on the Create report in Design view option.
- A blank grid with a Field Box and form element toolbar is seen that looks similar to the Design View of forms. Design the report in much the same way you would create a form. Double-click the title bar of the Field Box to add all of the fields to the report at once. Use the handles on the elements to resize them, move them to different locations, and modify the look of the report by using options on the formatting toolbar.
- Click the Print View button at the top, left corner of the screen to preview the report.
Steps:
- Click on the File menu and then on the Page Setup to modify the page margins, size, orientation, and column setup.
- After all changes have been made, print the report by selecting Print option from the File menu or click the Print button on the toolbar.
Pages in Access 2000 can be created using Wizard or Design View. As in other database object Wizard, you are guided through the process of creating a page if you use wizard. Pages can be also created using the existing page or using another feature, AutoPage.
Create Page using AutoPage
Click the New button in the database window, select the AutoPage:Columnar from the New Form dialog box, select the table of which you want to create a page and click the OK button. An AutoPage is created for you of the type, which you have selected.
Creating Page Using Page Wizard:
Steps:
- Select Page wizard option from the New page dialog box, Select the table or query where the data for the page come from and click on the OK button.
- Select the table or query that you are using to create a Page. Then, select the fields that will be included on the Page by highlighting each one the Available Fields window and clicking the single right arrow button > to move the field to the Selected Fields window. To move all of the fields to Select Fields, click the double right arrow button >>.
- Select the field that you want to group by if any and click on the Next button.
- Select the fields by which you want to sort your records and click Next button.
- Give the name for the Page and click on the Finish button.
Steps:
- Select the existing webpage from the New page dialog box and click Ok button.
- Select the webPage and click on the Open button.
Steps:
- Double click on the create data access page in design view option.
- Modify the design of the Page as you have designed the other database objects.
- Save the page for the further purpose.
Steps:
- Select Macros from the Objects list, Click on the New button.
- Give a name for the macro, select an action for the macro.
- Action arguments differ according to the Action chosen. Select the appropriate argument and save the macro with a meaningful name.
Select the command button in the from’s design view. Click on the properties button in the toolbar to open the properties dialog box. In the Event tab, click on the onclick field grid and choose a macro name.
The actions in the macro are executed when you click the command button in the form view.
Setting Database Password
Access allows you set password in your database to make it secure. To set the database Password, you have to open the database exclusively.
Steps:
- Open the database in exclusive mode by choosing exclusive option from the drop-down arrow next to the Open button.
- Click on the Tools option and then on the Security option. A submenu appears, click on the Set Database Password option.
- Type the Password you want to set and verify it. Then, click on the OK button.
Click on the Tools option and then on the Security option, then click on the Unset Database Password option. (Make sure that the database is opened in exclusive mode)
Type the Password in the dialog box and click on the OK button.
Review
Questions: v Differentialte between DBMS and RDBMS.
v Define Table in Access 2000.
v What is a Wizard in Access?
v What are the data types of Access 2000?
v What is a Primary Key and what’s the use of primary key in a table?
v What are Action queries used for?
v Why do you need to create a calculated field in a query?
v Differentiate between Query and Report.
v What is Referential Integrity and what is the use of enforcing Referential Integrity?
v Why do you need to choose at least three fields to create a crosstab query?
v What is the difference between Combo box and list box?
v What is a subform?
v What are macros?
v How can you create Data Access Pages in Access 2000?
v Can you set Database Password in Access in ordinary condition?