Back to All Cheatsheet Libraries cheatsheets

Microsoft Access

Keyboard shortcuts, database objects, and query reference for Microsoft Access.

Access is a rapid application builder, not just a database

It bundles a database engine, a form designer, a report writer, and a scripting layer into one file. That's why it persists in organisations decades after people declared it dead — nothing else lets one person build a working data application in an afternoon.

Its limits are real though, and worth knowing before you build something important on it: 2 GB per file, roughly 255 concurrent users in theory but far fewer in practice, and Windows desktop only. See the Gotchas tab for when to move to SQL Server.

Object	What it is	Notes
Table	Where data actually lives. Rows and typed columns, with a primary key.	Get these right first — everything else is built on top and is painful to retrofit.
Query	A saved question: filter, join, aggregate, or modify data.	Generates real SQL underneath. Switch to SQL View any time to read or edit it directly.
Form	The data-entry and navigation interface.	Users should never touch tables directly — a form enforces validation and prevents accidental mass edits.
Report	Formatted output for printing or PDF export, with grouping and totals.	Access reports remain genuinely strong — better banded reporting than most modern tools.
Macro	No-code automation attached to buttons and events.	Embedded macros are safer than VBA and cover most simple actions.
Module (VBA)	Visual Basic for Applications code for anything macros can't express.	Full programming language. Also why `.accdb` files are treated warily by security tooling.
Relationship	Defined links between tables, with referential integrity.	Database Tools → Relationships. Enforce referential integrity — it prevents orphaned records at the engine level.
Linked table	A table whose data lives elsewhere — SQL Server, another Access file, SharePoint, Excel.	The basis of the split front-end/back-end architecture, which is essential for multi-user use.

Data type	Holds	Use for
Short Text	Up to 255 characters.	Names, codes, references. Also postcodes and IDs with leading zeros — never store those as numbers.
Long Text	Up to ~1 GB, though ~64k displays in most controls.	Notes and descriptions. Can't be fully indexed or sorted reliably.
Number	Byte through Double, depending on Field Size.	Quantities and measurements. Never use Double for money — floating point rounds.
Currency	Fixed-point, 4 decimal places.	All monetary values. Exact arithmetic, unlike Double.
Date/Time	Date and time as a serial number.	Always store as this type, never as text — otherwise sorting and date maths break.
AutoNumber	Auto-incrementing long integer.	Surrogate primary keys. Never expose it as a meaningful business number — gaps are normal and expected.
Yes/No	Boolean, stored as 0 / -1.	Flags. Note -1 is True in Access, which surprises people writing SQL.
Attachment	Files stored inside the database.	Avoid. Consumes the 2 GB limit fast. Store a file path instead.
Calculated	An expression evaluated from other fields.	Convenient, but a query does the same thing more portably. Doesn't migrate to SQL Server.

One table per real-world thing

Customers, Orders, Products, OrderLines — not one "Sales" table with Product1, Product2, Product3 columns. If you're numbering columns, that's a separate table.

Give every table an AutoNumber primary key

Even where a natural key exists. Business identifiers change; surrogate keys don't, and relationships built on them stay stable.

Define relationships and enforce referential integrity

Database Tools → Relationships. Drag the primary key to the matching foreign key and tick Enforce Referential Integrity. This makes orphaned records impossible at the engine level rather than relying on form validation.

Add Cascade Update. Add Cascade Delete only deliberately — it will silently delete child records.

Model many-to-many with a junction table

Students and Courses need a third table (Enrolments) holding both foreign keys. There's no other correct way to represent it.

Validate at the table, not just the form

Field-level Validation Rule and Required apply no matter how data arrives — form, import, or query. Form-only validation is bypassed by every other route in.

Validation Rule: >=0 Validation Text: Quantity cannot be negative Validation Rule: Between #1/1/2000# And Date() Input Mask: 00000\-9999;;_ (US ZIP+4)

Index what you filter and join on

Foreign keys and any field used in a WHERE clause. Access indexes primary keys automatically but not much else — this is the main performance lever available.

Split the database before anyone else uses it

Database Tools → Access Database (split). Tables go in a back-end file on a share; forms, queries, and reports go in a front-end copy on each user's own machine.

This is not optional for multi-user use. An unsplit shared file is the primary cause of Access corruption.

Type	What it does	Caution
Select	Returns rows. The default and the safe one.	None — read-only.
Update	Changes values in existing rows.	No undo. Always run it as a Select first to see exactly which rows match.
Append	Inserts rows into another table.	Type mismatches fail silently on some rows — check the reported count.
Delete	Removes matching rows.	Back up first. With cascade delete on, it removes child records too.
Make Table	Creates a new table from results.	Overwrites the target table without warning if it exists.
Crosstab	Pivots rows into columns — a summary matrix.	Column headings come from data, so they change as data changes. Fix them with the Column Headings property if a report depends on them.
Union	Stacks results from several queries.	SQL View only — the graphical designer can't build it.
Parameter	Prompts for a value at run time.	Declare parameter types explicitly, or Access guesses and sometimes wrongly.

-- Wildcards: Access uses * and ?, not % and _ SELECT * FROM Customers WHERE Surname LIKE 'Sm*'; -- Dates are delimited with #, not quotes SELECT * FROM Orders WHERE OrderDate >= #2026-01-01#; -- Joins REQUIRE parentheses when there is more than one SELECT c.Name, o.OrderDate, p.ProductName FROM (Customers AS c INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID) INNER JOIN Products AS p ON o.ProductID = p.ProductID; -- TOP instead of LIMIT SELECT TOP 10 * FROM Orders ORDER BY Total DESC; -- Nz() is Access's COALESCE / ISNULL SELECT Nz([Discount], 0) AS DiscountValue FROM Orders; -- IIf() instead of CASE for simple branching SELECT IIf([Total] > 1000, 'Large', 'Standard') AS OrderSize FROM Orders; -- Concatenation uses & — + propagates Null and silently empties the result SELECT [FirstName] & ' ' & [Surname] AS FullName FROM Customers;

