Mastering Access Databases: Design, Queries, Forms, and VBA

This text appears to be a comprehensive guide to using Microsoft Access, covering foundational concepts to advanced techniques. It explains how to get started with Access and manage tables, including design, data types, relationships, and importing/exporting data. A significant portion focuses on working with queries for selecting, analyzing, and transforming data, introducing SQL and various operators. The guide also explores creating and manipulating forms and reports for data presentation and entry, along with detailed explanations of controls and their properties. Finally, it introduces Access programming fundamentals using macros and VBA, including debugging and working with data access objects like ADO and DAO.

Podcast

Listen or Download Podcast – Mastering Access Databases: Design, Queries, Forms, and VBA

Understanding Microsoft Access Queries

Queries are fundamental tools in Microsoft Access database applications, serving to extract data from tables, combine it in useful ways, and present it to the user. Essentially, an Access query is a question you ask about the information stored in your database tables. The word “query” itself comes from the Latin quaerere, meaning “to ask or inquire”. Queries are what convert raw data into meaningful information.

Access queries offer a wide range of capabilities:

• You can choose tables to draw data from, whether a single table or multiple tables linked by common data.
• You can select specific fields from these tables to include in your results.
• You can provide criteria to filter records, ensuring only those that meet certain conditions are included.
• You can sort records in a specific order to make the data easier to analyze.
• Queries can perform calculations such as averages, totals, or counts on your data.
• You can create new tables based on the data returned by a query (make-table queries).
• Query results, known as recordsets, can be displayed on forms and reports.
• A query’s recordset can serve as a source of data for other queries (subqueries).
• Action queries allow you to make changes directly to data in tables, such as updating values, appending new data, or deleting obsolete information.

When you run a query, Access combines the records and displays them in Datasheet view by default. The set of records returned is called a recordset. Unlike tables, the recordset returned by a query is typically not stored permanently within the database; only the query’s structure, which is the SQL syntax used to build it, is saved. This means that every time a query is executed, it reads the current data from the underlying tables, ensuring the recordset reflects any changes made since the last execution. A query’s recordset can be viewed as a datasheet, or used by forms, reports, macros, and VBA procedures.

You typically create a query using the Query Design button on the Create tab of the Ribbon. This opens the Query Designer, along with the Show Table dialog box where you add the tables or queries you need. The Query Designer has three views: Design view (where you build the query), Datasheet view (displays the results), and SQL view (shows the underlying SQL statement). The Design view is divided into two main parts: the table/query pane at the top (showing the field lists of added sources) and the Query by Example (QBE) design grid at the bottom.

Working with fields in the QBE grid involves adding fields from the table/query pane by double-clicking or dragging. Fields can be added one at a time, in groups, or all fields from a source using the asterisk (*). Using the asterisk includes all fields and automatically updates if the source table design changes, but offers less control over field order and might retrieve unnecessary data. The QBE grid includes rows to specify the field, its source table, sort order, whether to show the field in results, criteria, and ‘Or’ conditions. You run the query using the Run button on the Ribbon.

You can modify fields in the QBE grid, such as rearranging their order by dragging columns, resizing columns, removing fields, or inserting new fields. You can also hide a field from the query results by unchecking its Show box; this is useful for fields used only for sorting or filtering. Hidden fields without criteria or sort order applied are typically removed by Access’s optimizer when the query is saved. Sorting records is done in the Sort row, selecting Ascending or Descending. Sorting on multiple fields is processed from left to right in the QBE grid.

