Blog › ICP guides

Microsoft Access developer on retainer: record locked in edit mode on shared ACCDB, Access VBA developer, Access 2016 developer on monthly retainer

October 10, 2026 · ~15 min read

A Microsoft Access developer was maintaining a commercial HVAC quoting database for a mechanical contractor running Access 2016 (32-bit) on Windows 10 Pro workstations, with the ACCDB file shared from a Windows Server 2019 file server at \\SERVER01\data\quotes\QUOTES_DB.accdb. Eight estimators connected to the shared ACCDB simultaneously via mapped network drives. The application — originally built in Access 97 and migrated through Access 2003, 2010, and 2016 over the years — managed the complete commercial HVAC quoting workflow: site survey data entry, equipment selection from a linked price list table, labor hour estimation, subcontractor pricing, and customer proposal generation via an Access report that printed PDF quotes. The main data entry form, frmQuoteForm, was bound to the tblQuotes table. The form’s Default Record Locking property was set to Edited Record — one of three options in Access form properties: No Locks (optimistic locking), Edited Record (pessimistic locking, lock acquired when the user starts editing), and All Records (lock entire table for the form’s duration, extremely restrictive and rarely appropriate). With Edited Record locking, the Access ACE engine (the Jet database engine successor in Access 2007+) acquires a record-level lock on the tblQuotes row being edited the moment the user makes a change in any bound control on the form. This lock is held in the QUOTES_DB.laccdb lock information file (the lock companion file created by Access next to the ACCDB when any user opens it) and enforced by the ACE engine across all instances sharing the ACCDB file. On a Wednesday afternoon, estimator HVAC-EST-03 at workstation ESTIMATOR-WS03 navigated to quote record Q-2024-09847 (a commercial proposal for Horizon Towers Phase 2, fourteen rooftop HVAC units, total estimated at $287,430), clicked into the txtTotalLabor field to update the labor estimate after getting a revised subcontractor quote, and the ACE engine placed an exclusive record lock on the tblQuotes row for QuoteID = Q-2024-09847. The estimator’s lunch alarm went off; they minimized the Access window without saving or pressing Escape to cancel the edit, and walked out to lunch. Forty-seven minutes later, estimator HVAC-EST-07 at workstation ESTIMATOR-WS07 needed to access the same quote to add a change order line. When HVAC-EST-07 navigated to Q-2024-09847 in frmQuoteForm and attempted to click into any field, Access displayed: “Could not update; currently locked by user ‘DOMAIN\hvac-est-03’ on machine ‘ESTIMATOR-WS03’.” HVAC-EST-07 could view the quote record in read-only mode, but could not make any changes until HVAC-EST-03 either saved or discarded the pending edit. HVAC-EST-03 was at lunch with no plans to return for another 30 minutes. Lock conflict frequency per day: 4–8.

The root cause was the interaction between Access’s Edited Record pessimistic locking mode and the ACE engine’s lock lifetime semantics for shared ACCDB files. The ACE engine (Access Connectivity Engine, the successor to the Jet database engine) implements record-level locking for ACCDB files (Access 2007 and later file format) and page-level locking for MDB files (Access 97–2003 file format). Record-level locking in the ACCDB format means each individual row in a table can be locked independently, without locking adjacent rows on the same data page. The record lock is owned by the Access session — the instance of Access running on a specific workstation — and is held in the .laccdb lock information file that the ACE engine creates next to the ACCDB file on the file server. The lock is released when the owning Access session completes the edit by saving (pressing Shift+Enter, clicking the save button, or navigating to a different record while form navigation triggers the save), canceling (pressing Escape), closing the form, closing the Access application, or losing network connectivity to the file server. The lock is not released automatically by a timeout — unlike Oracle Database sessions that can be killed by a DBA, or SQL Server sessions that are subject to LOCK_TIMEOUT settings, there is no built-in configurable timeout that releases an ACE record lock held by a live Access session. The lock persists for the entire duration of the Access session’s uncommitted edit. An estimator who opens a quote record, makes a partial change, and then minimizes Access to attend to something else has locked that quote record for every other estimator on the shared ACCDB for the entire time they are away, with no automatic resolution. The only mechanisms available to release the lock without waiting for HVAC-EST-03 to return from lunch are: have HVAC-EST-03 save or discard their edit remotely (e.g., via a phone call); use the Windows server to close HVAC-EST-03’s network connection to the file share (which would eventually cause the ACE engine to detect a connectivity failure and release the lock); or, as a last resort, forcibly terminate the Access process on ESTIMATOR-WS03 via a remote administration tool. None of these are acceptable as routine operations in an 8-person estimating team working against a commercial bid deadline.