Before running any action query

Back up the file. Action queries have no undo, and Access has no transaction rollback in the query designer.
Build it as a Select query first and inspect the rows it returns. Only then change the query type.
Check the row count in the confirmation prompt. A number far higher than expected means your join or criteria are wrong.
Test on a copy of the database when the change is large or irreversible.

Group	Action	Shortcut	Notes
Views	Switch to Design View	`Alt + V, D` or `Ctrl + ,`	Works on tables, queries, forms, and reports.
Views	Switch to Datasheet View	`Alt + V, S`	Or use the view selector at the bottom-right.
Views	Run the query	`Alt + Q, R` or the `!` button	In Design View. For action queries this executes — check first.
Views	Show the Navigation Pane	`F11`	Toggles the object list. Often hidden in a locked-down front end.
Records	New record	`Ctrl + +`	Jumps to the blank row at the end.
Records	Delete current record	`Ctrl + -`	No undo. Prompts once, then it's gone.
Records	Save the current record	`⇧ + Enter`	Access saves on moving off a record anyway — this forces it.
Records	Undo the current field / record	`Esc` / `Esc Esc`	Once undoes the field, twice undoes the whole record — but only before it's saved.
Records	Copy value from the field above	`Ctrl + '`	Genuinely handy during bulk entry.
Records	Insert today's date	`Ctrl + ;`	`Ctrl+:` inserts the current time.
Records	Insert the field's default value	`Ctrl + Alt + Space`	Resets a field to its table-defined default.
Navigate	Find	`Ctrl + F`	Searches the current field by default — change the scope in the dialog.
Navigate	Go to a specific record	`F5`	Focuses the record-number box at the bottom.
Navigate	Open the VBA editor	`Alt + F11`	Same shortcut as every other Office app.
Navigate	Open the Immediate window	`Ctrl + G`	In the VBA editor. Run one-off expressions with `?expression`.
Navigate	Zoom box for a long expression	`⇧ + F2`	Expands a cramped query-designer cell into a readable editing box.

Gotchas

Multi-user is where Access breaks

Never share a single unsplit file. Multiple users editing the same file over a network share is the primary cause of corruption.
Split it: back-end (tables) on the share, front-end (everything else) copied to each user's local machine.
The documented ~255 concurrent-user limit is theoretical. Real-world reliability degrades well before that — treat around 10–15 as a practical ceiling.
An unreliable network connection during a write is what actually corrupts the file. Wi-Fi is notably worse than wired for this.
Never put an Access back-end in OneDrive, Dropbox, or SharePoint sync. File-sync clients and database locking are fundamentally incompatible.

The 2 GB limit arrives faster than expected

2 GB per .accdb file, and that includes temporary working space — the effective ceiling is lower.
Access bloats as you work: deleted records and query workspace aren't reclaimed automatically.
Run Compact & Repair regularly (Database Tools, or enable "Compact on Close"). Databases routinely halve in size.
Attachment fields consume the limit rapidly. Store file paths instead.
Splitting helps: front-end and back-end each get their own 2 GB.

Access is not a security boundary

Database passwords on .accdb files provide weak protection and are readily bypassed.
Hiding the Navigation Pane and disabling shortcut keys stops casual users, not determined ones.
Anything genuinely sensitive belongs in SQL Server with real authentication and row-level security.
.accde compiles and locks VBA source, which protects your code but not the data.
Many organisations block .accdb by email policy because of the VBA capability — plan another distribution route.

Knowing when to outgrow it

Move the back-end to SQL Server or Azure SQL when you hit any of: more than ~10 concurrent users, approaching 2 GB, needing real security, or needing remote access.
Access stays as the front end via linked tables — forms and reports keep working. This is a well-trodden migration, not a rewrite.
The SQL Server Migration Assistant for Access automates most of the schema and data move.
Note that Calculated fields, Attachment fields, and some Access-specific types don't migrate — replace them before you start.
Access Web Apps and SharePoint-hosted Access are discontinued. Don't build on them.

Tips

Compact & Repair on a schedule

The single most effective Access maintenance task. Reclaims space, rebuilds indexes, and fixes minor corruption before it becomes major. Enable "Compact on Close" for the back-end.

Read the generated SQL

Build in the designer, then switch to SQL View. It's the fastest way to actually learn SQL, and lets you write the things the designer can't (UNION, subqueries).

Distribute as .accde

File → Save As → Make ACCDE compiles VBA and prevents design changes. Ship this as the front end and keep the .accdb source yourself.

Split tables from everything else

Database Tools → Access Database. Non-negotiable for multi-user, and it means you can push a front-end update without touching anyone's data.

Link, don't import, from Excel

A linked Excel table stays current as the workbook changes. Import only when you want a fixed snapshot.

Use the Immediate window

Ctrl+G in the VBA editor. Type ?DCount("*","Orders") to evaluate an expression instantly — far faster than building a query to answer one question.

Resources

Access help & learning Microsoft's guides for tables, queries, forms, and reports. support.microsoft.com Database design basics Normalisation and relationships explained properly — read before building. support.microsoft.com Splitting a database The front-end/back-end split required for any multi-user deployment. support.microsoft.com SQL Server Migration Assistant Moving the back-end to SQL Server while keeping Access as the front end. learn.microsoft.com Access specifications The authoritative limits — file size, field counts, concurrent users. support.microsoft.com Microsoft Excel Where most Access databases start — and where Power Query often removes the need for one. build.ty1er.com