CHAPTER 2- MS-ACCESS (WHAT IS MS-ACCESS?).

 Ms-access is a database management system that lets you create, organize and manage your data stored in databases.

What is Data ?

Data is the information in a form suitable for processing by a computer, such as digital representation of text, numbers, graphic images or sounds. Strictly speaking, "data" is the plural of Latin word "datum", meaning an item of information; but it is commonly used in both plural and singular constructions.

      A database is an organised collection of related data you can easily retrieve and use.

      A database management system is a program, like MS-ACCESS, that allows you create, organize and manage your data stored in databases.

     In MS-ACCESS, data is organised into tables.

What is a Database ?

        Database is a collection of related items grouped together under a single heading, for example, A Customer File, Bank Account in a Bank, Employee Details etc.

According to Oxford dictionary "a database is an organized store of data for computer processing".

       A database can be said as a collection of files containing records of similar nature. Each record contains all the data relating to one subject in the file. The grouping of such files is called a Database.

       Database contains various records and fields. A column in the table can be referred to as a field and a row can be called as a record.

Each row contains complete information about one item.

Operations on Database ?

        Various types of operations can be done on the database which have been mentioned below-

1.  Insert Data-  The data can be inserted.         Into the database by creating a table.           Or multiple tables.

2.  Reproduction of Information - The stored data can be produced as output.

3.  Modification -  The data inserted into the database can be modified according to the needs.

4.  Editing - The existing information in the database can be edited to rectify the errors or for other purposes.

5.  Deletion- The unnecessary data in the database can be deleted.

6.  Sequence - The data in the database can be arranged in sequence either in ascending order or in descending order or any other criteria.

Components of Database -

1.  Tables :   Tables are where the actual 

                       Data is defined and entered.

Tables consist of records (rows) and fields (columns). A table is the primary unit of physical storage for data in a database. When a user accesses the database, a table is usually referenced for the desired data. Multiple tables might comprise a database, therefore a relationship might exist between tables. Because tables store data, a table requires physical storage on the host computer for the database.

Elements of a Table -

Fields - Fields are the columns in a table. A field describes a particular attribute of all the records in a table. For example : In The given table, ROLLNO, NAME, FATHER 'S NAME and DOB are the names of fields.

Records -

A record is a row of data in a database table consisting of a single value from each column of data in the table. The data in the columns in a table are all of the same type of data, whereas the rows represent a given instance. The above table contains 20 rows or 20 records.

Data -

Data is a set of characters that represents a valid value. In the above table 20,9020, satyen, Satyaprakash, 10/10/1987 are data values for the specific fields.

2.  Queries :  Queries are basically

                         Questions about the data in a

Database. A query consists of specifications indicating which fields, records and summarises you want to see from a database. Queries allow you to extract data based on the criteria you define.

3.  Forms :  Forms are designed to ease the data entry process . For example, you can create a data entry form that looks exactly like a paper form. People generally prefer to enter data into a well-designed form, rather than a table.

4.  Reports :  When you want to print records from your database, design a report. Access even has a wizard to help produce mailing labels.

Identifying the Features of MS-Access :-

MS-ACCESS is a multi-featured DBMS that can be used both by beginners and those who have previous DBMS experience. This section describes some of the key features of MS-ACCESS.

Windows Based Application -

MS-ACCESS is a Windows based application and therefore, we can cut, copy and paste data from any Windows application to and from MS-ACCESS. Since Microsoft corporation has developed both Windows and MS-ACCESS, the two products work well together.

Large Data Management Capacity -

MS-ACCESS maintains a single disk file for a database and all its associated onjects. The maximum size of this file can be 1GB. MS-ACCESS allows a maximum of 32,768 tables and objects in a database. However, MS-Access can create links to data tables in any other DBMS like FoxPro.

Therefore, the maximum size of tables and objects is limited only by the amount of storage space that the users have. MS-ACCESS can also support a maximum of 255 users at a time. MS-ACCESS can also support a maximum of 255 users at a time. These features make MS-ACCESS a powerful DBMS capable of handling large volumes of data, spread across several databases and users.

