我正在使用以下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")
出在哪里,因为我PC上的“ Access应用程序”将无法读取较早格式的访问数据库。
这样对吗?是否有解决方法来确定旧的Access数据库的文件格式?
您还可以“窥视”文件的第二十一个字节,并从中确定其文件类型:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句