I have a weekly task that involves exporting data for various users. I use a template database, created with Microsoft Access. The export includes SELECT * against (7) different tables the largest of which as about 1.3 million rows.This has been working fine in version 12.9 and 12.10.
The newest version of TOAD has a really annoying regression, in my opinion. If this change was made as a result of a request, then an option should have been provided to select the desired behavior. Each and every query I run results in the name of the table + ".mdb" as the default export database. For each query, I have to re-select the correct template file, which is a .accdb file format (not .mdb). Otherwise, I just spend a bunch of time creating (7) different .mdb files, instead of appending the results to empty tables in my single .accdb template file.
The first image shows the desired output filename (IRCS_Records.accdb):
However, after running a SELECT * on a table [e.g. SELECT * from custrequest_di_mbom; ], and then click on the Export Dataset button, I see the following:
No, I did NOT want the export file name automatically changed!
Also, please correct the misleading message shown below. The file does not get replaced depending on user options selected in the previous dialog; instead one appends rows to a table:
I noticed similar behavior when exporting to Excel. Also, the default Excel format should be the newer .xlsx format, not the older .xls format. We shouldn't have to always remember to specify the newer file extension.
Thank You
Hi Tom,
A change was made when you rt-click a dataset in the Editor and then choose “Export” so that it finds the table name in your query and then uses that as the file name. It used to just leave the last used file name there. I can see how this is annoying because you actually WANT the last used file name, which differs from the table name. Seems like the best behavior here would be to find the file name that you used last time you exported this query, but I think it’s too late for something like that in this beta cycle. I can look at that for the next version. For now I can at least make it remember the last used file extension for access and excel. It should be doing that already.
However, if you are exporting the same 7 tables every week, you can save yourself a lot of work by setting this up in the Automation Designer. Create all the actions in one app, then you can just run the app each week to run all of the actions. It would be just a couple of clicks to export all 7 tables.
-John
Hey Tom,
Edit Toad.ini with notepad. Find [SAVEAS]. Under it, add this line:
AccessExt=.accdb
That will make it default to .accdb for access.
Regarding the prompt: “File exists, do you want to replace it?” - this has come up before and I agree it would be nice to change it. Unfortunately, that text is just part of the “save file” dialog and there is no way that I can change it. I could make it not prompt at all, but I think that might cause problems for other users.
Hi John,
Are there any tutorials on using the Automation Designer that you mention? I didn’t even know about this possibility.
Sorta related, but perhaps I should open a new thread (?)…
Please consider allowing one to export to the 32-bit version of MS Access, when using 64-bit TOAD. I had to create a virtual machine, with Office 2013, 64-bit, so that I could export to Access. TOAD allows one to cross the bit-boundry when exporting from 64-bit TOAD to 32-bit Excel, so if you can do it for Excel, why not Access?
My preference is to stay with 64-bit TOAD, so that the TOAD application is not limited to just 2 GB of RAM.
Tom
Thank You.
I don’t know why, but it took me three tries to get this setting to “stick”. Here is an example I captured:
First, I added the line you indicated. I think I did this correctly. I did indeed save the file.
Then, I closed TOAD completely and re-opened it , just in case this file is only read during startup.
I would run a query, try exporting to Access, and find that the default file extension was .mdb! Whaaaaat?? So, I re-opened TOAD.ini, and that’s when I found my edit was now missing!
Finally, on the third try, the change stuck and the default extension in the export dialog was correctly displaying .accdb. Really strange that it took three tries. Oh well.
Tom
Hi John,
Regarding the prompt: "File exists, do you want to replace it?"....
Wouldn't it be possible to change the text conditionally, depending on what one has selected under "If file exists"?
So, if they have the choices shown above with Append rows, and a table is found with the automatically detected table name, then suppress the warning message.
However, if they have the selection shown below, then display the warning message:
Thanks,
Tom
I had problems changing the prompt, but I found a way to remove it. So it will be gone next beta when exporting to Excel File or MS Access. Whatever you configure in the options (prompt, append, overwrite, etc) will still happen when you hit “run”.
I don't know why, but it took me three tries to get this setting to "stick".
Sorry, I should have mentioned it - you should only make changes to Toad.ini while Toad is not running. Toad rewrites the file when it closes.
Are there any tutorials on using the Automation Designer that you mention
Yes, just google "Toad automation designer export dataset" and you should find plenty. Or look in Help -> Contents and search for Automation Designer there. It's late here now, but I'll write up some quick steps for that tomorrow if you need them.
Please consider allowing one to export to the 32-bit version of MS Access, when using 64-bit TOAD
Believe me, I'd like to. But my only option to write to MS Access (that I'm aware of) is to use the MS Access Runtime engine, which means bitness has to match. When I write to Excel, I have some components that can create the file directly, without even having Excel installed. If I knew of a way to get around this bit-matching nonsense, I'd do it.
Hi John,
Yes, just google “Toad automation designer export dataset” and you should find plenty. Or look in Help -> Contents and search for Automation Designer there. It’s late here now, but I’ll write up some quick steps for that tomorrow if you need them.
Yesterday, I found several tutorials on Google, as you indicated. I started working with one in particular, by Kuljit Sangha posted Dec. 6, 2011, since it is on this ToadWorld.com site:
https://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2011/12/06/using-automation-designer-for-everyday-tasks
However, I quickly ran into an “issue” that is befuddling me at the moment. I will start a new thread, as it is only tangentially related to this thread.
I had problems changing the prompt, but I found a way to remove it. So it will be gone next beta when exporting to Excel File or MS Access. Whatever you configure in the options (prompt, append, overwrite, etc) will still happen when you hit “run”.
Excellent, Thank You.
