vba - 在VBA中获取MS Access数据库的版本

Question

我在 Excel 365 > Visual Basic 编辑器中使用以下代码来尝试确定使用哪个版本的 MS Access 创建旧数据库。该代码适用于我作为测试创建的较新的 .accdb 数据库。据我所知，较旧的数据库最初是在 Access 97 中编写/设计的，但我想确定一下。

Public Sub GetAccessFormat()
    ' Attempt to determine the format of an Access database
    ' Note: Can Access 16.0 (Office 365) read databases from Office 2013 and earlier?
    On Error GoTo Error_NotAccessDatabase
    Dim fileName As String
    'fileName = "C:\Tmp\old.mdb"  ' Fails
    fileName = "C:\Tmp\new.accdb" ' Works
    
    If IsEmpty(Dir(fileName)) Then
        MsgBox "Could not find: " & fileName
        Exit Sub
    End If
    
    ' Open the database
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application") ' Is this the problem?
    objAccess.OpenCurrentDatabase fileName
    objAccess.Visible = False
    
    ' Get the file format
    Dim fileFormat As Integer
    fileFormat = objAccess.CurrentProject.FileFormat ' Gets here and fails for .mdb (returns "12" for the .accdb)
    Dim strAccessFormat As String
    strAccessFormat = "Your database is: "
    
    Select Case fileFormat
        Case 2
            strAccessFormat = (strAccessFormat & "Microsoft Access 2")
        Case 7
            strAccessFormat = (strAccessFormat & "Microsoft Access 95")
        Case 8
            strAccessFormat = (strAccessFormat & "Microsoft Access 97")
        Case 9
            strAccessFormat = (strAccessFormat & "Microsoft Access 2000")
        Case 10
            strAccessFormat = (strAccessFormat & "Microsoft Access 2002")
        Case 11
            strAccessFormat = (strAccessFormat & "Microsoft Access 2003")
        Case 12
            strAccessFormat = (strAccessFormat & "Microsoft Access 2007")
        Case 14
            strAccessFormat = (strAccessFormat & "Microsoft Access 2010")
        Case 15
            strAccessFormat = (strAccessFormat & "Microsoft Access 2013")
        Case 16
            strAccessFormat = (strAccessFormat & "Microsoft Access 2016/9")
        Case Else
            strAccessFormat = "Unknown Access file format"
    End Select
    
    ' Close database and display the format information
    objAccess.CloseCurrentDatabase
    strAccessFormat = (strAccessFormat + " (" & fileFormat & ".0)")
    MsgBox strAccessFormat
    Exit Sub

Error_NotAccessDatabase:
    ' Unable to open the database (not Access or not supported by this version of Office?)
    MsgBox "Unable to open as Access database: " & strFile & ", Error: " & Err.Description
    Exit Sub
End Sub

错误文本是“您输入的表达式引用了一个已关闭或不存在的对象。” (数据库确实存在，未在其他应用程序中打开，并且数据库上没有锁定)

我们安装了 Office 365(带有 Access 16.0)，因此我认为问题出在 Set objAccess = CreateObject("Access.Application") 上，因为我的电脑上的“Access Application”将无法读取旧格式的 Access 数据库。

这是正确的吗？是否有解决方法来确定旧 Access 数据库的文件格式？

Answer 1

您还可以“查看”文件的第二十一个字节并从中确定其文件类型:

Function GetAccessFileType(filePath As String) As String
    Dim strm As New ADODB.Stream
    strm.Type = adTypeBinary
    strm.Open
    strm.LoadFromFile filePath
    strm.Read 20
    Dim bytes As Variant
    bytes = strm.Read(1)
    Dim fileTypeNumber As Integer
    fileTypeNumber = CInt(bytes(0))
    strm.Close
    Set strm = Nothing
    Dim fileTypeString As String
    Select Case fileTypeNumber
        Case 0:
            fileTypeString = "Access 97 or older"
        Case 1:
            fileTypeString = "Access 2000/2003"
        Case 2:
            fileTypeString = "Access 2007"
        Case 3:
            fileTypeString = "Access 2010"
        Case 5:
            fileTypeString = "Access 2016 with BIGINT support (Type 5)"
        Case Else:
            fileTypeString = "Unknown (" & fileTypeNumber & ")"
    End Select
    GetAccessFileType = fileTypeString
End Function