Access 2007 Connection problem! After hours of processing the [ADOQuery].ExecSQL is producing exception

I am working on monolithic desktop application which interacts with the binary database files and some part with the access database 2007 (MDW database is in Access 97). This tool got some serious memory leaks and performance problem. I workout on the memory leaks and tool is now taking limited memory for large inputs (earlier it was crashing on large inputs). Please note that on large inputs this Tool runs for 1-3 days!

However this introduces a new problem now. After a certain period of time, the SQL queries/Database opertions are getting failed, For e.g. MyAdoQuery开发者_如何学编程.ExecSql is throwing exception of Type EoleException and Exception message such as “Cannot open the Microsoft Office Access database engine workgroup information file” comes up.

With the large input the amount of SQL transactions are high. Connections are open and closed frequently. I tried with some options like increasing Access Engine maxlocksperFile etc, but its not working...

Please suggest…

Ummm I'm not to familiar with Access(I use Postgre) at any rate, if your starting transactions and not commiting or rolling them back then that might just be the problem... you should use something likle(pseudo code):

start transaction
try
  execute queries and stuff
  commit transaction!!
except
  rollback transaction
end;

The first thing I'd do is update the workgroup file to the current format.

A97 is a long time ago, and the change from Jet 3.5 to 4.0 was huge. Back c. 2001 or so I had an app that was deployed in A2000 but I was using A97 for development, so the workgroup format was A97 (Jet 3.5). When I finally abandoned A97 for A2000, I updated the workgroup file to A2000 (Jet 4.0), performance improved for everybody using the app.

I don't recall if you can convert an MDW file or if you have to recreate it from scratch -- I haven't done this for so long I've simply forgotten.