Check to see if a table is in use

[cebrower]

We have a linked table that gets re-created once daily using a make table query followed by several update queries. Unfortunately, that table is used as a data source for several forms. If a user has one of those forms open the make table query fails. Is there a way in VBA to determine if that table is in use prior to running the make table query and maybe even to know which users have a form open that uses that table as a data source?

[isladogs]

Hi
First of all I would strongly recommend that any such update is done at a time when this issue isn't likely to be a problem e.g. 3 AM
Before running the update, ensure that the database is locked and, if anyone is logged in, they are given a few minutes to complete their work before the database is closed.

Secondly I would question the need for repeatedly creating a new copy of the table as that can result in instability,
Instead I recommend you empty the table using a delete query then add new records using an append query.

I am aware that this doesn't answer the specific questions asked but that is because I believe them to be the wrong questions.

Hope that helps, even if only indirectly.

[Micron]

Sometimes an ounce of prevention is worth a pound of cure. It's generally accepted that repeatedly deleting and recreating a table is not a good approach. Better to just append and update. If you had to flush all from that table as part of the process, then it's not much better than recreating the table in terms of it being locked - except some also think that repeatedly recreating a table can promote db corruption.

One thing you can do is try to create a locked recordset on the table with the dbDenyRead argument. If it errors, trap the error and present your custom message. Not sure if you should also specify dbDenyWrite as well. Wouldn't hurt I guess.

[cebrower]

To clarify: this table contains inventory information that is displayed on the order entry form used by the salespeople as well as production projections based on current inventory and confirmed orders. For the data to be accurate the inventory table must be updated daily by deducting yesterday's shipped orders and adding yesterday's production. That data entry is accomplished first thing in the morning, and then the table is recreated. Yes, I could delete and append (and plan to make that change), but I still have the same issue with the table being in use somewhere. The inventory manager doesn't know if the table is open somewhere until the query fails. Then she has to figure out who has the table open. Often a user might have the table open and then walk away from their desk for hours at a time or that user's work station might be in a different building on the facility or working from home. I was hoping to be able to create code that would warn her that the table is open and maybe even inform her which user(s) have it open.

[Micron]

You have an answer for part A? As for part B, I know of no way to tell who has a lock on a table. You can look at the laccdb file to see what machine has opened the db file but unless you know who is on what pc that's not much help. Having a user table would help if it included a check field or time stamp field for being logged in. Then you can use a routine to get their Windows login name and machine id and associate either of those with their actual name in the user table.

[isladogs]

Hi again.
This is a very common issue whenever table maintenance is required,. As such, it is normally solved by locking the database beforehand.
I use a boolean Kickout field for the purpose whose value is set true when maintenance work is needed. A hidden form checks for the Kickout value every 30 seconds and if its True, a closedown sequence is started.
The other issue of users leaving the app open when not in active use is normally solved using an inactivity timer. If the code detects that the user hasn't done any activity in a specified time e.g. 20 minutes, the app closes.

I can understand why you would like to know if a table is in use and by whom but its not something I've ever seen done. Do bear in mind that the table could be in use by several people simultaneously on different workstations. Even if its possible, there are better solutions as outlined above

[Minty]

Just as a slightly different tack on the problem at hand - If you had an inventory that was based on transactions in and out you wouldn't need to keep recreating this data, you could simply calculate it and it would be accurate at all times?
I realise this isn't a 5 minute fix, but it is the only real way to manage a moving inventory.