Automate and schedule Microsoft Access macros

If you are running various Microsoft Access databases (haven't quite gotten to SQL yet...) which need to be updated, it's usually nice to be able to automate the updates. While there is no built-in scheduler within Access, you can call macros from the command line, which then means you can script macros through the use of a batch file.

1. Create a new text file with an extension of .bat. You can use save as, or unhide extensions to use the .bat extension

2. In that file, put the full path to the Access executable surrounded by quotes, which will be

Office 2007 - C:\Program Files\Microsoft Office\Office12\MSACCESS.exe

Office 2003 - C:\Program Files\Microsoft Office\Office11\MSACCESS.exe

depending on the version of Access you're using.

3. Now put a space, then add the full path to the Access file you want to automate surrounded by quotes, including the file name. For example, "C:\Access Files\MyDatabase.mdb"

4. Now add a space to the end of that, and add the /x switch, followed by a space, then put the macro name (no quotes needed).

5. Save your .bat file, making sure it stays in .bat format.

6. Go to Start->All Programs->Accessories->System Tools->Scheduled Tasks, and create a new task. When asked what you want to run, browse to your .bat file and select that. Choose the rest of the options according to whatever schedule you want to put the macro updater on

In the end you should have a single line in your batch script that looks something like

"C:\Program Files\Microsoft Office\Office11\MSACCESS.exe" "C:\Access Files\MyDatabase.mdb" /x AutoMacroName

That's it. You now have a fully automated macro on a schedule. There used to be a point in time where we had a user who would start a macro when they arrived in the morning, and then sometime in the afternoon they'd be able to use the updated database. That's very inefficient, and can be a huge waste of time. This automation process can be a great time saver and allow you to update information after-hours, rather than making someone do it manually and wait for the process to finish.

The next thing you might want to look into is adding an email notification to your automated process so you know whether or not it finished. To do that, check out my other post about adding email notifications to ntbackup. The process behind both are practically the same. If you're interesting in more scripting, do a quick search and you should find a few more articles on the site too.

For more command-line switches for Microsoft Access, check out KB 209207

lpadmin error when trying to print in OS X 10.5

This is a known issue in OS X 10.5.7, and Apple has a support article with the supposed workaround. Trying to login as the lpadmin fails, and I'm sure if you found this article you're frustrated with trying to figure out which password you should be using. The trick is to upgrade the lpadmin group to support GUID membership. To do that, you can use a terminal command while logged in as a local administrator.

1. Go to Applications->Utilities->Terminal

2. Enter the command dseditgroup -o edit -p -a admin -t group _lpadmin and then press Enter

3. You should get a response saying _lpadmin has been upgraded to support GUID membership.

Try printing now and it should be fine. To see the Apple support page, check article TS2754

PS - I have seen this only act as a temporary fix on a few Macs that I have setup to connect to Active Directory. I'm not sure why, so if you run across this and can explain why this works for some AD Macs but not all (as a permanent fix), please leave a comment. Thanks!

Check which process is using which port in Windows

Ports are used all the time. Some are standardized (25 for SMTP), and others are dynamic. I ran into an issue setting up a TFTP server yesterday where it said it couldn't start because another process was already using the port, even though I couldn't think of any programs I had running that would have had it in use. I had to investigate, and being that it is simple, I thought I'd share the process. Also, matching ports to processes can help catch or track down spyware/malware, which typically uses some type of Internet connection.

1. Open a command prompt. You can do this by going to Start->Run, typing "cmd", and pressing OK

2. Use the command "netstat -a -n -o". This will list all active ports, along with the process id of the process that is using it. Find the port you want to check, then look at the PID listed for using that port. Remember the PID

3. Now use command "tasklist /FI "pid eq yourPID"", making sure to surround "pid eq yourPID" in quotes. This will filter (/FI) your tasklist results, and only show programs using the PID equal to yourPID. Make sure to substitute the numerical PID from step 2 in for the yourPID text in the command.

4. If you'd like to stop the process that you found, use the command "tskill yourPID" to terminate the process, remembering to substitute your process ID value in for the yourPID text.

You can also use Task Manager to track down the process once you have the PID if you would like. However, the PID column on the Processes tab is not displayed by default, so you will have to add that (go to the Processes tab, then to View->Select Columns...). You can then check and end the process with the more familiar GUI interface.

Tasklist is a very helpful tool. One additional use for tasklist is for tracking down programs that are using svchost.exe. If you're interested in that, check out my previous post.

Enable LACP on an HP Procurve switch

First off, make sure your switch supports LACP. Not all of them do. If yours doesn't, or at least doesn't out of the box, check to see if there are any firmware updates on HP's site that enable LACP support, which you can find at http://www.hp.com/rnd/software/switches.htm. You also want to make sure that the device you're connecting to the switch supports LACP too, but hopefully you've already done that since you're looking how to set it up.

Ok, to enable LACP (link aggregation control protocol) is pretty easy. You have to have admin access to your switch, and also access to the CLI (command line interface) which you can get through with a simple telnet session to your switch's IP address. There is dynamic LACP, which is more of an automated configuration, but it limits your LACP link to running in the default VLAN only. I believe you can just plug the Ethernet cables into your switch and it will automatically recognize LACP is setup, as long as it is already configured on the device you are connecting. If you have multiple VLANs and need LACP to run in a VLAN other than the default, the only option is to manually set up static LACP, which is what this post is going to cover.

1. Login to the CLI on your switch, which you can do by opening a telnet session to the switch's IP address

2. Enter the username and password when prompted

3. Use the command "config t" to get into the configuration terminal. You'll know this worked when you see (config) appended to the end of your device name.

4. Use the following command to create your LACP trunk of ports: "trunk 1-4 trk1 lacp" then press Enter. This will create a new LACP trunk using ports 1, 2, 3, and 4, and name it trk1. For trk1, you can use anything from trk1-trkX, where X is the last port number on your switch. You can substitute whichever ports you want to use to create your LACP trunk. If you just want to use trunking rather than LACP, simply replace LACP at the end with the word trunk

5. Use the command "show lacp" to check the settings. You should see columns for "LACP enabled", "LACP Partner", and "LACP status". Assuming you already have the device configured for LACP and plugged into your new LACP ports, those three columns should read "Active", "Yes", and "Success" respectively. If the first column says "Passive", go to step 5b. Otherwise skip right to step 6.

5b. Enable Active LACP mode on your trunk ports. To do this, make sure you're in config mode and then use the command "interface 1-4 lacp active", substituting the port numbers you used in your trunk in the command. The command above changes ports 1, 2, 3, and 4 to active LACP mode

6. Now you have to put your new LACP trunk into the correct VLAN. Let's say I want this trunk to be in VLAN 2. To do that, I'll use the command "vlan 2 untag trk1". Substitute the VLAN number and trunk number of the actual trunks you are going to be using

7. That's it. You should now have yourself a static LACP trunk on your HP Procurve switch, and it should be in the correct VLAN for you to start using.

If you want to remove a port from your trunk at some point, you can use "no trunk 1" which would remove port 1 from the trunk. You can use port ranges too, so "no trunk 1-4" would completely remove the trunk that was setup in the example.

For more information, check out the pdf instructions from HP here. To see the original post that pointed me in the correct direction, click here.