IT systems · Practical guide

Validating Microsoft Access: from the data model to a reliable result

An Access report can look plausible while summarising the wrong records. If it supports a quality decision, check the data model, query logic and operating controls together. Identify the file backend or SQL Server, forms and any VBA involved. A hidden table or protected startup form cannot demonstrate correct results or controlled changes.

Table of contents

At a glance

What does Microsoft Access validation involve?

Demonstrate that your Access application is suitable for its intended regulated use. Assess data relationships, queries, calculations, forms, custom code and actual storage. Test business failure cases, access paths, concurrent edits and restoration. The product name, an installed runtime or a database password does not demonstrate suitability. Intended use and risk determine the scope of evidence.

  • Clearly document the frontend and backend, including database format, drivers, and versions.
  • Test queries against known expected results, duplicates, missing links and boundary cases.
  • Evaluate formulation, macro-trust, data rights, and professional change history separately.
  • Multi-user operation, modifications, backup, and migration to be included in the documentation.

1. Describe the specific use case of Access and its limitations

This article addresses Access desktop applications. The evaluated Microsoft Support pages list, among others, Access for Microsoft 365, Access 2024, and Access 2021. This does not imply that any of the older, similarly listed product versions are still supported. Therefore, capture the actually installed version, the update channel, 32- or 64-bit execution, and the libraries in use. The file format alone does not adequately describe the application.

In a shared database, tables are located in the backend; the local frontend typically includes queries, forms, and reports. Alternatively, Access can connect to SQL Server. Microsoft distinguishes between linking and importing: linking connects the data sources, while importing creates a one-time copy. For your review, it is critical to determine which version a result actually uses.

Specify the process concretely, for example, the compilation of released test results for a quality assessment. Separate Excel files and standalone VBA solutions as attached components. Shared programming language does not imply identical architecture. An Excel validation does not automatically cover the Access data model and its access pathways.

Sources: [1] [2] [3]

2. GxP Relevance derived from usage

Access databases need different validation scopes. Identify the decisions that depend on the data, any required records and where an error would be detected. An informal contact list differs from a report that could hide unresolved test results during batch assessment. Even a small application may support a critical process step.

The regulatory examples relate to human medicinal product GMP. Annex 11 is the GMP guideline for computerised systems; the Commission continues to maintain the January 2011 revision in the relevant directory. Separate determination is required for other industries and regulatory areas. For FDA Part 11, additionally assess the underlying FDA record-keeping obligations and actual electronic usage. Neither access nor the term "database" individually fulfils all requirements.

As a starting point, a clear intended use, the relevant data, and a description of potential erroneous decisions are sufficient. From these, testable requirements can be derived. Manufacturer documentation supports understanding of technical functionalities; however, the suitability of your own tables, queries, and workflows must be independently demonstrated. A specific GAMP category cannot be determined from the product name alone.

Sources: [11] [12] [13]

3. Define key personnel, relationships, and data ownership responsibilities

Begin with the technical objects: sample, test, result, and released evaluation status must not inadvertently overlap or be indistinguishable from one another. Define the unique identifier that unambiguously designates each data record, identify mandatory fields, and establish how corrections are assigned. An additional result may represent a permissible repeat test or an unauthorized duplicate. This distinction must accurately reflect the intended application.

Microsoft describes referential integrity as protection against orphaned relationships. It is subject to specific conditions and cannot be enforced arbitrarily for linked tables within the Access frontend. Referential integrity is established within the Access backend; for other data holdings, the controls must be verified. Cascading deletions or updates must be deliberately assessed, as they can alter dependent data records.

The following internal work aid links architectural decisions with validation evidence. It does not replace a specification. For each line, assign the responsible party and the actual location where control is effectively enforced. This prevents assuming that form-based review serves as protection against all other writing methods.

Own work aid: Data model and control location
DecisionPotential errorSuitable evidence
Technical key Probe or testing is double-applied Attempt double submission of form and import
Link to the result The result refers to a test that does not exist Test missing reference and authorized correction pathway
Backend and write paths Direct table cell modification bypasses form rules Trace access with the intended user role.
Deletion plan Dependent records disappear Verify deletion of dependent data in test copy
Leading data repository Report uses outdated imported copy Compare source, import timestamp, and required inventory level

Sources: [6]

4. Queries, calculations, and VBA to be professionally reviewed

