Hey, Scripting Guy! How Can I Be Notified if an Access Database File Exceeds a Specific Size?

Hey, Scripting Guy! How Can I Be Notified if an Access Database File Exceeds a Specific Size?

  • Comments 1
  • Likes
Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I trigger an alert if an Access database file exceeds a specific size?

-- MA

SpacerHey, Scripting Guy! AnswerScript Center

Hey, MA. You know just the other day Microsoft.com posted 100 Reasons You'll Be Speechless, a Web page listing 100 reasons why “Seeing Windows Vista for the first time may leave you searching for words.”

Note. The Scripting Guys’ favorite reason? Number 7: You can freeze time! That sounded really cool, although we couldn’t find any information explaining how Windows Vista could then be used to unfreeze time. We also liked reason number 13: Because you need to know who the bad guys are. But then we noticed that they put a picture of the Scripting Guys right next to that statement. Hey, come on, we’re not bad guys, we’re – well, never mind.

At any rate, this was such a good idea that we decided we would stea – uh, we decided we would … emulate … it. With that in mind, here are 100 Reasons You’ll Be Speechless after reading the Hey, Scripting Guy! column:

1.

You can freeze time!

2.

Hey, Scripting Guy! is your exclusive Internet source for amusing anecdotes about the Scripting Son and his baseball career.

3.

Other writers take potshots at their editors and those editors simply edit out those caustic remarks. Our very own Scripting Editor doesn’t realize she can do that! (We told her the Delete key on her keyboard was broken, and she fell for it.)

4.

Uh, let’s see here …. did we mention that you can freeze time? Oh, right; we did.

5.

OK then … let’s see: freezing time, Scripting Son, Scripting Editor …. Hmmm ….

Oh, my: would you look at the time? Guess we better skip reasons 6 through 99 and just cut right to reason number 100:

100. You can find a script that will trigger an alert if a Microsoft Access database file exceeds a specific size.

In other words, you can find this script:

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
    ("SELECT * FROM __InstanceModificationEvent WITHIN 30 WHERE " _
        & "TargetInstance ISA 'CIM_DataFile' and " _
            & "TargetInstance.Name='C:\\Scripts\\Test.mdb'")

Do
    Set objLatestEvent = colMonitoredEvents.NextEvent
    If objLatestEvent.TargetInstance.FileSize > 204800 Then
        Wscript.Echo "The Access database file has exceeded its allowed size."
    End If
Loop

OK, let’s see if we can figure out how this baby works. (Reason 74: You can never be sure whether the Scripting Guy who writes this column actually knows what he’s doing.) As you can see, we start out by connecting to the WMI service on the local computer. Is it possible to get this script to work against a remote computer? You bet it is, which is good: after all, a script like this would be, at best, only marginally useful if it could work against only the local machine. (Reason 16: The Scripting Guy who writes this column knows more about being marginally useful than any person alive.) You want to run this script against a remote computer? Then simply assign the name of that remote computer to the variable strComputer:

strComputer = "atl-dbs-01"

That brings us to this panic-inducing line of code:

Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
    ("SELECT * FROM __InstanceModificationEvent WITHIN 30 WHERE " _
        & "TargetInstance ISA 'CIM_DataFile' and " _
            & "TargetInstance.Name='C:\\Scripts\\Test.mdb'")

To begin with, don’t panic: this is nothing more than the line of code that enables us to “subscribe” to a specified WMI event. And what event are we subscribing to? Well, we want to be notified any time an new instance of the__InstanceModificationEvent class is created; as you might have guessed, a new instance of this class is created any time a WMI object (be it a process, a service, a disk drive, or, yes, a file) is modified in any way. Of course, we don’t want to be notified every time something gets modified on the computer; that could result in receiving scores of alerts every second. Instead, we want to be notified only if the TargetInstance (that is, the new instance of the __InstanceModificationEvent class) meets the following criteria:

The TargetInstance is a member of the CIM_DataFile class (which simply means that this is a file as opposed to a folder, a process, a service, or anything else.)

