Check if a query is running

[Mr Banan Qry]

I’ve built an Excel front end application that uses a back end access database. The purpose of this application is to work through prospects. In the application there is a distribution functionality where users are provided with the next best prospect to work with (for instance by using estimate on potential revenue, scheduled recall etc).

The issue I’m having relates to users who are being distributed the same record, and I think it relates to the time it takes for the query to execute (1-4 sec). I've got a table where I store booked records (those that are being worked on and have been distributed).
The server where this database is stored is slow and not built with the intent of storing active databases.
I would like to know if there is a property available using DAO or ADO that I could use in order to find out if a specific query is being executed at the moment. My idea was to have execution paused until the query had finished

My work around is to create a very simple table that only would store one date value (the last execution of the query) and then check if that date value is within the last 5-10 seconds (and if it is have it paused for 5 seconds and the rechecked). When the query is allowed to be started it would start with updating the date value

I realise that there might be several related issues for this problem (for instance related to the server performance, table structure, the query itself), but I’m not very eager to work on issues in those areas.
I was hoping someone would know whether or not the property I’m looking for actually exists or if there is a better solution to this issue.

One prerequisite for any solution is that it’s available when using a different (Excel) fron end application.

 

[DavidAtWork]

I think you can use this to determine if your query is running

If SysCmd(acSysCmdGetObjectState, acQuery, "qryName") = 1 Then
Msgbox "This query is running"
End If

You'd have figure out how you could apply this to your application, but I would suggest you make this test before trying to execute the said query

David

 

[smig]

I think there is no way to code interupt a running query.

I think The only way is to have something printed before the query run and when it's done.

 

[jdraw]

Have you considered assigning a list of prospects to specific users? That would eliminate conflicts.