asked on

Batch file to copy Access mdb from server share to desktop program files\subfolder

I would like to create a batch file to copy a file from the MS Server 2003 share to a group of users(distribution user group) on their desktop folder c:program files\Access subfolder (location of mdb file. I would like to write out a log file (can be a text file of when the copy took place for each of the users) with a time and date stamp. What is the best syntax for this batch file? Is it best to use robocopy or just copy? I would also like to check if the user has the file in the proper folder and if not then record yes or no in the log file.
Here is what I have so far.
IF EXIST C:\program files\directory\file.mdb roboCOPY s:\directory long name\subdirectory long file name\filename.mdb /copy:DAT

The plan is to put this batch file in the login script to kick off for a user if they are in the application user group.

Michael Pfister

See code snippet. Make sure users have write permissions to the target dirctory.
Also write permissions to s:\directory long name\subdirectory\log\ for the user is required to create the logs. If this is not possible you must log locally to the computer.

Hope it helps

IF NOT EXIST "%ProgramFiles%\directory\file.mdb"  echo file.mdb not found >>"s:\directory long name\subdirectory\log\copymdb_%USERNAME%.log" 
 
IF EXIST "%ProgramFiles%\directory\file.mdb"  roboCOPY "s:\directory long name\subdirectory" "%ProgramFiles%\directory" file.mdb /log+:"s:\directory long name\subdirectory\log\copymdb_%USERNAME%.log"

Open in new window

lbeham

ASKER

Should there be a line of code to create the log file? I will check but I think they have write access to these directories. The mdb directory and file already exists, I just need to update it with a newer version.

lbeham

ASKER

And - should the Access user distribution group be setup by user machine name or login name?

Michael Pfister

The log file will be created automatically. All log information will be appended, so delete the log files from time to time.
Create a "Security Group" and add the users you need. Then use ifmember.exe to check for the group membership in your script, see sample.

ifmember.exe is a tool from the Windoows Server 2003 Resource Kit http://www.microsoft.com/downloads/details.aspx?familyid=9d467a69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en

ifmember "AccessDatabase"
if errorlevel 1 Call :CopyDB
: rest of your login script goes here, if required
Goto :EOF
 
:CopyDB
IF NOT EXIST "%ProgramFiles%\directory\file.mdb"  echo file.mdb not found >>"s:\directory long name\subdirectory\log\copymdb_%USERNAME%.log" 
 
IF EXIST "%ProgramFiles%\directory\file.mdb"  roboCOPY "s:\directory long name\subdirectory" "%ProgramFiles%\directory" file.mdb /log+:"s:\directory long name\subdirectory\log\copymdb_%USERNAME%.log"  
Goto :EOF

Open in new window

AmazingTech

Download Windows Server 2003 Administration Tools Pack from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=c16ae515-c8f4-47ef-a1e4-a8dcbacff8e3&DisplayLang=en

Install it on your workstation so you can copy dsget.exe and dsquery.exe from C:\windows\system32 save it to:

s:\directory long name\subdirectory long file name\Tools

Create a group named Access MDB Users and add your users.

Copy the attached code to a batch file something like MDSCopy.bat

Add executing MDSCopy.bat in the logon script anytime after the S: is attached.

Copy of questions:

1. Don't you only want to update the MDB if the server version is newer then the local version?
I'm curious as to why it needs to be downgraded.
2. Did you want a separate log file? Per workstation? Per User?

Set MDBFilename=Filename.mdb
Set LocalMDBLocation=C:\Program Files\Access
Set ServerMDBLocation=s:\directory long name\subdirectory long file name
Set ServerToolsLocation=s:\directory long name\subdirectory long file name\Tools
Set MDBLogFileLocation=s:\directory long name\subdirectory long file name\Logs
Set MDBLogFilename=AccessDB.Log
 
"%ServerToolsLocation%\DSQUERY" USER -samid %username% | "%ServerToolsLocation%\DSGET" USER -memberof | find /i "CN=Access MDB Users,"
IF ERRORLEVEL 1 GOTO :EOF
 
IF EXIST "%LocalMDBLocation%\%MDBFilename%" (
    ECHO %DATE%,%TIME%,%USERNAME%,%COMPUTERNAME%,%MDBFilename% exist>>"%MDBLogFileLocation%\%MDBLogFilename%"
    XCOPY /y "%ServerMDBLocation%\%MDBFilename%" "%LocalMDBLocation%\%MDBFilename%" | find /i "%MDBFilename%"
    IF ERRORLEVEL 1 (
        ECHO %DATE%,%TIME%,%USERNAME%,%COMPUTERNAME%,%MDBFilename% was updated from "%ServerMDBLocation%">>"%MDBLogFileLocation%\%MDBLogFilename%"
    ) ELSE (
        ECHO %DATE%,%TIME%,%USERNAME%,%COMPUTERNAME%,%MDBFilename% was had an error when updating from "%ServerMDBLocation%">>"%MDBLogFileLocation%\%MDBLogFilename%"
    )
) ELSE (
    ECHO %DATE%,%TIME%,%USERNAME%,%COMPUTERNAME%,%MDBFilename% does not exist>>"%MDBLogFileLocation%\%MDBLogFilename%"
    XCOPY /y "%ServerMDBLocation%\%MDBFilename%" "%LocalMDBLocation%\%MDBFilename%" | find /i "%MDBFilename%"
    IF ERRORLEVEL 1 (
        ECHO %DATE%,%TIME%,%USERNAME%,%COMPUTERNAME%,%MDBFilename% was copied from "%ServerMDBLocation%">>"%MDBLogFileLocation%\%MDBLogFilename%"
    ) ELSE (
        ECHO %DATE%,%TIME%,%USERNAME%,%COMPUTERNAME%,%MDBFilename% was had an error when copying from "%ServerMDBLocation%">>"%MDBLogFileLocation%\%MDBLogFilename%"
    )
 
)

Open in new window

lbeham

ASKER

The users may go into the application to view or print reports which updates the date on the MDB file. So it may be a later date then the date of the file which has the revisions. I am working on this today. I will let you know how it goes. I may have more questions so I will leave this open for now.

lbeham

ASKER

I would like to have just one log file created on the s:\directory long name\subdirectory long file name\Logs so that I can check just one place to see if all the file copies took place. If not then I can check to see if that person is out of office on vacation or working off site.

lbeham

ASKER

Is there a way to add the date and time of the access file copy to the log file for each user?

ASKER CERTIFIED SOLUTION

AmazingTech

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

lbeham

ASKER

"%ServerToolsLocation%\DSGET" USER -memberof | find /i "CN=Access MDB Users,"
It is getting stuck on the above line. I created a group with just myself in the security group for testing. It does not write out to the log file yet - any ideas? I would be happy to post the actual file to an upload location by not viewable online.

AmazingTech

Hmm. Weird. Should work. Break down the commands.

"%ServerToolsLocation%\DSQUERY" USER -samid %username%

"%ServerToolsLocation%\DSQUERY" USER -samid %username% | "%ServerToolsLocation%\DSGET" USER -memberof

"%ServerToolsLocation%\DSQUERY" USER -samid %username% | "%ServerToolsLocation%\DSGET" USER -memberof | find /i "CN=Access MDB Users,"

lbeham

ASKER

find /i "CN=Access MDB Users,"
I did as you suggested above. It is getting stuck on the above line. I checked the group and the name is spelled correctly. Every other line worked fine.