The single-user development testing invisibility of Access pessimistic lock bugs is characteristic of all Access databases that are developed on a single workstation and then deployed to a multi-user shared-file environment. When the developer who built frmQuoteForm tested the form on their own workstation against their own local copy of the ACCDB, there was only one Access session accessing the file at a time. The developer tested that editing a quote record worked correctly, that saving committed the changes, and that the Escape key canceled the edit without saving — all the expected single-user behavior. The lock behavior only becomes visible when two Access sessions on two different machines access the same ACCDB simultaneously, which by definition cannot happen in a single-developer-on-one-workstation test environment. The Access error message “Could not update; currently locked by user ‘DOMAIN\hvac-est-03’ on machine ‘ESTIMATOR-WS03’” identifies the lock holder by username and machine name, which the ACE engine reads from the .laccdb file. Reading the .laccdb file directly (it is a binary file but the Access user name and machine name strings are readable with a text editor or a VBA routine that opens it as a binary file) or using the Access Application.CurrentUser VBA function in combination with network drive information confirms the holding session. The Access developer community has a convention for reading the .laccdb to list all currently connected users and their lock states; a retainer developer familiar with this diagnostic can confirm the lock holder, the record being held, and the lock duration within minutes of being notified of a lock conflict.

Microsoft Access in 2026: the desktop database powering small business operations on file servers

Microsoft Access (originally codenamed “Cirrus”, first shipped in November 1992) is Microsoft’s desktop relational database and application development environment, bundled with Microsoft Office Professional and sold as a standalone product since its introduction. Access occupies a unique category: it is simultaneously a relational database engine (the Jet/ACE engine stores data in MDB or ACCDB files on disk), a form designer (for building data-entry screens), a report designer (for printing formatted output), and a VBA IDE (for writing automation logic). This combination made Access the default choice for small-business application development throughout the 1990s and 2000s: organizations that needed a custom application for quoting, inventory, customer management, project tracking, HR records, or compliance documentation could build it in Access without purchasing a server database license, hiring a server database administrator, or writing a full application from scratch. The result is a vast installed base of Access applications that continues operating in 2026 across small manufacturing shops, HVAC and mechanical contractors, architectural firms, legal practices, non-profit organizations, government agencies, and healthcare clinics — organizations whose Access database was built by a consultant or internal developer 10–25 years ago and has been maintained minimally since, handling critical business data that no one wants to migrate to a modern system because the migration cost and risk exceeds the operational pain of maintaining the Access application.

The split database architecture is the standard recommended practice for multi-user Access deployments: the data tables are stored in a backend ACCDB file on a file server (the backend), and the forms, reports, queries, macros, and VBA modules are stored in a frontend ACCDB file on each user’s local workstation (the frontend), with the frontend’s tables linked to the backend via ODBC or JET linked table connections. The split architecture reduces the risk of ACCDB file corruption — a known problem with monolithic single-file Access databases shared directly from a file server when a user’s workstation loses network connectivity while an ACE write operation is in progress — and allows the frontend to be updated independently of the backend data. Many legacy Access applications, however, were never split: the original developer built a single ACCDB file and told users to open it directly from the shared network drive, and it has been running that way for years. This monolithic shared-file architecture maximizes the corruption risk and lock contention surface area, and diagnosing performance and locking issues in a monolithic shared-ACCDB environment is a core competency of an experienced Access retainer developer.

Access developer scarcity is counterintuitive: Access is not an obscure tool, and many people know how to use it at a basic level. But Access development — writing robust multi-user VBA applications with correct locking semantics, proper error handling in DAO and ADO recordset code, split database architecture, linked table management, and optimistic concurrency conflict detection — requires skills that few developers actively build in 2026 because no one is learning Access development as a career choice. The developers who built the existing Access application inventory are aging out of the workforce. An Access developer who can diagnose a pessimistic locking conflict in a shared ACCDB, implement a correct optimistic locking strategy with a BeforeUpdate conflict handler, convert a monolithic ACCDB to a split database, and migrate an Access backend to SQL Server is providing expertise that is genuinely hard to find for an HVAC contractor or architectural firm whose entire quoting workflow depends on the Access application.

