0

Using: Delphi 10.2.3 Tokyo Enterprise / VCL forms application

I've written an application that connects via TADOConnection (JET 4.0 provider) to an existing Access back-end database (*.MDB) file that is located on a network shared drive (Z:). This Access back-end database is also connected to a front-end Access database by one or two users in the office to generate reports, and to input new data.

My program runs a SELECT..GROUP BY query on more than one tables in the database every 5 minutes to generate a report and display the result in a DBGrid (this report is displayed on a wall display). In order to minimize the time that the application spends in keeping the connection to the database open, I'm using a TClientDataSet/TDataSetProvider to cache the data and immediately disconnecting from the Access database (ie., closing the connection) as soon as the query is executed.

This is working well but there is a problem - when the application runs the query at the same time that a user has it open for editing via a form in the Access front-end database, my program will freeze. Windows Task Manager says for my program: "Not responding".

I think that this is because of table locks?

Is there any thing that I can do, any setting that I can change in the TADOConnection or TADOQuery component so that it does not freeze when the query is run?

If not, then is there any way to detect this lock condition, so that the scheduled query execution can be bypassed if the record is being edited at the time when the query is scheduled to run?

Thanks in advance for any tips or clues!

Steve F
  • 1,527
  • 1
  • 29
  • 55
  • Yes, use a real DBMS! Access is not designed for operating in a multi-user environment. – Delphi Coder Jan 31 '22 at 21:00
  • @DelphiCoder Ok, apart from using a SQL server database, is there any way that it can be done with Access? I am okay with just detecting the lock so that the query will not execute. Pls see updated question. To detect the lock condition, could I put an TADOTable.Edit for that table and catch an exception? – Steve F Jan 31 '22 at 21:30
  • Maybe it's possible to detect a lock. But I found only something in VB.net – Delphi Coder Feb 01 '22 at 14:28

0 Answers0