Access Query Problem.

[harshakusam]

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
------------

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

[FishVal]

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

[harshakusam]

Hi Fish,

could you pls offer some time for me.. so that i can explain what iam doing plss....

[harshakusam]

Hi Fish,

below is the function how iam doing ...

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 d.DEAL_FOLDER_STATUS,  d.VALUE_DATE, d.BUSINESS_DATE," & vbCrLf _
& "case " & vbCrLf _
& "when d.buy_setl_type_ind = 11 " & vbCrLf _
& "then    'NET PEND'  " & vbCrLf _
& "when d.buy_setl_type_ind = 12 " & vbCrLf _
& "then    'NETTED  ' " & vbCrLf _
& "when d.buy_setl_type_ind = 15 " & vbCrLf _
& "then    'NET AS GROSS' " & vbCrLf _
& "when d.buy_setl_type_ind = 19 " & vbCrLf _
& "then    ' NET GO GROSS' " & vbCrLf _
& "when d.buy_setl_type_ind = 21 " & vbCrLf _
& "then    'GROSS PEND' " & vbCrLf _
& "when d.buy_setl_type_ind = 26 " & vbCrLf _
& "then    'GROSS AGGR' " & vbCrLf _
& "when d.buy_setl_type_ind = 22 " & vbCrLf _
& "then    'GROSS AD HOC' " & vbCrLf _
& "when d.buy_setl_type_ind = 29 " & vbCrLf _
& "then    'GROSS   ' " & vbCrLf _
& "when d.buy_setl_type_ind > 40 " & vbCrLf _
& "then    'CLS     ' " & vbCrLf _
& "ELSE  cast(d.buy_setl_type_ind as char(2)) " & vbCrLf _
& "end As setl_type " & vbCrLf _
& ",d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type " & vbCrLf _
& ",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 _

MIS_Query = MIS_Query & ", case " & vbCrLf _
& "    when tm.trade_source_id = 'RMS' " & vbCrLf _
& "       then (substr(tm.fo_deal_id,5,10)) " & vbCrLf _
& "       Else: tm.fo_deal_id " & vbCrLf _
& "  end As fo_trade_id " & vbCrLf _
& " ,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind " & vbCrLf _
& ", d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id " & vbCrLf _
& "from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm " & vbCrLf _
& "where " & vbCrLf _
& "    cle.company_id = c.company_id " & vbCrLf _
& " and cle.cpty_id = c.cpty_id " & vbCrLf _
& " and d.company_id = cle.company_id " & vbCrLf _
& " and d.legal_entity_id = cle.legal_entity_id " & vbCrLf _
& "  and d.cpty_id = cle.cpty_id " & vbCrLf _
& " and c.record_state = 'V' " & vbCrLf _
& " and cle.cpty_id LIKE 'ABSA JB%' " & vbCrLf _
& " and cle.record_state = 'V' " & vbCrLf _
& " and d.record_state = 'V' " & vbCrLf _
& " and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8)) " & vbCrLf _
& " and s.deal_folder_id = d.deal_folder_id " & vbCrLf _
& " and s.ccy_id = d.buy_ccy_id " & vbCrLf _
& " and tm.trade_id = d.trade_id " & vbCrLf _
& " and tm.trans_id = d.trade_trans_id " & vbCrLf _
& " and tm.ver_id = d.trade_ver_id " & vbCrLf _
& " order by d.value_date ,  SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID  , d.CPTY_ID"

'MsgBox MIS_Query
    
    
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]

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....

[FishVal]

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.

[harshakusam]

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.....

[ChipR]

Instead of putting your code in the string, use the code to build the string. For example:

Dim strSelect as String
Dim strWhere as String
Dim strSQL as String

strSelect = "SELECT field1"
strWhere = "WHERE field1 = 123"

If txtField2Box > "" Then
  strSelect = strSelect & ", field2 "
  strWhere = strWhere & " AND field2 = 123"
End If

strSQL = strSelect & " FROM table1 " & strWhere
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)

DoCmd.RunSQL "SELECT field1 FROM table1 WHERE field1 = 123"
or
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.

[FishVal]

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

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?

[NeoPa]

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.

[harshakusam]

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

[NeoPa]

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

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.

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.

I even offered a solution, but you need to respond to my post before I can help you further.

[harshakusam]

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 ???

[NeoPa]

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.