Using VBA to export a pass-through query 1

[shauntwo]

Hi,

I'm using an Access front-end form to dynamically create some SQL from our Oracle server via a generic pass-through query, due to the fact that the query can routinely concern several thousand records that would otherwise consume a lot of time using Jet SQL. At any rate, I would like to automatically transfer the spreadsheet (using something like the DoCmd.TransferSpreadsheet method) to Excel, where I can then run some VBA to clean and format the sheet. However, I get an error in Access when my VBA module hits the TransferSpreadsheet action, that tells me something like "Operation is not supported for this type of object." Using Jet SQL to create the query is not really an option, due to potential size, so what else can I do to automatically throw the resulting datasheet into Excel?

Thanks for your help.
Shaun

 

[scriverb]

The transferspreadsheet method requires an access table to transfer from into your external spreadsheet. Why don't you use your generic pass-through query as input to a Make-Table Access query and create a temporary table? Then execute the TransferSpreadsheet action to export the data to the spreadsheet. You can finally delete the tempoary table from your database.

I hope I understood your problem. I believe that by running the pass-through on the SQL server you are taking advantage of the selection and speed process thus eliminating the burdent to the Jet. Now that you have selected the small group of records with the pass-through the ACCESS Make-Table can handle the process to create the table.

If I have missed the issue please get back with me and give me more information. Bob Scriver

 

[shauntwo]

Bob,

You probably understand my problem perfectly - it's me who's the novice here, but let me clarify my situation by including the heart of my problem code:

strSQL = strSELECT & strFROM & strWHERE

CurrentDb.QueryDefs("GenericPassThrough&quot.SQL = strSQL

DoCmd.TransferSpreadsheet acExport, 8, "GenericPassThrough", _
"C:\Documents and Settings\txtw\My Documents\Other Reports\Entity Report.xls", True

My GenericPassThrough is the saved pass-through query, obviously. Moreover, the only reason I'm querying the server directly is because of the time I save, dropping my usual query time from as much as half an hour to three seconds. Now, I know that had this been a regular select query, the transfer method would work fine. But you're suggesting instead that I convert the resulting pass-through to a temporary table, and then transfer the table to Excel. Forgive my VBA ignorance, but how would I go about doing that?

Thanks for your quick response.

Shaun

 

[scriverb]

Create a query with the following SQL:

SELECT GenericPassThrough.* INTO tblTempRecords
FROM GenericPassThrough;

After you have setup your GenericPassThrough with the updated SQL then execute this query through code:

docmd.openquery "qryMakeTableQuery"

Now you have a table that you can perform the TransferSpreadsheet command on instead of trying to use the passthrough query.

Give this a try.
Bob Scriver

 

[shauntwo]

Bob,

Thanks for your tips - that does the job great! A star for you is in order.

Shaun

 

[scriverb]

Shaun, Glad to be of assistance. I was pretty sure that the pass-through query was your problem. The ACCESS help on the TransferSpreadsheet indicates the need for a table in that parameter. I have not used it much but I would question even using an ACCESS query to be used as the parameter. It is simple to do what we came up with and you can delete the table right after the exporting process.

Thanks for the star. Bob Scriver