Study Guide MO-500 Certification Exam Microsoft Access Expert ( Office 2019) (eBook)

Anand Vemula (Autor)

eBook Download: EPUB

2025 | 1. Auflage
120 Seiten
Publishdrive (Verlag)
978-0-00-097340-5 (ISBN)

Infos

A comprehensive guide designed for users looking to enhance their skills in Microsoft Access. The book covers essential features, tools, and techniques for managing and analyzing data within Access. It introduces foundational concepts like creating tables, setting relationships, and designing queries to ensure data integrity and optimize functionality.

Throughout the book, readers will explore the process of designing forms and reports for displaying and interacting with data effectively. Special attention is given to more advanced features, including calculated fields, input masks, and validation rules, which ensure accuracy and consistency in data entry. The book also delves into how to filter and sort records, perform data analysis using aggregate functions, and automate tasks with macros and VBA code.

By the end of the book, readers will have a thorough understanding of how to create robust databases in Access, customize reports to meet specific business needs, and streamline data management tasks using automation. Designed for both beginners and intermediate users, the book provides step-by-step instructions, practical examples, and tips to optimize workflow, making it an invaluable resource for anyone aiming to become proficient in Access and unlock the full potential of this powerful database management system.

Textauszug

2.What’s New in Access 2019

Access 2019 includes a number of new features and enhancements that are relevant for this certification:

• Modern Charts: Create column, line, bar, and pie charts for more powerful visualizations.
• Large Number Data Type: Better support for big integer values and integration with SQL databases.
• Enhanced Accessibility: Improvements in keyboard shortcuts and screen reader compatibility.
• Support for dBASE Files: Reintroduction of support for importing and linking dBASE files.

 

Create and Manage a Database

Create a New Blank Database

To begin working in Access, users can create a new blank database directly from the launch screen. A blank database creates an .accdb file with no pre-existing objects. Here's how:

1. Launch Access and select Blank Database.
2. Provide a file name (Access adds the .accdb extension).
3. Choose a location to save the file.
4. Click Create to open the new database.

After creation, Access automatically adds a default table (Table1) in Datasheet View. You can start entering data directly or switch to Design View to define fields.

Create a Database from a Template

Access provides built-in templates for quick database creation. These include Contact Management, Asset Tracking, and Task Management.

1. Open Access and choose a template from the gallery.
2. Provide a file name and location.
3. Click Create.
4. Templates often include pre-defined tables, queries, forms, and reports.

Templates are customizable, allowing users to adapt fields and layouts to their business requirements.

Open an Existing Database

To open an existing database:

• Use File > Open to browse and select a file.
• Alternatively, choose from the Recent or Pinned files list.

Opening a database from a trusted location prevents Access from disabling macros or showing security warnings.

Back Up a Database

Regular backups are essential. Access offers a built-in backup feature:

1. Open the database.
2. Go to File > Save As.
3. Choose Back Up Database.
4. Access appends the current date to the backup file name by default.

Backup files help in restoring data integrity in the event of corruption or unwanted changes.

Compact and Repair a Database

Over time, databases grow in size and may become fragmented. Access offers a Compact and Repair tool to optimize performance:

• Compact: Reclaims unused space and reduces file size.
• Repair: Fixes minor corruption or index inconsistencies.

To run:

• Go to File > Info > Compact & Repair Database.

It’s good practice to compact and repair periodically or set it to run automatically when the database closes (via Access Options).

Configure Navigation Options

Modify the Navigation Pane

The Navigation Pane lists all objects in the database. Users can customize it to improve usability:

• Click the Navigation Pane menu (dropdown at the top of the pane).
• Choose Navigation Options to:

• Show object types (Tables, Queries, Forms, Reports).
• Group by custom categories.
• Show hidden objects or system objects.

Create Custom Categories

Custom navigation categories can help organize objects logically. For example, grouping queries by purpose (Reports, Data Entry, Analysis).

Steps:

1. Go to Navigation Options.
2. Click Add Item under Categories.
3. Rename the new category (e.g., “My Workflows”).
4. Add custom groups under it and assign objects by dragging them from other categories.

Set a Default Form on Startup

To improve user experience, you can specify a form that opens automatically when the database starts:

