Determine FileFormat Without Opening Database (Using Access 2007)

D

DJDave

New member
Local time
Today, 11:48
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
 
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
 
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...)
 
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"
 
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.
 
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

A
Using ACCDE format 64 vs 32 bits
Replies
10
Views
844
The_Doc_Man
The_Doc_Man
Minty
Odd Behaviour / Bug with Combo Column Values
Replies
8
Views
205
The_Doc_Man
The_Doc_Man
J
Open PDF files in Nitro instead of Adobe reader
Replies
13
Views
624
Isaac
Isaac
K
Is this a normal behavior in Access?
2
Replies
26
Views
737
The_Doc_Man
The_Doc_Man
A
Solved VBA compact-and-repair of a closed (corrupted) database from a new, blank Access file
2
Replies
24
Views
1,021
isladogs
isladogs

Users who are viewing this thread

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