Access-Joins determine which data sets are combined. An inner join returns only matching combinations; an outer join may also include data records without a matching partner. When multiple matching rows exist, a factual source dataset may appear multiple times in the result. A formally successful query therefore does not guarantee completeness nor correct weighting of a key performance indicator.

Create a concise reference dataset whose results are independently calculated. Verify filters, time period boundaries, null values, rounding, units, and the distinction between missing values and zero. Additionally, review the generated report: are the same filters applied? Are excluded datasets clearly identifiable? Is the underlying data status retrievable if corrected in a subsequent release?

In VBA, trace the actual execution path from the form event all the way to the data modification. Include error handling, aborted imports, and repeated executions. Document the tested code and frontend state. The technical calculation must independently derive from the same code; two outputs from the same faulty function do not constitute an independent comparison. Surface-level validations are insufficient to detect a hidden calculation rule.

Sources: [7] [11]

5. Separate rights, runtime, and macro trust

The user-based access control features available in earlier versions are not available for .accdb and .accde files. Microsoft references database servers with dedicated user management. Therefore, verify file and folder permissions, and where applicable, SQL Server permissions. A custom login screen is only effective if the relevant data accesses remain genuinely restricted by the intended controls.

An .accde file restricts design and code modifications. Runtime mode facilitates guided operation, but according to Microsoft, it is not a method for application protection. Neither should be considered a substitute for access control. During testing, attempt access outside the intended start form and evaluate it based on the established data retention policies.

The Trust Center deals with the execution of potentially unsafe content. Stores classified as trustworthy allow their execution; blocked code, in turn, does not automatically protect the data from inspection. Determine who is allowed to exchange shared files and how their origin is controlled. Macrosecurity does not replace either an authorisation concept or the evaluation of personal data and their permitted processing.

Sources: [4] [3] [5]

6. Test real-time processing and interfaces realistically

Microsoft describes different locking mechanisms for Access forms. Under optimistic editing, multiple users may modify the same dataset; upon subsequent save, a conflict may arise. For ODBC data, the RecordLocks documentation specifies divergent behavior: the setting should not be treated as a file-level lock in Access. Therefore, successful testing of the file backend should not be directly transferred to SQL Server without proper evaluation.

A compelling test scenario involves two test users initiating the same technical process. One user corrects a value, while the other continues processing the original data. Verify the alert, the stored final state, and the user's response. Add a connection drop during an import: which data has been received, which is missing, and what occurs upon subsequent restart?

When establishing a SQL connection, capture driver details, authentication methods, table mappings, and target server information. The Microsoft Help warns that the option to store a SQL password will save it unencrypted within the Access database. Have the intended connection pathway evaluated by IT. A functional connection with administrator privileges does not constitute valid evidence for future operation under restricted user permissions.

Sources: [8] [2]

7. Provide separate evidence for change history and professional release

A "last modified" field initially displays only the content of that field. This does not provide evidence of a complete, protected change history. For relevant records, describe which changes must be traceable, how direct table-level accesses should be handled, and who may modify or delete historical entries. A custom logging mechanism is part of the application and requires independent validation evidence.

Annex 11 addresses risk-based audit trails for GMP-relevant changes and deletions. Whether and to what extent a specific access solution meets the applicable requirements must be evaluated based on its logging process. Neither a file backup nor a regularly generated PDF serves as a general substitute for necessary traceability. Additionally, verify that the professional context remains readable following a correction.

Microsoft describes digital signatures for executable database components as a proof of origin and integrity; for .accdb and .accde, the Access for Microsoft 365 version 2211 or above. This does not prove an electronic signature of an examiner under a technical dataset. If such a signature is provided, it is important to specify and prove identity and permanent assignment separately.

Sources: [9] [11] [14]

Further reading:

8. Example: A duplicate alters the report

This example is a standalone audit aid and not a completed product test. An Access application calculates the mean of valid results from two samples. The predefined test values are 10 and 20 in the same unit; the intended value is 15. A second table contains evaluation notes. If the one-to-one relationship of these notes is incorrectly linked, the first result may be included twice in the calculation: from 10, 10, and 20, the result becomes approximately 13.33.

The issue does not necessarily lie in the computational function, but in the dataset employed. Therefore, ensure consistent documentation of table content, relationships, queries, and reports. A simple DISTINCT operation is not a general solution: two functionally distinct results may yield the same numerical value. The desired uniqueness must be defined by functional keys.

