VB Query syntax problem

[harshakusam]

Hi All,

Can someone pls help me ...

Below is the query which i usely run in Access

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

---------------------------------------------------------------------------------------------------------------

I want to run this query thru VB when i am trying to do this as below iam getting syntax error
--------------------------------------------------------------------------------------------

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

DoCmd.RunSQL (MIS_Query)

Is this error due to larger query...

Pls can someone pls help how to run this query it urjent.

[debasisdas]

which line is throwing the error ?

[harshakusam]

Hi

Could you pls provide your email id i will just drop you a mail with all screen shots

Thanks for your prompt response

[debasisdas]

Hi

Could you pls provide your email id i will just drop you a mail with all screen shots

No email please.

why not open a recordset using the query.

[harshakusam]

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
MIS_Query = Str(20000)

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, 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"
    
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]

In access when i run it will run without any errors but if iam using same in VB its throwing sytax error ... Can you pls look into this plsss

[debasisdas]

Do u know how to use ADO in VB 6 ?

[harshakusam]

Iam oracle guy .. first time iam using VB to automate some queries ....

[debasisdas]

try this

from project---->references------>select ADO 2.5 or higher library

they try the following code

 Dim CON As New ADODB.Connection
 Dim RS As New ADODB.Recordset
  
 Private Sub Command1_Click()
 CON.Open "your connection string here" [URL="http://www.connectionstrings.com/access"]find more[/URL] 
rs.open con, your query

 End Sub

[harshakusam]

Iam alreay connecting my database using below code

Function RefreshODBCLink edTables() As Boolean
On Error GoTo RefreshODBCLink edTables_Err
Dim strTblName As String, strConn As String
Dim db As Database, rs As Recordset, tbl As TableDef

DoCmd.Hourglass True

' ---------------------------------------------
' Register ODBC database(s)
' ---------------------------------------------
Set db = CurrentDb
Set rs = db.OpenRecordse t("tbl_ODBC")
With rs

While Not .EOF



DBEngine.Regist erDatabase rs("DSN"), _
"Oracle ODBC Driver for RDB", _
True, "Attributes =" & rs("Attributes" ) & Chr(13) _
& "Class=" & rs("class") & Chr(13) _
& "Cursor Option=" & rs("Cursor Option") & Chr(13) _
& "Descriptio n=" & rs("Description ") & Chr(13) _
& "Options=" & rs("Options") & Chr(13) _
& "Schema=" & rs("Schema") & Chr(13) _
& "Server=" & rs("ServerName" ) & Chr(13) _
& "Translatio n Option=" & rs("Translatio n Option") & Chr(13) _
& "Transport= " & rs("Transport" ) & Chr(13) _
& "UserID=" & rs("UID")



strTblName = rs("LocalTableN ame")
strConn = "ODBC;"
strConn = strConn & "DSN=" & rs("DSN") & ";"
strConn = strConn & "APP=Micros oft Access;"
strConn = strConn & "DATABASE=" & rs("schema") & ";"
strConn = strConn & "UID=" & rs("UID") & ";"
strConn = strConn & "PWD=" & rs("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableNa me") & ";"



If (DoesTblExist(s trTblName) = False) Then
Set tbl = db.CreateTableD ef(strTblName, _
dbAttachSavePWD , rs("ODBCTableNa me"), _
strConn)
db.TableDefs.Ap pend tbl
Else
Set tbl = db.TableDefs(st rTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If

rs.MoveNext
Wend
End With
RefreshODBCLink edTables = True

RefreshODBCLink edTables_End:
DoCmd.Hourglass False
Exit Function

RefreshODBCLink edTables_Err:
MsgBox Err.Description , vbCritical, "Refresh ODBC"
DoCmd.Hourglass False
Resume RefreshODBCLink edTables_End

End Function

[debasisdas]

you are connencting to oracle or access database ?

the following link might be helpful.

link

[harshakusam]

ms Access database