Determine Version of mdb File 2

[cattys]

Hi there,

my company intends to stop offering Access 97 software and replace it by Access 2000 by end of this year.
My task is now to determine, how many Acess 97 and lower files are on our Fileservers.
I decided to solve this task with a script but the fso method GetFileVersion does not work with mdb files (null string)
Now I have read that Office offers special COM components, but all I found about this, was how to print out a word file [CreateObject("Word.Application&quot]

Now I am asking you, is there any reference about the Access version of this Object?
Or have you had a similiar task and you still have the script at hand?

You would help me alot, cause I am stuck at the moment.

Thanks for your help,
cattys

 

[dilettante]

Hi cattys,

After much thrashing, wailing, and gnashing of teeth, I cobbled up this 'lil script that might do what you need.

It relies on opening each .MDB using the Jet OLEDB Provider and then pulls out the "Jet OLEDB:Engine Type" property of the connection object. I can't find any documentation of the values to expect except for the following:

'  Microsoft Jet         OLEDB:Engine Type property values
Global Const JET_ENGINETYPE_UNKNOWN = 0
Global Const JET_ENGINETYPE_JET10 = 1
Global Const JET_ENGINETYPE_JET11 = 2
Global Const JET_ENGINETYPE_JET20 = 3
Global Const JET_ENGINETYPE_JET3X = 4
Global Const JET_ENGINETYPE_JET4X = 5
Global Const JET_ENGINETYPE_DBASE3 = 10
Global Const JET_ENGINETYPE_DBASE4 = 11
Global Const JET_ENGINETYPE_DBASE5 = 12
Global Const JET_ENGINETYPE_EXCEL30 = 20
Global Const JET_ENGINETYPE_EXCEL40 = 21
Global Const JET_ENGINETYPE_EXCEL50 = 22
Global Const JET_ENGINETYPE_EXCEL80 = 23
Global Const JET_ENGINETYPE_EXCEL90 = 24
Global Const JET_ENGINETYPE_EXCHANGE4 = 30
Global Const JET_ENGINETYPE_LOTUSWK1 = 40
Global Const JET_ENGINETYPE_LOTUSWK3 = 41
Global Const JET_ENGINETYPE_LOTUSWK4 = 42
Global Const JET_ENGINETYPE_PARADOX3X = 50
Global Const JET_ENGINETYPE_PARADOX4X = 51
Global Const JET_ENGINETYPE_PARADOX5X = 52
Global Const JET_ENGINETYPE_PARADOX7X = 53
Global Const JET_ENGINETYPE_TEXT1X = 60
Global Const JET_ENGINETYPE_HTML1X = 70

The values that seem to consistently be returned in my tests are:

For Access 97: 4

For Access 2000: 5

Which seems to match up with the list above just fine. I tested against a bunch of MDBs of both versions, both my own and some that I downloaded that were identifed whether 2000 or 97. Everything seems to match up.

Of course then I had to cobble together directory-traversal logic... you'll see that this script is highly recursive as a result.

- - - - - - - - - - - - - - - - -

Just run the script (GUI based), enter the full path of the folder you want to begin scanning at when the InputBox pops up, and wait for completion. You'll get a "Done" MsgBox when it finally completes. The results are a report written to a text file in the script's own directory.

Be warned that if you run it against a whole boot drive (entering C:\ for the path to scan) it'll take awhile and it'll error out once it hits a protected system directory. This is true even if you have admin rights under Win2K/XP. You might try trapping these errors or even trapping those system directories and skipping them if you want to scan boot drives.

You should easily be able to modify this to be run via CScript if desired, or add other hacks and polish.

'MDB Version.vbs ------------------
'
'Find the Engine Type of .mdb files
'using the Jet OLEDB Provider
'----------------------------------
Option Explicit
Const cstrMyTitle = "MDB Version Reporter"
Dim fso, fPrint, strBase, folBase
Dim cnnDB, prpProp

Sub PrintHeading()
  fPrint.WriteLine "******** MDB Version Report ********"
  fPrint.WriteLine
  fPrint.WriteLine "Known Types:"
  fPrint.WriteLine "             4 = Access 97"
  fPrint.WriteLine "             5 = Access 2000 (2002?)"
  fPrint.WriteLine
  fPrint.WriteLine "TYPE  FILE"
  fPrint.WriteLine "----  -------------------------------"
  fPrint.WriteLine
End Sub

Function FmtType(ByVal strType)
  FmtType = Left(" " & strType & Space(6), 6)
End Function

Sub PrintDetail(ByVal strFile)
  On Error Resume Next
  cnnDB.Open strFile
  If Err.Number <> 0 Then
    fPrint.WriteLine &quot;****  &quot; & strFile
    fPrint.WriteLine &quot;      &quot; & Hex(Err.Number) & &quot;  &quot; & Err.Description
  Else
    fPrint.WriteLine _
      FmtType(cnnDB.Properties.Item(&quot;Jet OLEDB:Engine Type&quot;).Value) & _
      strFile
    cnnDB.Close
  End If
End Sub

Sub ReportFiles(collFiles)
  Dim filEach

  For Each filEach In collFiles
    If UCase(Right(filEach.Path, 4)) = &quot;.MDB&quot; Then
      PrintDetail filEach.Path
    End If
  Next
End Sub

Sub ReportFolders(collFolders)
  Dim folEach

  For Each folEach in collFolders
    ReportFolder folEach
  Next
End Sub

Sub ReportFolder(folCurr)
  ReportFiles folCurr.Files
  ReportFolders folCurr.SubFolders    
End Sub

Sub Initialize()
  Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot;)
  Set folBase = fso.GetFolder(strBase)
  Set cnnDB = CreateObject(&quot;ADODB.Connection&quot;)
  cnnDB.Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot;
  Set fPrint = _
    fso.CreateTextFile(&quot;MDB Version.txt&quot;, True, False)
End Sub

Sub Finalize()
  fPrint.Close
  Set fPrint = Nothing
  Set cnnDB = Nothing
  Set folBase = Nothing
  Set fso = Nothing
  MsgBox &quot;Done&quot;, vbOkOnly, cstrMyTitle
End Sub

'Scan and report
strBase = InputBox( _
  &quot;Base drive and folder to scan and report&quot;, _
  cstrMyTitle)
If strBase <> &quot;&quot; Then 'Empty string means user canceled.
  Initialize
  PrintHeading
  ReportFolder folBase
  Finalize
End If

Hope this gets you started!

 

[dilettante]

Oops!

You might also alter Initialize to set:

cnnDB.Mode = 1 'adModeRead

This is good practice (Jet will default to read/write, and you don't want even the risk of writing to these DBs), and it also might help if you don't have write access to an MDB file you want to touch.

Just a thought.

 

[dilettante]

One last thing... boy, you start picking at a thing and you can't let it go!

It seems there are a lot of cases where a folder isn't accessable, so I updated these two Subs as shown:

Sub ReportFolders(collFolders)
  Dim folEach

  On Error Resume Next

  For Each folEach In collFolders

    If Err.Number <> 0 Then
      fPrint.WriteLine &quot;----  &quot; & folEach.Path
      fPrint.WriteLine &quot;      &quot; & Hex(Err.Number) & &quot;  &quot; & Err.Description
    Else
      ReportFolder folEach
    End If

  Next
End Sub

Sub Initialize()
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set folBase = fso.GetFolder(strBase)
  Set cnnDB = CreateObject("ADODB.Connection")
  cnnDB.Provider = "Microsoft.Jet.OLEDB.4.0"

  cnnDB.Mode = 1 'adModeRead

  Set fPrint = _
    fso.CreateTextFile("MDB Version.txt", True, False)
End Sub

Ok, you're on your own now. ;-)

 

[cattys]

Hi dilettante,

Your answer helped a lot.
You actually did about 95% of my original task.
Now, do I have to give you a percentage of my salary? =)

Sincere thanks to you,
cattys

 

[dilettante]

I'd settle for a purple star, if you'd click on "mark this post helpful." ;-)

 

