The Access database someone built years ago still runs your study, but nobody wants to touch the macros. Here is how to document what it does, map tables to forms and visits, and retire it without losing a record.
Free sandbox · No credit card · 21 CFR Part 11 aligned
Document tables, forms, queries and macros
One page per object; note who uses each
Freeze and archive the .accdb
Read-only copy, date, size, owner
Export tables to CSV or XLSX
Plus a field list with types and codes
Draft eCRFs from the field list
AI proposes; a person approves
Test and train
Free sandbox, sample participants
Cut over on one date
No new entry in Access after it
What to know first
Why replace it
Access is a capable desktop database, and for a single-user registry or a pilot it can be perfectly adequate. The problems appear when the study grows: several coordinators share a file on a network drive and take turns, a database split into front end and back end drifts between copies, and a single corrupted file puts weeks of data at risk. Access also records no field-level history of who changed a value and why, so inspection questions are answered from memory.
Regulated and publishable studies need more than a form on top of tables: role-based access, a way to raise and close queries, electronic signatures, validated behaviour and a record that cannot be edited from the application. The 21 CFR Part 11 compliance checklist is a quick way to see how many of those a home-built system meets today. The audit trail software page shows what a field-level trail records.
There is also the key-person risk. If the one analyst who understands the VBA leaves, the study runs on a system no one can safely change. Replacing Access is as much about continuity as it is about compliance.
The path
Use Access's database documenter or a manual list to record each table, relationship, form, saved query, macro, VBA module and report, plus who uses it and how often. This inventory is the specification for the new build.
Close the database to users, take a read-only copy, and record the date, file size, owner and the last person to edit. Store it with the trial master file. Do this again on the cut-over date.
Export each table to CSV or XLSX and produce a field list with names, types, allowed values and units. If the database has lookup tables, export them too; they become choice lists.
Group tables by when and by whom the data is collected. Screening, baseline, each follow-up and adverse events usually become forms; participant-level tables become the participant record; one-to-many tables become repeating rows.
Upload the field list or the exported sheet to a draft form. The AI reads CSV, XLSX, PDF and DOCX and drafts questions, choices and units. Review every field; nothing is saved until you approve it.
Turn each macro and validation rule from the inventory into skip logic, an edit check that raises an auto-query, a built-in calculator or an export. Retire the ones nobody can explain, and record why.
Run sample participants through every visit in the free sandbox, trigger the checks and sign. Give each user a role, and show coordinators how queries work instead of overwriting values.
Set one date, retire the Access entry forms and keep the archive. Join the legacy export to the Capture export at analysis using a variable map.
Data mapping
| Access object | Capture equivalent | Effort |
|---|---|---|
| Participant table (primary key) | Participant record with a site-level ID such as 001-0042 | Low |
| One-to-one table per visit | A form attached to a visit in the visit schedule | Low to moderate |
| One-to-many table (medications, adverse events, labs) | Table section with repeating rows, each row with its own audit trail | Moderate |
| Lookup table or value list | Choice list in a single or multiple choice field, or a dropdown | Low |
| Data entry form | eCRF form, drafted by AI from the field list | Low to moderate |
| Validation rule or field property | Edit check (range high, range low, custom value) with auto-query | Moderate |
| Calculated query field | Built-in calculated field where one exists (BMI, eGFR, QTc and others), or derive at analysis | Moderate |
| Saved query | Export with date-range and by-site filtering, or a status overview | Low to moderate |
| Macro or VBA | Not carried over; replace with skip logic, edit check or export | Moderate to high |
| Report | Rebuilt from exports in your analysis tool | Moderate |
| Attachments stored in a table | Stored with the trial master file or the source document system | Archive task |
Try it in the free sandbox with sample data. Nothing is saved until you approve it.
A common sticking point
The most valuable Access pattern, a child table linked to a participant, maps cleanly to a table section. A participant can have any number of medications or lab results, and each row carries its own field-level audit trail with user, time, old value, new value and reason for change.
Row 2 of 3
Medication
CMTRTDose
CMDOSEFrequency
Start date
Auto-query: start date is after the visit date
Be realistic
Macros, VBA and custom reports. They are not converted. Anything the code does must be re-expressed as configuration. Most of it turns out to be validation and derived values, which map to edit checks and calculators; the rest is usually reporting, which moves to exports.
Data stays where it is. The current records are not imported into Capture. Keep the final archived file and the CSV exports as the legacy dataset, and merge at analysis. Document the date range each source covers.
The old audit history. If the Access file had a change log table, archive it. It is not copied into the new audit trail.
Flexible ad hoc editing. In Access any coordinator could correct a value in a datasheet view. In Capture a change to a saved value records a reason and is visible in the audit trail. Some teams find that stricter at first, and it is the point.
Linked tables and external feeds. If the database pulls from Excel, a lab file or another Access file, decide how that data will arrive after the move. Capture exports wide-format CSV or Excel with a data dictionary and supports SDTM export, but this page claims no connector to external systems.
Validation and cutover
Tables, forms, queries, macros, VBA and reports listed with owners.
Read-only .accdb, CSV exports and change log filed with the trial master file.
The same participant IDs in both systems so no one is counted twice.
Rebuilt as an edit check, replaced by a calculator, or retired with a reason.
Codes from lookup tables approved by the statistician before the build is locked.
Researchers, site coordinators and admins set up, with names kept to the site.
Sandbox scripts, results and approvals. See computer system validation.
Access entry retired and the file locked on that date.
What changes
There is no direct import of an .accdb file. Export tables to CSV or XLSX, upload the field list to a draft form so AI drafts the eCRFs, and merge the legacy export with the Capture export at analysis.
They are not carried over. Replace each with skip logic, an edit check, a built-in calculator or an export, and retire any whose purpose nobody can explain.
It depends on the study and the regulator. For regulated trials you need access control, an audit trail, signatures and validation evidence, which a home-built file rarely provides without significant additional work.
Use a table section with one row per item. Each row has its own field-level audit trail and the edit checks apply to every row.
Prefer a boundary such as a new cohort or protocol. If you must switch mid-study, keep participant IDs identical and choose one cut-over date.
Archive it with the trial master file. It is not copied into Capture, whose audit trail starts with the first entry.
Yes. The free sandbox has every feature with sample data, no credit card and no time limit.
Keep exploring
How to migrate from Excel spreadsheets
The sister guide for spreadsheet databases.
REDCap alternatives
If you are also weighing academic tools.
Audit trail software
What a field-level trail records.
21 CFR Part 11 compliance checklist
What to verify in any system.
Edit checks software
Replace validation rules and macros.
CRF templates
Standard forms to start from.
Upload your field list to the free sandbox, review the drafted eCRFs and test before you cut over. No credit card, and you pay only once you go live.