MOS Access Expert cheat sheet

Backstage Info > Encrypt with Password applies AES encryption to the whole .accdb; opening the file then requires the password, and removing the password requires entering it once more.

Compact and Repair Database shrinks the file, rebuilds indexes, and can recover a corrupted database; Access runs it automatically on close only when the Compact on Close option is enabled.

Trust Center settings (File > Options > Trust Center > Trust Center Settings) control whether macros and VBA run: Disable All Macros with Notification blocks code until the Message Bar's Enable Content is clicked.

Trusted Locations bypass the Message Bar entirely; files in a trusted folder open with code enabled, so never point a trusted location at a shared or user-writable folder.

Database split: Database Tools > Move Data > Access Database splits into a front-end (.accdb with forms, queries, reports) and a back-end (.accdb with tables); use the Linked Table Manager to repoint links.

Save As > Make ACCDE compiles VBA into a locked form, removes source code and design changes to forms/reports, and keeps tables and queries editable; ACCDE cannot be reversed to ACCDB.

Navigation Options (right-click the Navigation Pane title bar) control category grouping and whether objects are shown as a list, icons, or details; these settings are per-database, not global.

Object Dependencies (Database Tools > Object Dependencies) shows which queries, forms, and reports reference a table; it does not track VBA or macro references.

Back up with File > Save As or Save & Publish > Save Database As > Back Up Database, which writes a timestamped copy; Access never overwrites the original file during backup.

Data types and their storage: Short Text up to 255 characters, Long Text up to about 1 GB, Number (Byte 1, Integer 2, Long Integer 4, Double 8 bytes), Date/Time 8 bytes, Currency 8 bytes, AutoNumber 4 bytes (Long Integer or Replication ID).

The Field Size property applies only to Number fields; choosing the smallest size that fits the data range reduces file size and speeds joins on that field.

Validation Rule syntax uses Access SQL expressions, e.g. Between 0 And 100 or Is Null Or Like "A*"; Validation Text supplies the message shown when the rule fails.

A lookup field created with the Lookup Wizard stores a foreign key but displays the related value; the underlying Row Source is a query or value list, and Limit To List blocks values outside the list.

Indexed property values are No, Yes (Duplicates OK), and Yes (No Duplicates); a primary key is automatically indexed with no duplicates, and a table can have only one primary key.

Composite primary key: select multiple fields in Design View and click Primary Key; the combination must be unique, and each field may still contain duplicates individually.

Referential integrity in Relationships requires a unique index (usually the primary key) on the one side; Cascade Update Related Fields and Cascade Delete Related Records propagate changes to the many side.

Input Mask uses three sections separated by semicolons: the mask itself, whether to store literal characters (0 stores, 1 does not), and the placeholder character shown while typing.

Calculated field data type stores an expression such as [Quantity]*[UnitPrice]; the expression is evaluated at query time, so it cannot reference other calculated fields in the same table.

Query types: Select, Crosstab, Make Table, Append, Update, and Delete; action queries change data and prompt for confirmation unless SetWarnings False is run in a macro or VBA.

Totals query (Design > Totals) exposes the Total row with options Group By, Sum, Avg, Count, Min, Max, StDev, Var, First, Last, Expression, and Where; Where filters rows before grouping and is not displayed.

Join types in Design View: inner join (only matching rows), left outer (all rows from the left table plus matches), right outer (all rows from the right table plus matches); double-click the join line to change it.

Parameter query: enter a prompt such as [Enter Start Date] in the Criteria row; Access asks at run time, and the parameter inherits the field's data type unless declared with Parameters in SQL view.

Crosstab query requires exactly one Row Heading, one Column Heading, and one Value field; the value uses an aggregate such as Sum or Count, and a parameter for the column heading must be declared with PARAMETERS.

Top Values property (Design > Return) limits rows after sorting, e.g. Top 10 or Top 10 Percent; ties are included when the Top Values property is paired with a sort on the same field.

Update query: add the field to the grid, set Update To to the new value or expression, and run it; omitting Criteria updates every row in the table.

Append query maps source fields to destination fields by name or by position; data type mismatches cause the append to fail, and AutoNumber destination fields are skipped in the mapping.

Find Duplicates and Find Unmatched Query Wizards build the join and criteria automatically; Find Unmatched uses a left join with Is Null on the right table's key.

Layout View shows live data and lets you move, resize, and delete controls; Design View shows no data and is required to edit control properties, the Record Source, or event procedures.

The Field List pane (Add Existing Fields) drags bound fields onto the form; dragging a field onto the form in Layout View adds a bound text box plus its label.

Control layout: select controls and use Arrange > Stacked or Tabular to group them; the layout container keeps controls aligned and evenly spaced when the form is resized.

To remove a control from a layout without deleting it, right-click the layout border and choose Remove Layout, or drag the control out of the layout group.

Tab order is set in Design View with Form Design Tools > Design > Tab Order; it applies to the active section, and the Tab Stop property set to No skips a control.

Control Source binds a control to a field or expression; an unbound control has an empty Control Source, and a calculated control uses an expression such as =[Quantity]*[UnitPrice].

The form's Record Source can be a table or query; the Default View property (Single, Continuous, Datasheet, Split, Modal, Dialog) controls how the form opens.

Split Form shows a datasheet and a form view of the same record simultaneously; the datasheet is set with the Split Form Orientation and Split Form Datasheet properties.

Subform control links to a main form through Link Master Fields and Link Child Fields; the subform's Source Object names the form to embed.

Report sections: Report Header and Footer appear once per report; Page Header and Footer appear on every page; Group Header and Footer appear per group; Detail repeats per record.

Grouping and Sorting (Report Design Tools > Design > Group & Sort) adds up to 10 sort levels and 4 grouping levels in Access; each group can have a Header Section and Footer Section toggled on.

Aggregate totals in group or report footers use =Sum([Field]) or =Count(*); the Running Sum property (Over Group or Over All) accumulates values across records.

Layout View renders the report with real data so you can resize columns and apply themes; Design View is required to add sections, set the Record Source, or edit control properties.

Keep Together property on a group keeps the group on one page; the Detail section's Can Grow and Can Shrink properties let bound controls expand or collapse with their content.

Page setup: Page Setup > Page tab sets orientation and paper size; the Columns tab sets column count, size, and layout (Down Then Across or Across Then Down) for multi-column reports.

Force New Page property on a section (None, Before Section, After Section, Before & After) controls page breaks; use it on group headers to start each group on a new page.

The Report Wizard and Label Wizard generate a starting layout; after the wizard, Layout View is the fastest place to adjust field widths and alignment without disturbing the underlying sections.

Print Preview shows the final pagination and is the only view where the report's printed page count and margins are accurate; use it to verify totals before distributing.