.bat file to deploy database from server

[Leesha]

Hi,
It was suggested to me that I could write a .bat file to deploy an access database from a server to multiple workstations vs having to log into each one. Now this is an awesome idea but I've got no experience in writing .bat files. Has anyone done this ?

Thanks,
Leesha

[HansV]

That's not an Access question - if you don't receive a reply here soon I'll move this thread to a Windows forum. I'm sure our resident batch file expert John Gray will have a suggestion.

[Leesha]

Awesome! I wasn't sure where to put this one and I knew you'd move it if needed!
Thanks,
Leesha

[John Gray]

Hi,
It was suggested to me that I could write a .bat file to deploy an access database from a server to multiple workstations vs having to log into each one. Now this is an awesome idea but I've got no experience in writing .bat files. Has anyone done this ?

It all depends what you mean by "deploy"! Are you asking how to copy a .MDB file from a server to the C: drives of multiple workstations (which can be accessed by the administrative share \\pcname\c$ )?

If so, yes, it is pretty trivial - presuming that the workstations run XP. If it's Vista or Windows 7 there can be all sorts of permissions problems, depending on which is the target directory.

If you can supply more details, then I can have a bash...

It will end up being vaguely like:

Code: Select all

for %%a in (PC1 PC2 PC3 PC4) do copy \\server\sharename\sourcedir\Leesha.mdb \\%%a\C$\targetdir

but a bit more Stuff will need putting round it!

[Leesha]

Hi!

I'm so excited about the learning possiblities here! Here's the specifics/wish list:

1. The master copy of the database will be on the server named "headnurse"
2. The code would send it to the C Drive of the company computers. C:\RSM\Database - the database is in this folder
3. There are a variety of workstations and tablets that this would be sent to. Most run XP, but there are 2 with Vista and 2 with Windows 7 and I anticipate we'll gradually be going over to windows 7 over time.
4. Workstation/tablet names are things like "Roadwarrier1", "Roadwarrier2", "Roadwarrier3" etc. as well as other names.
5. If possible, an alert that comes up that there is new software on their computer after the code runs.

Potential issues:
Will it blow up if people are working in their databases and how to work around this.
Can it be scheduled to run using windows scheduler IE at night so that people aren't working in their databases.
Will it blow up if someone didn't leave their workstation on or just skip that workstation?

Thanks!
Leesha

[John Gray]

I'll work on it tomorrow, but the answers to your questions are:

1. If the database is open on someone's PC, then the copy to that PC will simply fail, and the next will be tried
2. Yes, it can be scheduled overnight
3. If the PC is not powered on, then the copy to that PC will fail

If you can get people to put the PCs into standby or hibernate, or you can arrange for them to be woken up by a Wake-on-LAN command from the server (an interesting task!), then they could be woken up a minute or so before the scheduled copying task runs.

In fact I have a scheduled task which backs up selected directories from the server to a TrueCrypt container on a specified and daily-changing PC overnight, but I use the Dell PC's BIOS power-on command to wake the PCs up from being powered off!

[Leesha]

I'm soooo excited. This is probably not even possible, but is there a way to do it backwards so to speak. I'm thinking of the laptop users. Is there a way to set the code so that the .bat file is on their computer and when they start it up it loads to look to see if there is a newer version on the server than on their computer and if there is it automatically installs. Both pieces of code would be useful. The one to deploy to the workstations overnight would be for one group of people and the other would be for the laptop users.

Leesha

[Jezza]

Leesha

You could try this:

REM This batch file copies the Access.mdb file from \\myServer\ server
REM ... once copied will start Access.mdb

COPY \\myServer\Access.mdb c:\myFolder\Access.mdb
"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "c:\myFolder\Access.mdb"

EXIT

[Leesha]

Hi Jerry! It's been a long time. I was thinking about you the other day when I saw that it was the 1 year birthday of the lounge since ou were the one that invited me on over from Woody's!!

My question on the above code, I don't actually need the access db to open, just replace the existing version.

Leesha

[Jezza]

Hi Leesha

I have been lurking a lot as I have been on project work for about a year so not as been as active as I would have liked to be. This piece of code is primarily just for the end user to drag it down themselves so it can be done like this:

Save this as copy.bat and place in \\myServer

COPY \\myServer\Access.mdb c:\myFolder\Access.mdb

