[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1452
  • Last Modified:

Determine Version of Database Programatically

Hi.  At my company, we have about 3500 Access Databases.  We are in the process of converting our existing databases from 97 to 2000.  I am looking for a tool to tell me what version of Access each database is written in without opening each one.  

Does anyone know of a tool or some code I can where I can automate trying to find the version?  I don't want to have to open each one individually.  

Thank you so much,
Deanna
0
deannamaio
Asked:
deannamaio
  • 9
  • 5
1 Solution
 
lightcrossCommented:
I have an exe that I created to do this.  I also have posted the code but if you dont have vb to generate the exe file let me know.  I can email you the exe file if needed.  I took this from a paq and fixed it up to work with access, plus there were some errors in the code.


Option Explicit


Private Declare Function GetFileVersionInfo Lib "Version.dll" Alias "GetFileVersionInfoA" (ByVal lptstrFilename As String, ByVal dwHandle As Long, ByVal dwLen As Long, lpData As Any) As Long


Private Declare Function GetFileVersionInfoSize Lib "Version.dll" Alias "GetFileVersionInfoSizeA" (ByVal lptstrFilename As String, lpdwHandle As Long) As Long


Private Declare Function VerQueryValue Lib "Version.dll" Alias "VerQueryValueA" (pBlock As Any, ByVal lpSubBlock As String, lplpBuffer As Any, puLen As Long) As Long


Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, ByVal Source As Long, ByVal Length As Long)


Private Type VS_FIXEDFILEINFO
    dwSignature As Long
    dwStrucVersionl As Integer ' e.g. = &h0000 = 0
    dwStrucVersionh As Integer ' e.g. = &h0042 = .42
    dwFileVersionMSl As Integer ' e.g. = &h0003 = 3
    dwFileVersionMSh As Integer ' e.g. = &h0075 = .75
    dwFileVersionLSl As Integer ' e.g. = &h0000 = 0
    dwFileVersionLSh As Integer ' e.g. = &h0031 = .31
    dwProductVersionMSl As Integer ' e.g. = &h0003 = 3
    dwProductVersionMSh As Integer ' e.g. = &h0010 = .1
    dwProductVersionLSl As Integer ' e.g. = &h0000 = 0
    dwProductVersionLSh As Integer ' e.g. = &h0031 = .31
    dwFileFlagsMask As Long ' = &h3F For version "0.42"
    dwFileFlags As Long ' e.g. VFF_DEBUG Or VFF_PRERELEASE
    dwFileOS As Long ' e.g. VOS_DOS_WINDOWS16
    dwFileType As Long ' e.g. VFT_DRIVER
    dwFileSubtype As Long ' e.g. VFT2_DRV_KEYBOARD
    dwFileDateMS As Long ' e.g. 0
    dwFileDateLS As Long ' e.g. 0
End Type