Conduct the test in a separate, representative environment. Predefine target values and justify each excluded data point. In the event of an error, first identify the root cause, then control the change, and reevaluate the affected scope. The following matrix is tailored to this example and does not constitute an officially mandated test list.

Own test matrix for a consolidated Access query evaluation
VariationPredetermined expected resultEvidence to retain
Two valid individual values 10 and 20 yield a mean value of 15 Input data, query, and report
Second note regarding the first sample No double weighting of the result Join Results and Key Technical Expertise
Probe without evaluation stamp According to defined rules, visible or substantively excluded Dataset list and reporting notice
Result is missing No silent substitute through numerical zero Error indication and amount of data used
Repeated Import No unauthorized duplicates or recognisable rejection Import log and data comparison

Sources: [7] [6]

9. Control, migration, and version management

In a shared access application, both the frontend and the data backend must be restored to operational status. Microsoft specifies separate backups for this scenario; the intended backup of the Access backend requires exclusive access. For linked data, the backup resides within the respective source system. Therefore, a copy of the frontend does not automatically include the data in SQL Server.

Verify a restoration in an appropriate test environment: Are links, user access, data status, and critical reporting intact? Document how records generated between the backup timestamp and system outage are handled. Completion of a successful backup job alone does not address these operational questions. Archiving requires, in addition, the readability and retrievability of required records throughout the specified retention period.

During migration or frontend replacement, compare keys, dataset volumes, relationships, and representative domain-specific results. A transition to SQL Server may affect query execution, locking behavior, and permissions. Additionally, 32-bit/64-bit changes can impact libraries and API calls. Assess implications and select appropriate regression testing. The tested frontend version must then be controlled and distributed; existing local copies require coordinated handling.

Sources: [10] [3] [8] [14]

Further reading:

10. Prepare the start with QA, IT, and process responsibility holders

To ensure a reliable start, you require more than just the most recently used database file. Provide source data, productive frontend, backend, integrations, and established work instructions. The process owner shall explain the technical rationale and any exceptions; development or maintenance personnel shall outline the underlying logic. IT is responsible for technical access and operations, while QA evaluates the regulated use and intended evidence generation.

The effort increases particularly due to unknown file copies, missing source code, unclear data ownership, numerous import variants, and non-reproducible calculations. Comprehensive records alone reduce search work but do not replace targeted verification. First, discuss a small, decision-critical process and the consequences of its failures. Only then can the remaining scope be justified and planned, without deriving a fixed duration or price from the product name.

  • Intended use, scope of application, and professional judgment are specified.
  • Frontend, backend, versions, drivers, and production file paths are known.
  • Key points, relationships, leading data sources, and correction pathways are described.
  • Reference data cover duplicates, missing values and relevant limits.
  • User rights and alternative input methods have been assessed.
  • Simultaneous processing and interrupted transfers are considered.
  • History and, if applicable, professional signatures are separately delineated.
  • Restoration, modifications, and responsible parties are defined.

Sources: [11] [3] [10]

Project experience and collaboration

Test application behaviour beyond the Access form

With the process owners, we trace a critical Access transaction through its relationships, queries and stored results. IT identifies actual access to the front end and back end. Where existing controls adequately cover the data path, focused evidence may suffice. Direct write paths or custom logging require additional challenge tests. We also examine concurrent editing and recovery. The evidence should explain how the application is used beyond operation of a single form.

Frequently asked questions about Microsoft Access

Must every access database be validated?

The intended use is decisive. Assess whether the application influences regulated decisions or generates, alters, or retains required records. Justify the scope based on specific failure consequences and existing controls. Even a small database may be relevant; conversely, a general office directory does not automatically require the same level of documentation evidence as a quality-relevant result evaluation.

Sources: [11] [13]

Is an SQL Server backend automatically the validated solution?

No. SQL Server can handle data retention and user management, while Access continues to support queries, forms, and custom code execution. The full intended use must be evaluated. This includes table joins, permissions, drivers, concurrent modifications, and domain-specific reporting. A migration may be justified but represents a significant change that requires thorough assessment and is not a substitute for missing application validation evidence.

Sources: [2] [4] [11]

Can .accde and Access Runtime be used as access control?

