Here is a detailed article that shows you how to extract important information about your Access installation (and the version of Access that your application is running under). This is very relevant to users of the Workbench
 

Extracting Your Access Software Configurations

In this article you will find out how you can also log information about the Access Software Configurations running on your users computers. This will help you run your database in a more regulated fashion. The sorts of information that I will describe are the location of the Access database, the version of the Access executable and the Jet Engine DLL and the value of the Sandbox mode. By retrieving this information you will be able to make sure that everyone is running the Access database the same way. This can ensure that they are using the latest service packs of Access and Jet.

Setting Up Access Examination Software

The details that we are going to concentrate on to determine the “up-to-date-ed-ness” of your Access software are Jet Version, Access Version, Access Executable Version, Access Executable Path and the Sandbox mode. These are all values that are useful to look into if your database is giving you problems. Whilst I m sure many of you have stumbled across code that return this sort of information before, you may have seen that code in the old world mode of Windows API programming. If that is the case, then you, like me, probably thought that this sort of information really wasn’t worth the effort to program. In the following examples I will show you how you can use newer Windows tools in such a way that you don’t even need to include a reference in your database. As the information that I am collecting on Access and Jet Engine versions is probably not required all the time, you can keep this software in a separate database or just turn it on occasionally.

<< click on the picture for a sample of what is logged

When you purchase the workbench, you will receive a database called accesslogging.mdb. As this article is part of a two part article, a lot of the software in the database may not make immediate sense to you. If you have a copy of my book on Access protection, you will find the download database is a further refinement of some of the logging downloads discussed in chapter 6. Anyway, for this article you really only need to refer to is the module called FX_GetAccessDetails.

Retrieving The Version Of The Jet Engine DLL

If you are like me and are now starting to run into a number of sites that are deploying Access 2003, you will probability be aware of that hellish dialog box that turns up when you try to run Access 2003 for the first time. The prompt in that dialog box says “Security Warning: Unsafe Expressions are not blocked”. The dialog box then goes onto to explain that the user needs to update the Jet Engine to Service Pack 8. OK, those explanations all make sense for you and me as It specialists but for users they are a complication that they may not be able to fix. At one site where I work, the systems administrators are not very highly trained and generally seem to be able to install Office and Access with all the latest service packs. As Microsoft decreed at some stage that this particular security Warning was not important enough to include Jet 8 as a mandatory upgrade, the poor old Access 2003 user is left to work this one out on their own. In this particular instance, the workers, whilst quite smart, seem to accept their lot in life and never ever report a bug and guess what; they have to wade through these Jet Service pack security warnings every time they open Access.

I digress, let’s look at how we can retrieve the version of the Jet Engine so that we can monitor this problem and come to the user’s aid. To do this we have to interrogate the Jet 4 DLL file and thankfully this is always stored in the same location in the system32 folder of Windows. Of course if Windows is not installed on the C drive then the following code will need to be altered and as my example shows, that old warhorse, NT, has a slightly different file path for storing the Jet driver. So let’s look at a function that will retrieve the version of Jet.

Function JetVersion_FX() As String

'Return the version of Jet that the user is using
'Use late binding so that you do not need a reference

Dim strVersion As String, strVersion2 As String

On Error Resume Next

strVersion = GetFileVersion_FX( _
 "c:\windows\system32\msjet40.dll")
If Len(strVersion) = 0 Then
  strVersion2 = GetFileVersion_FX( _
  "c:\winnt\system32\msjet40.dll")
  If Len(strVersion2) > 0 Then
    strVersion = strVersion2

  End If

End If

If Len(strVersion) = 0 Then
 strVersion = "Version unknown"

End If

JetVersion_FX = strVersion

End Function

The code illustrated above relies on a function that will retrieve the version number of any program file. We will now look at that function.

Finding The Version Of A File

As you would have seen in the previous section, I have used a subroutine called GetFileVersion_FX to retrieve the version of the Jet engine. This function which is detailed below uses the Windows File Systems Object to retrieve the version information from the file. In this case I have used late binding so that I don’t have to make a reference to the FileSystems object in the database. What that means is that I have declared a variant called objFSO and then used the CreateObject method to late bind the FileSystems object. Yes this is slower but as this is used every now and again, then this really doesn’t matter.

Public Function GetFileVersion_FX(FilePath As String) As String

'Return the version of a file
'Use late binding so that you do not need a reference
Dim objFSO, strVersion As String

On Error Resume Next

Set objFSO = CreateObject("Scripting.FileSystemObject")

strVersion = objFSO.GetFileVersion(FilePath)

