Making Startup DB that detects Office version then opens correct version of DB

heres my take on this. If you know which is which then simply create a new file association in windows and give the database files new extensions.

MyDb.AXP for instance, so associate the filenname extension XPA with Access XP.
or
MyDb. A2k could be associated with access 2k and you  can leave mdb associated with XP.

John

A bit long but here it goes.

Option Compare Database
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
Private Declare Function GetFileVersionInfoSize Lib "version.dll" Alias "GetFileVersionInfoSizeA" (ByVal lptstrFilename As String, lpdwHandle As Long) As Long
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 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)

Function OfficeXP() As Boolean
    Dim vffi As VS_FIXEDFILEINFO  ' version info structure
    Dim buffer() As Byte          ' buffer for version info resource
    Dim pData As Long             ' pointer to version info data
    Dim nDataLen As Long          ' length of info pointed at by pData
    Dim cpl(0 To 3) As Byte       ' buffer for code page & language
    Dim cplstr As String          ' 8-digit hex string of cpl
    Dim dispstr As String         ' string used to display version information
    Dim RetVal As Long            ' generic return value
   
    ' First, get the size of the version info resource.  If this function fails, then Text1
    ' identifies a file that isn't a 32-bit executable/DLL/etc.
    nDataLen = GetFileVersionInfoSize(GetOfficeDir & "msaccess.exe", pData)
    If nDataLen = 0 Then
        Exit Function
    End If
    ' Make the buffer large enough to hold the version info resource.
    ReDim buffer(0 To nDataLen - 1) As Byte
    ' Get the version information resource.
    RetVal = GetFileVersionInfo(GetOfficeDir & "msaccess.exe", 0, nDataLen, buffer(0))
   
    ' Get a pointer to a structure that holds a bunch of data.
    RetVal = VerQueryValue(buffer(0), "\", pData, nDataLen)
    ' Copy that structure into the one we can access.
    MoveMemory vffi, ByVal pData, nDataLen
    ' Display the full version number of the file.
    dispstr = Trim(Str(vffi.dwFileVersionMSh))
    ''debug.print "Access Version: " & dispstr
    If dispstr <> "10" Then
      OfficeXP = False
    Else
      OfficeXP = True
    End If
   
End Function

carlangaslangas,
I get a user-defined type not defined on the "Public Property Get Parent() As clFormWindow" and a

not defined error on the "GetOfficeDir".  
Is there a reference I need to add?

Will this work on Windows NT?

jobrienct,
I am looking for a way for the user to use one file regardless of their version of access to open up the correct front-end based on the version of office on their machine.  Via VBA the 'opening file' would determine the Office version.
Thanks,
Rich

carlangaslangas,
forget about the clFormWindow...that was another thing I was testing.  Still getting the GetOfficeDir error though.
Rich

sorry, i read the question wrong. You can determine the installed version from the registry.

you don't really need a database to open to determine this do you? How about a vbscript attached to an icon that looks up the access version in the registry and then opens the right file accordingly?

heres a bit of code that does the lookup in the wrong language, it would need to be converted to vbscript, but its pretty easy stuff.

Function RegRead(sRegValue)
  On Error Resume Next
  RegRead = oShell.RegRead(sRegValue)
  ' If the value does not exist, error is raised
  If Err Then
    RegRead = ""
    Err.clear
  End if
  On Error Goto 0
  ' If a value is present but uninitialized the RegRead
  ' method returns the input value (Way to go MS!).
  If  RegRead = sRegValue  Then
    RegRead = ""
  End if
End Function


the key that needs to exist is:

'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE',

but you need the msaccess version, theres a key for that, looking.

John

jobrienct,
Thanks.  I know next to nothing about VBScript.  Sounds like a good technique though if it will open the correct DB.  Would need some help in getting it working.

I found a good site that does what I want, however the VBScript would be nice.  Here it is if anyone is interested.  Determines the SP release as well:
http://www.mvps.org/access/api/api0065.htm

hrm, what im seeing is that you can determine the installed version by testing the path. heres some air code to try things with in the vbscript above...

if NOT IsNull(RegRead('HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access')) Then
  strVersion='A97'
End If
If NOT Isnull(RegRead('HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Access')) Then
  strVersion ='A2k'
End If
If NOT Isnull(RegRead('HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\10.0\Access')) Then
  strVersion ='AXP'
End If

John

>>How about a vbscript attached to an icon that looks up the access version in the >>registry and then opens the right file accordingly?

Ok, how do I go about this?  Never worked with VBS before, fairly comfortable with VBA though.
Rich

sorry, the comment that says the bit of code was not vbscript, well that is vbscript, i replaced the code and failed to edit the comment. this is vbscript:

PickMdb.vbs
--------------

Function RegRead(sRegValue)
  On Error Resume Next
  RegRead = oShell.RegRead(sRegValue)
  ' If the value does not exist, error is raised
  If Err Then
    RegRead = ""
    Err.clear
  End if
  On Error Goto 0
  ' If a value is present but uninitialized the RegRead
  ' method returns the input value (Way to go MS!).
  If  RegRead = sRegValue  Then
    RegRead = ""
  End if
End Function


you would just need to add a few lines to test for the existence of the keys and if one exists run the file associated with that version.

John

vcery similar, im looking at it now. basically you just create a file named CheckAccVer.vbs and attach it to your program icon.



in it you test for the existence of a registry key - you actually dont want to read it as above, in the registry these keys would return null, you want to check its existence, so heres that bit so far, sRegKey would be the string to match from above. so you need to call it with the key to test and if it returns true, run the progrma and load the file with the shell command. seems pretty straight forward.

get back with more in a few, can you ell me if the key for Access XP is as above? use regedit and follow the path, it should be 10.0 but i dont have it.


Function RegKeyExists(sRegKey)
  Set oShell = CreateObject("WScript.Shell")
  RegKeyExists = True
  sRegKey = Trim (sRegKey)
  If Not Right(sRegKey, 1) = "\" Then
    sRegKey = sRegKey & "\"
  End if
  On Error Resume Next
  RegReadReturn = oShell.RegRead(sRegKey)
  If Err Then
    If LCase(Left(err.description,7)) = "invalid" Then
      'key not found...
      RegKeyExists = False
    End if
    Err.clear
  End if
  On Error Goto 0
End Function

John

ok so heres what I did that works so far. Run Notepad and paste the following:

strKey1 = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Access"
strKey2 = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\10.0\Access"

bKeyExists = RegKeyExists(strKey1)

MsgBox bKeyExists

bKeyExists = RegKeyExists(strKey2)

MsgBox bKeyExists


Function RegKeyExists(sRegKey)
  Set oShell = CreateObject("WScript.Shell")
  RegKeyExists = True
  sRegKey = Trim (sRegKey)
  If Not Right(sRegKey, 1) = "\" Then
    sRegKey = sRegKey & "\"
  End if
  On Error Resume Next
  RegReadReturn = oShell.RegRead(sRegKey)
  If Err Then
    If LCase(Left(err.description,7)) = "invalid" Then
      'key not found...
      RegKeyExists = False
    End if
    Err.clear
  End if
  On Error Goto 0
End Function

Name the file ChkAccVer.vbs and save it to the desktop.

dclcik the icon it creates. I get 2 msgBoxes, True, False. this is accurate for my system since I have A2k and not AXP installed.

looking at the shell command to load the file now.

John

Yes, XP is 10.0.  I was about to get back to you about the null return string.  It was saying I had 97, 2000, XP...

Works perfectly to detect the Version.  Thank you.  
How do I open another file with VBS?
ie. file "c:\test2000.mdb"

And how do you attach an icon to the script?
Rich

Im seeing some indication that WsShell.Run might be able to just run the file and let the association do the rest, so try that too.

John

Thank you for all of your input.  I have most of it working, it opens up access.  The only slight problem I have is opening up the db.  I currently have a Win shortcut with this command line:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\test" /user %USERNAME% /wrkgrp "\\Fileserver\ACCESSWGSHARE.MDW"

How can I join the mdw as I load up access with this vbs?
Rich

Oh, forget it.  I just open up the same windows shortcut I had before, and it works perfectly!  Thank you for you time, excellent solution!  Increasing points.
Rich

glad you liked it, I enjoyed it to. Its good to get out (of access) once in awhile :)

John