[cattys]

hehe, I clicked on the vote button but never on the confirm link, sorry

 

[cattys]

Hi Dilettante,

today was the day I wanted to put your script into productional environment, but it all says "Permission Denied" on the fileservers.
My login is a FileServer Admin, so I can do everything there.
A local test with permissions went fine, even in a admin-only directory.

Do you have any idea why I get this permission denied on every direcrtory on the fileserver (even if I execute it locally by pcanywhere)?
In my understanding, the vbs is run with the current login or am I wrong?

A run with the parameters \\wksname\c$ also went fine, it just couldnt enter the c:\WINNT directory.

I would greatly appreciate your help again

Thanks,
cattys

 

[dilettante]

As usual, I'm not as smart as I need to be.

This modified Sub might be worth a try, it seems to work for me.

Sub ReportFolders(collFolders)
  Dim folEach

  On Error Resume Next
  For Each folEach In collFolders
    If Err.Number <> 0 Then
      Err.Clear
    End If
    ReportFolder folEach
  Next
End Sub

I think you have the proper security context all right. It seems that sharing a folder or setting certain kinds of permissions on it can set a "lock" on the folder though. This is where the error 70 (&H46) "Permission Denied" seems to erupt from. You can traverse the contents of such a folder, but perhaps you are prohibited from removing it or renaming it?

You need to trap and clear the error though, it really does occur - that's why I ended up putting in the original error-handling logic. Otherwise your script blows on an exception as soon as it hits such a folder.

I hope this helps. Sorry!

 

[cattys]

the script is now running perfectly, thanks a lot

 

[poney]

Hi Cattys,
I was just wondering where your name comes from.
To understand my wondering just go to

http://www.cattys.com.

You can answer to this if you wish thru my site's forum.

Roland