Run a Dos .bat batch file from Access

[tunsarod]

Hi Everybody,
I have been using dos .bat files to carry out some file management in the background with the batch files being called from within a form's vba module. This is working fine except in one particular scenario.

I send my client an updated version of the front-end via Dropbox. The working front-end, on detecting the new file is present, copies it to the front-end folder with the name of newfile.mdb.

When the user closes the front-end I want the existing application file to be renamed to say oldfile.mdb and the newfile.mdb file renamed to become the new working front-end when the user restarts the program.

It's essential that this happens without direct user involvement. No pressing of buttons.

I have a bat file 'fileswop.bat' with the following lines in it.

REM---------------------------------
TIMEOUT /T15 /nobreak

REN wkgfile.mdb oldfile.mdb
REN newfile.mdb wkgfile.mdb

REM----------------------------------

In the front-end I have a button which opens a 'Do you really want to quit?", MsgBox() that demands a Yes or No. Yes leads to an Application.Quit statement but before that I have this:

Call Shell(Environ$("COMSPEC") & " /c C:\myProg\FileSwop.bat", vbNormalFocus)

Apart from the filename I got everything else off the web.

So before the front-end closes it starts this bat file. I can see that the bat file starts because the dos command window opens briefly then dissapears. I know its not closed because I can hear disk activity at around the 15 second mark, which matches the Timeout /15 instruction. But the filenames just do not change.

If I run the bat file manually even a few seconds after application has closed then it works perfectly well. I concluded that the Dos file cannot rename the application that has just closed because it, the front-end application, is stil in memory. So I increased the Timeout to 30 seconds thinking it just needed more time. I also put the REN statements in a second bat file and after the appropriate Timeout pause called the second file but that didn't work either.

Any ideas folks.
Thanks in anticipation
Rod

p.s. If anyone thinks there's a better group to post this to I would be obliged to hear.

 

[Andrzejek]

I would try this:

MsgBox "Let me run FileSwop.bat"
Call Shell(Environ$("COMSPEC") & " /c C:\myProg\FileSwop.bat", vbNormalFocus)
MsgBox "I am done with FileSwop.bat"

And see how much time elapses between the 2 message boxes.
Chances are, your application is waiting for Shell command to finish before the next line of code is executed. Which would mean you are trying to rename the file that is still in use, and OS is not allowing you to do so.


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[tunsarod]

Yes I think you are right. AS you can see I tried making the bat file wait half a minute, which is way long enough for my front-end to close fully. Question is how to get around it.

Rod

 

[Cretin]

what didn't work. Did the program just hang there?(that usually means the .bat is waiting for intervention) or did it just not work?

Cretin

 

[tunsarod]

OK, problem solved but it was not easy because my dos skills were rusty and I didn't realise (do now) that MS have varied the Dos command structure with each Windows implementation. Essentially I have used : Ren, Rem, XCopy and Del commands to achieve my aims.

When I update my front-end application on my pc I put the updated app into a Dropbox folder that links to my client's site. When any of my client's users starts the front-end on their machine, code in a splash form compares a property in the file in Dropbox folder with the clean copy that I keep in the back-end folder on the 'fileserver'.

Previously I added a bespoke Database property to my front-end designed to hold a decimal version date derived from the Now() function. I update this before putting the new version into Dropbox.

If the copy in the Dropbox folder is later (newer) than the copy in the back-end folder then the front-end starts a batch file to transfer the new version onto the
fileserver. I tried doing this with VBA but because the front-end is in the region of 300mb large it takes too long and the user cannot procede while Access is managing the file transfer. By calling a batchfile my application carries on and the user is none the wiser.

Similarly when the user starts the main page of my app code in that form starts another batchfile that copies the new version, with a slight name change, from the fileserver to the folder on the user's pc where the working application is stored.

When the user closes the front-end and after the lock-file .ldb has been removed a third batch file renames the working app the new app so the new one becomes the working app and when the user starts the front-end again from a shortcut on the desktop he/she is none the wiser that it's a newly update application they are starting.

In order to keep as few files as necessary in the working folder I arrange my front-end to recreate the batchfile before it is called. This means that if I need to alter the way the batchfiles operate I can do this within my front-end application.