Access JET/ACE locking internals: Edited Record vs. No Locks, .laccdb diagnostics, and the optimistic locking fix

Access forms have three Default Record Locking settings that control how the ACE engine manages record locks when a user edits a record through a bound form. Edited Record: the ACE engine acquires a record-level exclusive lock (in ACCDB format) or page-level lock (in MDB format) on the record being edited the moment the user modifies any bound control on the form. The lock is held until the user saves (commits) or cancels (discards) the edit. In ACCDB format, this is record-level locking: only the specific row being edited is locked, allowing other users to edit other rows in the same table simultaneously. The lock is held for the entire duration of the uncommitted edit, with no automatic timeout. No Locks: no lock is acquired when the user starts editing. The record is held in memory without a lock. When the user saves (commits), Access applies optimistic concurrency: at save time, Access checks whether the record has been modified by another user since the current user last read it. If the record was not modified, the save proceeds normally. If the record was modified by another user in the interim, Access detects the conflict and displays a standard conflict dialog giving the user options: save their version (overwriting the other user’s changes), discard their changes and keep the other user’s version, or copy the current user’s changes to the clipboard. The Access developer can intercept this conflict in the form’s BeforeUpdate event handler to present a custom conflict resolution dialog instead of the default Access message. All Records: the ACE engine locks all records in the form’s recordsource table for the entire duration of the form being open. This prevents all other users from editing any record in the table while anyone has the form open in edit mode, and is appropriate only for single-user or administrative-use forms.

The technical mechanism of the ACE optimistic concurrency check in No Locks mode: when the Access form loads a record, the ACE engine stores the record’s current field values in a hidden comparison copy. When the user saves, Access executes an UPDATE tblQuotes SET ... WHERE QuoteID = [QuoteID] AND Field1 = [original_Field1] AND Field2 = [original_Field2] AND ... — a compare-and-swap pattern where the WHERE clause includes the original field values to detect any concurrent modification. If the UPDATE affects 0 rows (because another user modified the record since the current user queried it, changing one or more of the field values in the WHERE clause), Access knows a conflict occurred and triggers the conflict dialog. The Access developer can implement a more robust conflict detection strategy by adding a LastModified timestamp column to the table and comparing only this timestamp in the BeforeUpdate event rather than comparing every field, reducing the false-conflict rate that occurs when two users modify different fields of the same record simultaneously. For the HVAC quoting database, the fix involved: changing frmQuoteForm’s Default Record Locking from Edited Record to No Locks; adding a LastModified Date/Time column to tblQuotes with a default of Now(); adding an AfterUpdate macro on the table to set LastModified = Now() on every save; adding a Form_BeforeUpdate event handler that reads the current tblQuotes.LastModified for the record being saved and compares it to the LastModified value that was loaded when the form opened the record — if different, presenting a custom conflict message: “Another estimator has updated this quote since you opened it. Review their changes before saving yours.” with options to view the current version or cancel. Lock conflict frequency per day: 4–8 before fix, 0 (one legitimate merge notification in the first week, handled gracefully by the conflict dialog).

The 5-minute inactivity save is an additional defensive measure that reduces the window during which a pessimistic lock (or an unsaved No Locks optimistic edit) is held in memory by an idle Access session. The Access Timer event fires on a configurable interval set by the form’s Timer Interval property (in milliseconds). A Form_Timer event handler can check the form’s Dirty property: if Me.Dirty = True, the form has unsaved changes. A timer-based inactivity handler can prompt the user: “You have unsaved changes to quote Q-2024-09847. Save now to prevent conflicts with other estimators, or press Escape to discard your changes.” In Edited Record mode, the timer handler can force-save (call DoCmd.RunCommand acCmdSaveRecord) after a configurable idle threshold, or force-cancel (DoCmd.CancelEvent followed by navigating away from the record). This pattern mirrors the Oracle Forms inactivity timer approach and the Oracle Database session idle timeout — the principle is the same: any system that allows long-lived exclusive locks held by interactive users who may be interrupted must have a bounded lock lifetime or an explicit inactivity eviction mechanism to prevent the lock from being held indefinitely.

