Is there a script to display the version numbers (97,2000, etc.) of all the Access Databases in a set of directories?

I would recommend installing this software

http://www.jam-software.com/treesize/

which gives you lots

regards
Chandru

Are there machines which have access 97 or 2000 or 2003.

Is a script ok which can find which version of office is installed.So that you know that a machine having office 97 installed in the machine will only have MBD files of office 97

Or is your requirement to find this is for some aprticular reason.

The MDB files are on file servers, not on workstations.  The version of the client on the workstation will not tell me what I'm interesting in knowing.

I want to see the versions of all the .MDB files on a file server in all directories including and under the one I specify.

Chandru -

I fail to see how TreeSizes will help me.  I own that app and it doesn't tell you the version of .MDB files... at least to the best of my knowledge.  Educate me otherwise if that's the case.

It will tell me how many .MDB's I have, where they are, and all that stuff but it will not tell me what Access version they are at.

Yes that's true but in the file properties of an MDB file you will not be abel to knowt by which version the file was created.

We can only fine the version of the msaccess or office application as the exe have version number in the file properties

regards
Chandru

I know though that via OLE or ADO there is a way to tell the version of an MDB.  I was hoping someone had a script that could recurse through and report that information.

try

Sub chkVersion()
Dim dbPath As String, dbFile, db As DAO.Database
dbPath = "E:\DatabaseCollection\"
dbFile = Dir(dbPath & "*.mdb")
While dbFile <> ""
    Set db = OpenDatabase(dbPath & dbFile)
    Debug.Print dbPath & dbFile & vbTab & db.Version
    db.Close
    dbFile = Dir
Wend
End Sub

Hey Capricorn -

That looks much closer to what I'm after.  My not being a VBScript expert by any means, can you embody that in a fully executable VBS script for me?  It looks like I just got a subroutine there...

Thanks so much for chiming in!

Hi,

hope this will help:

Imports System
Imports System.IO

