1

I have a team of 3 people at work who all use batch files to run Access macros on a regular basis with a command similar to the following:

START "" /min /wait "msaccess.exe" "%TEMP%\Database.accdb" /x Main

At the end of all macros called this way, we end them with a QuitAccess (Exit) action. This usually ends the Access process and hands control back to the batch file to continue.

The issue is that sometimes after the Macro has finished (we can tell it has finished because exported text/Excel files exist) the Access database doesn't close, meaning that the calling batch file is stuck, waiting for Access to close.

When this occurs, we fix it by going into Task Manager and killing the MSACCESS.EXE process. Then the batch file continues.

There isn't anything we can do at the end of the macro, or afterwards in the batch file, to fix this, because the process is stuck in a state between the macro and the batch file.

Does anyone have any ideas on how to prevent this?

Improve this question
3
  • If you're closing Access from inside of Access, I can see how this might create issues. I would probably be a more robust solution if you converted the batch files to VBScript and then interacted directly with the Access object model. Commented Feb 28, 2017 at 15:11
  • @Comintern Hmm, interesting. We have PowerShell scripts which use the object model and we don't seem to have the issue there. I see why the VBScript would work but it would take a lot of work (we have upwards of 50 databases in our function). Will test and get back to you! Commented Feb 28, 2017 at 15:14
  • Keep in mind that you can always separate out the Access related functionality from your existing batch files either by having the existing batch files invoke a .vbs when it needs database interaction or have the .vbs shell your existing batch files for non-database functionality. Commented Feb 28, 2017 at 15:18

2 Answers 2

Reset to default
1

Have you confirmed that the macro's themselves are not producing an error that may interfere with the Exit procedure? I would explore adding some error handling to the macros to trap any error. Check out the "OnError" option in Macros where you can yield control to when an error takes places and do whatever you need in that step: ie: Exit when an error occurs if that is the desired outcome

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Any error that occurs during the macro produces a message box (we deal with this more often than we should!), so I don't think any query errors are getting in the way of the Exit procedure. That said, I'll look into us using OnError more in the macros (we already do this in VBA). Thanks!
0

I'm not a batch file expert but I don't see you closing the Access database anywhere. It seems like this link (Kill MSAccess process via batchfile) is addressing the same problem. This answer references a solution on this website: Opening Access database from Batch File or VBS :

To get the database to close you need to write a Startup function which controls all the events you wish to run and then use Docmd.Quit, failing that you could run another .bat file (using Windows scheduled task) one or 2 minutes later using: TASKKILL /F /IM "MSACCESS.EXE" EXIT

Improve this answer

1 Comment

Ah, forgot to mention that bit! All of the macros have a QuitAccess action at the end of them, where the Access process usually dies and control is handed back to the batch file. Will investigate your links and get back to you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.