problem with windows scheduler running msaccess database processin

[Guest]

I have a database that I load up unix statistics into and generate reports
out of each day. We use a product called Visualizer from BMC that also
processes unix statistics in graphical form. this product has a seperate
scheduler called Automator that you can kick off executables from the same as
you can from the windows scheduler... for years I have had this database
running in access 97 kicking off from here with no problem. I am running on
a server on windows 2003. since converting my database to access 2000 I am
seeing strange behavior, where the task that is running my database will just
hang for no apparent reason partway through the vba code run.

The automator script is kicked off via a windows scheduled task, and then as
part of that script the msaccess database is started. the task runs in the
background most of the time as normally no one is logged onto the server when
it runs. it has an autoexec macro which executes vba code. As a workaround
I tried to run the database from its own scheduled task from inside windows
scheduler, which seemed to help for a few days but now it is doing it again.
I cannot figure out why this thing keeps hanging on some days and then
running on other days, we have not been able to correlate any events
happening on our server to explain this. has anyone heard of behavior like
this and of any cause that can be corrected?

 

[Albert D. Kallal]

I would consider writing a windows script.

eg:

dim accessApp

msgbox "Click ok to run batch job",64

set accessApp = createObject("Access.Application")

accessApp.OpenCurrentDataBase("C:\Documents and Settings\Albert\My
Documents\Access\ScriptExample\MultiSelect.mdb")

accessApp.Run "TimeUpDate"

accessApp.Quit

set accessApp = nothing

msgbox "Job complete", 64

Of course, you would remove the above msgbox commands...

the above would run a standard sub called TimeUpDate in a standard code
module.

Note that the script also quits ms-access.

To test/run the above script, simply create a empty notepad (mytext.txt) on
your desktop. Paste in the above code, and save it. Now, re-name the
extension from .txt to .vbs. You note the icon change. Double click on the
icon to run your code. It is this script that you should place into the
scheduler....

the above assume you get rid of any startup forms, or auto exec macros
etc....

 

[IRS Intern]

I would reccomend using the scheduler built into SQL Server

SQLAGENT _ROCKS_

stuff like 'email me if it fails' is super-easy to configure