Hey, Scripting Guy! How can I determine which version of Access was used to create an Access database?

— TW

Hey, TW. You know, at least one of the Scripting Guys is a bit old-fashioned, to say the least. He doesn’t own a cell phone, and he won’t use drive-through windows. He wears his baseball hat forwards rather than backwards (!), and he absolutely refuses to buy books online.

That last one is particularly shocking: any time he wants a new book, he goes to a bookstore to buy it! (It is hard to believe that people still live that way, isn’t it?) But that’s because this Scripting Guy is a firm believer in serendipity, in the art of accidental discovery. He likes “brick-and-mortar” bookstores because, while wandering through the aisles, he might suddenly discover a really cool book, a book he would never have found online. (And, no, not even with all those new-fangled computer programs that can alert you to the fact that a book exactly like the last 30 books you bought has just been published.)

As it turns out, serendipity can be useful in scripting as well. When we first received this email, we immediately turned to the Microsoft Access documentation, figuring this would be an easy problem to solve. Unfortunately, though, we couldn’t find anything having to do with versioning. And so we set this question aside, chalking it up as one of those things that you’d think would be scriptable, but isn’t.

But then serendipity entered the picture. While looking up something else in the Access documentation (which we couldn’t find either; maybe we just don’t know how to use documentation) we ran across a property named FileFormat. You guessed it: FileFormat (rather than, say, Version) is the property that tells us which version of Access was used to create a database. Want to know which version of Access was used to create the database C:\Scripts\Test.mdb? Here you go:

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “C:\Scripts\Test.mdb”

intFormat = objAccess.CurrentProject.FileFormat
Select Case intFormat
    Case 2 Wscript.Echo “Microsoft Access 2” 
    Case 7 Wscript.Echo “Microsoft Access 95”
    Case 8 Wscript.Echo “Microsoft Access 97” 
    Case 9 Wscript.Echo “Microsoft Access 2000”
    Case 10 Wscript.Echo “Microsoft Access 2003”
End Select

We begin by creating an instance of the Access.Application object, then we use the OpenCurrentDatabase method to open the file C:\Scripts\Test.mdb. (As you can see, the path is the sole parameter we pass to OpenCurrentDatabase.) We then use this line of code to get the value of the FileFormat property (which, technically, is a property of the CurrentProject object) and store that value in a variable named intFormat:

intFormat = objAccess.CurrentProject.FileFormat

All that’s left now is to set up a Select Case statement to examine the value of intFormat and echo back the corresponding version of Microsoft Access. For example, suppose intFormat is equal to 9. In that case, this line of code will be triggered and “Microsoft Access 2000” will be returned as the version of the application that created the database:

Case 9 Wscript.Echo “Microsoft Access 2000”

We’d like to see you find an answer like that online!

Oh, right: if you’re reading this column then we guess you did find the answer online, didn’t you? Hmmm ….

By the way, you might have noticed that we started an instance of Access but then never specifically quit that instance. Does that mean we have an orphaned copy of Microsoft Access running on our computer? No. As it turns out, Access works a bit different from Word or Excel: when you instantiate Access within a script, that instance will terminate at the same time the script does. There’s a way to work around that if need be, but that’s a topic we’ll have to address some other time.