Search code examples
pythonms-accesssqlalchemypyodbcsqlalchemy-access

Multiple users working from same application through ODBC corrupt ms access DB file. What is wrong?

Multiple users use on their PCs same application that provides web interface to deal with MS Access .accdb located on shared drive. Application uses Flask+pyodbc+SQLAlchemy works through ODBC driver. More than 1.5 years it works perfectly for 10-15 users. After 2 weeks summer vacations something happened and MS Access files started corrupt. In this case backend returns error that DB file is not recognized.

Some simplified background, nothing special:

#db_models
engine = create_engine("access+pyodbc://login:pwd@MSACCESS")
base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

session lifetime is 12 hours. I re-use it for every query.

from db_models import session, Calls
calls_data = session.query(Calls).order_by(Calls.id.desc()).filter(*queries).all()

What I found or made:

  1. MS Access file corrupts when 2 or more users try to record in the same table. If one user works only there is no any issues. Seems records from different users try to add to same primary key.
  2. Before: if first user adds new record, second user can see new record after page refresh in browser. Data updated almost immediately. After: Second user doesn't receive any update, he gets old data.
  3. If I make session.close() before session.query in app second user get updated data. But in any case DB file corrupts if 2 users add new records.
  4. Don't know if it correct or not but MS Access lock file contains a lot of Null after host. Something like: HTUPPP16780 ADMIN NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
  5. Made Compact&Repair and deleted lock file - didn't help.
  6. Manually made new DB with tables structure and manually copied data. Changed link in ODBC driver to new DB.
  7. Returned to old application version that 100% worked before - didn't help.
  8. After any commit session updates and it possible to receive updated data on commit time. But before it works without any commits.

Many thanks in advance for any help and suggestions as already one week I cannot solve the issue.

Solution

  • Finally I got it worked:

    • Created empty .accdb file on local drive.

    • Imported all tables from file on shared drive as links and reconfigured ODBC from shared drive to local drive.

    Made it for each user. So there are no conflicts with any other users as ODBC works with file on local drive. Seems there are some magic communications between local and shared files on another level over ODBC that exclude an incomprehensible error I had.