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

Access Query Problem.

P: 36
Hi All,

MDB file with 110 passthru queries
Run each query and save it in excel..

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
------------
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
May 13 '09 #1
Share this Question
Share on Google+
13 Replies


FishVal
Expert 2.5K+
P: 2,653
You may try either of the following:
  • Have a one template passthrough query and change it SQL property as needed via QueryDef.SQL property.
  • Open connection to the external RDBMS, execute query on this connection to get recordset and export thus obtained recordset to Excel.

Regards,
Fish
May 13 '09 #2

P: 36
Hi Fish,

could you pls offer some time for me.. so that i can explain what iam doing plss....
May 13 '09 #3

P: 36
Hi Fish,

below is the function how iam doing ...
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.  
  19.  
  20. MIS_Query = "select d.DEAL_FOLDER_STATUS,  d.VALUE_DATE, d.BUSINESS_DATE," & vbCrLf _
  21. & "case " & vbCrLf _
  22. & "when d.buy_setl_type_ind = 11 " & vbCrLf _
  23. & "then    'NET PEND'  " & vbCrLf _
  24. & "when d.buy_setl_type_ind = 12 " & vbCrLf _
  25. & "then    'NETTED  ' " & vbCrLf _
  26. & "when d.buy_setl_type_ind = 15 " & vbCrLf _
  27. & "then    'NET AS GROSS' " & vbCrLf _
  28. & "when d.buy_setl_type_ind = 19 " & vbCrLf _
  29. & "then    ' NET GO GROSS' " & vbCrLf _
  30. & "when d.buy_setl_type_ind = 21 " & vbCrLf _
  31. & "then    'GROSS PEND' " & vbCrLf _
  32. & "when d.buy_setl_type_ind = 26 " & vbCrLf _
  33. & "then    'GROSS AGGR' " & vbCrLf _
  34. & "when d.buy_setl_type_ind = 22 " & vbCrLf _
  35. & "then    'GROSS AD HOC' " & vbCrLf _
  36. & "when d.buy_setl_type_ind = 29 " & vbCrLf _
  37. & "then    'GROSS   ' " & vbCrLf _
  38. & "when d.buy_setl_type_ind > 40 " & vbCrLf _
  39. & "then    'CLS     ' " & vbCrLf _
  40. & "ELSE  cast(d.buy_setl_type_ind as char(2)) " & vbCrLf _
  41. & "end As setl_type " & vbCrLf _
  42. & ",d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type " & vbCrLf _
  43. & ",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 " & vbCrLf _
  44.  
  45. MIS_Query = MIS_Query & ", case " & vbCrLf _
  46. & "    when tm.trade_source_id = 'RMS' " & vbCrLf _
  47. & "       then (substr(tm.fo_deal_id,5,10)) " & vbCrLf _
  48. & "       Else: tm.fo_deal_id " & vbCrLf _
  49. & "  end As fo_trade_id " & vbCrLf _
  50. & " ,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind " & vbCrLf _
  51. & ", d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id " & vbCrLf _
  52. & "from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm " & vbCrLf _
  53. & "where " & vbCrLf _
  54. & "    cle.company_id = c.company_id " & vbCrLf _
  55. & " and cle.cpty_id = c.cpty_id " & vbCrLf _
  56. & " and d.company_id = cle.company_id " & vbCrLf _
  57. & " and d.legal_entity_id = cle.legal_entity_id " & vbCrLf _
  58. & "  and d.cpty_id = cle.cpty_id " & vbCrLf _
  59. & " and c.record_state = 'V' " & vbCrLf _
  60. & " and cle.cpty_id LIKE 'ABSA JB%' " & vbCrLf _
  61. & " and cle.record_state = 'V' " & vbCrLf _
  62. & " and d.record_state = 'V' " & vbCrLf _
  63. & " and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8)) " & vbCrLf _
  64. & " and s.deal_folder_id = d.deal_folder_id " & vbCrLf _
  65. & " and s.ccy_id = d.buy_ccy_id " & vbCrLf _
  66. & " and tm.trade_id = d.trade_id " & vbCrLf _
  67. & " and tm.trans_id = d.trade_trans_id " & vbCrLf _
  68. & " and tm.ver_id = d.trade_ver_id " & vbCrLf _
  69. & " order by d.value_date ,  SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID  , d.CPTY_ID"
  70.  
  71. 'MsgBox MIS_Query
  72.  
  73.  
  74. DoCmd.RunSQL (MIS_Query)
  75.  
  76. DoCmd.TransferSpreadsheet acExport, 8, "MIS_Counts", StrMetrics, True, ""
  77.  
  78.  
  79.  
  80. Exit_Mod_MIS:
  81. DoCmd.SetWarnings True
  82.     Exit Function
  83.  
  84. Err_Mod_MIS:
  85.     MsgBox Err.Number & " - " & Err.Description
  86.     Resume Exit_Mod_MIS
  87.  
  88. End Function
