Determine FileFormat Without Opening Database (Using Access 2007) (1 Viewer)

D

DJDave

New member
Local time
Today, 06:29
Joined
Jan 21, 2014
Messages
6
Greetings once again...

SHORT VERSION:

Using Access 2007, can I return a value for CurrentProject.FileFormat WITHOUT opening the database?

LONG VERSION:

I'm looking for a way to determine the file version of a given Access database without actually "opening" the database in Access (I don't want it to ever be visible at all or in any way). I've tried several methods and done a bunch of research, and have come up stumped.

So to open a database in Access 2007 "without opening it," I'm using:

Code: 
Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Path-To\File.accdb")

From here, there are at least two different "version" indicators that can be used, as in this example:

Code: 
Debug.Print db.Properties("Version").Value
Debug.Print db.Properties("AccessVersion").Value

Now, if you run this code on an Access 2002/2003 .MDB file, you will get:

Code: 
4.0
09.50

If you run this code on an Access 2007 .ACCDB file, you get:

Code: 
12.0
09.50

Note two important factors:
  1. The db.Properties("Version") seems to be returning the JET version from what I can see in research. This is NOT what I'm after.
  2. The db.Properties("AccessVersion") returns THE SAME VALUE for an '07 ACCDB as it does for an '02-'03 MDB, since Microsoft never updated it, apparently.

Now.... the one way that I have found that ACCURATELY describes the version of a given access database file is to use:
Code: 
Debug.Print CurrentProject.FileFormat

...as this will always return, for example, 2.0 for an Access 2.0 .MDB file; 10 for an Access 2002/03 .MDB file; and 12 for and Access 2007 .ACCDB file (and there are others; these are AcFileFormat bitmask constants as described in Access Help).

BUT, this CurrentProject object doesn't seem to be available (so far as I can see) when using the "open without opening" method described above.

