Access won't open from batch file when scheduled

[css]

This may not be the correct community to post, so please let me know if I
should post elsewhere.

We're running Server 2003 & I'm having issues with Access starting from a
batch file.

The batch file executes daily. It copies the front end to the Scheduled
directory, then executes access & runs a macro that calls a vba function. I
have both the batch file & the vba function writing to a log file so I know
what's been going on overnight. Most days all runs fine. However, some times
the batch file just hangs. When I check the server in the morning, Scheduled
Tasks says the daily batch is running. Task Manager shows MSAccess.exe is
running, although it's not open & running on the screen. I stop the scheduled
task, end the process tree for MSAccess, then can restart the scheduled task
and all is fine.

Here's the code from the batch file:

rem log copy
echo OnRentFE.mdb copy has started %Date% %Time% >> "D:\Shared
Folders\MSAccess\Scheduled\results.log"

rem copy
xcopy "D:\Shared Folders\MSAccess\FE\Invtry\OnRentFE.mdb" "D:\Shared
Folders\MSAccess\Scheduled\OnRentFE.mdb" /y

rem log error
echo OnRentFE.mdb copy error level was %errorlevel% %Date% %Time% >>
"D:\Shared Folders\MSAccess\Scheduled\results.log"

rem log Access start
echo Items Returned report has started %Date% %Time% >> "D:\Shared
Folders\MSAccess\Scheduled\results.log"

rem start db
"c:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "D:\Shared
Folders\MSAccess\Scheduled\OnRentFE.mdb" /wrkgrp "D:\Shared
Folders\MSAccess\VRwrkgrp.mdw" /user Schedule /pwd schedule /X YestOR

rem log Access done
echo Items Returned report error level was %errorlevel% %Date% %Time% >>
"D:\Shared Folders\MSAccess\Scheduled\results.log"

My log file will show the copy started & completed with an error 0. Also it
will show that the Items Returned report has started.

The YestOR macro calls a public function. The first thing the function does
is write a line to the batch file also. This doesn't happen. Here's the code:

Open "D:\Shared Folders\MSAccess\Scheduled\results.log" For Append As #1
'open results.log
str = " OnRentFE.mdb - Start Items Returned report - pull data " & Now
Print #1, str
Close #1

Again, it works fine when I log on to the server & tell Scheduler to run it.
But not overnight. I try to make sure I log the administrator off the server,
but sometimes it'll just time out & lock the server. Originally, the code was
set using mapped drive s: instead of c: & d:. I thought it might have to do
with using the mapped drive, but I just don't know where else to look.

Thanks,
Connie

 

[Tom van Stiphout]

On Wed, 16 Apr 2008 04:41:00 -0700, css

Correct forum.
I don't see much wrong with your code. Nice debugging steps.

Check if the AutoExec macro might interfere with YestOR. I forgot
which one runs first, but I know both will run.

Open ... For Append As #1:
technically that should be:
dim intFileNum as Integer
intFileNum=FreeFile
Open ... For Append As intFileNum
but I don't expect that will make a big difference.

This caught my eye: "I try to make sure I log the administrator off
the server". I think that's a mistake. You can lock the console if you
want (Alt+Ctrl+Del - Lock Computer), but I would run it while a user
is logged in. Access is not designed to run as a service.

-Tom.

 

[css]

Tom:

Thanks for the fast reply!

There is no AutoExec macro for this database. I will try locking the server
instead of logging off. Force of habit to log off. I wanted to make sure that
if someone else was on the server while I was out of town, that either would
work.

Thanks again,
Connie

 

[Fred]

I know less than you do. But whenever my stuff like the doesn't run, it's
usually permission problems.