To add criteria to a query, you enter expressions in the Criteria row of the QBE grid. Criteria act as filtering rules. Access automatically adds delimiters like quotes for text strings or pound signs (#) for dates in simple criteria. You can use operators (mathematical, comparison, string, Boolean, etc.) and functions in criteria. The Like operator along with wildcards (*, ?, #, [], [!]) is used for pattern matching in text fields. You can specify non-matching values using Not or <> operators. For multiple criteria within a single field, you can use the “Or” keyword, enter criteria on separate ‘Or’ rows in the QBE grid, use the In operator for a list of values, use And for ranges, or use the Between…And operator for ranges. For criteria across multiple fields, conditions on the same row are combined with And, while conditions on different rows are combined with Or.

Multi-table queries are used to retrieve information from several related tables. Relationships between tables, typically based on primary and foreign keys, are crucial for this. When related tables are added to the Query Designer, join lines often appear automatically. Join lines represent the relationship between tables in the query. There are three basic types of joins:

• Inner joins: Return only records that have matching values in the joined field in both tables. This is the default join type in Access queries.
• Outer joins: Return all records from one table and only matching records from the other. Left outer joins return all records from the left table, and right outer joins return all records from the right table. They are useful for finding records that may not have a match in the related table. You can change the join type or create ad hoc joins directly within the Query Designer.

Beyond simply selecting data, Access offers more advanced query types:

• Aggregate Queries (or Group-By queries) group and summarize data using functions like Sum, Avg, Count, Min, Max, etc.. They are activated using the Totals button in the Query Designer.
• Action Queries perform operations on data. These include make-table queries (create new tables from results), delete queries (remove records), append queries (add records to existing tables), and update queries (modify existing records). Action queries cannot serve as data sources for forms or reports.
• Crosstab Queries summarize data in a matrix format using row and column headings and an aggregated value. They require at least three fields. They can be created using the Crosstab Query Wizard or manually in the Query Design grid.
• SQL-Specific Queries are action queries that cannot be created using the QBE grid and must be run from SQL view or code. Examples include UNION (merging results of two SELECT statements), CREATE TABLE (creating a new table structure), and Pass-through queries (sending SQL directly to an external database server for processing).

Query performance is important, especially with large datasets. Access has a built-in query optimizer that tries to execute queries efficiently. Optimizing performance involves factors like normalizing your database design (using related tables instead of one large table), using indexes on fields frequently used for filtering, sorting, or joining, and improving query design practices (e.g., avoiding SELECT *, limiting fields in aggregate queries, hiding unused fields, using Between…And for ranges). Regularly compacting and repairing your database also helps by updating table statistics and allowing queries to be re-optimized.

Additional related topics include using operators and expressions more deeply (Chapter 9), incorporating calculations and working with dates (Chapter 12), performing conditional analyses using parameter queries or functions like IIf and Switch (Chapter 13), understanding the fundamentals of SQL syntax (Chapter 14), and leveraging subqueries and domain aggregate functions for complex analysis layers within a single query (Chapter 15). Queries are also used extensively in data transformation tasks like finding/removing duplicates and manipulating text strings (Chapter 11).

Understanding and Working with Access Tables

Working with Access tables is foundational to database development, as tables serve as the primary containers for your raw information, often referred to as data. In essence, an Access database is largely an automated version of a manual filing system, where tables store different kinds of data in a carefully defined structure. Access is a relational database management system (RDBMS), which means it stores data in related tables.

Here’s a discussion of understanding and working with Access tables, drawing from the provided sources:

Fundamental Concepts

• Tables as Containers: A table is like a folder in a manual filing system, serving as a container for raw information about a single topic, such as employees or products.
• Records and Fields: Tables are organized into rows, called records, and columns, called fields. Each row (record) contains fields that are related to that specific record. Each column (field) represents an attribute and has properties that specify the type of data it holds and how Access should handle it. In other database systems, like Microsoft SQL Server, the term ‘column’ is often used interchangeably with ‘field’.
• Values: The intersection of a record and a field holds a value, which is the actual data element.

Relational Design and Multiple Tables

• Benefits of Multiple Tables: In a relational database like Access, data for a single person or item is often stored in separate tables. For instance, customer contact information might be in one table, and their order history in another. Storing data in related tables simplifies data entry and reporting by reducing the need to input redundant information. This approach makes the system easier to maintain, as all records of a given type are in the same table.
• Normalization: The process of separating data into multiple tables within a database is called normalization. The goal is to create tables that hold a minimum amount of information while remaining easy to use and flexible. The first three stages of normalization (First, Second, and Third Normal Form) are typically sufficient for most applications.
• First Normal Form (1NF): Requires that each field contain only a single value and the table not contain repeating groups of data.
• Second Normal Form (2NF): Achieved by splitting data into multiple tables, ensuring that non-key attributes are fully dependent on the primary key.
• Third Normal Form (3NF): Requires removing fields that can be derived from data in other fields or tables.
• Keys: To link related tables, you use key fields.
• Primary Key: A field or combination of fields that uniquely identifies each record in a table. Access can create one automatically if you don’t specify one. The primary key field is always indexed.
• Foreign Key: The corresponding field in a related table that refers back to the primary key in another table.
• Composite Primary Keys: Primary keys made up of multiple fields. Using composite keys can add complexity without necessarily adding stability.
• Table Relationships: These connect related tables, typically based on primary and foreign keys. The most common types are one-to-one, one-to-many, and many-to-many. Many-to-many relationships are modeled using a join table. Relationships are often visually represented by join lines in the Query Designer or Relationships window.
• Referential Integrity: Access allows you to apply rules that protect data from loss or corruption by preserving relationships during data operations like updates and deletions. Enforcing referential integrity ensures that child records have a matching parent record, preventing “orphaned” records.

Creating and Designing Tables

• Creating a New Table: You typically create tables using the Table Design button on the Create tab of the Ribbon, which opens the table in Design view. You can also click the Table button to start in Datasheet view.
• Design View: The Table Designer in Design view is divided into a field entry area (for name, data type, description) and a field properties area (for defining characteristics like field size, format, validation rules, etc.).
• Naming Conventions: Adopting a naming convention (e.g., prefixing table names with tbl) is recommended for identifying database objects, especially as databases grow in size and complexity. While Access allows spaces in names, avoiding them is best practice, especially when working with code or external systems.
• Field Properties: These are named attributes that modify a field’s characteristics (like size, format, caption) or behavior (like ‘Required’ or ‘Indexed’). They are enforced by the database engine. Examples include Field Size (for text/number fields), Format (how data appears), Input Mask (data entry format), Caption (label on forms/reports), Required (whether a value must be entered), and Indexed (for performance).
• Data Types: Each field must be assigned a data type (Short Text, Long Text, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, Attachment, Lookup Wizard). Choosing the correct data type is important for storage efficiency and data integrity.
• Validation Rules: Criteria applied to data entry to ensure only data that passes certain tests gets into the system. You can set a rule for a single field or the entire table.

Working with Data in Tables

• Datasheet View: Tables can be viewed in a spreadsheet-like form called a datasheet. Datasheet view displays a table’s content in rows (records) and columns (fields). You can enter new data, change values, navigate records, sort, and filter directly in Datasheet view. You can also perform basic data aggregation directly in the datasheet.
• Data Entry: While data can be entered directly into the datasheet, using forms is the recommended way as they can provide better structure, validation, and user guidance.

Additional Table-Related Concepts

• Indexing: Indexes are internal structures that speed up data access, querying, sorting, and grouping operations, especially in larger tables. Fields frequently used for filtering, sorting, or joining are good candidates for indexing.
• Manipulating Tables: You can save, rename, delete, and copy tables using the Navigation pane or menu options. When copying, you can choose to copy only the structure, structure and data, or append data to an existing table.
• Attachment Fields: A special field type that allows you to attach entire external files (like documents, images, audio, video) directly to records within an Access table.
• Importing, Exporting, and Linking: Access allows you to bring data from external sources into new or existing tables (importing), send data to external files (exporting), or connect directly to data in external files without copying it locally (linking). Linking allows working with external data in place, but might have limitations compared to native Access tables.
• Data Macros: Logic that can be attached to tables to enforce business rules or perform actions (like logging changes or validating data) when specific table events occur (like before/after changes or deletions).

Understanding these aspects of Access tables, from their basic structure and design principles to how they are related and interact with other database objects and external data, is essential for effective database development in Access.

Access Forms and Reports Explained

Forms and reports are fundamental components within Microsoft Access databases, serving as the primary interface for users to interact with the data stored in tables. While tables hold the raw data, forms provide a structured way to view, enter, and modify that data, and reports offer a means to present data in a formatted, often summarized, view for printing or analysis.

Here’s a discussion on creating and working with Access forms and reports based on the provided sources:

Understanding the Role of Forms and Reports

• Forms provide the most flexible way to view, add, edit, and delete data. They can be designed to resemble familiar paper documents, guiding the user through data entry and improving accuracy. Forms are also used for navigation (switchboards), dialog boxes, and displaying messages.
• Reports present data in a formatted, often printable, output. They are used for viewing and analyzing data, often summarizing information through grouping, sorting, and calculations. Unlike forms, reports are generally for consumption of data, not entry or modification. Reports can combine data from multiple tables, typically through a query.

Creating Forms

Forms are typically created using the Forms group on the Create tab of the Ribbon. Several methods are available:

• Form button: Creates a new form automatically bound to a table or query selected in the Navigation pane, opening in Layout view. This is useful for quickly getting a basic form with all fields.
• Form Design button: Opens a new blank form in Design view, not automatically bound to a data source. This provides more control over the design process from the start.
• Blank Form button: Creates a new blank form in Layout view, also not automatically bound to a data source. You add controls from the Field List.
• Form Wizard: Guides you through selecting a data source, fields, and a basic layout (Columnar, Tabular, Datasheet, Justified).
• More Forms button: Offers templates for specific form types like Multiple Items (tabular, shows multiple records), Datasheet (looks like a table datasheet), Split Form (shows datasheet and single-record form views simultaneously), and Modal Dialog (template for a pop-up dialog box).

Working with Forms

• Views: Forms can be displayed in Form View (for data entry/viewing), Datasheet View (row/column format), Layout View (adjust design while viewing data), and Design View (modify structure). You can switch views using the Views group on the Home tab or contextual tabs.
• Data Entry and Modification: Data can be entered directly into controls in Form View. Access handles automatic data-type validation. Data is automatically saved when moving between records or closing the form. Some controls or fields may be non-editable (AutoNumber, Calculated, Locked).
• Properties: Forms, sections, and controls have properties that define their appearance and behavior. These are accessed and modified via the Property Sheet. Properties are organized into tabs like Format, Data, Event, and Other. Examples include Caption (title bar text), RecordSource (links form to data), DefaultView (Single, Continuous, Datasheet, Split), AllowEdits/Deletions/Additions, PopUp, and Modal.
• Sections: Forms can include a Form Header (top, displays once or with first record), Detail section (main area, shows data for each record), and Form Footer (bottom, displays once). Sections have properties like Visible, Height, Back Color, Special Effect, and Display When.
• Layout: Layout View and Design View allow arranging controls. The Arrange tab on the Ribbon provides tools for aligning, sizing, and spacing controls. Tab order can be set for keyboard navigation.

Creating Reports

Reports can be created using the Reports group on the Create tab. They are typically based on a table or, more commonly, a query.

• Report Wizard: Simplifies the process by asking questions about data source, fields, grouping, sorting, summary options, and layout.
• Creating from Scratch: Using the Blank Report button opens a blank report in Layout View, which can then be switched to Design View. You add fields by dragging from the Field List.
• Design View (Banded Design): Access reports use a banded design divided into sections: Report Header, Page Header, Group Header (for each defined group), Detail, Group Footer, Page Footer, and Report Footer. Each section prints at a specific time during the report generation process.
• Page Setup: The Page Setup tab on the Ribbon controls report margins, orientation (Portrait/Landscape), and column settings (for snaking columns).
• Grouping and Sorting: Data is organized using grouping and sorting options, often controlled via the Group, Sort, and Total pane. Groups can be based on entire values, prefixes, or date/numeric intervals. Sorting defines the order within groups or for the entire report.
• Summaries: Group and Report footers are used for summary calculations (Sum, Avg, Count, etc.). The RunningSum property can create running totals or numbered lists.
• Advanced Techniques: Reports support advanced formatting and behavior through properties and code. Examples include hiding repeating values (Hide Duplicates property), starting page numbers over for each group, creating bulleted lists, using the NoData event to handle empty reports, and controlling layout using the two-pass processing model.

Shared Concepts: Controls and Properties

Both forms and reports are built using controls, which are objects placed on the design surface.

• Types: Common control types include Text Boxes (display/edit data, show expressions), Labels (static text), Command Buttons (run macros/VBA code), Check Boxes, Option Buttons, Option Groups, List Boxes, Combo Boxes, Images, and Lines/Rectangles (graphical elements). Subform/Subreport controls allow embedding other forms or reports.
• Binding: Controls can be Bound to a field in the underlying data source, Unbound (not tied to a field), or Calculated (display results of an expression). Bound controls update the source data when edited on a form.
• Adding Controls: Controls are added from the Controls group on the Ribbon or by dragging fields from the Field List (which automatically creates bound controls).
• Properties: Each control has a set of properties defining its appearance (Format, Font, Color, Size, Position, Visible), data source (Control Source, Row Source for lists/combos), behavior (Required, Indexed, Enabled, Locked, Validation Rules, Input Mask), and responses to events (Event procedures).
• Manipulation: Controls can be selected, moved, resized, aligned, and formatted. The Format Painter can copy formatting between controls. Default properties for control types can be set.

Designing effective forms and reports is a crucial step in creating a user-friendly and powerful Access application, allowing users to easily interact with and understand the data stored in the database’s tables.

VBA Data Access: ADO and DAO

Accessing data with Visual Basic for Applications (VBA) is a core aspect of developing robust database applications in Microsoft Access, offering greater flexibility than simply relying on bound forms. While bound forms and controls can display and modify data linked directly to tables or queries, VBA code allows you to access and manipulate data programmatically.

The primary tools for accessing data with VBA in Access are the ADO (ActiveX Data Objects) and DAO (Data Access Objects) object models. These object models are distinct from the Access object model itself, which includes user interface elements like forms and reports.

Understanding ADO and DAO

Both ADO and DAO provide ways to perform database operations such as:

• Adding, modifying, and deleting data in tables.
• Building and working with recordsets, which are structures containing rows (records) and columns (fields) of data from a table or query.
• Populating forms or controls with data.

ADO is the newer of the two syntaxes, based on Microsoft’s ActiveX technology. It features a relatively sparse and non-hierarchical object model, primarily revolving around the Connection, Command, and Recordset objects. ADO is often considered better suited for client-server databases like SQL Server because Microsoft provides a native ADO provider for SQL Server. Using ADO in VBA requires adding a reference to the Microsoft ActiveX Data Objects library and typically involves using the ADODB prefix for object types to avoid ambiguity.

Key ADO concepts include:

• Connection Object: Provides a link to a data source, necessary for any data operation. It holds information about the database connection in a connection string.
• Command Object: Used to execute commands against a data source, such as running SQL statements or stored procedures. The output of a Command object can be directed into a recordset.
• Recordset Object: A very versatile object often populated by executing a Command or directly using its Open method. You can open a recordset based on a table name, query name, or a SQL statement. Recordsets support navigation methods like MoveNext, MoveLast, MovePrevious, and MoveFirst. The EOF and BOF properties indicate the end or beginning of the recordset. Recordsets have a RecordCount property, though its availability depends on the CursorType setting.
• Updating Data with ADO: Recordset objects allow you to update data. You use methods like Edit to begin changes and Update to save them. New records can be added using AddNew followed by Update. Records can be deleted using the Delete method. The Execute method of the Command object can also be used to run action queries (Update, Delete) directly without opening a recordset.
• CursorType and LockType: ADO Recordsets have properties like CursorType (e.g., adOpenStatic for static data, adOpenDynamic for forward/backward movement, adOpenForwardOnly for forward movement only) and LockType (e.g., adLockOptimistic for optimistic locking, adLockReadOnly for read-only) which determine the recordset’s capabilities.

DAO is the older data access model that has been part of Access since its inception. Unlike ADO, DAO has a more complex, hierarchical object model, starting with the DBEngine, which contains Workspace objects, which in turn contain Database objects. Database objects contain collections like TableDefs and QueryDefs, and you work with data through Recordset objects containing Field objects. With the introduction of Access 2007, Microsoft updated DAO to ACEDAO to support new data types like Attachment and Multi-value fields. ACEDAO is the default data access library in Access 2016. DAO is often considered simpler and faster for certain tasks and smaller datasets compared to ADO. Similar to ADO, DAO Recordsets provide methods for navigation, updating, adding, and deleting records.

Choosing Between ADO and DAO

The decision between using ADO or DAO often depends on the specific situation and the developer’s familiarity.

• DAO: Generally easier and slightly faster for tasks involving local Access databases and smaller datasets, and it doesn’t require specifying a connection string explicitly when working with the current database (using CurrentDb).
• ADO: Recommended when working with SQL Server due to native ADO providers. Also potentially better for complex operations across disparate data sources.

Role of SQL

Regardless of whether you use ADO or DAO, SQL (Structured Query Language) is fundamental. You often define the data you want to access or the action you want to perform by providing a SQL statement to an ADO Command object or when opening an ADO/DAO Recordset or QueryDef. Understanding basic SQL commands like SELECT (for retrieving data) and clauses like WHERE (for filtering) is essential for effective data access with VBA.

VBA Data Access and the User Interface

VBA code is frequently used to enhance user interaction with data displayed on forms. Instead of strictly relying on bound controls, you can use VBA to:

• Retrieve data into a recordset and populate unbound controls on a form.
• Perform complex validation or calculations before saving data.
• Implement custom record navigation, such as using an unbound combo box to find a specific record using methods like RecordsetClone.FindFirst or Bookmark.
• Apply or remove filters on forms using VBA code or parameter queries based on user input.

In summary, accessing data with VBA provides a powerful and flexible way to interact with the data stored in Access tables or external data sources. By mastering ADO and DAO object models and leveraging SQL statements within your VBA code, you can build sophisticated applications that go far beyond the capabilities of bound forms and simple queries.

Debugging and Error Handling in Access VBA

Access applications, especially those incorporating significant amounts of Visual Basic for Applications (VBA) code, can be complex, making the process of identifying and fixing errors, known as debugging, challenging and time-consuming. Debugging involves finding and resolving problems when an application doesn’t run as intended.

While some problems stem from poor database design, such as misrepresenting data in queries or issues with referential integrity rules, these are distinct from bugs that appear within your VBA code. Design errors often manifest clearly, like a query returning incorrect data or a form failing to open, with Access sometimes providing error messages as guidance. Code bugs, however, can be much more subtle, sometimes going unnoticed for months or years, and even poorly written code can run without obvious errors. To tackle these, Access provides a comprehensive suite of debugging tools within the Visual Basic Editor (VBE).

Preventing Errors through Organization

Adopting good coding habits and conventions is the first step in reducing the occurrence of coding errors. Simple conventions, such as using descriptive names for variables and procedures, can help eliminate many syntactical and logical errors. Keeping modules clean by including only related procedures enhances organization and can increase confidence that private variables are not misused.

Traditional Debugging Techniques

Two long-standing debugging techniques involve using built-in functions and methods to display information during code execution:

• MsgBox: You can insert MsgBox statements into your code to display the value of variables, procedure names, or other strings at a specific point. MsgBox halts code execution and displays a message box that must be dismissed before the code continues. It’s easy to use and requires only a single line of code. However, it can be intrusive due to stopping execution and has limitations with displaying very long strings. For production applications, MsgBox statements used for debugging are typically suppressed using conditional compilation directives.
• Debug.Print: This method outputs messages to the Immediate window in the VBE. Debug.Print statements do not stop code execution and are not visible to the end user since the output only goes to the Immediate window. This makes it useful for inspecting variable values or tracking code progress. Limitations include issues with long strings not wrapping in the Immediate window. While generally efficient, excessive use might potentially slow down an application, suggesting that surrounding them with compiler directives is also a good practice for distributed applications.

Access Debugging Tools in the VBE

The Visual Basic Editor (VBE) offers powerful tools for monitoring code execution and diagnosing problems:

• Immediate Window (Debug Window): Accessible by pressing Ctrl+G or choosing View > Immediate. It allows you to execute single lines of code or entire procedures, inspect the values of variables, and view the output of Debug.Print statements.
• Breakpoints: You can set a breakpoint on a specific line of code to intentionally halt execution when that line is reached. Set a breakpoint by clicking the gray margin indicator bar to the left of the code line or using the Breakpoint toolbar button. When execution stops, you can then use the Immediate window or other debugging tools to examine variables and the state of the application. Breakpoints are automatically removed when you close the application.
• Stop Statement: Similar to a breakpoint, the Stop statement is an executable line of code that halts execution. However, unlike breakpoints, it must be manually removed or commented out from the code, or conditionally compiled out, to prevent the application from stopping unexpectedly for users.
• Stepping Through Code: From a breakpoint, you can control the flow of execution one statement at a time:
• Step Into (F8): Executes the current statement and moves to the next. If the statement calls another procedure, Step Into moves into that child procedure.
• Step Over (Shift+F8): Executes the current statement. If the statement calls another procedure, Step Over executes that entire child procedure without stepping into it, and then stops on the line immediately after the procedure call in the current routine.
• Auto Data Tips: When code execution is halted (in break mode), hovering the mouse cursor over a variable in the code window displays its current value in a small pop-up. This value updates dynamically as the variable’s value changes.
• Locals Window: This window displays a list of all variables currently in scope for the procedure where execution is stopped, along with their current values. For object variables, you can expand the entry to see the object’s properties and contents. Note that global variables are not shown in the Locals window; you need to use the Immediate window or Auto Data Tips to inspect them.
• Watches Window: Allows you to monitor the values of specific variables or expressions. You can add watches through the Debug menu or by right-clicking in the Watches window. You can specify the expression to watch, and optionally, limit the watch to a specific module or procedure. More powerfully, you can set conditional watches that cause execution to break when the watched expression evaluates to True (Break When Value Is True) or when the value of the variable or expression changes (Break When Value Changes). Watches are removed when you exit Access.
• Call Stack Window: When execution is stopped at a breakpoint, the Call Stack window displays the sequence of procedures that were called to reach the current location. This is useful for understanding the flow of execution, especially in nested procedure calls, and diagnosing issues that might arise from how procedures are called. You can double-click entries in the call stack to navigate to that line of code in the respective procedure.

Trapping Runtime Errors

Even with thorough testing, unexpected errors (runtime errors) can occur during the execution of your code. Access provides mechanisms to handle these errors gracefully rather than crashing or displaying a confusing message to the user. The On Error statement is used to specify what action to take when an error occurs.

• On Error Resume Next: Tells Access to ignore the error that just occurred and continue execution with the statement immediately following the one that caused the error. While useful for suppressing known, harmless errors, it makes debugging difficult as errors are silently skipped. It should be used carefully and typically reset using On Error Goto 0 afterwards.
• On Error Goto 0: Resets Access’s error handling to its default behavior, which is to stop code execution and display an error message.
• On Error Goto Label: Directs execution to a specified code label (ErrHandler: in the example) when an error occurs. This allows you to write custom error handling code within the procedure.
• Resume Keyword: Used within an error handler to continue code execution. Resume (or Resume 0) attempts to re-execute the statement that caused the error. Resume Next continues with the statement immediately after the one that caused the error. Resume Label branches execution to a specified label.
• Err Object: VBA creates the Err object whenever a runtime error occurs. This object contains information about the error, such as its Number and Description properties. The Number property is zero if no error has occurred. The Err object is frequently used within custom error handlers (On Error Goto Label) to display details about the error to the user or log the error information.

Error handling is also available for macros using the OnError action and the MacroError object, which provides information similar to the VBA Err object. This was an improvement over older versions of Access where macros lacked robust error handling.

By utilizing these debugging tools and implementing effective error trapping, developers can significantly improve the stability and reliability of their Access applications.

Download PDF Book

Read or Download PDF Book – Mastering Access Databases: Design, Queries, Forms, and VBA

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!