2

I was looking for a way to log queries from a 3rd party proprietary ms-access database application that contains a front-end and a backend.

The database runs in MS-Access 2003. I read and followed the instructions mentioned in an article suggested in the answer to this question, with the intent of creating the showplan.out file to find out which tables were being accessed by a query.

When finding the registry key specified in the article: \\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines I noticed that the path was not present beyond JET, so I created the rest of the path including the DEBUG key with it's value set to ON.

I also know that the Office installation that I am using has multiple versions of Office / Access installed (2003 and 2010) and that someone in the past, before I got here, likely used a registry hack to enable the installation of both versions.

Oh, and "My Documents" where Showplan.out is supposed to appear is stored on an external roaming profile server as well.

Is it possible to get the Showplan.out to output on Windows 7 64-bit in some other way, or am I better off installing Office, on a clean VM, copying my databases on, and trying it from there?

Community
  • 1
  • 1
leeand00
  • 25,510
  • 39
  • 140
  • 297
  • 1
    Try : http://stackoverflow.com/questions/12607296/how-to-get-query-plans-showplan-out-from-access-2010/12639283#12639283 It works for me on Access 2010, 64 – Fionnuala Sep 28 '12 at 19:26
  • Since you have a Split database....you could place Admin controls on the Front-End copy and have it spit out the file to your desktop while your in "Admin" mode. And FYI, if your Net-Admin setup Roaming correctly, the Profile Server isnt updated until you log-off. So until you Log-Off/Restart the computer, the file is still local in your profile path. – GoldBishop Sep 28 '12 at 20:14
  • Oh and i would probably setup a Dev box, if you cant get Office installed on your desktop (VM or Not). Probably best to have a 2k3/2k7/2010 VM (if you have the CPU budget in VM for it). That is if you have those versions of office on your network. – GoldBishop Sep 28 '12 at 20:17

1 Answers1

3
Debug.Print CurDir

That will print the current working directory as Access sees it. That is where you will find showplan.out. By default, this will be your My Documents directory, but there's no guarantee.

It looks like you have set the correct path and key in the Windows registry. I believe the location @Remou linked to in his comment is for Access 2007 and later (i.e., .accdb files).

UPDATE: Since you are on Windows 64, the actual registry key should be this:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Debug]
"JETSHOWPLAN"="ON"
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • Oh *ahem* yes...to further complicate things...it's Access 32 running on Windows 7 64-bit... – leeand00 Sep 28 '12 at 20:39
  • @leeand00: I am running Access 32 (specifically Access XP/2002) on Windows 7 64-bit and that key works correctly for me. – mwolfe02 Sep 28 '12 at 21:03
  • 2
    That whole `Wow6432Node` is the "Windows-on-Windows" registry hive for 32-bit programs running in a 64-bit environment. – mwolfe02 Sep 28 '12 at 21:05
  • @leeand00 make sure you have an understanding of how Win 64 runs 32-bit. Think of how Win 7 emulated Win XP applications, but more of a VM styled execution. Everything runs in 64 bit just the System dumbs down certain Windows Components in order for the 32-bit app to run. I learned the hard way....best to run 64 on 64 and 32 on 32....try to stay away from 32 on 64, just because you can does not always mean you should. – GoldBishop Sep 28 '12 at 21:24
  • 1
    @GoldBishop "IMPORTANT The 32-bit version of Office 2010 is the recommended option for most people" -- http://office.microsoft.com/en-us/word-help/choose-the-32-bit-or-64-bit-version-of-office-HA010369476.aspx – Fionnuala Sep 28 '12 at 22:11
  • I didn't have choice in all this...it was what I was given because for some reason I have a 64-bit system and all my users have 32, it was just kind of dropped into my lap. – leeand00 Sep 29 '12 at 01:51