To make it run at Start Up this can be run once by the user 9copy.lnk is a shortcut to the batch file on \\myServer

COPY \\myServer\copy.lnk" "%userprofile%\Start Menu\Programs\Startup"

Every time you want to get the users to have a new database you can add or remove copy.lnk from \\myServer.

I think that will do it but needs to be tested

[John Gray]

Prerequisites for my "don't rely on the user" solution to work are that each PC is PINGable from the server (which can mean that the PCs don't have a software firewall enabled), and that the administrative share C$ on each PC is accessible from the server.

Schematic:

Code: Select all

set up table of PC names
set up name of LOG file
for each PC name in table
  PING the PC so check that it's working
    log if PC fails the PING
  copy database from server to PC
    log success or otherwise of the copy, time-stamped
terminate BATch file
---
subroutine to obtain current data and time for the log records

How large is the Access database, and what version of Windows Server is running on the server?

[John Gray]

Here's my first bash at the code - pretty obviously there's only so much I can do to test it. There are a couple of complicated tricks in it, so inquire if there's anything you need to be explained.
I'm not entirely happy with just copying the new version of the database on top of the old on a PC without any form of backup, so you need to think about what recovery mechanism needs to be in place if a bad copy happens, or even the new database is flawed. Obviously we can do a rename on the PC for the old version of the database.

Anyway have a read through and let me know what you think!

Code: Select all

@echo off
title DBdeploy
echo +----------+
echo I DBdeploy I  deploy Access database from server to several PCs
echo +----------+

setlocal
::--------------------------------
:: TABLE of PC names and users for the copy
::   must have "::##" prefix followed by space, PCname, space, username
::## RoadWarrior1 Gladys
::## RoadWarrior2 Mabel
::## RoadWarrior3 Tarquin
::## RoadWarrior4 Roger
::## PCone        Leesha
::## PCtwo        Cynthia
::## PC30         John
::... and so on
::--------------------------------

:: set up some variables
set server=HeadNurse
set dbname=Leesha.mdb & :: of course this needs changing!  test using a dummy file?

:: set up the current date and time variables yy, mm, dd, hr, mn, stamp, currtime
call :datetime

:: set up a date-and-start-time-stamped log in the current directory/folder
set log=%~dpn0_%stamp%.log

:: obtain each PCname/username in turn, and process it
::        look for string  V--V  in this BATch file
set forcmds=findstr /i /b "::##" %~f0
for /f "tokens=1-2 delims=:# " %%a in ('%forcmds%') do (
  call :process %%a %%b
  )
:: inspect the log, assuming we're watching the BATch file run
start "" notepad %log%

:: that's it, folks!

endlocal
goto :eof

::----------------------------------------------------------------------------
:process  a single PCname and username
set pcname=%1
set username=%2

:: obtain current timestamp
call :datetime

if not defined pcname (
  echo %stamp%  ":process" called without any parameters
  echo %stamp%  ":process" called without any parameters >> %log%
  goto :eof
)

:: console message, in case anyone is watching
echo %stamp%  processing: %pcname% %username% ...

:: test whether this PC is active by PINGing it
::   look for "TTL=" as positive response
ping -n 1 %pcname% | findstr "TTL=" > NUL
if %errorlevel% gtr 0 (
  echo %stamp%  %pcname% used by %username% is not active >> %log%
  goto :eof
)

:: obtain timestamp for start of copy
call :datetime
set start=%currtime%

:: perform the database copy
set source=\\%server%\rest-of-path\%dbname%
set target=\\%pcname%\c$\RSM\Database
copy %source% %target%

:: save the errorlevel from copy
set el=%errorlevel%
  call :datetime
  set end=%currtime%
if %el% equ 0 (
  echo %stamp%  database copy for %pcname%, %username%, took from %start% to %end% >> %log%
  ) else (
  echo %stamp%  database copy for %pcname%, %username%, FAILED >> %log%
)
:: end of :process subroutine
goto :eof

::=============================================================================
:datetime
:: generalised DATE and TIME routine for all regions
::   %yy% will contain four digits, 20nn;
::   %mm%, %dd% and time variables %hr%, %mn%, %sc% and %th% contain two digits
::   %stamp% will be formed as %yy%%mm%%dd%_%hr%%mn%
if "%date%A" LSS "A" (set toks=1-3) else (set toks=2-4)
for /f "skip=1 tokens=2-4 delims=(-)" %%a in ('echo.^|date') do (
   for /f "tokens=%toks% delims=.-/ " %%e in ('date /t')     do (
      for /f "tokens=5-8 delims=:., " %%i in ('echo.^|time') do (
        (set %%a=%%e) & (set %%b=%%f) & (set %%c=%%g) & rem create yy, mm, dd
        (set hr=%%i)  & (set mn=%%j)  & (set sc=%%k)  & (set th=%%l)
        rem  note the variable names for Time: hr, mn, sc, th
)))
if 1%yy% LSS 200 (set yy=20%yy%) & :: ensure 4-digit years in %yy%
if 1%hr% LSS 20  (set hr=0%hr%)  & :: ensure 2-digit hours in %hr%
:: timestamp variable yyyymmdd_hrmn
set stamp=%yy%%mm%%dd%_%hr%%mn%
:: current time variable hr:mn:sc
set currtime=%hr%:%mn%:%sc%
goto :eof
::=============================================================================

[Leesha]

Hi,
Sorry for the late reply re DB size and server OS. I didn't get an email that there was a reply and have been busy digging out of 18 inches of snow! The skier in me loves it!
The OS is SB Server 2003 and the database size is 13kb. Each computer has a folder that is labeled as "old" in the RSM folder which is where I now manually copy the current DB before putting in the new version. This way they can always access the working perviously working version if there was an issue.
I can't wait to try the code!
Thanks,
Leesha

BTW, after reading the code, I see that there is a section for multiple users and one for a single user. I'm assuming I use one or the other with the datetime code that is at the bottom???

[HansV]

the database size is 13kb.

That is unlikely! Do you mean 13 MB?

[Leesha]

Hi Hans,
Frozen fingers/mind from shoveling.........12,800kb per my computer ---------I rounded up due to the DB still being added to/built, and left out the zeros.

BTW, this email came in almost immediately and yet I never got John's earlier one. I just saw it because I logged in to browse new posts for my learning curve. Is there some setting I need to check on my computer?
Thanks!
Leesha

[HansV]

Notifications can be a bit erratic - if you're awaiting a reply it's always best to check the forum from time to time.

[Leif]

BTW, this email came in almost immediately and yet I never got John's earlier one.

As far as I know, you will only get notification for the first 'new' post in a thread, not for every new post since your last visit. As Hans says, it is always best to check for new replies!

[John Gray]

BTW, after reading the code, I see that there is a section for multiple users and one for a single user. I'm assuming I use one or the other with the datetime code that is at the bottom???

I'm not quite sure what you mean by that - I'm simply listing all the PC names each with their 'usual user' (all dummy information) because it might assist in working out which machine has failed to accept the copy. The user name is just a comment field, and isn't necessary - of course we need all the PC names so as to copy to each!

If you are testing the BATch file, make sure you change the source and target settings to match reality, and I suggest you set up a tiny text file to copy as dbname instead of Leesha.mdb.

And I've also remembered that COPY does not give a non-zero errorlevel if it fails, whereas XCOPY does - so I'll have to work out the correct form of the copy statemment later...

[Leesha]

Hi John,
I was referring to this line of code..........I wasn't sure if I was supposed to only use this if deploying to one PC vs the group or if I was supposed to use the entire thing. Anyway, I verified that I could ping each of the computer that I'm testing and that was succesful. I changed the code in the areas that it looked like I was supposed to change the code - computer name and user. I verified that each computer had a the path C:\RSM\Database. I was able to watch the code run the cmd box. The consistent error was that the network path could not be found. I'm uploading the code and the error file so you can see if anything jumps out that I missed.
Thanks!
Leesha

[grovelli]

This is such a great thread
1) John's code packs a real punch but how does one know the meaning of all the commands? I've tried http://www.microsoft.com/resources/docu ... x?mfr=true" onclick="window.open(this.href);return false;
but I don't see all the commands John uses such as "Title" or "Stamp" or "findstr" or "start" or "set" and what the meaning of "%" is in as Set command or where the labels such as :process or :datetime the Call command is supposed to jump to are.
2) Is there any way to process each line in the batch file step by step like you can do in the VBA window so you see what's happening?