Converting MS Access MDB to SQLite with ODBC: Multi-User Concerns & Data Integrity

Migrating MS Access Backend to SQLite While Maintaining ODBC Connectivity and Concurrency

The process of migrating an MS Access database (.mdb/.accdb) to SQLite involves three interrelated challenges: schema conversion fidelity, data type compatibility, and maintaining multi-user access through ODBC. The core tension arises from SQLite’s architecture diverging from MS Access’s hybrid file-based/client-server behavior. While SQLite eliminates Jet/ACE engine dependencies and offers portability, its concurrency model and lack of native ODBC integration with MS Access frontends introduce risks of data corruption, locking conflicts, and query inefficiencies.

Key technical hurdles include:

1. Schema Translation: Access-specific features (e.g., AutoNumber fields, composite primary keys, Yes/No Boolean types) require manual adjustments for SQLite’s typeless affinity system.
2. ODBC Driver Limitations: Third-party SQLite ODBC drivers (e.g., sqliteodbc, ChWerner) may misinterpret data types or fail to handle concurrent write operations gracefully.
3. Concurrency Assumptions: MS Access frontends expect row-level locking and transactional isolation, but SQLite employs database-level locks during writes, leading to contention.
4. Data Migration Pitfalls: Date/Time formats, floating-point precision discrepancies, and memo field truncation during bulk transfers.

The forum discussion highlights a critical misunderstanding: SQLite can serve multi-user workloads but lacks built-in client-server arbitration. ODBC exacerbates this by introducing additional latency and connection pooling complexities. Successful migration demands rigorous testing of write-heavy scenarios under simulated user loads.

Root Causes of ODBC Locking Conflicts, Data Corruption, and Schema Mismatches

1. SQLite’s Write Locking Mechanism vs. MS Access’s Jet-Based Expectations

MS Access users accustomed to Jet/ACE’s pessimistic locking (file-level or page-level) may misinterpret SQLite’s WAL (Write-Ahead Logging) mode or default rollback journal behavior. SQLite serializes write transactions, causing blocking when multiple ODBC connections attempt simultaneous INSERT/UPDATE operations. The ODBC layer compounds this by holding transactions open longer than necessary, especially in bound forms or reports.

Example: An MS Access frontend using a linked SQLite table via ODBC initiates a form update. The ODBC driver opens a transaction, writes to the database, but fails to release the lock promptly due to Access’s lazy transaction management. Subsequent users encounter "database locked" errors despite low activity.

2. Data Type Mismatches and Precision Loss

MS Access’s Double type maps to SQLite’s REAL, but differences in byte ordering (little-endian vs. big-endian) and IEEE-754 compliance (as debated in the forum) can cause rounding errors during ODBC transfers. Similarly, Access Date/Time fields stored as Double (OLE Automation dates) may convert incorrectly to SQLite’s TEXT (ISO8601) or INTEGER (Unix epoch) formats.

Critical Failure Case: A payroll application stores Currency values in Access with four decimal places. During migration, SQLite rounds these to two decimal places via ODBC’s type inference, causing accounting discrepancies.

3. Inadequate ODBC Driver Configuration

The sqliteodbc driver defaults to Read Uncommitted isolation level, which conflicts with Access’s default Read Committed expectations. Misconfigured DSNs (Data Source Names) may disable WAL mode or enforce unnecessary strict typing, leading to schema validation errors.

Example: A developer creates a DSN without enabling Journal Mode=WAL, resulting in frequent SQLITE_BUSY errors during peak usage.

4. Schema Conversion Oversights

Automated tools like dbMigration.NET or sqlite-gui often fail to:

• Convert Access AutoNumber to SQLite AUTOINCREMENT (which requires INTEGER PRIMARY KEY).
• Handle Access’s LOOKUP fields or validation rules.
• Preserve index collations (e.g., case-insensitive Text fields).

Result: Queries in the Access frontend return incorrect results or fail entirely due to missing indexes or case-sensitive comparisons.

5. Legacy VBA Code Assuming Jet/ACE Behaviors

MS Access VBA routines leveraging CurrentDb.OpenRecordset or DAO.Recordset methods may implicitly rely on Jet-specific features like immediate commit modes or row-level versioning. When redirected to SQLite via ODBC, these methods introduce unexpected transaction boundaries.

Mitigation Strategies for Schema Conversion, ODBC Tuning, and Concurrency

1. Schema Migration Best Practices

Step 1: Manual Schema Audit

• Map Access data types to SQLite’s affinity system:
  • AutoNumber → INTEGER PRIMARY KEY AUTOINCREMENT
  • Yes/No → BOOLEAN (stored as 0/1 integers)
  • Hyperlink → TEXT with URI validation triggers