Typical Microsoft Access developer retainer work and what it looks like in a work log

Edited Record pessimistic locking with no inactivity timeout is the canonical Access invisible production lock bug for any shared ACCDB used by multiple concurrent users editing records in the same table simultaneously. It appears in the work logs of Access retainer engagements for HVAC and mechanical contractor quoting systems, architectural firm project management databases, small manufacturing shop scheduling and inventory databases, legal firm matter management databases, and HR and compliance record-keeping databases where the Access application was built with a single developer on a single workstation and deployed to a multi-user shared-file environment without the developer ever testing the multi-user locking behavior. The work log entry that makes this diagnosable: “Access 2016 32-bit; database QUOTES_DB.accdb on \\\\SERVER01\\data\\quotes\\; form frmQuoteForm; table tblQuotes; Default Record Locking was Edited Record; record locked: QuoteID Q-2024-09847 (Horizon Towers Phase 2, 14 rooftop units, $287,430); locking user DOMAIN\\hvac-est-03 on ESTIMATOR-WS03; lock held 47 minutes (estimator at lunch); second estimator (DOMAIN\\hvac-est-07) received ‘Could not update; currently locked by user hvac-est-03’ on QuoteID Q-2024-09847; lock conflict frequency before fix: 4–8 per day; fix: frmQuoteForm Default Record Locking changed to No Locks; LastModified datetime column added to tblQuotes; Form_BeforeUpdate conflict detection handler added (compares LastModified at load vs. at save); conflict frequency after fix: 0; hours: 2h.”

Access-to-SQL Server linked table migration is the most common planned Access retainer engagement for organizations that have outgrown the performance and concurrency limits of a shared ACCDB file but do not want to replace the Access frontend. The migration replaces the backend ACCDB data tables with SQL Server tables, relinks the Access frontend to SQL Server via ODBC linked tables, and rewrites form recordsources and VBA DAO recordset code to use ODBC or ADO connections instead of Jet linked table cursors. SQL Server eliminates the ACE engine shared-file architecture entirely: SQL Server handles all locking (using row-level or page-level locks depending on isolation level), eliminating the .laccdb file-based locking model, the corruption risk of network connectivity interruptions, and the file-share multi-user concurrency limitations. SQL Server Read Committed Snapshot Isolation (RCSI) provides non-blocking reads for Access forms querying SQL Server data, which means Access form load performance improves dramatically because form queries no longer block on concurrent write locks. Work log: “QUOTES_DB.accdb backend migration to SQL Server 2019; Access 2016 32-bit frontend; SQL Server 2019 on Windows Server 2019 \\\\SERVER01\\SQLEXPRESS; Access Upsizing Wizard used to migrate 11 tables (tblQuotes: 48,000 rows, tblQuoteItems: 312,000 rows, tblCustomers: 2,100 rows, tblPricebook: 8,400 rows, 7 lookup tables); 4 tables required manual schema adjustment after upsizing (autonumber PK converted to IDENTITY(1,1), Yes/No fields converted to BIT, Memo fields converted to NVARCHAR(MAX)); ODBC System DSN created on each of 8 workstations (DSN name: QUOTES_DB_SQL, server: SERVER01\\SQLEXPRESS, database: QUOTES_DB, authentication: Windows Integrated); Access frontend tblQuotes and tblQuoteItems linked tables refreshed to ODBC DSN; all 23 forms and 14 reports tested against SQL Server backend; frmQuoteForm Default Record Locking confirmed No Locks (SQL Server backend uses RCSI, optimistic concurrency via @@ROWVERSION matches Access conflict detection); QUOTES_DB.accdb backend archived; hours: 16h migration + 6h testing.”