1. Go to File > Options > Current Database.
2. In the Display Form dropdown, select the desired form.
3. Optionally hide the Navigation Pane and enable full-screen mode.

This is useful for kiosks or user-facing applications.

Hide or Lock Navigation Pane

To lock down the user interface:

• Right-click the pane and choose Navigation Options.
• Uncheck “Display Navigation Pane.”

Or press F11 to toggle it on/off.

Advanced users can also set startup properties or use AutoExec macros to control navigation behavior.

Manage Database Design

Use Design View for Tables

Design View allows you to define fields, data types, and field properties.

1. Open a table and switch to Design View.
2. Define each field with:

• Field Name (e.g., CustomerID).
• Data Type (e.g., Short Text, Number, Date/Time).
• Description (optional, shows in status bar).

1. Set a Primary Key to ensure unique records.

Best Practice: Use meaningful field names and always define a primary key.

Set Field Properties

Each data type offers custom properties:

• Short Text: Field size, Required, Allow Zero Length.
• Number: Field size (Byte, Integer, Long Integer), Default Value, Validation Rule.
• Currency: Decimal places, Format.
• Date/Time: Input Mask, Default Value (e.g., =Date()), Format.

You can use expressions to auto-populate fields or enforce data rules.

Use Input Masks

Input masks guide users to enter data in a specific format.

Example: For phone numbers [phone]

To apply:

1. In Design View, select the field.
2. Go to the Input Mask property.
3. Use the wizard or enter a custom mask manually.

Indexing Fields

Indexes speed up searching and sorting operations.

• By default, primary keys are indexed.
• You can manually index other fields (e.g., LastName).

To do this:

1. In Design View, set the Indexed property to Yes (Duplicates OK) or Yes (No Duplicates).

Indexing improves performance, especially for large datasets.

Use Relationships Window

Relationships enforce referential integrity between tables.

Steps:

1. Go to Database Tools > Relationships.
2. Add the relevant tables.
3. Drag a field from one table to the related field in another.
4. Check Enforce Referential Integrity (optional: cascade update/delete).
5. Click Create.

Types of relationships:

• One-to-One: Rare, used in splitting data for security.
• One-to-Many: Most common (e.g., Customers to Orders).
• Many-to-Many: Requires a junction table.

Use Edit Relationships to modify joins or enable cascading actions.

Use Property Sheet

In Design View, the Property Sheet gives detailed control over table, field, form, and control settings.

• Toggle with F4.
• Use it to change form layout, control sources, formatting, tab stops, etc.

Use Table Analyzer Wizard

Found under Database Tools, the Table Analyzer Wizard helps normalize flat tables by:

• Identifying repeating fields or values.
• Suggesting how to split data into multiple related tables.
• Automatically creating relationships.

This is particularly helpful for users unfamiliar with normalization.

Split a Database

Splitting separates the database into:

• Back-End: Holds tables and data.
• Front-End: Contains forms, queries, reports, macros, and links to the back-end.

Benefits:

• Better performance and security.
• Multiple users can share the same data file (back-end).
• Each user works from a local copy of the front-end.

To split:

1. Go to Database Tools > Access Database.
2. Follow the wizard steps.

You can re-link tables using the Linked Table Manager.

Document and Save a Database

Save and Rename Objects

Objects (tables, queries, forms) must be saved individually:

• Press Ctrl+S or click the Save icon.
• Provide a descriptive name (e.g., frmCustomerDetails).
• Use consistent prefixes (tbl, qry, frm, rpt, mac) for easy identification.

Example:

• tblProducts
• qryTopSelling
• frmProductEntry

Use Object Dependencies

Access can show which objects depend on each other:

1. Select an object.
2. Click Database Tools > Object Dependencies.

This helps prevent breaking dependencies when modifying tables or queries.

Use Database Documenter

For full documentation:

• Go to Database Tools > Database Documenter.
• Choose which objects to analyze.
• Generates detailed reports on fields, data types, relationships, etc.

Output can be printed or saved as a PDF.

Encrypt a Database with a Password

1. Open the database...

Produkt-Details

• Erscheint lt. Verlag: 4.6.2025
• Sprache: englisch
• Themenwelt: Sozialwissenschaften ► Pädagogik
• ISBN-10: 0-00-097340-8 / 0000973408
• ISBN-13: 978-0-00-097340-5 / 9780000973405