Database using MS Access (PGDCA / DCA)

Introduction to MS Access

Microsoft Access, developed by Microsoft, is a relational database management system (RDBMS) that integrates the relational Microsoft Jet Database Engine with a user-friendly graphical interface and software development tools. It is included in the professional and higher editions of the Microsoft Office suite. This comprehensive software offers all the necessary features for data definition, manipulation, and control, making it ideal for managing extensive amounts of data. Whether used as a standalone RDBMS on a single workstation or in a shared client/server mode across a network, Access supports both .accdb and .mdb formats for desktop databases. Access is commonly used by small businesses and individuals for managing data, creating forms and reports, and building simple applications.

Here's an introduction to some key features and components of Microsoft Access:

1. Relational Database Management System (RDBMS):

   - Access is based on the concept of relational databases, allowing users to organize data into tables, establish relationships between tables, and perform queries to retrieve and manipulate data.

2. Graphical User Interface (GUI):

   - Access provides a user-friendly graphical interface for designing and interacting with databases. Users can create tables, forms, queries, and reports using intuitive visual tools.

3. Tables:

   - Tables are the foundation of an Access database, where data is stored in rows and columns. Users can define fields (columns) with specific data types and properties to organize and store data efficiently.

4. Forms:

   - Forms present a user-friendly platform for entering, viewing, and editing information stored in tables. Access allows users to design custom forms with various controls (such as text boxes, buttons, and dropdown lists) to streamline data entry and improve usability.

5. Queries:

   - Queries are used to retrieve, filter, and analyze data stored in tables. Users can create queries using a visual query designer or by writing SQL (Structured Query Language) statements to perform complex data operations.

6. Reports:

   - Reports allow users to present data in a structured and visually appealing format. Users can design custom reports to summarize, analyze, and present data from tables and queries in printed or electronic formats.

7. Integration with Other Microsoft Office Applications:

   -  Access effortlessly connects with different Microsoft Office applications, like Excel, Word, and Outlook.. Users can import and export data between Access and other Office applications, and automate tasks using macros and VBA (Visual Basic for Applications) code.

8. Security and Sharing:

   - Access provides features for securing databases and controlling access to data. Users can set permissions to restrict access to specific tables, forms, queries, and reports, and encrypt databases to protect sensitive information.

Extension of MS Access

Prior to MS Access 2007, the file extension used was *.mdb. However, with the introduction of MS Access 2007, the extension was modified to *.accdb. It is important to note that older versions of Access are unable to read accdb extensions, whereas MS Access 2007 and subsequent versions have the capability to read and modify earlier Access versions. A desktop database in Access, whether it is in .accdb or .mdb format, functions as a fully operational relational database management system (RDBMS).

Objects in MS Access

In Microsoft Access, objects are the building blocks used to design and interact with databases. These objects allow users to organize, store, manipulate, and present data efficiently. Here are the main objects in Microsoft Access:

1. Tables:

   - Tables are the foundation of a database in Access. They store data in rows and columns, with each column representing a field and each row representing a record. Tables define the structure of the data and hold the actual data records.

2. Queries:

   - Queries are used to retrieve, filter, and manipulate data stored in tables. Users can create queries to extract specific information from one or more tables based on criteria defined by the user. Queries can also perform calculations, aggregate data, and generate new fields.

3. Forms:

   - Forms offer a convenient platform for users to input, access, and modify data within tables. Users can design custom forms with various controls, such as text boxes, buttons, and dropdown lists, to streamline data entry and improve usability. Forms can be used to display data from one or more tables and can include features like navigation buttons and data validation.

4. Reports:

   - Reports are used to present data from tables and queries in a structured and visually appealing format. Users can design custom reports to summarize, analyze, and present data for printing or electronic distribution. Reports can include features like headers, footers, grouping, and calculations.

5. Macros:

   - Macros are used to automate common tasks and actions in Access. Users can create macros to perform actions like opening forms, running queries, printing reports, and executing specific commands. Macros provide a way to automate repetitive tasks and streamline database operations without writing code.

6. Modules:

   - Modules are used to write and execute Visual Basic for Applications (VBA) code in Access. Users can create custom procedures and functions in modules to extend the functionality of Access databases. Modules allow for advanced customization and automation of database operations through programming.

7. Relationships:

   - Relationships define how tables are related to each other in a database. Users can create relationships between tables based on common fields to establish connections and ensure data integrity. Relationships help maintain consistency and prevent data redundancy in the database.

These objects work together to create and manage databases in Microsoft Access, providing users with the tools they need to organize, analyze, and present data effectively.

Naming convention in MS Access

Naming conventions in Microsoft Access refer to guidelines or rules for naming objects within a database. Following consistent naming conventions helps maintain organization, clarity, and consistency in the database design, making it easier for developers and users to understand and work with the database.

Here are the guidelines for naming fields, controls, and objects in Microsoft Access desktop databases:

1. Length:

   - Names can be up to 64 characters long.

2. Characters:

   - Names can contain any combination of letters, numbers, spaces, and special characters except for ".", "!", "`", and brackets "[" "]".

   

3. Leading Spaces:

   - Names cannot begin with leading spaces.

4. Control Characters:

   - Names cannot include control characters with ASCII values ranging from 0 through 31.