Access VBA DAO recordset error handling with correct lock release is the most common Access retainer work involving explicit programmatic record locks. Access VBA code that opens a DAO Recordset with dbOpenDynaset type and dbPessimistic locking (explicit pessimistic mode) and calls rs.Edit followed by rs.Update must call rs.CancelUpdate before closing the recordset in any error path where rs.Update was not reached. A VBA procedure that uses On Error GoTo ErrorHandler and jumps to the error label after rs.Edit but before rs.Update holds the DAO pessimistic lock on the current record until rs.Close or the procedure scope ends — and if the error handler does not call rs.CancelUpdate, the lock held from rs.Edit is held until the Recordset object is released. In a shared ACCDB environment, this means a VBA procedure that fails mid-edit can hold a page or record lock for the duration of the procedure’s error handling, which blocks other Access sessions. Work log: “QUOTES_DB.accdb; VBA module modQuoteTotals; procedure UpdateQuoteTotals; DAO Recordset rs opened with dbOpenDynaset, dbPessimistic on tblQuotes WHERE QuoteID=quoteID; rs.Edit at line 14; txtGrandTotal validation at line 18 raises error on Null value from disconnected subform; On Error GoTo ErrorHandler jumps to line 94; error handler calls rs.Close without rs.CancelUpdate; DAO pessimistic lock on tblQuoteHeader held until rs goes out of scope at procedure end; lock held for duration of error message display (user confirmation dialog in error handler: 18 seconds on average); fix: rs.CancelUpdate added before rs.Close in ErrorHandler; all DAO recordset procedures in modQuoteTotals audited for missing CancelUpdate on error paths; 3 additional procedures found with same pattern; all fixed; hours: 1.5h.”

How HourTab tracks Microsoft Access developer retainer hours

Access Edited Record pessimistic lock bugs are invisible in single-user development testing by the same mechanism that makes all multi-user lock bugs invisible when only one session accesses the data at a time. The developer who builds frmQuoteForm tests it on their own workstation: they open a quote record, click into a field, edit a value, and save — all working correctly because there is only one Access session accessing QUOTES_DB.accdb at a time. The developer never tests the multi-user scenario of two estimators editing the same quote simultaneously, because the developer was building the form alone on a single workstation and probably never had a second Access instance running against the same ACCDB file during development. The Edited Record lock behavior is entirely correct for a single-user scenario; it only becomes a problem in production when multiple estimators share the file from a Windows Server file share and one of them leaves a record locked in edit mode indefinitely.

The work log entry that makes Access lock work auditable must name every element of the causal chain: the Access version (Access 2016 32-bit vs. Access 365 64-bit — locking behavior is identical, but API declare compatibility differs for VBA code), the ACCDB file path (including the UNC server path, which identifies the file-share shared storage context), the form name, the table name, the record identifier with enough human-readable detail for the client to recognize the specific quote, the locking user identity (from the .laccdb file or the Access error message text), the lock duration, the impact on other estimators, and the conflict frequency before and after the fix. A log that says “fixed Access lock issue, 2h” is not auditable to a commercial HVAC contractor’s operations manager who is trying to explain to their estimating team why they keep getting locked out of quotes. A log that names the form, the record, the locking user’s identity from the .laccdb file, the 47-minute lock duration, the No Locks change and BeforeUpdate conflict handler implementation, and the zero-conflict outcome after the fix is auditable and demonstrates the value of a long-term Access retainer relationship for a contractor whose entire quoting workflow depends on the Access database. HourTab gives Microsoft Access developers a public retainer-hours URL they send to clients — HVAC and mechanical contractors, architectural firms, small manufacturers, legal practices, and non-profit organizations that run core operational workflows on Access databases maintained by an Access specialist on monthly retainer.

The broader context for Access retainer billing is that the Edited Record pessimistic lock held by an interrupted Access session is the Access instantiation of the universal principle that any system allowing long-lived exclusive locks held by interactive users demands a bounded lock lifetime. Oracle Forms developer retainers cover the equivalent in Oracle Forms: LOCKING_MODE AUTOMATIC fires SELECT ... FOR UPDATE on the Oracle table row when the operator presses the first key in a field, holding the Oracle session row lock for 2+ hours when the operator is interrupted, blocked by exactly the same mechanism — the lock is associated with the interactive session and has no automatic timeout. VBA developer retainers cover the equivalent in Access VBA DAO recordsets: a DAO Recordset opened with dbPessimistic locking calls rs.Edit to acquire the JET page lock, and the error path calls rs.Close without rs.CancelUpdate, holding the lock for the duration of the error handler execution. DataFlex developer retainers cover the equivalent in DataFlex: an implicit lock acquired by FIND in write mode that creates a record lock not released on the validation failure path, causing lock contention in multi-user DataFlex shared-file deployments by the same invisible single-user development testing pattern.

FAQ: Microsoft Access developer retainers