External table in JOIN query lock external DB (MS-ACCESS)

Question

I have two mdb files :

one for each document (let say doc1.mdb : DocDB in query below)
one shared (cost.mdb) used by all documents (doc1, doc2 etc..)

Here is the query giving me some difficulties in multi-process environment only :

 UPDATE T_Relation_Cost_T As DocDB  
 INNER JOIN [C:\cost.mdb].[T_Cost_By_m2_T] As CostDB
 ON [DocDB].[F_GenericCostSupplierID_F]= [CostDB].[F_CostSupplierID_F]  
 SET [DocDB].[F_FinishGroupID_F] = 'XXXX'   
 WHERE([DocDB].[F_CostTabID_F] = 2)

This query is simplified for the example.

When executed in one process : this query works.
When executed in multi-process (two exe) : this query doest not works because a lock appears on cost.mdb when the query is executed at the same time by the two exe.

The error reported by ADO is that cost.mdb is not accessible.

I can't move on SQL Server this cost.mdb database or even more all databases I'm looking for ideas : LockType, Cursor, Recordset property but have no clue
Should I do a SELECT in cost.mdb with some criteria than UPDATE independently doc1.mdb in a second query to avoid the lock ?

What about waiting until the first process removes the lock? You can use also some process locking mechanisms like Mutex. — Vojtěch Dohnal, Aug 01 '17 at 11:22
Dear Vojtěch, Thanks for your proposal. I tried this at first glance. My problem is that in reality I can have 32 exe on a server working on same ressources. So waiting is time consuming and doesn't work 100% or perhaps setting a very long timeout ~30 sec. — pc78, Aug 01 '17 at 12:08
This is something to be included in the question. Did you try using MUTEX? — Vojtěch Dohnal, Aug 01 '17 at 13:49
See [this answer](https://stackoverflow.com/a/1943836/2224701). — Vojtěch Dohnal, Aug 01 '17 at 13:51
answer to previous question : No, I did not try to USE MUTEX — pc78, Aug 01 '17 at 13:59

External table in JOIN query lock external DB (MS-ACCESS)

0 Answers0