Schedule Export Event in Access 97/2k 1

[Frazzled1]

Each night (at a specified time) I need to run some code or start a macro to export data to a file. How can I schedule a task with no user intervention that will run in the background? This need to be a fully automated process. I expect the Access will need to remain open 24/7.

 

[dougcranston]

Frazzled1,

Ok, before I try to answer your question, a couple of assumptions, and maybe a question.

Assumption:
Running NT or >
Have WSH installed.
IE5 or >
Have task scheduler installed or AT
System is up and running.

Question:
Is this running on a server or a users workstation?
Export to what type of file? CSV, Access, Word, Excel?

A solution:
Timed Trigger - Use one of these.
Use either AT or Task Scheduler to set the date and time to execute the job.

What to use to build the event..code.
Use WSH and VBScript to create a script to complete the functions you want to get done. Combine it with ADO and you really can do some powerful things.

This is a very specialized nitch area, that of MS Office automation using VBscript/WSH.

Some reading, please see:
ADO to Query Data from a Closed Excel Workbook

http://www.beyondtechnology.com/geeks023.shtml

Excel Objects

http://www.microsoft.com/officedev/articles/Opg/004/004.htm

OLE Automation in Javascript

http://www.webreference.com/js/column55/index.html

Creating Excel Sheet codewise

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_10265721.html

sing Excel As a Reporting Engine

http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=2

Devguru reference for WSH, VBScript (including Filesystemobject used to create and read text files and alot more) and Javascript.

http://www.devguru.com

Hope this helps get you started.
DougCranston

 

[dougcranston]

Frazzled1,

Although the following is based on a snippet of one of my Access scheduled jobs, this might help you visualize a solution/process. Always different and sometimes better ways, but this is my meager solution approach.

sample.cmd This is the job that actually is called by AT or Task Scheduler.
==================

@echo off
rem ''''''''''''''''''''''''''''''''''''''''''''''
rem ProgramName: sample.cmd
rem Written by: Douglas Cranston
rem Purpose: Test for the existence of
rem a new sample.xls file for sending
rem to the selected personnel
rem Run sampleit.vbs script
rem Cron: Set up in AT to run on the
rem 1st working day of new month
rem ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

rem
rem Test if the file exists and if so jump to
rem run the necessary commands, else quit
rem
rem
c:
cd\cmdfiles\wrk
rem
if exist sample.xls del sample.xls
rem
cd\cmdfiles
rem
rem since old xls is gone run prgm
rem to create new version.
rem

"c:\program files\microsoft office\office\msaccess.exe" e:\database\sample.mdb sampleextrct
rem sampleextrct is your Access or Excel macro, in my case creates a report in Excel format

rem
rem test to see if .xls exists and if so
rem jump to prcess goto and run from there
rem else fail
rem
cd\cmdfiles\wrk
if exist sample.xls goto prcess
rem
rem Fall out and stop if file does not exist
rem
rem Notify Admin that the file was missing
rem
c:
cd\cmdfiles
call sampleno.vbs
echo date /t >> c:\cmdfiles\sample.log
echo time /t >> c:\cmdfiles\sample.log
echo sampleno >> c:\cmdfiles\sample.log
exit
rem
rem Label for the goto that when file exists
rem it jumps to this part of the program and
rem executes the remaining commands.
rem
rcess
rem
rem Notify Admin that the program successfully ran
rem
c:
cd\cmdfiles
call sampleyes.vbs
echo date /t >> c:\cmdfiles\sample.log
echo time /t >> c:\cmdfiles\sample.log
echo sampleyes >> c:\cmdfiles\sample.log


sampleno.vbs
================
Dim MyBody
Dim MyCDONTSMail

set MyCDONTSMail = CreateObject("CDONTS.NewMail&quot
MyCDONTSMail.From = "yourmail@server.net"
MyCDONTSMail.To = "administrator@server.net"
MyCDONTSMail.Subject = "Sample FAILED"
MyBody="Update was not successful." & VbCrLf
MyCDONTSMail.Body=MyBody
MyCDONTSMail.Send
set MyCDONTSMail=nothing

sampleyes.vbs
==================
Dim MyBody
Dim MyCDONTSMail

set MyCDONTSMail = CreateObject("CDONTS.NewMail&quot
MyCDONTSMail.From = "adminstrator@server.net"
MyCDONTSMail.To = "sampleDIST@list.server.net"
MyCDONTSMail.Cc = "backupadmin@server.net"
MyCDONTSMail.Subject = "Sample Update per Standing Request"
MyCDONTSMail.AttachFile("c:\cmdfiles\wrk\lavoz.xls&quot
MyBody = "Attached is the monthly listing " & VbCrLf
MyBody = MyBody & " in Excel format. Any questions, contact" & VbCrLf
MyBody = MyBody & "Adminstrator at 913-555-5555" & VbCrLf
MyCDONTSMail.Body=MyBody
MyCDONTSMail.Send
set MyCDONTSMail = nothing

 

[Frazzled1]

To answer Dougcranston's questions:
The environment is Win 2k sp3 workstation, the output/input files will be fixed width txt files. A separate process will take care of the file transfer, my problem is how to schedule the process in Access. Since I can't find a scheduler in Access I propose to use the win 2k scheduler (as suggested) to start a separate database with the necessary tables attached from the main system. On open, the access autoexec will kick off a series of macros and code to produce the required files. Do you see any problems with this process?
Thanks for your comments and quick response Doug!!

 

[dougcranston]

Frazzled1,

Looks like that should work.

Only concern might be conflicts with the DB open potentially by other users when the script triggers.

Assuming you will be running this at a time of day the users are not likely to be using the DB and the server/system will not be overly taxed doing other things.. Using the Task Scheduler, you can set it up to login in with a users ID and Password with appropriate rights to run the script and avoid conflicts with the systems security of NT.

Good luck.
DougCranston

 

[mp9]

Frazzled1 - if you're still looking for a Access scheduler try the Handy Access Launcher - it's freeware and is available from

http://www22.brinkster.com/accessory/resources/001.shtml

or

http://www.geocities.com/waddly/accory.html

HAL let's you schedule compacts, repairs and macro runs so you can write a macro (or a function that is then called by a macro) and then use HAL to schedule/automate running that macro.

Hope this helps.