How Can I Determine Which Version of Access was Used to Create a Database?

How Can I Determine Which Version of Access was Used to Create a Database?

  • Comments 9
  • Likes
Hey, Scripting Guy! Question

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

-- TW

SpacerHey, Scripting Guy! AnswerScript Center

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

By default, Access will present you with a dialog box asking if you really want to open the database. To bypass that warning, set your macro security level to Low.

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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Perfect, that was exactly what I was looking for.

    Thanks!

  • That's so cool.

    Thank you most kindly!

  • Hi there,

    Sorry for asking such a basic question, but how do run this?  i.e. in what application and how.

    thanks

    Joe

  • @joe

    The technique should work in any scripting language you normally use.

    Here is how to use it in PowerSHell

    $access=New-Object -com Access.Application

    $access.OpenCurrentDatabase('e:\testc\test.mdb')

    switch($access.CurrentProject.FileFormat){

         2 {'Microsoft Access 2'   }

         7 {'Microsoft Access 95'  }

         8 {'Microsoft Access 97'  }

         9 {'Microsoft Access 2000'}

       10 {'Microwsoft ess 2003'  }

       11 {'Microwsoft ess 2007'  }

       12 {'Microwsoft ess 2010'  }

    }

    $access.Quit()

  • hi there - does anyone know how to make the above work in visual studio? i.e. what file type to paste the above in to make it run?

  • @Dan

    You need to use Office/Access Interop to open an MSAccess file then just get the same property.

    How you do this depends on the langauge used.  To get more info you would need to post in the forum for your Visual Studio language.

  • Love the PowerShell - just did it for me.

    Thanks

  • If you have several databases to query, it is worth creating a form with the folder path and file name. here is a sample of the script:

    Option Compare Database
    Option Explicit

    Private Sub btnFindAccessVersion_Click()

    Dim objAccess As Object
    Dim DB As DAO.Database
    Dim strFilePath As String
    Dim strFileName As String
    Dim strMSAccessFormat As String
    Dim intformat As Integer

    Set DB = CurrentDb

    strFilePath = Me.tbxFilepath
    strFileName = Me.tbxFilename

    Set objAccess = CreateObject("Access.Application")

    objAccess.OpenCurrentDatabase strFilePath & "\" & strFileName

    intformat = objAccess.CurrentProject.FileFormat

    Select Case intformat

    Case 2
    strMSAccessFormat = "Microsoft Access 2"
    Case 7
    strMSAccessFormat = "Microsoft Access 95"
    Case 8
    strMSAccessFormat = "Microsoft Access 97"
    Case 9
    strMSAccessFormat = "Microsoft Access 2000"
    Case 10
    strMSAccessFormat = "Microsoft Access 2003"
    Case 12
    strMSAccessFormat = "Microsoft Access 2007"
    Case 14
    strMSAccessFormat = "Microsoft Access 2010"

    End Select

    objAccess.CloseCurrentDatabase

    MsgBox intformat & " - " & strMSAccessFormat

    End Sub

  • thanks