Summary
We eliminated weekly data corruption in a mission-critical Microsoft Access system by replacing the file-based backend with a stable PostgreSQL database - without disrupting users or rewriting the application.
Project details
Business Impact
- 0 data corruption incidents since deployment
- Continuity in existing forms and workflows
- Future-proofed legacy system for remote access, backups, and integrations
Background
The client’s internal database application, built in Microsoft Access, supported core business operations including donor relations management, pledge payment requests, and opportunities management. For years, the system ran with a shared .accdb MS Access backend file hosted on a file server. This setup was prone to corruption - roughly once a week - often requiring IT intervention, downtime, and manual data recovery.
Due to staff familiarity with the existing interface and the immediacy of the corruption problems, the organization needed a backend modernization that could retain their Access forms and reports with minimal retraining and minimal development time.
Solution
We migrated the Access backend to a PostgreSQL database, accessed via ODBC. The implementation followed a phased approach.
Phase 1: Baseline controls for MS Access backend corruption
- Regular compact and repair procedure to produce a reliable backup artifact and correct corruption before it could balloon.
- Compact and repair backup automation
- Split frontend and backend Access files
Phase 1 results
- Corruption limited to network interruptions only; corruption incidents no longer a weekly occurrence.
Phase 2: Migrate MS Access backend to PostgreSQL server
- Schema translation: Converted table structures and relationships from Access to PostgreSQL, preserving naming conventions and field types where possible.
- ODBC configuration: Deployed and tested PostgreSQL ODBC drivers across client machines with centralized configuration.
- Postgres/ODBC integration: Configured and tuned ODBC and Postgres components for compatibility and performance
- Code compatibility audit: Ensured that existing Access macros and VBA queries continued to work with minimal changes—handling only a handful of reserved keyword collisions and datatype nuances.
- Backup and rollback plan: Maintained the ability to roll back to any previous version of the system configuration.
Phase 2 results
- Data corruption is no longer an issue at all, due to PostgreSQL’s robust transactional engine.
- Performance has improved, especially on large datasets and multi-user operations.
- Robust integrations with reporting software and Python automations were made that were not possible with the Access backend.
Tech Snapshot
| Component | Details |
|---|---|
| Legacy Frontend | Microsoft Access (.accdb format) |
| Original Backend | Shared .accdb file hosted on an SMB share |
| New Backend | PostgreSQL 13 (hosted on internal VM, ready for future cloud hosting) |
| Connection Method | ODBC (psqlODBC driver), DSN-less connection strings |
| Schema Migration Tools | Manual translation, only minutes to apply during go-live |
| Link Table Update | Automated relinking using VBA and DSN-less connection configuration |
| Code Compatibility | Minor VBA updates (reserved keywords, Yes/No → boolean conversions, case sensitivity edits) |
| Backup Strategy | pg_dump scheduled nightly; access frontends stored in version control for rollback |
| Result | Zero corruption incidents since migration; nearly transparent to users |
Guidelight's Approach
We treat legacy systems with respect - not merely as obstacles, but as trusted tools that deserve careful upgrades. In this case, we honored the client’s budget, immediacy, and staff habits while delivering a backend with enterprise-grade stability.