Shell and Wait in ms-access, determining when processing has finished

[Skippy]

I want to read the metadata from a folder full of jpegs, write the metadata into a database table, then write some metadata back to the jpegs. I am using json files to capture data from exiftool and also to instruct exiftool what to write into the jpegs.  The problem is how to make ms-access wait until the metadata reading process is finished before starting to consume the json file.  One way to do this is to put a msgbox command after the shell command that calls exiftool which asks the user to wait until the cmd window has closed.  When the user sees the black box on the task bar disappear, they press the OK and things move to the next step.  Obviously, you can't hide the exiftool command window (using the window mode arg in the shell command) if you want to do this. 

I have now found some VBA code (shellwait) that can make the ms-access script wait until exiftool finishes.  I have tested it on ms-access 2010 32 bit, running on 64 bit Win 8.1.  It works on my test folder, however I had to add in a few lines of coded to check that exiftool.exe exists before calling shellwait.  If exiftool is missing, a silent failure will occur, so you need to check for exiftool first.  Also you can't call exiftool directly if you are getting exiftool to write a json file, you have to call the cmd window followed by exiftool + args.  This is because the ">" symbol which directs output to a file works at the cmd prompt but not within a shell cmd string.  The cmd string I pass to shellwait looks like this cmd /c F:\Test\exiftool.exe -iso -CreateDate -FileName -FileSize  -json C:\Temp\exif\141_0604 > F:\Test\out.json.  The /c switch is probably necessary as it terminates the exiftool process after it has run, otherwise the ms-access application would not proceed.

The shellwait code is available from http://access.mvps.org/access/api/api0004.htm.

Code for checking if exiftool exists in folder where the ms-access database application is.

Code Select Expand


Dim fso as object   
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(Application.CurrentProject.path & "\ExifTool.exe") Then
      MsgBox "Exiftool needs to be in same directory as database application", vbExclamation
      GoTo exitproc
End If

Check my other posts for how to consume the json file.