May 13 '09 #4

P: 36
Hi Fish,

I wanna run above query which i mentioned "MIS_Query" .. Could you pls help me ...

Iam getting syntax error when i run this.. i know this is because of access doen's understand case. substr and so....

Can you pls help me in resolving this pls...


It would be great great great helpful for mee....
May 13 '09 #5

FishVal
Expert 2.5K+
P: 2,653
Well.

No wonder you are getting syntax error.
DoCmd.RunSQL is for "action" SQL (UPDATE, DELETE etc) only. Moreover it executes SQL command on connection to current mdb file.

You should create a query object (aka view) in Access database and tune it to make passthrough query to the external database. Then set QueryDef.SQL of this "all-purpose passthrough query" to what you like at the time and export it to Excel.

Alternatively, you may open ADO connection to the external database, then open recordset via SQL you have and export thus obtained recordset to Excel.

What way would you prefer?

Kind regards,
Fish.
May 13 '09 #6

P: 36
Hi Fish,,

Iam getting sytax error because my select stament contains keywords like case, substr etc... . I can run normal select statement with out any issue.

Can you pls expain how to do this plsss.....
May 13 '09 #7

Expert 100+
P: 1,287
Instead of putting your code in the string, use the code to build the string. For example:

Expand|Select|Wrap|Line Numbers
  1. Dim strSelect as String
  2. Dim strWhere as String
  3. Dim strSQL as String
  4.  
  5. strSelect = "SELECT field1"
  6. strWhere = "WHERE field1 = 123"
  7.  
  8. If txtField2Box > "" Then
  9.   strSelect = strSelect & ", field2 "
  10.   strWhere = strWhere & " AND field2 = 123"
  11. End If
  12.  
  13. strSQL = strSelect & " FROM table1 " & strWhere
  14. DoCmd.RunSQL strSQL
You can see that the code is executed and changes the string that is the SQL command, but it's not part of it.
The result is (hopefully)
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "SELECT field1 FROM table1 WHERE field1 = 123"
  2. or
  3. DoCmd.RunSQL "SELECT field1, field2 FROM table1 WHERE field1 = 123 AND field2 = 123"
Of course, you can use more complicated logic than If...Then, but make sure that your code is acting on the string, not in it.
May 13 '09 #8

FishVal
Expert 2.5K+
P: 2,653
@harshakusam
No wonder.

As I've already said DoCmd.RunSQL passes SQL statement to Access database engine. Sure it doesn't support SQL dialect you use.

Ok. Very simple question - do you get it working via passthrough query designed in query editor?
May 13 '09 #9

NeoPa
Expert Mod 15k+
P: 31,712
Harshakusam,

Your SQL looks to me like T-SQL (for SQL Server).

To run it in Access, you either need to use a passthru query (as you've done so far) or change your SQL to be Jet SQL (the version of SQL that Access uses). It's a bit like translating the SQL code to another language.

In this case (running the SQL natively in Access) you also need to link the tables through to the server so that Access can even see them.

There are other options that Fish has mentioned, that can be run using code, but I think this is where you're getting confused.

How do you think you would like to proceed from here. We can help and direct, but we won't simply do it for you. You need to choose what you want to do and let us know where, if at all, you're still having trouble.
May 13 '09 #10

P: 36
Hi Fish,

Yes all my queries runs fine when i run in query editor as passthrough query ...
Y its not running is because access does't understand keyword's like case,Substr .. etc... . Iam i right fish ?

Neo,

I will connect to my database using Oracle ODBC Driver for RDB.
All my tables are linked... Only problem is access not understanding my sql Y because my sql contains case, substrt which access doesn't no...
Because of that its throwing syntax error
May 14 '09 #11

NeoPa
Expert Mod 15k+
P: 31,712
@NeoPa
OK. It's Oracle SQL rather than T-SQL (For MS SQL Server), but otherwise you're just telling us what I've already told you.
@NeoPa
I even offered a solution, but you need to respond to my post before I can help you further.
May 15 '09 #12

P: 36
Hi Neo,

I have to use same query In VB and run thru code.. but its giving me error...

DoCmd.RunSQL passes SQL statement to Access database engine ... here comes my problem... When i run same query from editor i think it is passing to database i think..

So how i can run my query from VB ???
May 15 '09 #13

NeoPa
Expert Mod 15k+
P: 31,712
I can appreciate that the English won't necessarily be the best (and make what allowances I can for that), but I have trouble understanding why you say Access database engine in one place (which makes perfect sense), then follow it later in the explanation with passing to database. This is ambiguous in any language. Access?; Oracle?; what?

In short I don't follow what you are trying to say.

PS. You still haven't responded to my earlier posts (as far as I can tell). If you simply start again when you come to something you don't understand I will quickly get frustrated with you and walk away.
May 18 '09 #14

Post your reply

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