Automate....

[kusamharsha]

Hi All,

I use to run daily 110 access queries for users. All queries are passthru queries so every time when i click on query it will ask me password, so for each query i have to enter password and it will run.



I have to run each query and output i have to export into excel and then save it.

Is there any way to automate it so that all queries will run and and each queries output is saved in excel in particular location plss... help mee..

Thanks
Harsha

[tinytree]

Maybe dao programing resolve your problem,can you unload your database?

[kusamharsha]

Hi Tiny,

I had already written VBcode.... my code works fine for normal queries.. but all my queries using case, substr, and sooo.. so if you see access won't understand case, substr.. if you want access to run such queries then you have to make them as passthru queries.. then the query will work fine.. if i want same query to run thru VB it's throwing error (sytax error) .. so my problem is how to handle such queries in VB...

Below is the one sample query i provided for you ref ....


Sample query
------------

select d.DEAL_FOLDER_STATUS, d.VALUE_DATE, d.BUSINESS_DATE,
case
when d.BUY_SETL_TYPE_IND = 11
then 'NET PEND'
when d.BUY_SETL_TYPE_IND = 12
then 'NETTED '
when d.buy_setL_type_ind = 15
then 'NET AS GROSS'
when d.buy_setl_type_ind = 19
then ' NET GO GROSS'
when d.BUY_SETL_TYPE_IND = 21
then 'GROSS PEND'
when d.BUY_SETL_TYPE_IND = 26
then 'GROSS AGGR'
when d.BUY_SETL_TYPE_IND = 22
then 'GROSS AD HOC'
when d.BUY_SETL_TYPE_IND = 29
then 'GROSS '
when d.BUY_SETL_TYPE_IND > 40
then 'CLS '
ELSE cast(d.buy_setl_type_ind as char(2))
end as setl_type
,d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type
,d.BUY_CCY_ID, d.BUY_AMOUNT, d.SELL_CCY_ID, d.SELL_AMOUNT, d.DEAL_RATE, tm.acct_ccy_equiv_amt,tm.TRADE_SOURCE_ID
, case
when tm.trade_source_id = 'RMS'
then (substr(tm.fo_deal_id,5,10))
else tm.fo_deal_id
end as fo_trade_id
,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind
, d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id
from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm
where
cle.company_id = c.company_id
and cle.cpty_id = c.cpty_id
and d.company_id = cle.company_id
and d.legal_entity_id = cle.legal_entity_id
and d.cpty_id = cle.cpty_id
and c.record_state = 'V'
and cle.cpty_id LIKE 'ABSA JB%'
and cle.record_state = 'V'
and d.record_state = 'V'
and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8))
and s.deal_folder_id = d.deal_folder_id
and s.ccy_id = d.buy_ccy_id
and tm.trade_id = d.trade_id
and tm.trans_id = d.trade_trans_id
and tm.ver_id = d.trade_ver_id
order by d.value_date , SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID , d.CPTY_ID

[tinytree]

Yes,It isn't work at access sql. you can reference dao and excel,then in your program
you can define variable like this:
dim dbs as database
dim rst as recordset
dim mybook as workbook
dim myexcel as excel.application
set dbs=dbengine(0).opendatabase("your database's full path")
set rst=dbs.openrecordset("your table or sql")
set myexcel=new excel.application
set mybook=myexcel.workbooks.open("your excel's full path")
and then you can mainipulate your database and export some data to excel