{"id":65833,"date":"2006-12-15T11:55:00","date_gmt":"2006-12-15T11:55:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/12\/15\/how-can-i-list-all-the-access-database-files-on-a-computer\/"},"modified":"2006-12-15T11:55:00","modified_gmt":"2006-12-15T11:55:00","slug":"how-can-i-list-all-the-access-database-files-on-a-computer","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-list-all-the-access-database-files-on-a-computer\/","title":{"rendered":"How Can I List All the Access Database Files on a Computer?"},"content":{"rendered":"

\"Hey,<\/p>\n

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

— MG<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a><\/p>\n

Hey, MG. You know, in this day and age most people don\u2019t believe in Santa Claus. Well, we happen to know that these people are wrong: Santa Claus really is<\/i> real. How do we know that? Because he\u2019s spent the past couple days spamming the Scripting Guy who writes this column.<\/p>\n

It\u2019s 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\u2019s name; that way the little tyke (who, by the way, is 6\u2019-1\u201d 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<\/i> Santa; no kid that we know expects to get a letter from<\/i> Santa. <\/p>\n

Of course, the logical assumption is that Santa simply needs this information for other purposes. But that doesn\u2019t make any sense, either. After all, he knows when you\u2019ve been sleeping, he knows when you\u2019re awake; he even knows if you\u2019ve been bad or good. (So be good<\/i>, 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?<\/p>\n

If we didn\u2019t know any better we\u2019d think someone was using the Internet to try and scam innocent people. But that\u2019s a bit far-fetched; surely no one would even think<\/i> of using the Internet for evil rather than good.<\/p>\n

Oops; there\u2019s another one. Sorry, Santa, but that\u2019s one email too many; no milk and cookies for you this year. However, we\u2019ll still give you (and everyone else) a script that can locate all the Microsoft Access database files on a computer:<\/p>\n

strComputer = \".\"\nSet objWMIService = GetObject(\"winmgmts:\\\\\" & strComputer & \"\\root\\cimv2\")\nSet colFiles = objWMIService.ExecQuery _\n    (\"Select * from CIM_DataFile Where Extension = 'mdb' OR Extension = 'ldb'\")\nFor Each objFile in colFiles\n    Wscript.Echo objFile.Name\nNext\n<\/pre>\n
In the spirit of the holidays, this is a \u201ctraditional\u201d script for locating Access database files; we\u2019ll 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<\/b> class) that have an Extension<\/b> equal to mdb<\/i> or ldb<\/i>:<\/p>\n
Set colFiles = objWMIService.ExecQuery _\n    (\"Select * from CIM_DataFile Where Extension = 'mdb' OR Extension = 'ldb'\")\n<\/pre>\n
And before you ask, no, we didn\u2019t make a mistake here: in WMI the period is not considered part of the file extension. That\u2019s why we use the value mdb<\/i> rather than .mdb<\/i>.<\/p>\n
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<\/b> of each file:<\/p>\n
c:\\scripts\\restored_files.mdb\\test.mdb\nc:\\scripts\\test.mdb\nc:\\windows\\shellnew\\access9.mdb\nc:\\windows\\system32\\ias\\dnary.mdb\nc:\\windows\\system32\\ias\\ias.mdb\n<\/pre>\n
Ho, ho, ho, eh?<\/p>\n
Now, this script works just fine; if there\u2019s a drawback to it, it\u2019s 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\u2019s 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\u2019s look at the code first:<\/p>\n
On Error Resume Next\nSet objConnection = CreateObject(\"ADODB.Connection\")\nSet objRecordSet = CreateObject(\"ADODB.Recordset\")\nobjConnection.Open \"Provider=Search.CollatorDSO;Extended Properties='Application=Windows';\"\nobjRecordSet.Open \"SELECT System.ItemPathDisplay FROM SYSTEMINDEX WHERE System.FileExtension = '.mdb'\" & _\n    \" OR System.FileExtension = '.ldb'\", objConnection\nobjRecordSet.MoveFirst\nDo Until objRecordset.EOF\n    Wscript.Echo objRecordset.Fields.Item(\"System.ItemPathDisplay\")\n    objRecordset.MoveNext\nLoop\n<\/pre>\n
Why is this script so much faster than the first script we showed you? That\u2019s an easy one: this script takes advantage of Windows Desktop Search 3.0. So then why didn\u2019t we just show you this script in the first place? That\u2019s also an easy one. As it turns out, unless you\u2019re running Windows Vista you don\u2019t have<\/i> Desktop Search 3.0; instead, you need to download and install it. (And unless you\u2019re running Windows XP Service Pack 2 or Windows Server 2003 you can\u2019t even do that; Desktop Search isn\u2019t 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\u2019ll need to use WMI.<\/p>\n
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\u2019t just sound useful, it is<\/i> useful. And, as a special holiday treat, the Scripting Guys have published an article<\/b><\/a> that explains how you can start using Desktop Search 3.0 in your system administration scripts.<\/p>\n
It was the least we could do.<\/p>\n
At any rate, this is the last Hey, Scripting Guy!<\/i> for 2006; we\u2019ll be back with a brand-new column on January 2nd<\/sup>. Have a happy holiday season, and we\u2019ll see everyone in a couple weeks.<\/p>\n
Oh, and if you see Santa, could you tell him that our Social Security Number is 000-11-2222? We\u2019re still not sure why he needs it, but seeing as how he is<\/i> Santa, well, we can\u2019t afford to get him upset with us.<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019t believe in Santa Claus. Well, we happen to know that these people are wrong: Santa Claus really is real. How do we […]<\/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-65833","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 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\u2019t believe in Santa Claus. Well, we happen to know that these people are wrong: Santa Claus really is real. How do we […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65833","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=65833"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65833\/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=65833"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65833"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65833"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}