Help running multiple MS Access queries !

[harshakusam]

Hi All,

Iam new to access
Can some one pls help me pls..

My database is :- Access
I connect to my database using ODBC using oracle RDB driver.

I have a watchlist.mdb file. Which has almost 120 queries in it, evryday we use to run these queries and export to excel and send it users. It will take 2 1/2 hrs to complete this task.

Can someone pls help me to find some solution. So i can stop this by doing manually

[FishVal]

I guess you need to iterate CurrentDb.QueryDefs collection calling DoCmd.TransferSpreadsheet for each query in the collection.

Regards,
Fish.

[harshakusam]

Hi Fish,

Thanks for replay ...

Could you pls shed some light how to proceed as this is supposed to done by the end of this week... it will be realy helpful if you help in this...

Cheer's
Harsha

[iheartvba]

harshakusam,
It seems FishVal's post is quite self - explanatory (as his posts usually are). Here is how to execute it, I may have missed something because it seems you are an IT professional so would be more experienced then me, but here goes:

Create a Form in Access with a button with the following on click event:

Private Sub cmdExport120Queries_Click()
DoCmd.TransferSpreadsheet acExport, "Type in the spreadsheet type", "Name of The First Query You Want To Export", "Destination Where you want to spreadsheet to be created", "Does the Query have field names"
DoCmd.TransferSpreadsheet acExport, "Type in the spreadsheet type", "Name of The Second Query You Want To Export", "Destination Where you want to spreadsheet to be created", "Does the Query have field names"
'...and so on
End Sub
 

[FishVal]

@harshakusam
Something like the following:

Public Sub ExportAllQueriesToXLS()
 
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
 
    Set db = CurrentDb
 
    For Each qry In db.QueryDefs
        DoCmd.TransferSpreadsheet acExport, , qry.Name, "X:\Export\" & qry.Name
    Next
 
    Set qry = Nothing
    Set db = Nothing
 
End Sub
 

[NeoPa]

@iheartvba
I think Fish's method was intended to indicate the use of the For...Each loop in fact (although any attempt to help is always appreciated).

Private Sub cmdExportAllQueries_Click()
  Dim qdf As DAO.QueryDef
 
  For Each qdf In CurrentDb.QueryDefs
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:=qdf.Name, _
                                   FileName:=qdf.Name & ".Xls", _
                                   HasFieldNames:=True)
  Next
End Sub

[harshakusam]

Hi Neopa,

Does above code will work..

Shoould run all my quries 1 by one and export the output to excel and save it in particular location.

[harshakusam]

Below is the code i use
---------------------------------------

Public Function Metrics()
On Error GoTo Err_Mod_MIS
 
Dim ftp_Date
Dim LocMetrics, StrMetrics As String
Dim RstMetrics As Recordset
Dim MIS_Query As String
 
 
DoCmd.SetWarnings False
ftp_Date = Format(Date, "yyyymmdd")
LocMetrics = DLookup("location", "tbl_location", _
            "[function]='Metrics'")
MsgBox LocMetrics
StrMetrics = LocMetrics & "Metrics_" & ftp_Date & ".xls"
MsgBox StrMetrics
 
MIS_Query = "select * into MIS_COUNTS from XXXX "
 
DoCmd.RunSQL (MIS_Query)
 
DoCmd.TransferSpreadsheet acExport, 8, "MIS_Counts", StrMetrics, True, ""
 
 
 
Exit_Mod_MIS:
DoCmd.SetWarnings True
    Exit Function
 
Err_Mod_MIS:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Mod_MIS
 
End Function

[harshakusam]

Below is the query i want assign it to MIS_Query.in my mdb file it is a passthry query i will run this with out any issues but when i copy the sql and try to run as separate then iam getting syntax error. It is because of case not allowed in access, but how i can able to run this with out any issues when it is a passthru query any difference
-------------------------------------------------------------

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

[NeoPa]

@harshakusam
I think it should yes.

The code as is will put the file in the current directory with a name matching the name of the query, but with ".Xls" on the end.

[NeoPa]

@harshakusam
I can't say I've tried using a Pass-Thru query myself for this, but I expect any QueryDef or Table should work consistently.

If the SQL you posted is stored as a QueryDef and the Return Records property is set to true, then I see no reason why it wouldn't work.

[NeoPa]

@harshakusam
I don't know what this one is about. I see no connection between this post and the question :S

[harshakusam]

Hi Neopa,

I my self confused and confusing all...

I have a mdb file it's having almost 120 passthru queries in it.

We daily run each query and export the output to excel
All this process takes 2 hrs

I want this to automate by some way is it possible

[FishVal]

I guess you should clarify what those queries actually do.
Taking into account the code you've posted they probably are not SELECT queries.

[NeoPa]

@harshakusam
We seem to be going over old ground here.

I believe I've already answered these points as well as I'm able. There was a pointer in an earlier post for which I've seen no response from you. There's really not much point in starting again if you don't reply to points in the conversation. I can only see that we would get back to the same point and waste much time and effort in the process.

I appreciate that it's probably hard for you to communicate well in English, but it still won't get us anywhere if you don't reply to what I post.