Determine version of every Access Database on the LAN

We maintain hundreds of Access databases on our LAN.  We are moving to Office 2013 and I need to determine in which version of Access each database was saved.  Access 2010 and 2013 apparently cannot open all versions of mdb file, so I need to know which ones I need to convert.  Does anyone have the code for me to determine in which version each mdb and accdb is saved?  Ideally, I need to run the code from one database that will loop through all the filed on the LAN (including subfolders) and save the file name, path, and version to a table.  The link below contains another solution that loops through and tells me if a certain file type is linked to the databases.  If the version could be collected during this process, that would be great.

Solution to determine if DBF files are linked
LVL 1
upsfaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Attached that is a DB that's a start (scans directories and saves info on them).

Someone posted another version a while back that does the same thing and was a little newer, but I can find it at the moment.

But the framework is all here for doing what you want.  It just needs to be updated for newer DB's.

Jim.
FindDBs.zip
0
upsfaAuthor Commented:
Right, the framework is there, but i cant figure out the code to determine the database version and how to integrate the code.
0
Rey Obrero (Capricorn1)Commented:
you can use
application.Version -- will give you the  office version
11.0 for 2003
Currentdb.version  - will give you the Access engine version
4.0 for 2003



Access 2000,2002,2003 are all Version 4

Database Engine      Version (year released)      Microsoft Access
Microsoft Jet      4.0 (2000)                         2000 (9.0)
Microsoft Jet      4.0 (2002)                         2000 (10.0)
Microsoft Jet      4.0 (2003)                         2000 (11.0)
Microsoft Ace    12.0 (2007)      


..
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

upsfaAuthor Commented:
Thanks, but I don't know where to put it in the code.
ption Compare Database

