Toad World® Forums

Run and schedule a query created in Microsoft Access


#1

Hi,
I am suing TDA 1.1, I established the connection to my access database, my query is visible under the Views TAB; once I click twice on my query, the Object Details window become available. To see the results, I am clicking on Data tab. I would like to run automatically this query, so I clicked on Send to Report. The option tells me, the query needs to be run first, so the query runs but I do not see any result.
Please tell me what I am doing wrong. My objective is to run the query and create a schdule task so it will run automatically and the results can be sent by email to the end user. Thanks a lot.
Please note, I also try to get around by putting my query in the query builder section, but when I run the query I get the attached error. Thanks in advanced for any comment on this issue.
aduc-container-is-there.png


#2

oops. I meant I am using Toad Data for Analysis 1.1 version, please review the details on my problem in previous post. Thanks a lot


#3

We currently do not support MS Access View/Queries as such. You will need to copy and paste the query into an editor or Query Builder and save that file. A .SQL/.TSM file containing the SQL statement can be scheduled to be automated.

I created CR 51,231 as a future enhancement.

Debbie


#4

Version 2.0 of Toad for Data Analysis will be coming out shortly. In this version there is a rigth click menu option to Execute the query. If parameters are needed it will ask for this and create a SQL file for you in an editor. This can be saved and used in an automation script.

let me know if there are any additional items needed for automation tasks with Access.

Debbie


#5

/*Executing procedure Sales by Year

Forms!Sales by Year Dialog!BeginningDate DateTime Input

Forms!Sales by Year Dialog!EndingDate DateTime Input

*/

execute Sales by Year #1994-01-01 00.00.00#,#1994-12-01 00.00.00#

This can now be used in Automation. If your query does not contain variables you are set. If you are using variables manually edit your file to send the values.

Debbie

I have a small correct to the last posting. I am using NWidn.mdb sample access database and testing with query ‘Sales by Year’.

In version 2.0 the right click execute sends the following query to the editor and then prompts for the variables.

execute Sales by Year ?,?

Since the variables are not stored in the script, this can not be used in TDA Automation.

In version 2.1 the same steps ask for the variable values first and create the following script.


#6

Hi,

I can run the access query in TDA now, but if the query contains if statements then this is not recognized by the query in TDA. For example the access query has the following, if([des2]like ‘EX’, “Exchange”, “REPAIR”. When the query runs in TDA, the results do not match the results from access. Any idea?