Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

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

Setting MsAccess Showplan ON and OFF in the registry from Visual Basic.
1. Access uses JETSHOWPLAN setting in the Registry Key
   [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet]
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?
0
DbOnTrack
Asked:
DbOnTrack
  • 4
  • 2
  • 2
1 Solution
 
peter57rCommented:
" Access uses JETSHOWPLAN setting in the Registry Key
   [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet]"

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.
0
 
DbOnTrackAuthor Commented:
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
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Debug]
"JETSHOWPLAN"="OFF"

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]]
"JETSHOWPLAN"="ON"

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

0
 
peter57rCommented:
Are you using Access97 then?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DbOnTrackAuthor Commented:
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?
0
 
Leigh PurvisDatabase DeveloperCommented:
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
\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug
Have a look at, say, an article like this:
http://articles.techrepublic.com.com/5100-10878_11-5064388.html
For Registry manipulation, well there are load of examples out there.  Literally any search for VBA Registry.
MS documentation on the API functions: http://msdn.microsoft.com/en-us/library/ms724875(VS.85).aspx

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

0
 
DbOnTrackAuthor Commented:
100% Pass, spot on what I was looking for. Thank you - I can move on now.
0
 
DbOnTrackAuthor Commented:
Thank you,
Leigh,
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





0
 
Leigh PurvisDatabase DeveloperCommented:
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).
Cheers.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now