Determine version of every Access Database on the LAN

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

Right, the framework is there, but i cant figure out the code to determine the database version and how to integrate the code.

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)      


..

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

Select allOpen in new window

Somewhere around line 24, but not sure how to call Currentdb.version,

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.

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.

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

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.

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

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!

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

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

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

Select allOpen in new window

Perfect solution!

@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

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

Have you thrown in
MsgBox err.number & " " & err.description
so you can document the error number you want to clear?

Thanks for the help, much appreciated.