Hey, Scripting Guy! How can I list all the Access database files (.mdb and .ldb files) on a computer?

— MG

Hey, MG. You know, in this day and age most people don’t believe in Santa Claus. Well, we happen to know that these people are wrong: Santa Claus really is real. How do we know that? Because he’s spent the past couple days spamming the Scripting Guy who writes this column.

It’s true: over the past 24 hours or so the Scripting Guy who writes this column has gotten at least 50 emails from Santa. (Or possibly from his elves; the mails all seem to have come from different email addresses.) Each of these emails points him towards a Web site where he can register his child’s name; that way the little tyke (who, by the way, is 6’-1” and 180 pounds) can get a letter from Santa. Which, to be honest, seems a bit odd to us. After all, traditionally children have written letters to Santa; no kid that we know expects to get a letter from Santa.

Of course, the logical assumption is that Santa simply needs this information for other purposes. But that doesn’t make any sense, either. After all, he knows when you’ve been sleeping, he knows when you’re awake; he even knows if you’ve been bad or good. (So be good, for goodness sake!) Why would Santa need to know our email address and home phone number? Why would he need a credit card number? And surely Santa has no use for our Social Security Number. Does he?

If we didn’t know any better we’d think someone was using the Internet to try and scam innocent people. But that’s a bit far-fetched; surely no one would even think of using the Internet for evil rather than good.

Oops; there’s another one. Sorry, Santa, but that’s one email too many; no milk and cookies for you this year. However, we’ll still give you (and everyone else) a script that can locate all the Microsoft Access database files on a computer:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery _
    ("Select * from CIM_DataFile Where Extension = 'mdb' OR Extension = 'ldb'")
For Each objFile in colFiles
    Wscript.Echo objFile.Name
Next

In the spirit of the holidays, this is a “traditional” script for locating Access database files; we’ll show you an alternate approach in a minute. For now, you can see that this is a pretty simple approach. We start out by connecting to the WMI service on the local computer. However, we could just as easily run the script against a remote machine: all we have to do is assign the name of that remote machine to the variable strComputer. We then use the following query to return a collection of all the files (that is, all instances of the CIM_DataFile class) that have an Extension equal to mdb or ldb:

Set colFiles = objWMIService.ExecQuery _
    ("Select * from CIM_DataFile Where Extension = 'mdb' OR Extension = 'ldb'")

And before you ask, no, we didn’t make a mistake here: in WMI the period is not considered part of the file extension. That’s why we use the value mdb rather than .mdb.

That query returns a collection of all the .MDB and .LDB files on the computer. All we have left to do now is set up a For Each loop to loop through the collection, echoing back the Name of each file:

c:\scripts\restored_files.mdb\test.mdb
c:\scripts\test.mdb
c:\windows\shellnew\access9.mdb
c:\windows\system32\ias\dnary.mdb
c:\windows\system32\ias\ias.mdb

Ho, ho, ho, eh?

Now, this script works just fine; if there’s a drawback to it, it’s the fact that, depending on the size of your hard disks, the script could take a minute or two (or more) to complete. With that in mind here’s an alternative approach, one that takes only a second or two to complete. Is there a catch involved here? Of course there is; in fact, there are several catches involved. But let’s look at the code first:

On Error Resume Next
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordSet.Open "SELECT System.ItemPathDisplay FROM SYSTEMINDEX WHERE System.FileExtension = '.mdb'" & _
    " OR System.FileExtension = '.ldb'", objConnection
objRecordSet.MoveFirst
Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("System.ItemPathDisplay")
    objRecordset.MoveNext
Loop

Why is this script so much faster than the first script we showed you? That’s an easy one: this script takes advantage of Windows Desktop Search 3.0. So then why didn’t we just show you this script in the first place? That’s also an easy one. As it turns out, unless you’re running Windows Vista you don’t have Desktop Search 3.0; instead, you need to download and install it. (And unless you’re running Windows XP Service Pack 2 or Windows Server 2003 you can’t even do that; Desktop Search isn’t available for any other flavor of Windows, at least not at the moment.) On top of that the preceding script works only on the local machine; if you need to search for Access files on a remote computer you’ll need to use WMI.

That said, Desktop Search 3.0 is very fast and very cool: imagine being able to search your entire computer for emails or instant messages as well as for files. And imagine being able to search for files not just by file extension but by Title, by Keyword, by Song Artist, by Horizontal or Vertical Resolution, etc. etc. Trust us, it doesn’t just sound useful, it is useful. And, as a special holiday treat, the Scripting Guys have published an article that explains how you can start using Desktop Search 3.0 in your system administration scripts.

It was the least we could do.

At any rate, this is the last Hey, Scripting Guy! for 2006; we’ll be back with a brand-new column on January 2^nd. Have a happy holiday season, and we’ll see everyone in a couple weeks.

Oh, and if you see Santa, could you tell him that our Social Security Number is 000-11-2222? We’re still not sure why he needs it, but seeing as how he is Santa, well, we can’t afford to get him upset with us.