Originally posted by Tiana Antul:
Happy Friday to everyone!
It was wonderful to see such a great turnout to the Watertown meeting this month. A huge thank you to Lloyd Burke for what I thought was an outstanding presentation.
How outstanding was it? I went back to work and toiled and tried and erred and today I got my first batch file to run properly! He was right… it’s so easy a monkey could do it.
Once you’ve written your first successful batch file and gotten over the little bumps that you’re likely to run into along the way, you’re going to want to automate everything you do on a regular basis. I thought I’d share my experience and some of the mistakes I made (including one rather LARGE rookie one) to spare others the same headaches.
I started with a new report I was recently asked to provide to the Department of Public Health. They are analyzing trends in opiate use and were interested in getting weekly reports on all overdose incidents reported to our Police Department.
I started with a relatively simple select query to pull the data. Knowing that I intend to create a scheduled task that will run this report every Sunday for the previous week, I set the date parameters to “Between Date()-7 and Date()-1 + #11:59:59#”. I have to add the #11:59:59# because in our RMS the date field is a combined date and time. This ensures that I’m capturing everything that was reported from the previous Sunday all the way through midnight on following Saturday.
Next step? Macro time! I created a macro that would run the above-mentioned query and export the results to my local drive in Excel file format.
For the intents of creating a batch file that you will run by using Task Scheduler, you will want this Macro to automatically execute when Access is opened. There are two ways to do this (that I know of), but the easiest way is to simply name the Macro “AutoExec”. The name will bold face and the macro will automatically run whenever you open the database.
Lesson Learned #1: If you use this database for any other work, DO NOT use an AutoExec Macro to shut down Access when it finishes running. Rather, have the batch file shut down Access. I made this mistake and nearly had a panic attack the next time I went to open my database and it immediately closed on me because of this epic mistake. There’s really no way to delete or edit a macro in a database without being able to open the database and keep it open (not that I’m aware of anyway). Thank goodness for computer backups (I guess those IT people we complain about all the time actually do come in handy now and then! Phew!).
Okay, step 3. Write your batch file. For our intents and purposes, these really truly are easy to write. I promise you. I ran into one glitch where I was asking the batch to delete a file on my PC before running my macro so that Access could write over the existing file without having to ask an end user if it was okay. It took me a while, but I figured it out, and this is lesson #2:
Lesson Learned #2: Make sure that all your files are located in the same directory. This makes the batch file happy.smiley I keep my database, the notepad text file, batch file, and the Excel file that gets exported from Access, all in the same folder on my local drive.
The next problem I ran into was the batch file not being able to locate folders and files that I was trying to point to by using a directory. Solution?
Lesson Learned #3: When pointing to a file or folder in batch script, make sure that there are no spaces in the folder or file names. I always knew this to be good programming practice, but it honestly never caused me any issues until now, and apparently I got sloppy in my naming practices. Once I removed the spaces and tried running the batch file again, everything worked like a charm. And this stuff runs fast!
The next step was to create the scheduled task. I couldn’t see my task scheduler when I opened up my control panel so I had to do a search for it. Scheduling a task is kind of fool proof since you’re walked through every step. I told my computer to run the batch file every Sunday at noon. There was even an option for the task to run whether I was logged in or not. (This coming Monday I’ll find out if it ran as expected). With any luck the report will sitting on my computer waiting for me to email it off. If not, then I know the issue is with the scheduled task and I will trouble shoot from there.
The next and final step is to look for a way to include script in the batch file that will email this report automatically. My initial research points to SMTP, but most solutions involve using a 3rd party program, which isn’t an option for me because I don’t have Admin privileges and I can’t download software on my work PC.
So this is what I’ve done a la the Great and Powerful Lloyd Burke, and my missteps which hopefully help you to avoid the same mistakes that I made. Feel free to call or email me if you want help doing this. I can’t guarantee to know the answer but there’s no problem we can’t figure out a solution to. Please share your experiences too! This is great stuff and Lloyd is leading the automation revolution.