SO, is there any way to return the FileFormat value without opening the database visibly? (Merely basing it in the extension isn't right either.... that would be easy but not accurate as several formats use MDB.)

Thanks Ten-Million in advance for any help... :D
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:29
Joined
Jul 9, 2003
Messages
16,363
What about This?

Code: 
Function FindVersion(strDbPath As String) As String
Dim dbs As Database
Dim strVersion As String
Const conPropertyNotFound As Integer = 3270

On Error GoTo Err_FindVersion

' Open the database and return a reference to it.
Set dbs = OpenDatabase(strDbPath)

' Check the value of the AccessVersion property.
strVersion = dbs.Properties("AccessVersion")

Debug.Print strVersion
' Return the two leftmost digits of the value of
' the AccessVersion property.
strVersion = Left(strVersion, 2)

' Based on the value of the AccessVersion property,
' return a string indicating the version of Microsoft Access
' used to create or open the database.
Select Case strVersion
Case "02"
FindVersion = "2.0"
Case "06"
FindVersion = "95"
Case "07"
FindVersion = "97"
Case "08"
FindVersion = "2000"
Case "09"
FindVersion = "2002"
End Select

Exit_FindVersion:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Function

Err_FindVersion:
If Err.Number = conPropertyNotFound Then
MsgBox "This database hasn't previously been opened " & _
"with Microsoft Access."
Else
MsgBox "Error: " & Err & vbCrLf & Err.Description
End If
Resume Exit_FindVersion
End Function

Found by searching the forum:- The Search
 
D

DJDave

New member
Local time
Today, 06:29
Joined
Jan 21, 2014
Messages
6
Uncle Gizmo said:
What about This?

Code: 
Function FindVersion(strDbPath As String) As String
Dim dbs As Database
Dim strVersion As String
Const conPropertyNotFound As Integer = 3270

On Error GoTo Err_FindVersion

' Open the database and return a reference to it.
Set dbs = OpenDatabase(strDbPath)

' Check the value of the AccessVersion property.
strVersion = dbs.Properties("AccessVersion")

Debug.Print strVersion
' Return the two leftmost digits of the value of
' the AccessVersion property.
strVersion = Left(strVersion, 2)

' Based on the value of the AccessVersion property,
' return a string indicating the version of Microsoft Access
' used to create or open the database.
Select Case strVersion
Case "02"
FindVersion = "2.0"
Case "06"
FindVersion = "95"
Case "07"
FindVersion = "97"
Case "08"
FindVersion = "2000"
Case "09"
FindVersion = "2002"
End Select

Exit_FindVersion:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Function

Err_FindVersion:
If Err.Number = conPropertyNotFound Then
MsgBox "This database hasn't previously been opened " & _
"with Microsoft Access."
Else
MsgBox "Error: " & Err & vbCrLf & Err.Description
End If
Resume Exit_FindVersion
End Function

Found by searching the forum:- The Search
Click to expand...

I've actually already tried that... :( Using db.Properties("AccessVersion") returns the wrong number for an .ACCDB (it gives 09.50 when that is the number for a 2002/2003 .MDB...)
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:29
Joined
Jul 9, 2003
Messages
16,363
What about this then?

Uses appAccess.CurrentProject.FileFormat

By Chris (stopher) Found HERE:- http://www.access-programmers.co.uk/forums/showpost.php?p=778026&postcount=2


Code: 
Public Function FileVersion(sPathFile As String) As String
Dim appAccess As Access.Application

Set appAccess = New Access.Application

appAccess.OpenCurrentDatabase sPathFile

'this bit comes straight from FileFormat Help
Select Case appAccess.CurrentProject.FileFormat
    Case acFileFormatAccess2
        FileVersion = "Microsoft Access 2"
    Case acFileFormatAccess95
        FileVersion = "Microsoft Access 95"
    Case acFileFormatAccess97
        FileVersion = "Microsoft Access 97"
    Case acFileFormatAccess2000
        FileVersion = "Microsoft Access 2000"
    Case acFileFormatAccess2002
        FileVersion = "Access 2002 - 2003"
    Case acFileFormatAccess2007
        FileVersion = "Microsoft Access 2007"
    'Case acFileFormatAccess2010
    '    FileVersion = "Microsoft Access 2010"
    'Case acFileFormatAccess2013
    '    FileVersion = "Microsoft Access 2013"
End Select

appAccess.CloseCurrentDatabase
Set appAccess = Nothing

End Function

Note:-
I added :-
Code: 
Case acFileFormatAccess2007
        FileVersion = "Microsoft Access 2007"

and this wouldn't work on my machine, possibly 'cause I got ACC2007 :-

Code: 
    'Case acFileFormatAccess2010
    '    FileVersion = "Microsoft Access 2010"
    'Case acFileFormatAccess2013
    '    FileVersion = "Microsoft Access 2013"
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:29
Joined
Jul 9, 2003
Messages
16,363
Just thinking:-

' Case acFileFormatAccess2 '2
' Case acFileFormatAccess97 '8
' Case acFileFormatAccess2000 '9
' Case acFileFormatAccess2002 '10
' Case acFileFormatAccess2007 '12
' Case acFileFormatAccess2010 '?
' Case acFileFormatAccess2013 '?

The acFileFormat**** is just a number, thats all you need for other versions.
So if I tried to open a 2010 or 2013 file, the number would be returned, enabling updating of the above case statement.
 
D

DJDave

New member
Local time
Today, 06:29
Joined
Jan 21, 2014
Messages
6
Uncle Gizmo said:
What about this then?

Uses appAccess.CurrentProject.FileFormat

By Chris (stopher) Found HERE:- http //www access-programmers co uk/forums/showpost.php?p=778026&postcount=2


Code: 
Public Function FileVersion(sPathFile As String) As String
Dim appAccess As Access.Application

Set appAccess = New Access.Application

appAccess.OpenCurrentDatabase sPathFile

'this bit comes straight from FileFormat Help
Select Case appAccess.CurrentProject.FileFormat
    Case acFileFormatAccess2
        FileVersion = "Microsoft Access 2"
    Case acFileFormatAccess95
        FileVersion = "Microsoft Access 95"
    Case acFileFormatAccess97
        FileVersion = "Microsoft Access 97"
    Case acFileFormatAccess2000
        FileVersion = "Microsoft Access 2000"
    Case acFileFormatAccess2002
        FileVersion = "Access 2002 - 2003"
    Case acFileFormatAccess2007
        FileVersion = "Microsoft Access 2007"
    'Case acFileFormatAccess2010
    '    FileVersion = "Microsoft Access 2010"
    'Case acFileFormatAccess2013
    '    FileVersion = "Microsoft Access 2013"
End Select

appAccess.CloseCurrentDatabase
Set appAccess = Nothing

End Function

Note:-
I added :-
Code: 
Case acFileFormatAccess2007
        FileVersion = "Microsoft Access 2007"

and this wouldn't work on my machine, possibly 'cause I got ACC2007 :-

Code: 
    'Case acFileFormatAccess2010
    '    FileVersion = "Microsoft Access 2010"
    'Case acFileFormatAccess2013
    '    FileVersion = "Microsoft Access 2013"
Click to expand...

Progress!! :D This indeed accomplishes the functional part of what I want! ...Now just trying to figure out a way to do it that it doesn't cause a new Access window to open and then disappear every time I click the button. I tried adding appAccess.Visible=False, but the window still shows up even with said statement being placed before and/or after the appAccess.OpenCurrentDatabase(...) command.

Code: 
Dim appAccess As Access.Application
Set appAccess = New Access.Application

With appAccess
    .Visible = False
    .OpenCurrentDatabase ("C:\Path-To\File.accdb")
    .Visible = False
End With

Me.txtCustom2.Value = appAccess.CurrentProject.FileFormat

appAccess.CloseCurrentDatabase
Set appAccess = Nothing


(BTW I had to strip the URL out of the quote.... so it's slightly misquoted, lol....)
 
You must log in or register to reply here.

Similar threads

K
Opening a form in a library and retrieving a returned value
2
Replies
21
Views
716
Josef P.
J
E
Solved Import Access 2.0 database to Access 365
Replies
18
Views
813
isladogs
isladogs
A
Solved Moved to New Computer, New Version of Access Issue with acFormatPDF
Replies
18
Views
417
pbaldy
pbaldy
J
Help with using ADOX to Set TableDef connect property in foreign DB w/out actually connecting
Replies
4
Views
255
Pat Hartman
P
Kayleigh
Avast blocking auto-update macro
Replies
4
Views
247
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom