MOS Access Expert common mistakes
MOS Access Expert · Understand the traps and how to avoid them.
Creating a split database by copying the .accdb file into two folders manually, or using Save As to make a 'backend', instead of running the Access Database splitter.
Candidates remember that the front end holds queries, forms and reports and the back end holds tables, but they don't know the Database Tools > Move Data > Access Database command that performs the split and links the tables automatically.
What to do instead
Use Database Tools > Move Data > Access Database. Name the back-end file, let Access create it and link the front-end tables, then verify the link arrows in the Navigation Pane and set the front-end options for a shared back end.
Leaving the default 'Enable all macros' or trusting the location of the file instead of declaring a trusted location or signing the database, so the exam scenario's database opens with the security warning bar and code or startup actions do not run.
Candidates treat Trust Center settings as an advanced topic and skip them; they also confuse the message bar warning with a corrupted file.
What to do instead
Open File > Options > Trust Center > Trust Center Settings and add the folder as a trusted location, or enable the option to trust a signed database. Know that the Message Bar appears when the database is outside a trusted location and that content must be enabled for the objects to run.
Compacting and repairing the front end only, or never compacting at all, leaving a bloated back end and a database that fails to open after an interrupted session.
Candidates think Compact and Repair is only about reclaiming disk space and don't realize it also repairs corruption and should be run on the back end as well as the front end.
What to do instead
Use Database Tools > Compact and Repair Database on both files, and use Compact on Close for the front end. For a damaged file, run Compact and Repair before attempting any other recovery step.
Choosing a Text field for a value that will be joined or sorted, or leaving the default Short Text for a foreign key, so queries return wrong matches or sort '10' before '9'.
Candidates focus on what the data looks like rather than how it will be used, and they don't recall how Access's Text sorting compares to Number sorting.
What to do instead
Use Number (Long Integer) for surrogate keys and foreign keys, Date/Time for dates, Currency for money, and Short Text only for genuinely textual data. Check the Field Size property and the Format property before creating relationships.
Setting a validation rule in the table but typing the rule with the wrong syntax, e.g. putting the message in the Rule property or forgetting to quote text values, so the rule is rejected or fires on valid data.
Candidates know validation exists but not that text literals need quotation marks, that dates need # delimiters, and that the Validation Text is a separate property.
What to do instead
Write rules such as >0, Between #1/1/2024# And #12/31/2024#, or In("CA","NY"), and put the user-facing sentence in Validation Text. Test the rule by entering a failing value in Datasheet view.
Creating a lookup field with a value list in the table and then expecting the table to store the displayed text, or building the lookup on the wrong side of the relationship.
Candidates confuse the display of a lookup with the stored value, and they don't realize a lookup created in Table Design adds a hidden relationship and stores the bound column, not the display column.
What to do instead
Decide the bound column deliberately (usually the primary key), set Column Count and Column Widths so the key is hidden and the description shows, and use a table/query lookup rather than a value list when the list must stay current.
Building a totals query by adding every field to the grid, so each row is grouped by all fields and the aggregate returns one row per record instead of the intended summary.
Candidates click Totals and then drag all fields in, not realizing that every field with Group By becomes part of the grouping key.
What to do instead
Include only the grouping fields and the aggregate fields. Set the aggregate's Total row to Sum, Count, Avg, etc., and delete or set Where for fields used only as criteria.
Adding criteria to the wrong row of the query grid, or putting OR conditions in the same row, so the query returns too few or too many records.
Candidates don't distinguish the Criteria row (AND across columns) from the Or row (OR), and they forget that a criterion on a field not shown still filters.
What to do instead
Put AND conditions on the same row across columns and OR conditions on successive rows. Use the Show checkbox to hide helper fields without removing their criteria, and verify with Datasheet view counts.
Editing a form in Design view when the task says Layout view, or dragging controls in Layout view expecting them to stay put, so the layout changes and the task is marked wrong.
Candidates default to Design view out of habit and don't know that Layout view lets them see live data while rearranging controls and that the stack/table/tabular layout controls how controls move together.
What to do instead
Switch with the View button to Layout view for positioning and formatting while data is visible, use Design view only for property changes that Layout view hides, and use the layout guides and Arrange tab to control stacking.
Sizing or sorting a report by dragging in Print Preview, or changing the record source of a report in Layout view without checking the grouping and sorting, so the report prints with cut-off fields or wrong order.
Candidates treat the report like a form and don't realize Print Preview is read-only for layout, and they forget that grouping and sorting are set in the Group, Sort, and Total pane, not by the query alone.
What to do instead
Use Layout view or Design view for sizing and the Group, Sort, and Total pane for grouping and sort order. Set the Can Grow and Can Shrink properties for memo fields and check the print margins in Page Setup.