Microsoft Access Database Cheatsheet

Microsoft Access is a powerful database management system that allows users to store, manage, and retrieve data in a relational database format. Whether you’re a beginner or an experienced user, having a cheatsheet handy can be immensely helpful for quickly accessing essential information and commands. In this cheatsheet, we’ll cover key aspects of Microsoft Access with helpful code snippets.

1. Introduction to Microsoft Access

Microsoft Access is a relational database management system that helps users create and manage databases. It includes features such as tables, queries, forms, and reports, making it an all-in-one solution for database development.

2. Creating a Database

To create a new database in Microsoft Access:

CREATE DATABASE DatabaseName;

Replace “DatabaseName” with your desired database name.

3. Tables

3.1 Creating a Table

CREATE TABLE TableName (
    Field1 DataType,
    Field2 DataType,
    ...
);

Replace “TableName,” “Field1,” “Field2,” and “DataType” with your specific table and field names.

3.2 Adding Fields to a Table

ALTER TABLE TableName
ADD COLUMN NewField DataType;

Replace “TableName,” “NewField,” and “DataType” with your specific table, new field, and data type.

4. Queries

4.1 Basic SELECT Query

SELECT Field1, Field2
FROM TableName;

Replace “Field1,” “Field2,” and “TableName” with your specific field names and table.

4.2 WHERE Clause

SELECT *
FROM TableName
WHERE Condition;

Replace “TableName” and “Condition” with your specific table and query condition.

5. Forms

5.1 Creating a Form

Navigate to the “Create” tab and select “Form Design.” Drag and drop fields from the table onto the form.

5.2 Form Controls

• Text Box: =FieldName
• Combo Box Row Source: SELECT FieldName FROM TableName
• Command Button (for opening a report): DoCmd.OpenReport "ReportName", acViewPreview

6. Reports

6.1 Creating a Report

Navigate to the “Create” tab and select “Report Design.” Drag and drop fields from the table onto the report.

6.2 Adding Controls to a Report

• Text Box: =FieldName
• Label: Used for displaying text on the report

7. Data Validation

Set validation rules for fields:

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CHECK (Condition);

Replace “TableName,” “ConstraintName,” and “Condition” with your specific table, constraint name, and condition.

8. Database Relationships

Establish relationships between tables:

ALTER TABLE Table1
ADD FOREIGN KEY (Field1) REFERENCES Table2(Field2);

Replace “Table1,” “Field1,” “Table2,” and “Field2” with your specific table and field names.

9. SQL in Access

Access supports SQL for advanced queries:

SELECT *
FROM Table1
JOIN Table2 ON Table1.Field = Table2.Field;

Replace “Table1,” “Table2,” and “Field” with your specific table and field names.

This cheatsheet serves as a quick reference guide for Microsoft Access database management. Keep it handy to streamline your database development process and enhance your productivity.

FAQ