How do I get my Ms Access CurrentProject to use the Registry key created by SetRegValue instead of the normal Microsoft Jet Key?

Posted on 2009-04-24
Last Modified: 2012-05-06
Setting MsAccess Showplan ON and OFF in the registry from Visual Basic.
1. Access uses JETSHOWPLAN setting in the Registry Key
2. The SetRegValue Solution creates a new Register Key
   [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\"CurrentProject.Name"]
  Call SetRegValue("[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]", "JETSHOWPLAN", "ON")
How do I get my CurrentProject to use the Registry key created by SetRegValue instead of the normal Microsoft Jet Registry Key?
Question by:DbOnTrack
    LVL 77

    Expert Comment

    " Access uses JETSHOWPLAN setting in the Registry Key

    Not here it doesn't - there's no JetShowPlan key on my machines at that location.
    There's only the one you mention in point 2.

    Author Comment

    Hi Peter57r,
    Thank you for your reply.
    Below is the registry setting used by MsAccess on my machine - when I maually edit this setting the Showplan.out file is created. I cannot set this ON or OFF from code.

    Windows Registry Editor Version 5.00

    When I used : SaveSetting CurrentProject.Name, Section, Key, sValue
    The following keys were created in the Registry - when set to "ON" and tested I cannot find the showplan.out file anywhere.

    Windows Registry Editor Version 5.00
    [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ApmPrgFa.mdb]
    [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ApmPrgFa.mdb\[HKEY_LOCAL_MACHINE]
    [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ApmPrgFa.mdb\[HKEY_LOCAL_MACHINE\SOFTWARE]
    [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ApmPrgFa.mdb\[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft]
    [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ApmPrgFa.mdb\[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet]
    [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ApmPrgFa.mdb\[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5]
    [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ApmPrgFa.mdb\[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines]
    [HKEY_CURRENT_USER\Software\VB and VBA Program Settings\ApmPrgFa.mdb\[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Debug]]

    I don't know how to get my "CurrentProject" to use this setting that I now have code control of.
    DbOnTrack - Alan

    LVL 77

    Expert Comment

    Are you using Access97 then?

    Author Comment

    No I am using Access 2003.
    I understand that VB will only allow changes to the Registry under the Key "VB and VBA Program Settings"
    MsAccess itself does not use these Keys.
    Can the Registry be edited by Windows API calls or only manually be RegEdit.exe?
    LVL 44

    Accepted Solution

    Hopefully Peter is off doing something enjoyable on this fine Saturday morning.
    I agree with his questions though - your needs seem slightly unfocused.

    By that I mean when you ask
    "get my CurrentProject to use the Registry key created by SetRegValue instead of the normal Microsoft Jet Registry Key"
    Ultimately, the CurrentProject object has nothing really to do with the use of ShowPlan - if that's what your main concern is.
    If your request is not related to the CurrentProject object per se but instead just regarding getting Access (in fact Jet) to accept another registry location to use for the ShowPlan flag then that's not possible.  The engine looks in that location.
    The SetRegValue function is really just merely a convenience as a built in function in VBA.
    If you want to manipulate the registry in other locations then, indeed, API calls are more standard.  (There are other routes too - but that which springs to mind I think you'd always need Admin privileges for).
    Your key to set should always be to
    Have a look at, say, an article like this:
    For Registry manipulation, well there are load of examples out there.  Literally any search for VBA Registry.
    MS documentation on the API functions:

    For one of those admin functions I mentioned...

    Sub SetShowPlan(blnOn As Boolean)
        Dim objRegistry As Object
        cKeyPath = "SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug"
        Set objRegistry = GetObject("winmgmts:\\.\root\default:StdRegProv")
        objRegistry.SetStringValue &H80000002, cKeyPath, "JETSHOWPLAN", IIf(blnOn, "ON", "OFF")
        Set objRegistry = Nothing
    End Sub

    Open in new window


    Author Closing Comment

    100% Pass, spot on what I was looking for. Thank you - I can move on now.

    Author Comment

    Thank you,
    Your code sample  is exactly what I was looking for. I felt I was being led off Track a bit.

    I have tested the solution and it works pefectly.
    I am now able to capture the Showplan for a single query in its own file.
    I will now able to test a whole process that uses multiple queries and capture both the Isam Stats and the Showplan details in a single file as well.
    Thanks a million.
    I love working with MsAccess from 1.1 to 2003 not sure about 2007 yet.
    Db0nTrack - Alan

    LVL 44

    Expert Comment

    by:Leigh Purvis
    Perhaps the reading material you'd been examining beforehand had confused the issue for you and had led you off track?
    I'm convinced Pete wasn't - he was asking valid questions to establish what you were doing. (For example your earlier registry path referred to "Jet\3.5" - which, as he pointed out would have applied to Access 97).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now