An .accde file restricts modifications to certain design and code components. Runtime mode guides users through the provided interface. Microsoft explicitly does not designate it as application protection. Verify effective file- or server-level permissions and access to data outside the startup form. Hidden navigation alone does not prevent unauthorized data usage.

Sources: [3] [4]

What distinguishes Access validation from Excel or VBA validation?

In Access, additional relationships between tables, professional keys, joins, and common shared data sets are central. VBA can occur in both products but is executed in different workflows. Only adopt appropriate existing evidence. Test the specific Access data volume, event control, and output. Linked Excel files remain independent input or output components within the process.

Sources: [1] [7] [11]

How do we validate a query without performing an exhaustive full review of all data?

Use a justified reference dataset containing known target values and targeted edge cases. For summarization, consider cases such as multiple assignments, missing partners, zero values, and excluded records. Compare not only the final count but also the total number of data records used. Add additional cases where different rules or critical error consequences are present.

Sources: [7] [11]

Does Access have a complete GMP audit trail?

A standalone Access file alone does not provide a fully validated GMP audit trail. A process-appropriate logging mechanism within the application or backend, including independent verification evidence, is required. Annex 11, Section 9, links the implementation of an audit trail for GMP-related changes and deletions to a risk assessment. Evaluate your specific solution against direct write accesses and other workarounds. An alteration date or file placement alone neither demonstrates completeness nor protection of the required history.

Sources: [11] [4] [14]

Is digital database signature considered an electronic release?

The described Microsoft feature relates to trust in executable components and their origin. It does not automatically establish professional authorization for a specific dataset. If a user is to electronically justify a regulated release, the significance of the action, identity assignment, linkage to the audit trail, and required protective measures must be clearly defined and demonstrated.

Sources: [9] [11] [13]

What must be ready prior to project initiation and who must participate?

The following production files, an available source base, backend access for a suitable test environment, and a description of the functional usage are required. Involve process owners, application maintenance, IT, and QA. Early clarification is needed regarding who defines specification values, who can explain technical defects, and who makes decisions on open findings. Unknown local copies should be visible within the inventory.

Sources: [3] [11]

What factors determine effort and duration?

Significant factors include in-house development, data model, import pathways, number of productive variants, existing validation evidence, and reproducibility of results. Missing source code or unclear authorizations may initially require retrospective work. A clearly defined process supported by robust reference data can be more effectively addressed. A credible estimation emerges from this assessment; deriving a reliable processing duration solely from access privileges is insufficient.

Sources: [11] [1] [3]

When must we retest after updates or a migration?

Assess which requirements may be affected by the change and its dependencies. A driver update may necessitate different tests than a new reporting filter. During migration, additionally verify data mapping and technical results. Document the decision regarding the scope of regression testing and the released version. A comprehensive revalidation following every change does not follow.

Sources: [11] [3] [8]

Sources and review date

Microsoft sources on Access desktop applications were checked on 6 October 2026. Check the relevant product and file format boundaries. A source list does not establish that a customer's installation was tested. The regulatory examples concern human medicines GMP. The tables, reference data and tests are our examples.

  1. Microsoft: Split an Access database
  2. Microsoft: Import or link to data in an SQL Server database
  3. Microsoft: Deploy an Access application
  4. Microsoft: What happened to user-level security?
  5. Microsoft: Decide whether to trust a database
  6. Microsoft: Create, edit or delete a relationship
  7. Microsoft: Join tables and queries
  8. Microsoft: Form.RecordLocks property (Access)
  9. Microsoft: Show trust by adding a digital signature to an Access database
  10. Microsoft: Protect your data with backup and restore processes
  11. European Commission: EU GMP Annex 11 – Computerised Systems
  12. European Commission: EudraLex Volume 4
  13. FDA: Part 11, Electronic Records; Electronic Signatures – Scope and Application
  14. European Commission: EU GMP Chapter 4 – Documentation

Sources checked: .

Daniel Herrmann, Daniel Herrmann Consulting

Daniel Herrmann Consulting

Your access application should be clearly defined and reviewed.

Daniel Herrmann Consulting assists you in translating the data model, technical results, and operating conditions into a justified validation scope.

  • Computer system validation →

    Bring your GxP systems into validated operation, from laboratory software, MES and eQMS to ERP. We define the scope, plan risk-based tests using GAMP 5 and hand the evidence to QA for release.

Send a project enquiry Customer testimonials →