The TargetInstance has a Name equal to C:\\Scripts\\Test.mdb. The Name property, in case you’re wondering, is equivalent to the file path. Of course, if that’s the case, then shouldn’t the Name be equal to C:\Scripts\Test.mdb? Well, technically, that is what it’s equal to. However, any time you reference a value in a Where clause that includes the \ character that character must be “escaped;” that simply means it must be prefaced with a second \ character. That’s how C:\Scripts\Test.mdb ends up looking like this: C:\\Scripts\\Test.mdb.

Oh, and in case you’re wondering, Test.mdb just happens to be the Access database file we’re keeping a watchful eye on.

Note. Good observation: what is the WITHIN 30 portion of our query used for? Well, that simply tells the script to check every 30 seconds to see whether or not the database file has been modified. (That is, whether or not a new instance of the __InstanceModificationEvent class, an instance meeting the prescribed criteria, has been created.) There’s nothing magical about the value 30; that simply means that once the file has been modified we’ll receive a notification within the next 30 seconds. That might be too long of a lag time, or it might be too short of a lag time. In either case, just change the 30 to the desired number of seconds. (Reason 33: Hey, Scripting Guy! includes a link to a Webcast that explains WMI events and the WMI event terminology in detail.)

After we subscribe to the WMI event service we next set up a Do loop designed to run, well, forever. (Notice that there’s no exit criteria of any kind.) Inside that loop, we use this line of code to instruct the script to sit patiently and wait for the next instance modification event (or at least the specific such event we subscribed to) to occur:

Set objLatestEvent = colMonitoredEvents.NextEvent

What if no such event ever occurs? No problem; like we said, the script will sit here forever if need be. (Reason 56: Some Scripting Guy scripts just sit there and do nothing. Just like the Scripting Guys themselves.) As soon as a new instance modification event does occur, however, we use this line of code to check to see if the FileSize property is larger than 204,800 bytes:

If objLatestEvent.TargetInstance.FileSize > 204800 Then

Again, there’s nothing special about the value 204,800; we had to pick a target file size and so we picked this one. In case you’re wondering, 204,800 bytes is equal to 200 kilobytes: 200 * 1024. (Reason 29: We do the math so that you don’t have to ... Well, unless it’s algebra. Then you’re on your own.)

And what if the file size does exceed 204,800 bytes? In that case, our sample script simply echoes back a message to that effect:

Wscript.Echo "The Access database file has exceeded its allowed size."

And then we loop around and wait for the next such event to occur.

Just a few quick notes before we go. To begin with, this script works best when run in the command window under the CScript scrip host; that way you don’t have to deal with message boxes popping up, and you can terminate the script simply by closing that instance of the command window. In addition, you can easily modify this script to do something a little fancier than simply echoing back a message; for example, the script could write an event to the event log or send an email using SMTP mail. That’s entirely up to you. (Reason 91: We let our readers do most of the work.)

By the way, MA, we know that you’re using Microsoft Operations Manager (MOM). Hopefully you can take the code we’ve shown you today and modify it as needed to get it to trigger a MOM alert. If you run into any problems, drop us a line and we’ll see if we can find someone who can help you. We’d help you ourselves, but if there’s one thing that the Scripting Guys know very little about (and there definitely is at least one thing we know very little about) it’s MOM.

At any rate, we hope that helps, and we hope this gives people reason to continue reading Hey, Scripting Guy! each and every day. And if that isn’t reason enough, well, don’t worry: we’ll be bringing you plenty of useful information in the coming months. For example, we still haven’t told anyone the story of Scripting Guy Dean Tsaltas and the Killer Cows of Oxford, England. Peter Costantini, the oldest living Scripting Guy, continues to defy the laws of science and nature by getting even older. And, hey, the next high school baseball season is just around the corner. It should be great year, huh?

Note. The Scripting Editor suggested that we add, “Plus we will have a lot more useful scripting information to bring you.” We’re not sure what difference that makes to anyone, but what the heck: yes, we’ll also have a lot more useful scripting information to bring you. Not to mention the Scripting Guys going to Barcelona, the upcoming Turducken Bowl, and much, much more.

Like, say, freezing time.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment