How can I run a database update automatically as a batch process?

[M Skabialka]

I have adapted a Document Manager database to gather file names from a
certain server folder and all sub-folders. To update this information an
update button is pushed, and it then collects data on over 20 thousand
records looking for changes, additions and deletions in the file name, date,
size or path. However, users are adding to and deleting files in the
folders on a daily basis. Since it takes about an hour to run, I would like
to know how to automate the process of gathering file info, and set this
process to happen automatically, say once a night, or once a week on
weekends. Users are required to log off, not just lock their computer, at
the end of the day so one of them can't start it then log off.

Is there a way to run a Scheduled task on the server where the BE is stored,
and the files that are tracked, where the task is the same as pushing the
update button? I would also like it to compact the BE after it is done as
it gets really bloated. I also have a table in the BE which was extracted
from a SQL database. Can an Access 2003 table link to one in a SQL
database, or is there a way to delete the table and import the current SQL
table back into the BE?



I'm thinking the task has to open the BE and run an autoexec but haven't
tried this before. Is there a sample database out there that shows how this
is done?

Thanks,

Mich

 

[Albert D. Kallal]

If you have ms-access on the server, then you can most certainly "schedule"
the task to run.

Just create a windows script that call your code in your module.

I explain how here:
http://www.members.shaw.ca/AlbertKallal//BatchJobs/Index.html

 

[M Skabialka]

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\some path name\someMdb.mdb")
accessApp.Run "TimeUpDate"
accessApp.Quit
set accessApp = nothing

I guess this assumes Access is on the server, which it isn't, so will have
to run from a user machine. Can a vb script also be written to do the
compact and repair? These don't look like DOS commands, so how do you find
out the syntax for VB scripts to do this?