Re: How to determine an Access DB's file format

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Based on the site I referenced here is a function that determines the file format / version of an .mdb file. The proc still needs more error handling though for example if you try to open a newer version of .mdb than the msaccess.exe you have installed.



Public Function getMDBversion(sFilename As String) As String
On Error GoTo errHandler

Dim objAccess As Object, nFormat As Long

getMDBversion = "Unknown file format" ' default return value

' check that file exists
If Len(Dir(sFilename)) = 0 Then
getMDBversion = "ERROR: File doesn't exist."
Else
' last used version of msaccess.exe
Set objAccess = CreateObject("Access.Application")

With objAccess
' set macro security to low for this session
.AutomationSecurity = 1
.OpenCurrentDatabase sFilename
nFormat = .CurrentProject.FileFormat
.CloseCurrentDatabase
End With

Select Case nFormat
Case 2
getMDBversion = "Microsoft Access 2"

Case 7
getMDBversion = "Microsoft Access 95"

Case 8
getMDBversion = "Microsoft Access 97"

Case 9
getMDBversion = "Microsoft Access 2000"

Case 10
getMDBversion = "Microsoft Access 2003"

' Case 11
' getMDBversion = "Microsoft Access 2007" ' is there a "11"?

Case 12
getMDBversion = "Microsoft Access 2007 .accdb (ACE) format"

End Select
End If

exitHandler:
If Not (objAccess Is Nothing) Then Set objAccess = Nothing
Exit Function

errHandler:
getMDBversion = "ERROR " & Err.Number & ": " & Err.Description
Resume exitHandler

End Function


--
---------------
John Mishefske, Microsoft Access MVP
.