5

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?

Community
  • 1
  • 1
cswingle
  • 585
  • 1
  • 8
  • 13
  • Access has ADO & DAO, although its DAO native. Give us an example of what kind of Dynamic Append SQL statement you are trying to do. In the past i have built helper functions to assist in the building of Access SQL statements – GoldBishop Sep 26 '12 at 18:32
  • As far as how Access views a SQL statement, it is a very basic SQL statement engine. You will notice, outside of some framework syntax, it isnt very robust. You can do Insert, Delete, Drop, Select, and some Access-specific statements but that is all. The ability to do Batch Execution is very frustrating (left Access for MS SQL and now back to Access for a client). The only way to get the Batch process effect is via a VBA Macro and RecordSet's. – GoldBishop Sep 26 '12 at 18:40
  • 1
    @GoldBishop: I updated the question. The full details are in the other question I reference. – cswingle Sep 26 '12 at 18:45
  • First question, what was the result of the Select statement (minus Insert clause)? As well, based on your linked question, this is the case in all DBMS's, they cache various components but basically the same operation on all of em, whether you see the execution plan say it or not. Thought about doing a RecordSet data pull and then iterating through it for inserting? – GoldBishop Sep 26 '12 at 19:00
  • You could try http://dbaspot.com/ms-access/137827-access-2007-showplan.html – Fionnuala Sep 26 '12 at 19:34
  • The result is the same when it's just a "simple" `SELECT`: Access asks the remote server for every row in the table without the `WHERE` clause. FYI, when the date range is specified statically in the query (`> #1/1/2012#`, for example), this passes through to the remote server as a `WHERE` clause. I just want the date to come from the last updated date in the local table instead of statically. – cswingle Sep 26 '12 at 19:41

3 Answers3

5

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.

Jacob
  • 1,423
  • 16
  • 29
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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
0

You can use this article without working hard, or knowing what registry to enter, it makes everything you need to make it work, and to find the file.

https://www.access-programmers.co.uk/forums/threads/jet-showplan-manager.302921/

I tried it and it works perfect.

The first thing is that there is an access-file to download that makes the hard work of looking what folder in the resistry to add the value of the debugging system, and thats the main thing you need in your question.

There are 2 files to download so use the most relevent file for your access version.

Second, it gives you the option to select the drive and folder you want to save the log file generated by JET-ShowPlan.

user18625097
  • 1
  • 2