Professional MS Access VBA Development — Stable, Secure, and Scalable Code.

64-bit Office VBA migration (PtrSafe)

When your organization upgrades from 32-bit to 64-bit Office, any legacy Windows API Declare statements in your VBA modules stop compiling — or crash at runtime with a Bad DLL calling convention error. The fix requires adding the PtrSafe keyword to each Declare, replacing Long with LongPtr for pointer-sized handles, and testing compile-clean on the actual target bitness. This is a module-by-module audit — not a find-and-replace — because some APIs need #If VBA7 conditional compile blocks to stay compatible with both 32-bit and 64-bit installs.

DAO vs. ADO — and why it matters

DAO (Data Access Objects) is the native Jet/ACE engine interface — the right choice for local Access table operations. It's faster because it talks directly to the engine without an ODBC layer. ADO (ActiveX Data Objects) is better suited for SQL Server backends, ODBC-linked external sources, and cross-database connections. The most common Access VBA performance problem I see: ADO recordsets opened against local Access tables when DAO was appropriate, or full-table recordsets opened when a filtered SQL string returning one row would do the same work in 1/100th the time. Fixing the wrong one is a targeted change — not a rewrite.

COM automation — Excel, Outlook, Word from Access VBA

Access VBA can control any COM-capable Office application: open Excel workbooks and write query results to specific cells, generate and send Outlook emails with report attachments, populate Word templates with Access data. The part that breaks in production: object lifecycle management. Without explicit .Quit calls and Set obj = Nothingin the exit block (not just the happy path), COM handles leak. By the fifth automated run, Excel instances are piling up in Task Manager and the next call throws a cryptic “ActiveX component can't create object.”

VBA error handling — real handlers vs. Resume Next patches

Every procedure touching a recordset, a file path, a COM object, or user-entered data should have an On Error GoTo [label] handler — not On Error Resume Next. A proper handler names the procedure, logs Err.Number and Err.Description, closes open objects in the exit block regardless of how the procedure ends, and either re-raises or swallows the error deliberately. On Error Resume Nexthas a narrow legitimate use (checking if an object property exists before assignment) — it's not a substitute for error handling you haven't written yet.

Class modules — when and why to use them in Access VBA

Class modules let you encapsulate related logic into reusable objects with their own properties and methods. In Access they're most useful for wrapping COM automation sessions (so Outlook handles close cleanly even on error), building database connection managers that don't leak across form events, and constructing state-machine controllers for complex multi-step workflows. Without them, large Access applications accumulate monolithic form code modules with 2,000+ lines that nobody can test, reuse, or safely modify. Class modules are the structural fix — they don't make VBA more complex, they make it less.

Access–SQL Server VBA integration

Access databases linked to a SQL Server backend need VBA that understands the boundary between local and server-side work. Pass-through queries run T-SQL directly on the server and return results — they're the right tool for bulk operations, stored procedure calls, and anything that would be slow over a linked table. VBA that calls linked tables directly and filters in Access is the performance killer. The diagnostic is: trace which record sources are doing client-side filtering on server data and replace them with pass-throughs that filter on the server.