Public Function CheckFileVersion(FilenameAndPath As Variant) As Variant
    On Error GoTo HandelCheckFileVersionError
    Dim lDummy As Long, lsize As Long, rc As Long
    Dim lVerbufferLen As Long, lVerPointer As Long
    Dim sBuffer() As Byte
    Dim udtVerBuffer As VS_FIXEDFILEINFO
    Dim ProdVer As String
    lsize = GetFileVersionInfoSize(FilenameAndPath, lDummy)
    If lsize < 1 Then Exit Function
    ReDim sBuffer(lsize)
    rc = GetFileVersionInfo(FilenameAndPath, 0&, lsize, sBuffer(0))
    rc = VerQueryValue(sBuffer(0), "\", lVerPointer, lVerbufferLen)
    MoveMemory udtVerBuffer, lVerPointer, Len(udtVerBuffer)
    '**** Determine Product Version number *
    '     ***
    ProdVer = Format$(udtVerBuffer.dwProductVersionMSh) & "." & Format$(udtVerBuffer.dwProductVersionMSl)
    CheckFileVersion = ProdVer
    Exit Function
HandelCheckFileVersionError:
    CheckFileVersion = "N/A"
    Exit Function
End Function


Private Sub Command1_Click()
'Example to use this function
MsgBox "MSACCESS Version is " & CheckFileVersion("C:\Program Files\Microsoft Office\Office\msaccess.exe")
End Sub
0
 
deannamaioAuthor Commented:
I can't generate the vb so, if you could email the exe file that would be great.  As soon as i get it, i will accept your comment as the answer.  Can you email me at deanna@savvygals.com
0
 
lightcrossCommented:
sure thing.  I will do it first thing when I get in.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LambertHeenanCommented:
lightcross:

All very nice and wonderful code, but it tells you the version of MSACCESS.EXE, or other executable files. What was asked was how to tell the version of an MDB file. Not sure how to do that myself, except that this kBase article illustrates it

http://support.microsoft.com/default.aspx?scid=kb;en-us;321329

But not having VC++ I can't make use of it myslef.
0
 
lightcrossCommented:
ahh...dang it.  3500 databases?  that is alot of databases!  hmmm.

deanna, are the databases all stored on a single pc?  what is the path to these mdb files?  you want to know what version each mdb file is written in (97 or 2000)?
0
 
lightcrossCommented:
you can use either one of these to get the version of the current mdb:

Private Sub Command5_Click()
MsgBox SysCmd(7)
MsgBox (SysCmd(SYSCMD_ACCESSVER))
End Sub

if i knew of the path(s) to all the db's then maybe you could check them all from a central location.
0
 
deannamaioAuthor Commented:
They databases are all stored on the same machine. If the code can search subdirectories then, this might work.  the Path would be \\jeeves\sys\u  and the databases are in sub-folders of that path.  

And you had it right, I need to know the version each mdb file is written in.  

I don't know if the path listed above is considered, "central" but, let me know if you can give it a shot.

I have increased the points for this question.  

Thank you,
Deanna
0
 
lightcrossCommented:
the problem is that the databases are in subdirectories.  do you know how far they go back?
is it like
\\jeeves\sys\u\sub1 or \\jeeves\sys\u\sub1\sub2\sub3

and after you discover the many mdb version what do you plan on doing then?
0
 
deannamaioAuthor Commented:
as we discover which are left in 97, they will be coverted to 2000.  As for the subdirectories, it could bo down 4 levels.  I tried the exe you sent.  Put it into the \\jeeves\sys\u\ directory and it didn't work.  Is it possible that we could make it unspecific to the path, or add a dialog box where I type in the path.  I would also be willing to grant the point for a URL to a website for a product that I can purchase for under $100.00.  

Thanks for all your help, Deanna
0
 
lightcrossCommented:
as far as i know there is no such product on the market.  however i will try to do something to solve your problem.  what do you envision the interface looking like?  a box where you type the path?  wouldn't that be too much typing?  how about a root path you type in and then a listbox that shows all the mdb in subfolders?
0
 
deannamaioAuthor Commented:
Sorry it has taken me so long to respond...was away from a pc for a few days.  I don't care how much typing it takes, and the interface can be very elementary...as long as there would be a place to type in the path and the output makes reference to the file name and the Access version it uses...the rest doesn't matter to me.  

Once again, thank you so much for your help with this.
0
 
lightcrossCommented:
as i looked further into things .MDB files do not have information in it that includes the version of access used to create it.  well, at least not if you right click it.  so the standard mathod of viewing versions of files such as .EXE or *.DLL cannot be used for this.  Did you get the email I sent to you?
0
 
lightcrossCommented:
I found it!

This guy has 2 different programs on his site.  

1.  listmdb - Creates a list of ALL MS Access databases on servers, workstations, CDs etc together with the date created/modified, the list of users and the Access version.. Invaluable when upgrading Access to ensure that every database has been identified and converted.
 
2. accver - Allows you to work with multiple versions of MS Access. Detects the version of Access used to create your MDB, MDA or MDE files and either reports the version number or starts the appropriate version of MS Access.

http://www.aylott.com.au/accver.htm

The programs are very inexpensive but EXCELLENT!

Good luck

Vic
0
 
deannamaioAuthor Commented:
Thank you for all of your work.  You have found the exact solution we were looking for. We have purchased a license and I have the program running right now.  

Thanks again for your hard work.  
0
 
lightcrossCommented:
im glad it worked out for you.  isnt that program awesome?  which program did you guys buy, listmdb or accver?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now