MS Access 2010: get (Windows) user who blocks database

0
votes

in a Windows Company network environment we have several users who access MS Access Databases via MS Excel VBA Programms. For updating the Database we´re bound to a legacy system. For entering data we use normal Access Forms and normalized tables. The Excel VBA don´t query the normalized tables directly but use a different Access Database which containes non-normalized records. The latter is deleted (via the Kill Command) and newly created after each "session" for entering new datas in the forms. So, if any user queries the database via an Excel Programm while the other programms wants to delete and re-create it, the deletion fails with the error 70 - access denied. (I guess in the Excel Programms there are some bugs and not all connections are closed, that´s why a user who keeps the programm open for a long time will block the deletion for a long time - but i´m not in charge of those programms O_o ).

So, I´d like to get the windows login (or any other information) of the user who currently blocks the database when the error is thrown. Is there any way to do this?? Alternatively I´d like kick any blocking user out of the database when it´s supposed to be deleted - is there a way to set some kind of priority for the kill command??

Thanks a lot!

1
excelms-accessvbams-access-2010

1 Answers

2
votes

To find out who has a file open use PsFile with the path to the file being blocked.

In terms of your architecture, if the non-normalized records are generated by queries which are (or can be) stored in your Access database, you should consider pointing the Excel file to a query, rather than generating a whole separate file for this purpose. You use the "Get External Data" section of the "DATA" ribbon to insert refresh-able tables in your Excel workbook.