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:
Many thanks in advance for any help and suggestions as already one week I cannot solve the issue.
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.