If Len(strVersion) > 0 Then

  strVersion = Trim$(strVersion)
Else strVersion = ""
End If

GetFileVersion_FX = strVersion

Set objFSO = Nothing

End Function

The really great thing about this file version subroutine is that you can use it to retrieve the version of any program that you or your users has permission to view. For example, you could find the version of my Workbench program with the following piece of visual basic

StrWBvers = GetFileVersion_FX _
("c:\program files\vb123\workbench.exe")
msgbox “Version of program is “ & strWBvers

In case that you are wondering what the latest version of Jet is, at the time this went to press, you would probably should have either "4.0.8015.0" or “4.0.8018.0" on your computer. These are Jet Engine 8 that are mentioned in the Access 2003 startup screens. And in case you are wondering if this really is a problem, on one site we have logged six different versions of the jet engine. As we have been having problems with corruptions on one database, we have restricted the editing of data on complex forms to those users who are running the latest version of the jet engine. We haven’t seen the problem since but that could be just good luck.

Retrieving The Sandbox Mode

The next piece of vital intelligence that I demonstrate is how to retrieve the sandbox mode that is in your users registry. As I alluded to in the previous section on Jet Engines, Access 2003 really does like to get involved with the sandbox mode and even though this particular “security” approach has been around for quite a while, 2003 brought it to the fore because it turned it on as the default. For my mind there are occasions where I just have to know what the sandbox setting is or some of our software will not run. In particular a system where we incorporate formulas into a big export system (based on queries) failed with sandbox on and the client really wasn’t too keen on us rewriting the software. So we had to work out when a user had the sandbox mode turned on and just stop them until the sandbox was emptied (so to speak).

That’s why we did it, let’s now look at the code that retrieves the sandbox entry from the registry. In this case I am going to use another library called Windows Scripting that makes retrieval of the registry item a piece of cake. Once again I have used late binding so that I don’t have any issues with registration of the Windows Scripting library in the software database. You can also note that the functions will return either the numerical sandbox value or you can also retrieve a text description of the Sandbox Mode through the optional argument of the function.

Public Function SandboxMode_FX( optional sandboxDescription As String) As Integer

'Find the current sandbox setting in the registry

Dim strAccessVersion As String
Dim objShell, strSandBox As String

On Error Resume Next
Set objShell = CreateObject("WScript.Shell")

