By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,488 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,488 IT Pros & Developers. It's quick & easy.

Help running multiple MS Access queries !

P: 36
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
Apr 21 '09 #1
Share this Question
Share on Google+
14 Replies


FishVal
Expert 2.5K+
P: 2,653
I guess you need to iterate CurrentDb.QueryDefs collection calling DoCmd.TransferSpreadsheet for each query in the collection.

Regards,
Fish.
Apr 21 '09 #2

P: 36
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
Apr 21 '09 #3

100+
P: 171
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExport120Queries_Click()
  2. 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"
  3. 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"
  4. '...and so on
  5. End Sub
  6.  
Apr 21 '09 #4

FishVal
Expert 2.5K+
P: 2,653
@harshakusam
Something like the following:

Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportAllQueriesToXLS()
  2.  
  3.     Dim db As DAO.Database
  4.     Dim qry As DAO.QueryDef
  5.  
  6.     Set db = CurrentDb
  7.  
  8.     For Each qry In db.QueryDefs
  9.         DoCmd.TransferSpreadsheet acExport, , qry.Name, "X:\Export\" & qry.Name
  10.     Next
  11.  
  12.     Set qry = Nothing
  13.     Set db = Nothing
  14.  
  15. End Sub
  16.  
Apr 21 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
@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).
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExportAllQueries_Click()
  2.   Dim qdf As DAO.QueryDef
  3.  
  4.   For Each qdf In CurrentDb.QueryDefs
  5.     Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
  6.                                    TableName:=qdf.Name, _
  7.                                    FileName:=qdf.Name & ".Xls", _
  8.                                    HasFieldNames:=True)
  9.   Next
  10. End Sub
Apr 21 '09 #6

P: 36
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.
Apr 21 '09 #7

P: 36
Below is the code i use
---------------------------------------
Expand|Select|Wrap|Line Numbers
  1. Public Function Metrics()
  2. On Error GoTo Err_Mod_MIS
  3.  
  4. Dim ftp_Date
  5. Dim LocMetrics, StrMetrics As String
  6. Dim RstMetrics As Recordset
  7. Dim MIS_Query As String
  8.  
  9.  
  10. DoCmd.SetWarnings False
  11. ftp_Date = Format(Date, "yyyymmdd")
  12. LocMetrics = DLookup("location", "tbl_location", _
  13.             "[function]='Metrics'")
  14. MsgBox LocMetrics
  15. StrMetrics = LocMetrics & "Metrics_" & ftp_Date & ".xls"
  16. MsgBox StrMetrics
  17.  
  18. MIS_Query = "select * into MIS_COUNTS from XXXX "
  19.  
  20. DoCmd.RunSQL (MIS_Query)
  21.  
  22. DoCmd.TransferSpreadsheet acExport, 8, "MIS_Counts", StrMetrics, True, ""
  23.  
  24.  
  25.  
  26. Exit_Mod_MIS:
  27. DoCmd.SetWarnings True
  28.     Exit Function
  29.  
  30. Err_Mod_MIS:
  31.     MsgBox Err.Number & " - " & Err.Description
  32.     Resume Exit_Mod_MIS
  33.  
  34. End Function
Apr 21 '09 #8

P: 36
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
-------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. select d.DEAL_FOLDER_STATUS,  d.VALUE_DATE, d.BUSINESS_DATE, 
  2. case
  3. when    d.BUY_SETL_TYPE_IND = 11
  4. then    'NET PEND'
  5. when    d.BUY_SETL_TYPE_IND = 12
  6. then    'NETTED  '
  7. when   d.buy_setL_type_ind = 15
  8. then    'NET AS GROSS'
  9. when   d.buy_setl_type_ind = 19
  10. then    ' NET GO GROSS'
  11. when    d.BUY_SETL_TYPE_IND = 21
  12. then    'GROSS PEND'
  13. when    d.BUY_SETL_TYPE_IND = 26
  14. then    'GROSS AGGR'
  15. when    d.BUY_SETL_TYPE_IND = 22
  16. then    'GROSS AD HOC'
  17. when    d.BUY_SETL_TYPE_IND = 29
  18. then    'GROSS   '
  19. when    d.BUY_SETL_TYPE_IND > 40
  20. then    'CLS     '
  21. ELSE  cast(d.buy_setl_type_ind as char(2))
  22. end as setl_type
  23. ,d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type
  24. ,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
  25. , case 
  26.     when tm.trade_source_id = 'RMS' 
  27.        then (substr(tm.fo_deal_id,5,10))
  28.        else tm.fo_deal_id
  29.   end as fo_trade_id
  30. ,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind
  31. , d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id
  32. from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm
  33. where
  34.     cle.company_id = c.company_id
  35. and cle.cpty_id = c.cpty_id
  36. and d.company_id = cle.company_id
  37. and d.legal_entity_id = cle.legal_entity_id
  38. and d.cpty_id = cle.cpty_id
  39. and c.record_state = 'V'
  40. and cle.cpty_id LIKE 'ABSA JB%'
  41. and cle.record_state = 'V'
  42. and d.record_state = 'V'
  43. and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8))
  44. and s.deal_folder_id = d.deal_folder_id
  45. and s.ccy_id = d.buy_ccy_id
  46. and tm.trade_id = d.trade_id
  47. and tm.trans_id = d.trade_trans_id
  48. and tm.ver_id = d.trade_ver_id
  49. order by d.value_date ,  SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID  , d.CPTY_ID
Apr 21 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
@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.
Apr 21 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
@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.
Apr 21 '09 #11

NeoPa
Expert Mod 15k+
P: 31,709
@harshakusam
I don't know what this one is about. I see no connection between this post and the question :S
Apr 21 '09 #12

P: 36
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
Apr 21 '09 #13

FishVal
Expert 2.5K+
P: 2,653
I guess you should clarify what those queries actually do.
Taking into account the code you've posted they probably are not SELECT queries.
Apr 21 '09 #14

NeoPa
Expert Mod 15k+
P: 31,709
@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.
Apr 21 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.