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

问题:

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

Update: This is the query I'm trying to get working (a full description is in the other stackoverflow question linked above). It is supposed to update the local copy of a table with the new rows from the remote copy. But what happens is Access pulls the entire remote table (which is huge and causes ODBC to timeout), then runs the WHERE locally.

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 hack in Access 2010 for getting more information about how Access views a query?

回答1:

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 the folder Debug to put the key in.

回答2:

You will need the following key:

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

The above is exported from my Windows 7 registry running Access 2010, and produces showplan.out for me.