Function FindDatabases(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
'On Error Resume Next
Dim FSO As Scripting.FileSystemObject

Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim rs As dao.Recordset
Dim rs2 As dao.Recordset
Dim strsql As String
Dim strpath As String
On Error Resume Next

    Set FSO = New Scripting.FileSystemObject
    
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    
    For Each FileItem In SourceFolder.Files
      If FileItem.Name Like "*.mdb" Or FileItem.Name Like "*.accdb" Then
      strpath = FileItem.Path
strsql = "Select [name], [database] from msysobjects in '" & strpath & "' where left(connect,5) = 'dBase'"
Set rs = CurrentDb.OpenRecordset(strsql)
If rs.RecordCount < 1 Then
rs.Close
GoTo Jump
End If
Set rs2 = CurrentDb.OpenRecordset("tblDBs")
rs.MoveFirst
Do While Not rs.EOF
rs2.AddNew
rs2!SourcedbName = strpath
rs2!LinkDBFName = rs![Database] & "\" & rs![Name]
'rs2!Connect = rs!Connect
rs2.Update
rs.MoveNext
Loop
rs.Close
rs2.Close
Jump:

End If
Next
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            FindDatabases SubFolder.Path, True
        Next SubFolder
    End If
End Function


'here is where you execute
Function FindDBFConnect()
Dim strfilepath As String
strfilepath = BrowseFolder("Browse to selected folder")
FindDatabases strfilepath, True
End Function

Open in new window

0
upsfaAuthor Commented:
Somewhere around line 24, but not sure how to call Currentdb.version,
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
That's not part of the utility I posted.  This utility looks like it's trying to find out where tables are connected to.

Is this your own that your adding to?

Jim.
0
upsfaAuthor Commented:
Funny, I could not open the file you attached, but I thought it was the same file that was in the link that I posted the names are very similary.  So, this is not your code.  When I try to open the file you sent I got the message "you are trying to open a read-only database".  Then I saved the zip, extracted and now I get prompted to convert to a new version.  When I do that, I get all sorts of errors.  I have Access 2010.
0
Rey Obrero (Capricorn1)Commented:
try this codes to get the db version

Dim fldr As String, dbFile, dbx As DAO.Database
fldr = "C:\FolderName\"
dbFile = Dir(fldr & "*.mdb")
While dbFile <> ""
     Set dbx = DBEngine.OpenDatabase(fldr & dbFile)
     Debug.Print dbFile, dbx.Version
     dbx.close
     dbFile = Dir
Wend
0
upsfaAuthor Commented:
It's a start.  It only seems to run on my HD.  Drive mappings to the LAN don't seem to work.  I also tried using the server name.  Also, it does not go into sub folders, or look for acccdb.
0
Rey Obrero (Capricorn1)Commented:
Function FindDatabases(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
'On Error Resume Next
Dim FSO As Scripting.FileSystemObject

Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim dbx As dao.database
Dim strpath As String
On Error Resume Next

    Set FSO = New Scripting.FileSystemObject
   
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
   
    For Each FileItem In SourceFolder.Files
      If FileItem.Name Like "*.mdb" Or FileItem.Name Like "*.accdb" Then
      strpath = FileItem.Path
             Set dbx = DBEngine.OpenDatabase(strPath)
             Debug.Print FileItem.Name, dbx.Version
             dbx.close
      end if
      Next
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            FindDatabases SubFolder.Path, True
        Next SubFolder
    End If
End Function


'here is where you execute
Function FindDBFConnect()
Dim strfilepath As String
strfilepath = BrowseFolder("Browse to selected folder")
FindDatabases strfilepath, True
End Function


.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
upsfaAuthor Commented:
tweaked the code to pull in the full path of file.  would like to direct results to an access table (or a csv file) .  Thanks very much.

'here is where you execute
Function FindDBFConnect()
Dim strfilepath As String
strfilepath = BrowseFolder("Browse to selected folder")
FindDatabases strfilepath, True
End Function
0
Nick67Commented:
Access 2010 and 2013 apparently cannot open all versions of mdb file, so I need to know which ones I need to convert.

As far as I know, all mdb files should be able to be opened and used.  Support for ADP files has been completely removed from A2013.  In the newer versions, the ability to modify some objects has been impacted -- but not their functionality.

The biggest PITA for me was the deprecation of the Calendar control -- although I have successfully been able to make that work in both A2013 and A2010, damn their eyes anyway!  The murdering of PivotCharts and PivotTables may be of more concern to others.

A2013 will no longer open mdb's created by A97.
You can no longer open an Access 97 database in Access 2013. You'll need to upgrade the file. To do this, open it in Access 2010 or Access 2007, save it in the .accdb file format, and then open it in Access 2013.

This change also affects databases that are linked to Access 97 databases.
On the other hand, Access 97 databases were problematic right from the release of A2000 --  maybe you have some unconverted ones -- but I doubt it!

But strictly looking at version numbers alone is probably not going to tell you much.  There's an article here about what has been 'improved'
http://office.microsoft.com/en-us/access-help/discontinued-features-and-modified-functionality-in-access-2013-HA102749226.aspx

Be aware that the VBA references on files silently update -- A reference to MS Office Excel 11.0 will update to MS Office Excel 15.0 when you open the file in A2013.  It does NOT downgrade, however.  I would suggest that as you look through your .mdb files for deprecated features that you do so in A2003.  Do it in A2013 and you may get a lot of phone calls about broken files!
0
upsfaAuthor Commented:
Capricorn1,

Thanks, this works great.  Only problem is when we encounter a folder on the LAN that we dont have rights for.  The Select Data Souce dialogue box pops up and the code stops.  Can you add something to handle the Access Denied error?

Thanks
0
Rey Obrero (Capricorn1)Commented:
add error handling codes


On error goto ErrHnd


'processing codes here



ErrHnd:
If err.number=<Place error number here> then
    err.clear
    resume next
end if

end function
0
upsfaAuthor Commented:
I got this to work, but the strange part is that I placed the error # 1 as a place holder until I determined what the actual error number is, but it worked fine with the number 1.

Function FindDatabases(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
'On Error Resume Next
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem    As Scripting.File
    Dim dbx         As dao.Database
    Dim strpath     As String
   
   'New error handling line below
   On Error GoTo ErrHnd
   
   'Old error handling line removed below
    'On Error Resume Next



    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
        If FileItem.Name Like "*.mdb" Or FileItem.Name Like "*.accdb" Then
            strpath = FileItem.Path
            Set dbx = DBEngine.OpenDatabase(strpath)
            Debug.Print FileItem.Name, dbx.Version, strpath
            'add to CSV
            fCSV.WriteLine FileItem.Name & "," & dbx.Version & "," & strpath
            dbx.Close
        End If
    Next
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            FindDatabases SubFolder.Path, True
         Next SubFolder
        
'New error handling between *********

'***************
ErrHnd:
If Err.Number = 1 Then
Err.Clear
   Resume Next
End If
'************
 
 End If
End Function

Open in new window

0
Nick67Commented:
Look at the placing of your final 'end if'

    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            FindDatabases SubFolder.Path, True
         Next SubFolder
       
'New error handling between *********

'***************
ErrHnd:
If Err.Number = 1 Then
Err.Clear
   Resume Next
End If
'************
 
End If

9 for 10 you could comment out everything between ''ErrHnd:" and that final end if, and it would also work.  The net effect of that misplaced 'end if' is to skip the errorring line and carry on -- which incidentally you were trying to do anyway.
0
upsfaAuthor Commented:
Perfect solution!
0
Nick67Commented:
@upsfa

Not that the points are a big deal, but @capricorn1 supplied the code you are using, and should probably get the lion's share of the points.  Up near the top, where the question is posed and assigned to zones, you could click on 'request attention' and have the Q reopened, and reallocate the points if you so desire.

Nick67
0
upsfaAuthor Commented:
Agreed.  I did not even realize that it was not Capricorn1 who sent the last response.  

BTW - if i remove everything between ErrHnd: and the final End If.  The program stops when I encounter an error.  If i leave the Resume Next in place, it seems to work.  Still testing.

ErrHnd:
  ' If Err.Number = 1 Then
  ' Err.Clear
    Resume Next
  'End If
End If
0
Nick67Commented:
Have you thrown in
MsgBox err.number & " " & err.description
so you can document the error number you want to clear?
0
upsfaAuthor Commented:
Thanks for the help, much appreciated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.