'Read the sandbox mode from the registry
strSandBox = objShell.RegRead( "HKEY_LOCAL_MACHINE\Software\Microsoft\" & _ "jet\4.0\engines\sandboxmode")

Select Case strSandBox

  Case 0

    sandboxDescription = "Sandbox mode is disabled at all times."

  Case 1

    sandboxDescription = "Sandbox mode is used for Access applications," _
     & "but not for non-Access Applications."

   Case 2 sandboxDescription = "Sandbox mode is used for non-Access" & _
    "applications, but not for Access Applications."

   Case 3 sandboxDescription = "Sandbox mode is used at all times"

End Select

SandboxMode_FX = strSandBox

AccessVersion_FX_exit:

  Set objShell = Nothing

End Function

You can find out a lot more about this sandbox issue if you read the article I wrote on this topic for the May 2004 edition of Smart Access. One could speculate at this stage that a security measure based on a registry key is probably not all that secure. Of course if you are running a Windows Server, you should be able to talk to your Server administrator to have the sandbox mode locked through a Windows Policy. Leave that one in your court; me I just want to know when my software will fail and Sandbox mode can be an important factor deciding my software’s fate.

Finding Out About The Access Executable

As you will be well aware, Microsoft has released at least two versions of each Access executable since well before the world of viruses and scammers came to plague us. Generally this hasn’t really affected things all that much because Microsoft does a good job of making sure that old software works as before. Lately Microsoft has been forced to backtrack in some instances; mostly these changes have been brought about in the name of security. One great example was the changes to Outlook that followed all those viruses that liked to use contacts lists to extract email addresses for further maliciousness. After that particular backtrack, anyone who had Access software that used mailing lists or sent large number of text emails would have found themselves with customers whose software stopped working. Given this unnatural progress of Access software, it is always a good thing to know which version of Access a user is using. Thankfully with Access 2002, the Application object of Access introduced two new properties to tell us about the version status of Access. The following code which is snipped from the AccessVersion_FX function in the download database shows you how to make a full build number for Access.

strAccessVersion = Application.Version
exeVersion = strAccessVersion & "." & Application.Build

For Access 2002 on my machine, that returns a build number of “10.0.6501”. Now for earlier versions of Access such as 2000 or 97, you would have to use the GetFileVersion_FX function that I described earlier to retrieve the build number. As I have covered that ground before, I will now show you how you can retrieve the location of the Access executable on the users machine. For that I will use the Wscript library once again to read the Registry. In the VBA code that follows I have stripped out most of the code relating to other versions other than Access 2002 (10.0).

Dim strAccessVersion As String

Dim objShell, strMSAccessPath As String

On Error Resume Next

strAccessVersion = Application.Version

Set objShell = CreateObject("WScript.Shell")

Select Case strAccessVersion

  Case "8.0"

    AccessVersion_FX = “Access 97

  Case "9.0"

    AccessVersion_FX = “Access 2000"

  Case "10.0"

    AccessVersion_FX = "Access 2002"
    strMSAccessPath = objShell.RegRead( "HKEY_CLASSES_ROOT\Access.Application.10\" & _
     "shell\open\command\")
    strMSAccessPath = AccessExePath(strMSAccessPath)

  Case "11.0"

    AccessVersion_FX = "Access 2003"

  Case "12.0"

    AccessVersion_FX =”Access 2006”

End Select

exePath = strMSAccessPath

AccessVersion_FX_exit:

Set objShell = Nothing

End Function

What the RegRead method (see above) of the Windows Scripting library does is return the command line path that Access uses to start the database. In the following line you will see that the path that is returned also has some other variables such as /Nostartup and %1.

"C:\Program Files\msofficexp\Office10\MSACCESS.EXE" /NOSTARTUP "%1"

As this is the only registry item that actually tells you where a particular Access executable is, we just have to strip out all the characters that are not related to the executable path. This function follows.

Function AccessExePath(strCmdPath As String) As String

'Extract the excutable path from the startup command line

If Len(strCmdPath) > 0 Then

  strCmdPath = Left(strCmdPath, _
   InStr(1, strCmdPath, ".EXE", 1) + 3)
  If Left(strCmdPath, 1) = Chr(34) Then

    strCmdPath = Mid(strCmdPath, 2)

  End If

AccessExePath = strCmdPath Else AccessExePath = "" End If

End Function

As we have the path to the executable, we now have a way to extract the version number directly from the file. This is something that you will need to do with Access 2000 or Access 97.

Knowing what versions of Access and where those versions of Access are is good intelligence when you are battling with issues relating to who is running what and why. One of the things that I like to use the path to the Access executable for is to gauge how easy it would be to setup a single shortcut on the file server to run the database with workgroup security. If I find that everyone is actually using an Access executable in the one place, then I know that I can setup a single security shortcut that will work for all users. Unfortunately if the installation path of Access are fairly random, then I know that will be battling to keep the shortcut files synchronized.

What Are The Latest Versions of The Access Exectuable

As of end of June 2005, these are the latest version of Access. Access 2000 is on service pack 3 build 9.0.0.6620 Access 2002 is on service pack 3 build 10.0.6501 Access 2003 is on service pack 1 build 11.0.6355.0

No doubt these will be changed by the time you actually pull this article out of your in-box and relax on the sofa for a good long read.

Useful Further Reading and Resources

Garry’s book on Access security and protection includes a lot of detail on logging and monitoring users in chapter 6 and some details on setting up a system table in chapter 3.

Garry’s article on Sandbox expressions that appeared in April 2004 of Smart Access.

Rick Dobson’s article on Access 2003 Macro security in April 2004 of Smart Access.

Conclusion

That brings us to the conclusion of the interesting things that you can find out about the configurations of Access that are using your database. As of now you can be the expert in retrieving the true version details of a program on another computer, you can find out the Sandbox mode state on a computer before your software falls over and you make sure that everyone is singing from the same song sheet when it comes to Access and the Jet Engine. You should now also have the skills to go hunting around the registry for those little gems of information that Access and Office stores to describe itself.

Demonstration Databases For Registered Users Of The Workbench (3)

When you purchase the Access Workbench, you will receive a number of demonstration databases that can improve how you run your database. These include internal logging of user details, how to shutdown your database and send messages to your users and how to setup and retrieve the version information for your database.

This is included in the downloads when you purchase the Workbench. The database for this article is called

accesslogging.mdb

Advanced User Logging

In a similar article, I will demonstrate how you can take this this information and store it in a database every time a user logs into your database. When you couple this with other information like the time and user details of your users, you will be a wiser person when comes the time to make management decisions about your database.

Release Versions Details

You will receive a demonstration database that shows you how to display release details for "your Access database". Read more

Other Access Workbench Topics
Home Page | What is an LDB File | Advanced Ways To Start The Workbench   | Orders

The Access Workbench Help File  - Internal Database Logging