Solved

Determine Version of Database Programatically

Posted on 2004-04-29
15
1,427 Views
Last Modified: 2012-05-07
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
Comment
Question by:deannamaio
  • 9
  • 5
15 Comments
 
LVL 2

Expert Comment

by:lightcross
Comment Utility
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
 

Author Comment

by:deannamaio
Comment Utility
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
 
LVL 2

Expert Comment

by:lightcross
Comment Utility
sure thing.  I will do it first thing when I get in.
0
 
LVL 11

Expert Comment

by:LambertHeenan
Comment Utility
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
 
LVL 2

Expert Comment

by:lightcross
Comment Utility
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
 
LVL 2

Expert Comment

by:lightcross
Comment Utility
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
 

Author Comment

by:deannamaio
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:lightcross
Comment Utility
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
 

Author Comment

by:deannamaio
Comment Utility
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
 
LVL 2

Expert Comment

by:lightcross
Comment Utility
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
 

Author Comment

by:deannamaio
Comment Utility
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
 
LVL 2

Expert Comment

by:lightcross
Comment Utility
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
 
LVL 2

Accepted Solution

by:
lightcross earned 400 total points
Comment Utility
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
 

Author Comment

by:deannamaio
Comment Utility
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
 
LVL 2

Expert Comment

by:lightcross
Comment Utility
im glad it worked out for you.  isnt that program awesome?  which program did you guys buy, listmdb or accver?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now