Public Class Test
    Public Shared Sub Main()
        Try
            ' Only get files that with the mdb ext
            Dim dirs As String() = Directory.GetFiles("c:\", "*.mdb")
            Console.WriteLine("The number of files starting with c is {0}.", dirs.Length)
            ' Insert code fragment to get mdb version here
            '...
        Catch e As Exception
            Console.WriteLine("The process failed: {0}", e.ToString())
        End Try
    End Sub
End Class

Vital -

Even if I don't insert any of the VB function, your code segment returns an 'Expected Identifier' compilation error in line 4.

well, just make vb.net windows app and use folowing fragment to obtain mdb's files list
Dim dirs As String() = Directory.GetFiles("c:\", "*.mdb")
next if it neccessary get version of each file

amendala,
i don't write vbs, but if you can wait for a while i can write a small access db to get all the db names and version placed in a table.. just on my way to  a meeting..

Hey any help you can provide me is friggin' great!  You're making my morning and it is certainly appreciated.

I'm hoping you've got a clear picture of what I'm after.  I'd like to target for instance \\myserver\d$ and have it show me all the versions of those databases.

However you want to get that information to me (either on a console screen or in an Access Database or whatever) is fine by me.  I'll monitor the thread and get you any info you may need.

amendala,
download the sample db


https://filedb.experts-exchange.com/incoming/ee-stuff/5596-dbX.zip

Okay, I got it and tried to run it against a path like "c:\" just to test what it does and it runs for a while and eventually returns a "Permission denied" error.

I'm logged in as a Domain Admin, part of the local Administrators group, and have access to everything it should be touching.

... ??? What now.

do you have Admin rights in the computer you are using?

what version of access are you using?

Yeah I do.  I'm part of Domain Admins which is part of the local Administrators group.  For troubleshooting purposes, I took just your database and put it in a folder and ran it against that path and it did create the table and function normally.  For some reason though, when I target the admin shares of some of my file servers, I eventually get a Permission Denied error.

Access 2007

I'd also like to ask what values the "dbVersion" correspond to.  Yours says "4.0", how does that number correlate to Access?  I think your DB is Access 2003 which should be 11, no?  I know, one thing at a time!  ;-)

ok,just tried it  C:\

when you hit  C:\System Volume Information ' got the same error

access 2003 - application version is 11

dbVersion 4  is for A2k,2002,2003
dbVersion 3 is A97

Okay, that clears that up.  Now, for the Permission denied fix... :)

Again, can't say thanks enough for the help!

are you going to use this for the C:\?

No, I was using that for tests.  I'm planning on targeting admin shares of file servers such as \\MyFileServer\d$

ok. then just test it with the shares folder...good luck

That's what I did and I eventually get the "Permission Denied" error.  <Shrug>  I'm testing it now to see if it works so long as I target any directory below the root.

change the  Function GetDB with this

Function GetDB(strPath As String, _
                dctDict As Scripting.Dictionary, _
                Optional blnRecursive As Boolean) As Boolean
             

   Dim fsoSysObj      As Scripting.FileSystemObject
   Dim fdrFolder      As Scripting.folder
   Dim fdrSubFolder   As Scripting.folder
   Dim filFile        As Scripting.File
   
   ' Return new FileSystemObject.
   Set fsoSysObj = New Scripting.FileSystemObject
On Error GoTo WhatError
   'On Error Resume Next
   ' Get folder.
   If strPath = "C:\System Volume Information" Then Stop
   Set fdrFolder = fsoSysObj.GetFolder(strPath)
   If Err <> 0 Then
      ' Incorrect path.
      GetDB = False
      If Err.Number = 70 Then
        Err.Clear
        GoTo GetDB_End
        Else
        GoTo GetDB_End
      End If
   End If
   ' Loop through Files collection, adding to dictionary.
   For Each filFile In fdrFolder.Files
      dctDict.Add filFile.Path, filFile.Path
   Next filFile

   ' If Recursive flag is true, call recursively.
   If blnRecursive Then
      For Each fdrSubFolder In fdrFolder.SubFolders
         GetDB fdrSubFolder.Path, dctDict, True
      Next fdrSubFolder
   End If

   ' Return True if no error occurred.
   GetDB = True
   
GetDB_End:
   Exit Function
   
WhatError:
    If Err.Number = 70 Then
        Err.Clear
        'Resume Next
        GoTo GetDB_End
    End If
End Function

I've replaced the function and am running it again against one of the admin shares to see what happens.

another thing, you may have to change the dbPath field in the tblDB length to 255

So I'm running on your original code (not using the segment above) and I've copied all the databases from all my file servers into a central location that I know I won't have permissions issues with.

When I run the form against it, I get an error that says "syntax error (missing operator) in query expression [insert file path and name here]"

It happens at the line of code that says "CurrentDb.Execute "insert into tblDB(..."

Ideas?  If you're tired of this issue, you can drop it, but if there's a quick fix, I'd love to have it since I think things will work for me this way being centralized.

file name or folder might have special characters


replace the line with this one


        CurrentDb.Execute "insert into tblDB(dbpath) values(" & Chr(34) & varItem & Chr(34) & ")"

Did that and now I run into a "Could not find file" error where the file name and path appears truncated.

For instance, if the real path was: \\myserver\f$\directory\A12345filename.mdb, it shows as:

Could not find file \\myserver\f$\directory\A12345f

Odd... Thoughts?

And its at "Set db = OpenDatabase..." where it bombs out.

guess you did not see my comment above

http:#a20284026
another thing, you may have to change the dbPath field in the tblDB length to 255

Indeed you are correct, I blew that one.

So one last thing... is there any way to get the "Do Until rs.EOF" loop to ignore errors like invalid passwords, unrecognized database format, etc.?

Some of the DB's apparently have passwords and are corrupted and it would be nice just to skip over them.  Is there code I can change to make that happen?

use this sub

Sub getVersion()
On Error GoTo WhatError
Dim db As DAO.Database, rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblDB")
rs.MoveFirst
Do Until rs.EOF
    Set db = OpenDatabase(rs("dbpath"))
        rs.Edit
        rs("dbVersion") = db.Version
        rs.Update
        db.Close
    rs.MoveNext
WhatError:
    If Err.Number = 3031 Then
        Err.Clear
        rs.MoveNext
    End If

Loop
MsgBox "Done, db files were saved in table tblDB "
End Sub


post here the other error number

The other error is 3031 which is "Not a valid password".  I added it as an 'or' to the if statement in WhatError but it made no difference likely because the error isn't one that comes up UNTIL "Set db = OpenDatabase...." is executed.  So I'm not sure how to catch and deal with that one but that's the other error.

Is there a way to mod the function such that if the database has a password it simply skips it?  Not sure.  You're the expert.

Oops.  the other error is 3343 "Unrecognized Database Format".

I suppose then that the Not A Valid Password error (3031) isn't being handled properly either because it shows up.  <Shrug>  Not sure how the function needs to change to catch it.

So the two errors I've seen are 3343 and 3031.  Phew, sorry for the confusion.

include it here

    If Err.Number = 3031 or err.number=3343  Then
        Err.Clear
        rs.MoveNext
    End If

That's exactly what I did but it doesn't catch either of them.  The errors still throw up a run-time error window.  It's as if it isn't ever getting to the WhatError: label.

test this one

https://filedb.experts-exchange.com/incoming/ee-stuff/5622-dbX1.zip

Okay that one doesn't throw up any errors but it never shows as having completed.  If I open the table, it has all the paths, but the version numbering stops at one of the trouble databases so it appears its getting hung on one of them rather than skipping over it.

sorrry,
find this line on the GetDb function and comment or delete it

   If strPath = "C:\System Volume Information" Then Stop

A little more insight... I went to manually open the database that it appeared to stop on and it says "You do not have the necessary permissions to use the [filename and path] object.  Have your system administrator or the person who created this object establish the appropriate permissions for you."

So obviously there's some internal DB security your code is hitting.  I just wish we could find a way to skip over any database that can't be opened properly due to corruption, permissions, or otherwise.  I'm trying to get an overall look at what version levels my MDBs are at so I don't need every single one.  Just getting most of them would be sufficient.

I can't believe you've stuck with this as long as you have.  Thanks so much for your help capricorn.  I don't expect it to continue but if you're persistent at trying to get it nailed, this little tool you're making could be used by thousands of people that I know are looking for the solution based on my googling.

I commented that out but no difference.  It doesn't appear that that is what is stopping it.

I just tried to run it against another directory and the DB that it stopped on (when opened manually), throws the "Unrecognized Database Format" error.  So it's as if isn't throwing error windows now but isn't continuing when one DOES happen.

are you getting the list of dbs stored  to tblDB?

Yes.  It pulls all the paths properly and they aren't truncated.

The versioning begins to work as well but if you go down the column, stops at a certain database.  (It's different for each directory I scan).

The common factor is that when I go to open the database it stopped on manually by doing File -> Open, I can an error like "Unrecognized Database Format" or some permissions error.  So your code is working, it just isn't CONTINUING when an error occurs.  It doesn't throw up an error window anymore but it doesn't move on to the next database and continue putting the version numbers in.  It just seems to sit there.

Okay, major progress.  Now it gets all the way through and thet table is complete (with the exception of the ones that had errors).

The three errors that come up are 3420, 3033, 3197.  I scanned a variety of directories and those are the major common ones.

Also, I'm noticing versions that show 2 and 1.1, what do those correspond to?  Access 95 and prior?  EEK!  Hope not!  ;-)

they were the earlier version of access db (2 and 1.1) prior to Acc95

Sweet.  Well I just commented out the "Msgbox" line and it proceeds all the way through now ignoring the ones that it can't deal with.

You deserve a beer, a cup of coffee, you name it.  If you were in the Seattle area I'd offer it up!  :)

Thank you so much for your help.  It is immensely appreciated... I'll drop a comment here if anything changes but it appears to be running just the way I need for now.

Phenominal work!

i am not in Seattle but i am in Seatac...

Well I'm in Olympia!  Do you reside in SeaTac?

yep...

Well then... I suppose I'll have to drop a line here the next time I'm on my way up to ClaimJumper for dinner.