• Remove Access-specific features:
  • Replace LOOKUP fields with foreign key constraints.
  • Convert Attachment fields to BLOB or external file paths.

Step 2: Use Specialized Conversion Tools with Post-Processing

• dbMigration.NET:
  • Configure Type Compatibility settings to enforce BOOLEAN for Yes/No fields.
  • Enable Preserve AutoNumber option.
  • Post-conversion, run PRAGMA foreign_key_check; to validate constraints.
• sqlite-gui:
  • Use the “Import via ODBC” feature but manually adjust column collations afterward.

Step 3: Index Optimization

• Recreate all Access indexes explicitly:

  CREATE INDEX "Customers_LastName" ON "Customers" ("LastName" COLLATE NOCASE);  
  

• Add covering indexes for common Access queries involving TOP N or ORDER BY.

2. ODBC Configuration for Stability and Performance

DSN Settings:

• Enable Write-Ahead Logging:

  Journal Mode=WAL  
  

• Increase busy timeout to 10 seconds:

  Timeout=10000  
  

• Force strict typing to prevent data loss:

  StrictTyping=1  
  

MS Access Linked Table Adjustments:

• Use Passthrough queries for write operations to bypass Jet’s query optimizer.
• Disable Record Locks in form properties to prevent premature locking.

VBA Code Modifications:

• Replace CurrentDb.Execute with explicit transaction control:

  Dim conn As ODBC.Connection  
  Set conn = CurrentProject.Connection  
  conn.BeginTrans  
  On Error Resume Next  
  conn.Execute "UPDATE Orders SET Status='Shipped' WHERE OrderID=123;"  
  If Err.Number <> 0 Then  
      conn.Rollback  
  Else  
      conn.CommitTrans  
  End If  
  

3. Concurrency and Locking Workarounds

WAL Mode Activation:

PRAGMA journal_mode=WAL;  

This allows concurrent reads during writes but requires VFS support (enabled in most ODBC drivers).

Retry Loops for Busy Errors:
Wrap critical operations in VBA retry logic:

Function SafeExecute(SQL As String, Optional Retries As Integer = 3) As Boolean  
    Dim i As Integer  
    For i = 1 To Retries  
        On Error Resume Next  
        CurrentDb.Execute SQL  
        If Err.Number = 0 Then  
            SafeExecute = True  
            Exit Function  
        ElseIf Err.Number = 3197 Then  ' SQLITE_BUSY  
            DoEvents  
            Sleep 500  ' Requires API declaration  
        Else  
            Exit For  
        End If  
    Next  
End Function  

Database Connection Pooling:

• Use a lightweight middleware (e.g., Python’s sqlite3 with FastAPI) to handle concurrent requests and serve as an ODBC data source.

4. Data Type and Precision Assurance

Explicit Column Typing:
Define columns with SQLite’s STRICT keyword (requires SQLite 3.37+):

CREATE TABLE Payroll (  
    EmployeeID INTEGER PRIMARY KEY,  
    HoursWorked REAL,  
    PayRate REAL,  
    TotalPay REAL GENERATED ALWAYS AS (HoursWorked * PayRate) STORED  
) STRICT;  

Data Validation Triggers:

CREATE TRIGGER Validate_Currency BEFORE INSERT ON Invoices  
BEGIN  
    SELECT  
        CASE  
            WHEN NEW.Amount NOT LIKE '%.__' THEN  
                RAISE(ABORT, 'Currency must have two decimal places')  
        END;  
END;  

Byte Order and Endianness Fixes:
Convert doubles using VBA’s CopyMemory API to swap bytes before insertion:

Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _  
    (Destination As Any, Source As Any, ByVal Length As Long)  

Sub SwapDoubleBytes(dbl As Double)  
    Dim arr(0 To 7) As Byte  
    CopyMemory arr(0), dbl, 8  
    ' Reverse byte array for big-endian  
    Dim i As Integer  
    For i = 0 To 3  
        Dim temp As Byte  
        temp = arr(i)  
        arr(i) = arr(7 - i)  
        arr(7 - i) = temp  
    Next  
    CopyMemory dbl, arr(0), 8  
End Sub  

5. Fallback to Client-Server Architecture

If locking issues persist, consider:

• SQLite → PostgreSQL Migration: Use pgloader to convert SQLite to PostgreSQL, then point MS Access to PostgreSQL via ODBC.
• SQLite Proxy Server: Deploy sqliteproxy (Go-based) to mediate connections and provide connection pooling.

Conclusion

Migrating MS Access to SQLite via ODBC is feasible for small teams but demands meticulous schema adjustment, ODBC tuning, and concurrency-aware coding. Prioritize WAL mode, explicit transactions, and data validation to avert corruption. For high-write environments, augment SQLite with a concurrency proxy or transition to a client-server RDBMS while retaining the Access frontend.