|HCS Consulting Group.|
|We make Complex Systems simple|
By Albert D. Kallal
Friday, April 27, 2007
Running Batch Jobs in in ms-access.
What ever happened to the punched card?
IBM announced on December 1, 1964, that round corner cards would be a regular feature on its general purpose punched cards. Round corner cards, announced a year earlier by IBM, accounted for more than two-thirds of the company's punched card volume (measured in the tens of billions a year) by 1965. Virtually all punched cards had been manufactured with square corners since they were first used in the 1890 census.
Wow, just like the next new great OS, round corners on punched cards was all the buzz in 1964.
Batch Jobs in Ms-access
The concept of running a nightly batch job harks back to the old days of punched cards and late nights in the computer room. While you're sleeping, some poor guy was loading up a hopper with punched cards and running batch jobs. I am temped to go off topic and ask what was being done in those batch jobs? (processing the days data that resulted from key punch machines is the answer by the way).
In a sad way, I never did use punched cards in a work environment. During my first year in computer science at University of Alberta was the same year they hauled out the punched card machines (that was in the 1980's). So, I did spend some of my extra time playing with those mechanical machines before they faded into history. My experience with punched cards is thus limited, but I did run some batch jobs on the mainframe, and I even learned about "control" cards for easy data entry on a keypunch machine.
Today cheap high speed computer processing on every desktop means most data processing work is done interactively. In place of batch jobs to do payroll calculations and print cheques, we now enter amounts into a form and click on a button. Payroll calculations are now done in a blink of an eye with desktops that have more processing power then what a mainframe had 15 years ago.
The result is that batch processing is not common these days.
However, considerable amounts of computer work is batch orientated. A daily report that runs off a list of customers who have over due bills, or printing out new orders for the shipping department at the end of the work day are candidates for batch jobs.
For some reason the transition to a graphical interface has resulted in a reduced, or shall I dare say "lost" art of batch files.
Lets put together a windows script that runs some code in an access application.
Windows lets you write old style (dos) .bat file, or you can use modern windows scripting.
I suggest that you use windows scripts.
Windows scripts let you launch applications and THEN tell that application to do things. The actual term is called automaton, and most windows applications support automaton. That means your script can control ms-access (or word, or whatever).
Windows scripts are happy to be run via the windows scheduler.
Not only can the script start ms-access and run code, but the end of the script it can shutdown ms-access. Thus, you can schedule a task, but not have to have m-access loaded and running all the time.
A basic windows script looks like the following
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\some path name\someMdb.mdb")
set accessApp = nothing
The above script will launch ms-access, and then run a subroutine called TimeUpDate in a standard code module.
What your code does in the standard sub called TimeUpdate is up to you. You can print reports, or do whatever you want.
To run the above script, simply cut and past the above into a text file in notepad. Then save this notepad document
(note that you need to have turned on windows file extensions to enable you to rename the file extensions).
Now, re-name the above text file you just created as
You notice how the icon for the text document changes.
The text document looks like:
And, when you rename the file to MyBatch.vbs, you notice
how the icon changes to:
At this point, just double click on the icon, and your script will run. Good for you, you just made your first script.
Note that you can have the above script run via the windows scheduler. Also, if your current ms-access application has a startup form, or startup scripts, then I would suggest that you create a new separate front end mdb that does NOTHING when you launch the mdb file. Use linked tables, and place reports, queries, and code you need to run via a batch file.
The above scripts can then be run by the windows task scheduler. You can find the scheduler in start->control panel. Switch to classic view and you find the scheduled task icon
Select the above, and then go file->new ->scheduled task.
Use the "browse" button in the above to select your new "vbs" script as we written above. Then simply fill out the rest of the options in the above and you now have a scheduled task for your nightly batch processing in ms-access.