5. Double Quotation Mark:

   - In Microsoft Access projects, table, view, or stored procedure names cannot include a double quotation mark (").

By adhering to these guidelines, you can ensure that your database objects have valid and consistent names, facilitating efficient database design and management.

Table Creation in MS Access

Creating tables in Microsoft Access is a fundamental aspect of database design, allowing users to organize and store data efficiently. Table creation in Microsoft Access involves defining the structure of the data that will be stored in the database. 

Table Creation in MS Access

Tables can be created using one of the following methods:

1. Table Wizard: 

This feature was available in older versions of Microsoft Access. It provides a step-by-step guide to help users create tables efficiently.

• Table Wizard guides users through the process of creating a table by asking a series of questions about the data.
• Users select the fields they want to include in the table and specify the data types for each field.
• The wizard automatically generates the table structure based on the user's selections, making it easy to create tables quickly without the need for detailed design decisions.
• Table Wizard is suitable for users who are new to database design or who need to create simple tables without delving into the technical details of field properties.

2. Design View: 

This option allows users to manually design and customize the structure of the table. It provides more flexibility and control over the table's layout and properties.

• Design View allows users to create tables by defining the structure of the data.
• Users specify the field names, data types, field sizes, and other properties for each field.
• This method offers greater control and flexibility over the table's design, allowing users to customize the table according to their specific requirements.
• Design View is ideal for creating complex tables with precise data types, validation rules, and relationships.

Table Creation in MS Access

3. Datasheet View: 

This view allows users to directly enter and edit data in the table. It provides a spreadsheet-like interface for quick and easy data entry.

• Datasheet View provides a spreadsheet-like interface for entering and editing data directly into the table.
• Users can create tables by manually entering data into the datasheet or by copying and pasting data from other sources.
• Datasheet View is convenient for quickly creating simple tables or for adding data to existing tables without specifying detailed field properties.
• However, it offers limited options for customizing the table's structure and properties compared to Design View.

Before creating a table, it is necessary to create a Microsoft Access file. Once the file is created, users can proceed with creating tables using any of the aforementioned methods.

Field Properties/Attributes

In Table Design view, users specify the following attributes for each field:

Field Name: A descriptive name for the field.

Data Type: The type of data that the field will store (e.g., text, number, date/time).

Field Size: The maximum length or size of the field's value.

Format: The appearance of the data in the field (e.g., date format).

Default Value: A default value for the field, if applicable.

Validation Rule: A rule that restricts the values that can be entered in the field.

Required: Specifies whether the field is mandatory or optional.

Users can also define primary keys, which uniquely identify each record in the table, and establish relationships between tables to maintain data integrity.

Once the table design is finalized, users can save the table and begin entering data. Tables can be modified later if necessary, allowing for flexibility in database design.

Overall, table creation in Microsoft Access provides users with a powerful tool for structuring and organizing data, enabling efficient data management and analysis.

Data Type in MS Access

In Microsoft Access, data types define the kind of data that can be stored in a field within a table. Each data type has specific characteristics and limitations, which determine how data is stored, displayed, and manipulated. Here's an overview of common data types in Access, their uses, and sizes:

Data Type in MS Access

1. Text:

   - Use: Stores alphanumeric text or string values.

   - Size: Can store up to 255 characters. Use the Memo data type for longer text fields.

   - Example: Storing names, addresses, descriptions.

2. Number:

   - Use: Stores numeric values for calculations and mathematical operations.

   - Size: Various sizes depending on the subtype:

     - Byte: 0 to 255

     - Integer: -32,768 to 32,767

     - Long Integer: -2,147,483,648 to 2,147,483,647

     - Single: -3.402823E38 to -1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values

     - Double: -1.79769313486232E308 to -4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232E308 for positive values

   - Example: Storing quantities, prices, percentages.

3. Date/Time:

   - Use: Stores date and time values.

   - Size: Stores dates from January 1, 100 to December 31, 9999 and times from 0:00:00 (midnight) to 23:59:59 (one second before midnight).

   - Example: Storing dates of birth, transaction dates, appointment times.

4. Currency:

   - Use: Stores currency values with fixed decimal places for accurate financial calculations.

   - Size: Stores values ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.

   - Example: Storing monetary values, such as prices, salaries, budgets.

5. Yes/No:

   - Use: Stores boolean values representing true/false or yes/no conditions.

   - Size: One bit (0 or -1).

   - Example: Storing binary data, such as "Active" or "Inactive" status.

6. Memo:

   - Use: Stores large blocks of text or notes.

   - Size: Can store up to 65,536 characters.

   - Example: Storing lengthy descriptions, comments, or narratives.

7. Attachment:

   - Use: Stores files or documents as attachments within the database.

   - Size: Each attachment can store multiple files, limited by the overall size of the database.

   - Example: Storing images, documents, spreadsheets attached to records.

These are some of the commonly used data types in Microsoft Access, each tailored to specific types of data and usage scenarios. By selecting the appropriate data type for each field, users can ensure efficient storage, retrieval, and manipulation of data within their databases.

Conclusion

In conclusion , this blog cover a brief description of following topics

Introduction to MS Access, it's Features, Object in MS Access, Table Creation in MS Access, Data type in MS Access, File Attributes in MS Access, File Extension in MS Access, Name Convention in MS Access

In summary, I can say that these topics are related to DBMS(MS ACCESS) Subjects and very helpful for those who pursuing BCA,PGDCA, DCA ,'O' Level Courses from different universities 

I hope this blog helps you a lot Happy learning....

Frequently Asked Question(FAQ)

What is MS Access ?

Microsoft Access, developed by Microsoft, is a relational database management system (RDBMS) that integrates the relational Microsoft Jet Database Engine with a user-friendly graphical interface and software development tools.

What are the objects in MS Access?

In Microsoft Access, objects are the building blocks used to design and interact with databases.

What are data types in MS Access ?

In Microsoft Access, data types define the kind of data that can be stored in a field within a table. Each data type has specific characteristics and limitations, which determine how data is stored, displayed, and manipulated.