Here is the VBA code I use to create a batchfile that renames the existing and new versions. Notice that there is a loop in it that waits for the .ldb file to disappear.

REMEMBER THIS IS THE VBA IN MY FRONT-END THAT CREATES THE BATCH FILE

''delete the file FileSwop.bat in the \Prog folder
If Dir("C:\Prog\FileSwop.bat") = "FileSwop.bat" Then
Kill ("C:\Prog\FileSwop.bat")
End If

''create the file
Dim fs As FileSystemObject, a
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:\Prog\FileSwop.bat", True)

a.Write "@echo off"
a.WriteLine
a.Write "echo fileswop.bat - is executing."
a.WriteLine
a.Write "echo Please Wait your files are being copied"
a.WriteLine
a.WriteLine
''copied from somewhere on the web this next bit of code minimises the command window - thanks whoever you are. Notice my use of Chr(34) to output the (") quotation mark.
a.Write "@echo off"
a.WriteLine
a.Write "if not " & Chr(34) & "%minimized%" & Chr(34) & "==" & Chr(34) & Chr(34) & " goto :minimized"
a.WriteLine
a.Write "set minimized=True"
a.WriteLine
a.Write "start /min cmd /C " & Chr(34) & "%~dpnx0" & Chr(34)
a.WriteLine
a.Write "goto :EOF"
a.WriteLine
a.Write ":minimized"
a.WriteLine
a.Write "Rem Anything after here will run in a minimized window"
a.WriteLine
a.WriteLine

'this bit creates a cycling delay
a.Write "@echo off"
a.WriteLine
a.Write "set /a counter=0"
a.WriteLine
a.WriteLine
a.Write ":numbers"
a.WriteLine
a.Write "set /a counter=%counter%+1"
a.WriteLine
a.Write "if %counter% ==2000 (goto :filetest) else (goto :numbers)"
a.WriteLine
a.WriteLine

'resume the count cycle

a.Write ":filetest"
a.WriteLine
a.Write "set /a counter=1"
a.WriteLine
a.WriteLine

'test for the presence of the .ldb lock-file and if it still exists
'loopback until it's no longer found. This ensures the front-end has
'closed.
a.Write "if exist C:\Prog\dbi2003.ldb (goto :numbers)"
a.WriteLine
a.WriteLine

'delete old2003.mdb if it exists - assumes that the previous old app backup is no longer needed

a.Write "if exist C:\Prog\old2003.mdb (del C:\Prog\old2003.mdb)"
a.WriteLine
a.WriteLine

'rename dbi*.mdb to old*.mdb - backup the current application just in case.

a.Write "if exist C:\Prog\dbi2003.mdb (ren C:\Prog\dbi2003.mdb old2003.mdb)"
a.WriteLine
a.WriteLine

'rename new*.mdb as dbi*.mdb - renames the new file making it the
'current working file. NOTE: re-starting the front-end before this
'process is complete can cause problems - or at the very least the
'front-end app may hang while starting until the copy process ends.

a.Write "if exist C:\Prog\new2003.mdb (ren C:\Prog\new2003.mdb dbi2003.mdb)"
a.WriteLine
a.WriteLine

'fetch another copy of the latest new*.mdb file from \Data to \Prog

a.Write "Xcopy " & Chr(34) & "\\fileserver\Users\Data\new2003.mdb" & Chr(34) & " " & Chr(34) & "C:\Prog\" & Chr(34) & " /v/q/y"
a.WriteLine
a.Close

'This is the end of the batch file creation. A new copy
'of FileSwop.bat now exists in the \Prog folder.

'call FileSwop.bat - use a Do/Loop to wait until FileSwop.bat is available.

Dim stAppName As String, isReady As Boolean, retVal
isReady = False
stAppName = "C:\Prog\FileSwop.bat"

Do Until isReady = True
If Dir(stAppName) = "FileSwop.bat" Then isReady = True
Loop

retVal = Shell(Environ$("COMSPEC") & " /c " & stAppName, vbHide)

'MsgBox retVal 'confirms fileswop.bat ran successfully

'now fileswop.bat is running this application can close.

CurrentUserName = ""
Application.Quit acQuitSaveNone

The resulting batch file: swopfile.bat

@echo off
echo fileswop.bat - is executing.
echo Please Wait your files are being copied
echo This should take no more than 2 minutes
@echo off
if not "%minimized%"=="" goto :minimized
set minimized=True
start /min cmd /C "%~dpnx0"
goto :EOF
:minimized
Rem Anything after here will run in a minimized window

@echo off
set /a counter=0

:numbers
set /a counter=%counter%+1
if %counter% ==2000 (goto :filetest) else (goto :numbers)

:filetest
set /a counter=1

if exist C:\Prog\dbiCare2003.ldb (goto :numbers)
if exist C:\Prog\old2003.mdb (del C:\Prog\old2003.mdb)
if exist C:\Prog\dbi2003.mdb (ren C:\Prog\dbi2003.mdb old2003.mdb)
if exist C:\Prog\new2003.mdb (ren C:\Prog\new2003.mdb dbi2003.mdb)
Xcopy "\\fileserver\Users\Data\new2003.mdb" "C:\Prog\" /v/q/y


Always fully qualify the path for the first use of a filename following any command. If you run the file by doble-clicking it directly it will process filenames without full paths but if you try to run it from within Access using Shell app nothing happens unless the full path is provided to each 'Ren', 'Del', 'Xcopy' and 'if exist' command.

Hope this helps somebody.
Regards
Rod

 

[Blorf]

I may be oversimplifying your problem but.... why not just change your users shortcuts so they point to a dos batch file that first copies your database, then launches it. That way you always know the user is opening the most current version all the time with out the need for any coding other than the dos file.

I build many apps for my company, and this is how I distribute and it has worked for a long time now.

My batch file looks for the Access lock file, and if it's there it tries to delete it. If it's still there after the delete attempt, then it's a fair bet that the user already has the app open, and the open command can be re-routed to just notify the user that the file is already open. This avoids the corruption of copying over an open file. Here is an example of one of my batch files should you go this route. The batch file works in Windows 7, 8 and 10 so no compatibility issues. It also puts the file in a sub directory with the current user and copies a few needed .dll files.

@echo off
SET STRING=%cd%
SET SUBSTRING=Desktop
ECHO %STRING% | FINDSTR /C:"%SUBSTRING%" >nul & IF ERRORLEVEL 1 (goto run) else (goto norun)

:run
c:
cdmd ccw
cd ccw
md %username%
cd %username%
if exist PO_CCW.laccdb del PO_CCW.laccdb
if exist PO_CCW.laccdb goto :message
copy \\kronos\Applications\Masters\PO_CCW.accdb
copy J:\PO_CCW\StrStorage.dll
copy J:\PO_CCW\dynapdf.dll

start PO_CCW.accdb
goto end
:message
msg %username% PO_CCW is already open
pause
goto :end

:norun
echo Please contact I.T. for correct location
pause
goto :end

:end

I hope this helps.

ChaZ



There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[strongm]

>MS have varied the Dos command structure

Partially becasue it hasn't been a DOS prompt since about 2002 ...

 

[Blorf]

Us old people call it a DOS prompt for the same reason we say Video Tape when we record videos on or phones. Command Shell, Digital Camera. We know what we mean.

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[tunsarod]

Thanks Chaz, that's an interesting idea.

I think that Dropbox provides a fantastic solution to getting an update from my computer at home to my client's offices but the deployment from his Dropbox folder to each of his user's machines has to run invisibly without affecting their use of the application. It's early days yet but I was struggling for a couple of months to pull together a process that worked reliably, which this seems to do, so I think I'll watch see how it goes before tweaking it any further.

However, I could use some help though. When these batch files are running a minimised command prompt is visible in the task bar and some users have noticed this. Can you tell me how (if its possible) to run batch files totally hidden with not even a minimised icon showing? I worry that a curious user might open the batchfile window and mess up a file transfer that's in progress.

Rod

Hey Strongm, As you can probably tell this return to using batch files is a visit to the distant past for me. Who says time travel is impossible? Why only yesterday I travelled 24 hours into the future. I think I might do it again.