{"id":63723,"date":"2007-10-25T03:03:00","date_gmt":"2007-10-25T03:03:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/10\/25\/hey-scripting-guy-how-can-i-be-notified-if-an-access-database-file-exceeds-a-specific-size\/"},"modified":"2007-10-25T03:03:00","modified_gmt":"2007-10-25T03:03:00","slug":"hey-scripting-guy-how-can-i-be-notified-if-an-access-database-file-exceeds-a-specific-size","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-be-notified-if-an-access-database-file-exceeds-a-specific-size\/","title":{"rendered":"Hey, Scripting Guy! How Can I Be Notified if an Access Database File Exceeds a Specific Size?"},"content":{"rendered":"

\"Hey, \n

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

— MA<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, MA. You know just the other day Microsoft.com posted 100 Reasons You’ll Be Speechless<\/B><\/A>, a Web page listing 100 reasons why \u201cSeeing Windows Vista for the first time may leave you searching for words.\u201d <\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. The Scripting Guys\u2019 favorite reason? Number 7: You can freeze time! That sounded really cool, although we couldn\u2019t find any information explaining how Windows Vista could then be used to unfreeze<\/I> time. We also liked reason number 13: Because you need to know who the bad guys are.<\/B> But then we noticed that they put a picture of the Scripting Guys right next to that statement. Hey, come on, we\u2019re not bad guys, we\u2019re \u2013 well, never mind.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

At any rate, this was such a good idea that we decided we would stea \u2013 uh, we decided we would \u2026 emulate \u2026 it. With that in mind, here are 100 Reasons You\u2019ll Be Speechless after reading the Hey, Scripting Guy!<\/I> column:<\/P>\n\n\n\n\n\n\n\n
\n

1.<\/P><\/TD>\n

\n

You can freeze time!<\/P><\/TD><\/TR>\n

\n

2.<\/P><\/TD>\n

\n

Hey, Scripting Guy! is your exclusive Internet source for amusing anecdotes about the Scripting Son and his baseball career.<\/P><\/TD><\/TR>\n

\n

3.<\/P><\/TD>\n

\n

Other writers take potshots at their editors and those editors simply edit out those caustic remarks. Our very own Scripting Editor doesn\u2019t realize she can do that! (We told her the Delete key on her keyboard was broken, and she fell for it.)<\/P><\/TD><\/TR>\n

\n

4.<\/P><\/TD>\n

\n

Uh, let\u2019s see here \u2026. did we mention that you can freeze time? Oh, right; we did.<\/P><\/TD><\/TR>\n

\n

5.<\/P><\/TD>\n

\n

OK then \u2026 let\u2019s see: freezing time, Scripting Son, Scripting Editor \u2026. Hmmm \u2026.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

Oh, my: would you look at the time? Guess we better skip reasons 6 through 99 and just cut right to reason number 100: <\/P>\n\n\n\n
\u2022<\/TD>\n\n

100. You can find a script that will trigger an alert if a Microsoft Access database file exceeds a specific size.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

In other words, you can find this<\/I> script:<\/P>

strComputer = “.”<\/p>\n
Set objWMIService = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)<\/p>\n
Set colMonitoredEvents = objWMIService.ExecNotificationQuery _\n    (“SELECT * FROM __InstanceModificationEvent WITHIN 30 WHERE ” _\n        & “TargetInstance ISA ‘CIM_DataFile’ and ” _\n            & “TargetInstance.Name=’C:\\\\Scripts\\\\Test.mdb'”)<\/p>\n
Do\n    Set objLatestEvent = colMonitoredEvents.NextEvent\n    If objLatestEvent.TargetInstance.FileSize > 204800 Then\n        Wscript.Echo “The Access database file has exceeded its allowed size.”\n    End If\nLoop\n<\/PRE>\n
OK, let\u2019s 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\u2019s 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<\/I> 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:<\/P>
strComputer = “atl-dbs-01”\n<\/PRE>\n
That brings us to this panic-inducing line of code:<\/P>
Set colMonitoredEvents = objWMIService.ExecNotificationQuery _\n    (“SELECT * FROM __InstanceModificationEvent WITHIN 30 WHERE ” _\n        & “TargetInstance ISA ‘CIM_DataFile’ and ” _\n            & “TargetInstance.Name=’C:\\\\Scripts\\\\Test.mdb'”)\n<\/PRE>\n
To begin with, don\u2019t panic: this is nothing more than the line of code that enables us to \u201csubscribe\u201d 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<\/B> 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\u2019t want to be notified every<\/I> 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<\/B> (that is, the new instance of the __InstanceModificationEvent class) meets the following criteria:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
The TargetInstance is a member of the CIM_DataFile<\/B> class (which simply means that this is a file as opposed to a folder, a process, a service, or anything else.)<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The TargetInstance has a Name<\/B> equal to C:\\\\Scripts\\\\Test.mdb. The Name property, in case you\u2019re wondering, is equivalent to the file path. Of course, if that\u2019s the case, then shouldn\u2019t the Name be equal to C:\\Scripts\\Test.mdb? Well, technically, that is what it\u2019s equal to. However, any time you reference a value in a Where clause that includes the \\ character that character must be \u201cescaped;\u201d that simply means it must be prefaced with a second \\ character. That\u2019s how C:\\Scripts\\Test.mdb ends up looking like this: C:\\\\<\/B>Scripts\\\\<\/B>Test.mdb.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Oh, and in case you\u2019re wondering, Test.mdb just happens to be the Access database file we\u2019re keeping a watchful eye on. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Good observation: what is<\/I> the WITHIN 30<\/B> 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\u2019s nothing magical about the value 30; that simply means that once the file has been modified we\u2019ll 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<\/B> to the desired number of seconds. (Reason 33: Hey, Scripting Guy!<\/I> includes a link<\/B><\/A> to a Webcast that explains WMI events and the WMI event terminology in detail.)<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After we subscribe to the WMI event service we next set up a Do loop designed to run, well, forever. (Notice that there\u2019s 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:<\/P>
Set objLatestEvent = colMonitoredEvents.NextEvent\n<\/PRE>\n
What if no such event ever<\/I> 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<\/I> occur, however, we use this line of code to check to see if the FileSize<\/B> property is larger than 204,800 bytes:<\/P>
If objLatestEvent.TargetInstance.FileSize > 204800 Then\n<\/PRE>\n
Again, there\u2019s nothing special about the value 204,800; we had to pick a target file size and so we picked this one. In case you\u2019re wondering, 204,800 bytes is equal to 200 kilobytes: 200 * 1024. (Reason 29: We do the math so that you don\u2019t have to … Well, unless it\u2019s algebra. Then you\u2019re on your own.) <\/P>\n
And what if the file size does<\/I> exceed 204,800 bytes? In that case, our sample script simply echoes back a message to that effect:<\/P>
Wscript.Echo “The Access database file has exceeded its allowed size.”\n<\/PRE>\n
And then we loop around and wait for the next such event to occur.<\/P>\n
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\u2019t 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<\/B><\/A> to the event log or send an email<\/B><\/A> using SMTP mail. That\u2019s entirely up to you. (Reason 91: We let our readers do most of the work.)<\/P>\n
By the way, MA, we know that you\u2019re using Microsoft Operations Manager (MOM). Hopefully you can take the code we\u2019ve 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\u2019ll see if we can find someone who can help you. We\u2019d help you ourselves, but if there\u2019s one thing that the Scripting Guys know very little about (and there definitely is<\/I> at least one thing we know very little about) it\u2019s MOM.<\/P>\n
At any rate, we hope that helps, and we hope this gives people reason to continue reading Hey, Scripting Guy!<\/I> each and every day. And if that isn\u2019t<\/I> reason enough, well, don\u2019t worry: we\u2019ll be bringing you plenty of useful information in the coming months. For example, we still<\/I> haven\u2019t 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<\/I>. And, hey, the next high school baseball season is just around the corner. It should be great year, huh?<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. The Scripting Editor suggested that we add, \u201cPlus we will have a lot more useful scripting information to bring you.\u201d We\u2019re not sure what difference that makes to anyone, but what the heck: yes, we\u2019ll also have a lot more useful scripting information to bring you. Not to mention the Scripting Guys going to Barcelona<\/B><\/A>, the upcoming Turducken Bowl<\/B><\/A>, and much, much more.<\/P>\n
Like, say, freezing time.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I trigger an alert if an Access database file exceeds a specific size?— MA Hey, MA. You know just the other day Microsoft.com posted 100 Reasons You’ll Be Speechless, a Web page listing 100 reasons why \u201cSeeing Windows Vista for the first time may leave you searching for words.\u201d Note. […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[54,49,3,5],"class_list":["post-63723","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-access","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I trigger an alert if an Access database file exceeds a specific size?— MA Hey, MA. You know just the other day Microsoft.com posted 100 Reasons You’ll Be Speechless, a Web page listing 100 reasons why \u201cSeeing Windows Vista for the first time may leave you searching for words.\u201d Note. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63723","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63723"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63723\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63723"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63723"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63723"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}