Retrieve Access File Format 2

[Knicks]

I have a piece of code that works from within a database to get the Access Version of that database. What I am trying to do is create an inventory of Access databases and their versions so I would like to modify the code to work from another database instead of the current db. If possible it would be great if it could loop through a directory and output the Access Versions of each mdb it comes across.

Here is the code I have so far. I am not sure if this function supports having a string to another database.

Public Function getDefaultFileFormat() As Long




On Error GoTo ErrHandler

getDefaultFileFormat = GetOption("Default File Format")

Exit Function

ErrHandler:

MsgBox "Error in getDefaultFileFormat( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
'The function will return 8 for Access 97 file format, 9 for Access 2000 file format, or 10 for Access 2002 - 2003 file format.

End Function

 

[jrbarnett]

Knicks - that code of yours doesn't retrieve the current version of that database.
What that retrieves is the default access file version of the instance of Access you are using, which may (or may not) be the version of the file you are using.

Debug.Print SysCmd (acSysCmdAccessVer)

will get you the version of Access - but not the version of the file format being used - will post back when I've figured out how to find out the file format version.

John

 

[jrbarnett]

Got it:

Debug.Print Currentdb.Properties ("AccessVersion")

For my (fully patched) versions:

Access 97 - returns 07.53
Access 2000 - returns 08.50
Access 2002/2003 - returns 09.50

Don't have Access 2007 here - can check that tomorrow.

To loop through a directory of files, use the dir() function to enumerate *.mdb, then use OpenDatabase to connect to it, and retrieve the AccessVersion property from the database object (ie use your database object .Properties ("AccessVersion").

John

 

[Knicks]

Thank you very much for clarifying that. No wonder why I kept on getting a "9", which was the default file format for Access 2000 - no matter what db I put in!

I'll give it a test...

 

[jrbarnett]

Right, I've tried it with an accdb file in Access 2007.

It also retrieves 09.50, same as Access 2002/2003 which isn't good. So I think you need a combination of that version plus the Version property (which is the Jet database engine version) or you can use the Access version number code above.

John

 

[Knicks]

jrBarnett - Are you still out there?

For the database properties "accessversion" that works very well (I don't have 2007 databases).

Do you know if a property exists for file size or file date? I would love a list of other options besides "accessversion" that would work.

Any insight on other options that I could gather would be great.

 

[jrbarnett]

The file size in bytes and last modified date aren't stored in the Access DB itself. You can get them by querying the file system though - doing the equivalent of a "dir" command prompt.

Some basic information:

Sub FileInformation()

Dim fso, f1

Set fso = CreateObject("Scripting.FileSystemObject")

' Get a File object to query.

Set f1 = fso.GetFile(CurrentDb.Name)

' Print information.

Debug.Print "File last modified: " & f1.DateLastModified & vbCrLf & "Name: " & CurrentDb.Name
Debug.Print "File size: " & f1.Size & " bytes"

End Sub

For more information on FileSystemObject and the Windows Scripting runtime, see

http://msdn2.microsoft.com/en-us/library/6kxy1a51(VS.85).aspx

John

 

[PHV]

No need of special object for this:

Debug.Print "File last modified: " & FileDateTime(CurrentDb.Name) & vbCrLf & "Name: " & CurrentDb.Name
Debug.Print "File size: " & FileLen(CurrentDb.Name) & " bytes"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Knicks]

Thank you both, this looks great. I am having a bit of trouble with the looping, it seems to get 4 out of the 5 databases and then has runs into a file error without finding the last mdb. I use a button to call the procedure and input the path via a form field. I was having trouble with the looping prior to adding the form field as an input, it hasn't made it any worse, just more automated over using an input box. That code is:

Dim getpath As String

getpath = Me.txtAccessPath


Call CreateMDBtable(getpath)


Here is the code CreateMDBtable code, the line that fails is:
Set Readdb = OpenDatabase(fullpath)



Public Sub CreateMDBtable(Spath As String)

Dim Readdb As DAO.Database
Dim dbs As DAO.Database
Dim TsRec As DAO.Recordset

Dim dbreadname As String
Dim SnextFile As String
Dim fullpath As String

Set dbs = currentDb
Set TsRec = dbs.OpenRecordset("tblfiles", dbOpenDynaset)

SnextFile = Dir$(Spath & "\*.mdb")

While SnextFile <> ""

SnextFile = Dir$
fullpath = "" & Spath & SnextFile & ""
Debug.Print fullpath
Set Readdb = OpenDatabase(fullpath)

TsRec.AddNew
TsRec!fname = SnextFile
TsRec!fpath = Spath
TsRec!fversion = Readdb.Properties_("accessversion").Value
TsRec!Fsize = FileLen(Readdb.Name)
TsRec!Flastdate = FileDateTime(Readdb.Name)
TsRec.Update

Readdb.Close
Wend

TsRec.Close


End Sub

 

[PHV]

...
SnextFile = Dir$(Spath & "\*.mdb")
While SnextFile <> ""
'[s]SnextFile = Dir$[/s]
  fullpath = Spath & "\" SnextFile
  Debug.Print fullpath
  Set Readdb = OpenDatabase(fullpath)
  TsRec.AddNew
  TsRec!fname = SnextFile
  TsRec!fpath = Spath
  TsRec!fversion = Readdb.Properties("accessversion").Value
  TsRec!Fsize = FileLen(Readdb.Name)
  TsRec!Flastdate = FileDateTime(Readdb.Name)
  TsRec.Update
  Readdb.Close
  [!]SnextFile = Dir$[/!]
Wend
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Knicks]

PHV,

Awesome works like a charm!