Importing Exporting and Linking External Files -

       MS-ACCESS can import from or export to FoxPro, MS-Excel , oracle and other data formats. Importing creates an MS-ACCESS table, exporting an MS-ACCESS table creates a file in the format that we are exporting to. Linking means, we can use external data without creating an MS-ACCESS table. We can link to FoxPro, MS-Excel and ASCII data.

Opening Access -

When you start Access 2010, you see the Microsoft Office Backstage view, where you can get information about the current database, create a new database, open an existing database etc.

Backstage view also contains many other commands that you can use to adjust, maintain or share your databases. Commands in Backstage view generally apply to entire databases, not to objects inside of a database.

You can get to Backstage view at any time by clicking the File tab.

Create a Database from Scratch -

 If none of the templates fits your needs or if you have Data in another program that you want to use in Access, you may decide that it is better to create a database from scratch.

In Access 2010, you have two choices : a standard desktop database or a web database.

For both types of database, you usually want to begin with one of these steps :

  * Create new tables and then input data

     Into those tables.

  * Import data from other sources, 

     Creating new tables in the process.

Create a Blank Database -

1.  Start Access.

2.  On the New tab in Backstage view, click

     Blank Database or Blank Web Database.

3.  On the right, type a name for your

     Database in the File Name box. To

     Change the location in which you

     Create the file, click Browse next to the

     File Name box, browse to and select

     The new location and then click OK.

4.  Click Create. Access creates the

     Database and then opens an empty

     Table (named Table 1) in Datasheet

     View.

5.  Access puts the cursor in the first empty cell in the click to Add column of the new table.

NOTES :

*  Entering data in Datasheet view is designed to be very similar to entering data in an Excel worksheet. The main restriction is that data must be entered in contiguous rows and columns, starting at the upper -left corner of the datasheet. You should not try to format your data by including blank rows or columns as you might do in an Excel worksheet, because doing so will waste space in your table. The table merely contains your data.

All visual presentation of that data will be done in the forms and reports that you design later.

*  The table structure is created while you enter data. Any time that you add a new columns to the datasheet, a new field is defined in the table. Access sets the data type of the field based on the type of data that you enter. For example, if you have a column in which you have entered only date values, Access will set the data type of that field to Date/Time. If you later attempt to enter a non-date value (such as a name or a phone number) in that field, Access displays a message informing you that the value does not match the data type of the column. When possible, you should plan your table so that each column contains the same type of data, whether it is text, dates, numbers or some other type. This makes it much easier to build queries, forms and reports that Select just the data that you want.

*  If you do not want to enter data yet, click close.

NOTE - Access will delete Table 1 if you close it without saving.

Table -

A table is a database object that you use to store data about a particular subject, such as employees or products. A table consists of records and fields.

Each record contains data about one instance of the table subject, such as a particular employee. A record is also commonly called a row or an instance.

Each field contains data about one aspect of the table subject, such as first name or e-mail address. A field is also commonly called a column or an attribute.

A record consists of field values is also commonly called a fact.

A database can contain many tables, each storing information about a different subject. Each table can contain many fields of different types of data, such as text, numbers, dates and hyperlinks.

External Data -

  You can link to a variety of external data sources, such as other databases, Text files and Excel workbooks. When you link to external data, Access can use the link as if it were a table. Depending on the external data source and the way that you create the link, you can edit the data in the linked table and can create relationship that involve the linked table. However, you cannot change the design of the external data by using the link.

Table and Field Properties -

Tables and fields have Properties that you can set to control their characteristics or behaviour.

Table Properties -

       In an Access database, table Properties are Attributes of a table that affect the appearance or behaviour of the table as a whole. Table Properties are set in the table's property sheet, in Design view. For example, you can set a table's Default View property to specify how the table is displayed by default.

Field Properties -

      A field property applies to a particular field in a table and defines one of the field's Characteristics or an aspect of the field's behaviour. You can set some field Properties in Datasheet view. You can also set any field property in Design view by using the Field Properties pane.

Closing a Database -

After finishing your work, you can close your database (without existing from MS-ACCESS) in the following manner :

Click File tab and then select Close option.