Geek asks and answers

How to get query plans (showplan.out) from Access 2010?

I am trying to figure out how to run a dynamic append query in Access 2010 with an ODBC database table (see question 12592953 ), and someone suggested enabling showplan debug output: techrepublic link . But Access 2010 / accdb databases apparently don't use the Jet engine, so that doesn't help me.

Update: This is the request I'm trying to get (full description is given in another stackoverflow related question above). It is supposed to update the local copy of the table with new rows from the deleted copy. But what happens is access, which pulls out the entire remote table (which is huge and calls ODBC before the timeout), then runs the local one WHERE.

INSERT INTO local (dt, latitude, longitude)
SELECT dt, latitude, longitude
FROM remote_odbc, (SELECT max(dt) AS max_dt FROM local) AS sub
WHERE remote_odbc.dt > max_dt;

Is there something similar to the Jet / Debug / showplan registry entry in Access 2010 for more information on how Access views the request?

+5
source share
2 answers

Thanks @Fionnuala

For Access 2010 32 bit, you will need the following key:

On Windows 7 32 bit:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

or on Windows 7 64 bit:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

I needed to create a folder Debugfor entering the key.

+5
source

You will need the following key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

The above file is exported from my Windows 7 registry with Access 2010 access and creates showplan.out for it.

+2
source

More articles:


All Articles