Link to home
Start Free TrialLog in
Avatar of shannonbirt

asked on

Terminating msaccess.exe threads via a script for compact and repair

I want to automate the compact and repair of a Access 2003 database.

The application is split into a front end and a back end database, the database i want to compact is the backend database and hence i need all access threads terminated before compact and repair.

It is not easy to make sure there is no msaccess threads running at the time of compaction (if things are left unattended) so i would prefer a method that terminates the msaccess.exe threads (which there can be many for seperate users as its running on TS).

Ideally in script so i can schedule it via windows scheduler etc.

Ideas on commands necessary would be appreciated.
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image

This can be programmed into the FE. For example via a hidden form, checking the time every five minutes and forcing a shutdown after 22:00. Likewise, the FE can refuse to open before 4:00. That leaves you six hours to schedule your automated compact and repair overnight.

The form can also check whether a specific small text file exists in the BE folder. If it exists, the FE is shut down. Thus, you can force the shutdown at any time by creating that file and waiting five minutes.

Does that help?
you can use application.close as bottom there and trigger it to get the db closed (FE). also a start up form which do not connect to the (BE) would have time check before it goes the real startup form.

then you are safe to use the task scheduler as you know the application is closed ( you will trigger that first in the scheduler) and you are sure no one can open the backend.

waiting for your reply
Avatar of shannonbirt



How solid is application.close at closing the FE ? can it be stopped by errors or msgbox's etc. Eg a msgbox prompting the user can stop code from executing in the FE and i would guess it could stop application.close ? as its waiting for a user prompt. I ask this because i think i have tried application.close to close down the FE before and wasn't getting 100 % success at it actually closing the FE and i did find that the msgbox command could stop execution (its pending a click from the user).

If application.close is pretty reliable at closing the FE then i could schedule the FE to close on a timer.
there is a method (code) to stop sending confirmation messages to users. it would suppress all application messages (unless invalid data saving to the db i guess - but you would close the db connections for that). combined with application close i hope that works for you

waiting for your reply
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

For the sake of everyone here *PLEASE* ... stop posting 'waiting for your reply'
It is implied that you are waiting for a reply by the fact that you posted!

"How solid is application.close at closing the FE ?"

I just had an issue with this the other night - still not resolved.  In a transaction based processed, I was testing failure conditons ... and if a failure occurred, I would Rollback, clear variables and execute an Application.Quit - after informing the user via a message.  I kept getting the message that basically ? 'Quit' was not available at this time".

Marcus ?

For maintainence what i do is terminate all msaccess.exe threads then run compact and repair manually. Terminating the FE in this fashion doesn't cause me any problems because the front end file is replaced at every loggin with the most recent FE file so on the unlikely case of corruption etc in the FE from termination its not an issue as the FE gets replaced on loggin with the most recent FE backup.

I have tried application.close in the past and maybe 90 % of the threads will close leaving a few open for no reason.

I would prefer to attempt to close the application using application.close, but as insurance i would also like to then make sure all threads / connections are closed by terminating the msaccess.exe threads also. Anyone have a script that can do this ?
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I wasn't done about "what to do when Quit is not available?": When that happens, the shut down process should issue generous quantities of DoEvents and retry, possibly also informing the user also.

Here was my situation ... The Rollback part ... again, I was testing - forcing a Rollback to occur by creating an 'issue' with one of my action queries (not shown) ... so that it would fail and cause a Rollback.  Note that initially, I just had Application.Quit ... which caused the error I described before ("Quit is not available now). You basically had to End Task in TM to get closed out.  Eventually, I came up with a partial workaround seen below - using     Application.CloseCurrentDatabase first ... which prevented the error message and closed the app, but not the Access application window.  Here is the (partial) code:

    Set wks = Nothing
    MsgBox "An error has occurred during a one-time update process for a new version of the software related to" & Chr(13) & _
           "sold Consignment Items that have already been Invoiced." & Chr(13) & Chr(13) & _
           "If you are seeing this error message, *please* write down the error information below and call Tech Support immediately." & Chr(13) & Chr(13) & _
           "The program must close after you click OK in order to avoid data corruption." & Chr(13) & Chr(13) & _
           "Error Information --------------------" & Chr(13) & _
           "  Error number: " & Err.Number & Chr(13) & _
           "  Description: " & Err.Description, 48, "Software Update Error"
    DoCmd.Close acForm, Me.Name
    On Error GoTo 0
   'joe note: The app closes, but not Access. You must click the X to close the Access application window.
   'without the Application.CloseCurrentDatabase, an error message occurs say that the Quit action is not available.
   'So, the final solution to completely close the app is not known at this time - 03-03-2008.

    Application.Quit acQuitSaveNone
harfanq: thanks for the ideas, i do infact use my own msgbox type form which i use in my system timers if i need to alert the user etc. The issue is i havn't used this form everywhere and i have used msgbox elsewhere in my application. Slowly but surely i will change all the confirmations / prompts to my custom form, but that will take some time.

I have found the below which actually kills all msaccess.exe threads running on a computer. I think i will try and work through my code to kill the FE application on a timer, and then just to make sure will use the below script as part of compact and repair script.

Option Explicit
Dim objWMIService, colProcessList, objProcess
Dim strComputer
strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery("SELECT * FROM Win32_Process WHERE Name = 'msaccess.exe'")
For Each objProcess in colProcessList

Have allocated points to clear the question. Thanks Harfanq, MX.
Wow, that scrip looks dangerous! -- (^v°)


In order to make sure that Application.Quit is available, use a Timer event. It will run only once your current event is handled, and you can retry (e.g. every 100 milliseconds) simply by leaving the timer interval set.


Thanks for the points and especially for the detailed feedback.
Success with your project!