A Field Inspection System Rebuilt in Power Apps After Fourteen Years in Access
An energy operator ran asset inspections for 1,900 sites out of an Access database on a file share, with no audit trail and no way to work where there is no signal. We rebuilt it on Dataverse with an offline canvas app for the field and a model-driven app for the back office.
About the Client
The Challenge
The asset inspection system was a Microsoft Access database. An engineer had built it 14 years earlier and it was still the system of truth for statutory and routine inspections across 1,900 sites. Front end on individual laptops, back end on a file share, with the failure profile that combination always has: record locking when two people opened the same asset, corruption when a laptop dropped off the VPN mid-save, and a recovery procedure that meant restoring last night's copy.
There was no audit trail. Access held the current value of a field and nothing about how it got there. When a regulator asked who had signed off a pressure reading and when, the answer was reconstructed from the inspector's notes. That is why the replacement got funded — not the corruption, not the keying time, but the inability to prove who changed what.
In the field the process was worse than the database. Technicians carried printed sheets because several remote sites have no usable mobile coverage, filled them in by hand, photographed defects on personal phones, and re-keyed everything into Access at the end of the week. Photos rarely reached the record at all. The gap between an inspection happening and being visible averaged 6 days.
They had already tried a mobile forms product. It captured the inspection well enough but was a standalone island: it could not read the asset register, so technicians picked from a stale dropdown, and it could not write a work order back. Mobile capture is the easy half; integration decides whether anything improves.
Our Approach
We treated this as a data modelling project with an app on top, not an app project with a database behind it. The Access schema was the problem; the interface was a symptom.
Schema Reverse-Engineering and Process Mapping (Weeks 1-4)
Read the Access schema and found what years of incremental change produce: repeating column groups instead of child records, lookup fields degraded into free text so one site appeared under several spellings, and duplicate assets whenever a technician could not find an existing one. Mapped the real process alongside it, which differed from the database because people had worked around its limits for years.
Dataverse Data Model and Alternate Keys (Weeks 3-7)
Modelled asset, site, inspection, inspection line, defect and work order as related tables, with choice columns replacing free text and business rules carrying validation that had lived in the technician's head. The asset tag became an alternate key, which is what makes the migration and every later integration idempotent. Alternate keys have edges: ten per table, sixteen columns and 900 bytes per key, plus a character list — slash, angle brackets, asterisk, percent, ampersand, colon, backslash, question mark, plus — that breaks retrieve and upsert calls in key data. A number of legacy asset tags contained slashes, so the tag was normalised during migration. Auditing, row-level security and change tracking come as platform features.
Canvas for the Field, Model-Driven for the Office (Weeks 6-9)
Two apps on one data model, split by user rather than preference. Technicians got a canvas app: phone-shaped, sequenced through one inspection at a time, with barcode scanning, camera capture and offline capability. The office got a model-driven app, which generates its interface from the data model and gives grids, views, queues, dashboards and security roles without anyone drawing a screen.
Offline Design and Media Handling (Weeks 8-13)
Offline is a data design problem, not a switch, and the limits are specific. Only Dataverse tables sync — SharePoint and Excel do not work offline at all — which settled the data layer. An offline profile holds at most three million records across all tables, fifteen relationships of which only one may be one-to-many or many-to-many, and fourteen image columns. Flows do not run offline and calculated and rollup fields are not re-evaluated on the client, so anything the technician has to see was stored rather than derived. The profile pulls only the assets on assigned sites plus open inspections for the next fortnight; downloading the full register to a phone is the classic way to make an offline app unusable. Records created offline queue with a visible sync state, conflict rules favour field capture on readings, and photos upload separately. Background sync needs the app in the foreground with the screen unlocked, which went into the crew briefing.
Migration, Integration and Cutover (Weeks 12-18)
Migrated 410,000 records through dataflows with deduplication against the alternate key and a reconciliation report the asset team signed off row by row. Then built the integration the previous attempt lacked: a defect raised in the field creates a work order in the maintenance system through a flow with retry and a failure queue. Ran two cycles in parallel before Access was retired.
Solution Architecture
Data layer: Dataverse tables for asset, site, inspection, inspection line, defect and work order, with enforced relationships, choice columns, a normalised alternate key on the asset tag, and field-level auditing
Field app: A standalone canvas app in phone form factor with barcode scanning, camera capture, an offline profile scoped to assigned sites, and a visible sync queue
Back-office app: A model-driven app generating grids, views, dashboards, business process flows and security roles from the data model
Integration: Flows writing defects into the maintenance system as work orders, with retry policies, a failure queue and a reconciliation report rather than fire-and-forget calls. Reporting through Power BI
Security and lifecycle: Security roles mapped to site and business unit, managed solutions promoted through dev, test and production, and connection references on a service identity
Results
Client identity withheld under confidentiality. Figures are representative of engagements of this scope.
Technologies Used
Key Takeaways
If your organization is facing a similar challenge, here's what we learned:
Access does not fail loudly, which is why it survives. A shared database file works for years and then corrupts on a Tuesday. The business case is rarely the corruption — it is the missing audit trail, or that nobody left can modify it.
Offline is a set of published limits, not a toggle. Dataverse tables only, three million records, fifteen relationships, fourteen image columns, no flows and no client-side recalculation. Design the profile against those numbers before promising the app works where there is no signal.
Check the alternate key rules against your real identifiers. Ten keys per table, sixteen columns, 900 bytes — and a character list including the slash that breaks retrieve and upsert. Legacy asset tags are full of slashes. Normalise during migration.
Canvas versus model-driven is a question about the user. Field work is task-shaped, sequenced and phone-sized: canvas. Back-office work is grids, views and security roles generated from the data model: model-driven, and far faster to build.
Mobile capture without integration just moves the typing. If the app cannot read the live asset register and cannot write back, someone in the office still retypes everything. See our Power Apps development approach to write-back.
Related Case Studies
Still Running the Business on an Access Database?
The replacement is rarely hard. The data model and the migration are, and they decide whether the new system is better or just newer. We start there.