Garry shows you how to extract important information about your Access installation (and the version of Access that your application is running under).
In this article you’ll find out how you can log information about the Access software configurations running on your users’ computers. Why do you care? I can think of at least two reasons:
• By retrieving this information, you’ll be able to make sure that everyone is running your Access application in the same way and track down installation and version-related problems.
• You can ensure that your users have installed the latest service packs for both Access and Jet.
As I’ll demonstrate in this article, knowing this information can let you solve some very thorny problems. After reading this article, you’ll be able to determine:
- The location of your Access database.
- The version of the Access executable running on your computer.
- The version of the Jet engine DLL.
- The Sandbox mode for your Access version.
As my list indicates, the details that I’m going to concentrate on will allow you to determine the “up-to-datedness” of your Access software: the Jet version, Access version, Access executable version, Access executable path, and Sandbox mode. These are all values that are useful to look into if your database is giving you problems. At one site where I work, even the systems administrators aren’t very highly trained and don’t seem to be able to install Office and Access with all the latest service packs. As you can imagine, determining what packs are installed is a key issue to ensuring reliable behavior for your application.
While you may have stumbled across code that returns this sort of information before, that code was probably written in the “old-world” mode of Windows API programming. If that’s 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’ll show you how you can use newer Windows tools in such a way that you don’t even need to add a reference in your application. Since the information that I’m collecting on Access and Jet engine versions probably isn’t required all the time, you can keep this software in a separate database or just turn it on occasionally when you’re trying to track down a problem.
The accompanying download includes database AccessLogging.mdb. As this article is part of a two-part series that includes last month’s “Logging Access Startup Details and Version Control,” some of the code in the database will only make sense if you’ve read both articles. For this article you really only need to refer to the module called FX_GetAccessDetails. If you have a copy of my book on protecting Access applications, you’ll find the download database is a refinement of some of the logging downloads discussed in chapter 6.
Jet engine version
If you’re like me and are now starting to run into a number of sites that are deploying Access 2003, you’ll be aware of that hellish dialog box that turns up when you try to run Access 2003 for the first time–the one with the prompt “Security Warning: Unsafe Expressions are not blocked.” The dialog box then goes on to explain that the user needs to update the Jet engine to Service Pack 8. Those explanations all make sense for you and me as IT specialists, but for users they’re a complication that they may not be able to fix.
Because Microsoft decreed that the security problem flagged by this dialog wasn’t important enough to actually include Jet 8 as a mandatory upgrade, the poor old Access 2003 user is left to work this one out on his or her own. At the site with the naïve system administrators, the users (while quite smart) seem to accept their lot in life and never ever report a bug. Guess what–they have to wade through these Jet service pack security warnings every time they open Access. Spotting this problem in a logging report and providing a solution will make you the hero to every user.
To retrieve the Jet engine version, you have to interrogate the Jet 4 DLL file. Thankfully, the Jet DLL is always stored in the same location in the Windows System32 folder. There are potential problems: If Windows isn’t installed on the C drive, then the following code will need to be altered; as my example shows, that old warhorse, NT, has a slightly different file path for storing the Jet driver.
The function that retrieves the Jet version looks like this:
Function JetVersion_FX() As String Dim strVersion As String Dim 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
This code relies on a function that will retrieve the version number of any program file. So, in the next section, I’ll look at that function.
File version
This uses the Windows File Systems Object to retrieve the version information from the file. In this case I’ve used late binding so that I don’t have to add a reference to the FileSystems object in the database. I’ve declared a variant called objFSO and then used the CreateObject method to late bind the FileSystems object. This results in slightly slower performance (not a critical factor in an infrequently used application) but ensures that I avoid problems with accessing different versions of the File Systems Object:
Public Function GetFileVersion_FX( _ FilePath As String) As String Dim objFSO As Object Dim 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 have permission to access. For example, you could find the version of my Workbench program with this code:
StrWBvers = GetFileVersion_FX _ ("c:\program files\vb123\workbench.exe") msgbox "Version of program is " & strWBvers
In case you’re wondering what the latest version of Jet is, if your Jet DLL has version number 4.0.8015.0 or 4.0.8018.0 then you have the latest version (at the time this article went to press). These versions are the “Jet Engine 8” versions that are requested in the Access 2003 dialogs.
And, in case you’re wondering if this really is a problem, on one site I’ve logged six different versions of the Jet engine. Remember that each user accesses the application’s back-end database using whatever version of Jet is on their computer. If users have different versions of Jet on their computers, then the result is that a single back-end database may be being updated by many different versions of the Jet engine. If this sounds like a recipe for trouble, you’re probably right. Because my client had been having data corruption problems on one database, I restricted data editing on complex forms to those users who were running the latest version of the Jet engine. Since that change, I haven’t seen the corruption problem recur (but that could be just good luck).
Sandbox mode
Even though Sandbox mode has been around for several versions of Access, it was Access 2003 that really brought it to the fore because Access 2003 turned it on by default. There are occasions where you have to know what the Sandbox setting is–in several of my applications, if Sandbox mode is enabled my software won’t run. In particular, an application where I incorporate formulas into an export query fails if Sandbox mode is turned on (and my client really wasn’t too keen on having me rewrite the software to avoid the problem). So, I had to develop a method that allowed me to check whether Sandbox mode was turned on and stop the user from proceeding until it was turned off.
To extract this information, I’m going to use the Windows Scripting library to make retrieval of this Registry item a piece of cake. Once again I’ve used late binding so that I don’t have any issues with registration of the Windows Scripting library. You can see that the functions will return either the numerical Sandbox value or a text description of the Sandbox mode through the function’s optional argument:
Public Function SandboxMode_FX( _ Optional sandboxDescription As String) As Integer 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 for the May 2004 issue of Smart Access. One could speculate (I do) that a security measure based on a Registry key isn’t all that secure. Of course, if you’re running your application on a Windows server, you should be able to talk to your server administrator and have the Sandbox mode locked through a Windows Policy. Regardless of how you handle setting the Sandbox mode, this code will let you know when your software will fail.
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 much because Microsoft does a good job of making sure that new software works like the previous version. Lately, Microsoft has been forced to backtrack in some instances so that the later version breaks applications that ran under the old version; mostly these changes have been brought about in the name of security.
One example was the changes to Outlook to handle those viruses that used the Outlook Contacts list to extract e-mail addresses for further maliciousness. The solution was to pop up a dialog box when an application accessed the Contacts list. After that particular backtrack, anyone who had Access software that used mailing lists or sent large numbers of text e-mails found themselves with customers whose software stopped working.
Given this unnatural progress of Access software, it’s 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 that let you determine which version of Access your application is running under. The following code, which is snipped from the AccessVersion_FX function in the download database, shows how to generate a full build number for Access:
strAccessVersion = Application.Version exeVersion = strAccessVersion & "." & _ Application.Build
For Access 2002 on my machine, this function returns a build number of 10.0.6501.
For earlier versions of Access (for example, Access 2000 or 97), you would have to use the GetFileVersion_FX function that I described earlier to retrieve the build number. To use GetFileVersion_FX, you have to know where the Access executable is. So the next step is to retrieve the location of the Access executable on the user’s computer. To retrieve that information, I use the WScript library to, once again, retrieve values from the Windows Registry. In the VBA code that follows, I’ve stripped the code down to just the code required for versions prior to Access 2002:
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
The RegRead method of the Windows Scripting library’s Shell object used in this function returns the command line path that Access uses to start the database. In the following line you’ll see that the path returned also includes some additional text after the executable path:
"C:\Program Files\msofficexp\Office10\MSACCESS.EXE" /NOSTARTUP "%1"
The next step, then, is to strip out all the characters that aren’t related to the executable path. The code to handle stripping out the excess looks like this:
Function AccessExePath(strCmdPath As String) As String 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 I now have the path to the executable, I can extract the version number directly from the file. Knowing what versions of Access and where those versions of Access are is useful intelligence when you’re battling with issues relating to who is running what and why. For instance, I use the path to the Access executable to gauge how easy it would be to set up a single shortcut on the file server to run the database with workgroup security. If I find that all the users are sharing an Access executable from the same place, then I know that I can set up a single security shortcut that will work for all users. Unfortunately, if paths to the Access executables are random, then I know that I’ll be battling to keep the shortcut files synchronized.
For your reference, as of late June 2005, these are the latest versions 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 have changed by the time you actually receive this article and relax on the sofa for a good long read.
That brings me to the end of the list of interesting things that you can find out about the configurations of Access that are using your database. Now you can be the expert in retrieving the true version details of your program when it’s running on another computer, or when your code is failing because Access is in Sandbox mode (before your software falls over), and you can 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 store to describe themselves.
Useful Further Reading and Resources
Logging Access Startup Details and Version Control
Remote Updates of a Back-End Database
The FileSystemObject