asked on

Problem using OLEDB

Hi Experts,

I have a peculiar problem when i try to execute the following code.

Private Sub Command1_Click()
Dim mrsMisc As New ADODB.Recordset
Dim mconConnection As New ADODB.Connection
Set mrsMisc = New ADODB.Recordset
Set mconConnection = New ADODB.Connection
mconConnection.Open "Provider = MSDAORA ; Data source = MRSD; User Id=eqtusr; Password=eqtusr;"
mconConnection.CursorLocation = adUseClient
lstrSqlMiscQuery = " (SELECT TRD_DT as EventDate, EVENT_TM as EventTime," _
& "NVL(EVENT_TYPE_CD,' ') as EventTypeCode, NVL(ISSUE_SHRT_NM,'') as IssueName," _
& " NVL(NEW_ISSUE_SYM_ID,'') as NewIssueSymbol, NVL(ITS_FL,'') as ITSFlag, " _
& " MKT_CLASS_CD as MarketClassCd, NVL(QUAL_ST,' ') as QualSt, '' as MktClsCndCd, '' as EuroHldFl, 'IssueMaint' as TableName, '' as MktOpenTm, '' as MktCloseTm, '' as MktAllowTm FROM ISSUE_MAINT WHERE ISSUE_UNIQUE_ID = 24463 AND (EVENT_TYPE_CD IN ('OTCMAST_CUSIP','OTCSECDELT','OTCSECMAST','OTHER CHG','OTHER-CHG','SYMBOLNAME','UPCFLAG' ,'MSEQ6002' , 'ISSU6702' ,'MSEG6001' ,'MSAW6001') or QUAL_ST ='X' )AND TRD_DT >= TO_DATE('02/26/2003','MM/DD/YYYY') AND TRD_DT <= TO_DATE('02/26/2003','MM/DD/YYYY')AND MKT_CLASS_CD IN ('C' , 'CQS' , 'ECM' ,'T') UNION ALL SELECT TRD_DT as EventDate, EVENT_TM as EventTime, EVENT_TYPE_CD as EventTypeCode, '' as IssueName, '' as NewISsueSymbol, '' as ITSFlag, ' ' as MarketClassCd, ' ' as QualSt, MKT_CLS_CNDTN_CD as MktClsCndCd, EURO_HLDY_FL As EuroHldFl, 'MktCntrlTm' as TableName, " _
& " NVL(US_MKT_OPN_TM,'') as MktOpenTm, NVL(US_MKT_CLS_TM,'') as MktCloseTm, NVL(US_MKT_ALLOW_TM,'') as MktAllowTm FROM MKT_CNTRL_TM WHERE TRD_DT >= TO_DATE('02/26/2003','MM/DD/YYYY') AND TRD_DT <= TO_DATE('02/26/2003','MM/DD/YYYY') AND MKT_SSSN_CD = 'US' AND EVENT_TYPE_CD IN ('MKTTIMES','MKTCLSCOND','BEG-EMC','END-EMC') AND MKT_CD in ('C' , 'CQS' , 'ECM' ,'T') UNION ALL SELECT TRD_DT as EventDate, EVENT_TM as EventTime, EVENT_TYPE_CD as EventTypeCode, '' as IssueName, '' as NewISsueSymbol, '' as ITSFlag, ' ' as MarketClassCd, ' ' as MktClsCndCd, ' ' as QualSt, '' as EuroHldFl, 'MktCntrlSys' as TableName, '' as MktOpenTm, '' as MktCloseTm, '' as MktAllowTm FROM MKT_CNTRL_SYSTM WHERE TRD_DT >= TO_DATE('02/26/2003','MM/DD/YYYY') AND TRD_DT <= TO_DATE('02/26/2003','MM/DD/YYYY') AND MKT_SSSN_CD = 'US' AND MKT_CD = 'CQS') ORDER BY EventDate,EventTime "
mrsMisc.CursorLocation = adUseClient
mrsMisc.Open lstrSqlMiscQuery, mconConnection ', adOpenForwardOnly
MsgBox mrsMisc.RecordCount
End Sub

I am getting the following error: "Operation is not allowed when the object is closed." in mrsMisc.RecordCount
But when i change the Provider from MSDAORA to oraOLEDB it is working fine.
Also if i remove the brackets before SELECT and before ORDER BY (not required for UNION ALL) and execute the about code using MSDAORA, it is working perfectly.

Thanks in advance

Richie_Simonetti

add:
mconConnection.Open "Provider = MSDAORA ; Data source = MRSD; User Id=eqtusr; Password=eqtusr;"
mconConnection.CursorLocation = adUseClient
with
mconConnection.Open "Provider = MSDAORA ; Data source = MRSD; User Id=eqtusr; Password=eqtusr;"
if mconConnection.state =adstateopen then.....

Arthur_Wood

change this:

mrsMisc.CursorLocation = adUseClient
mrsMisc.Open lstrSqlMiscQuery, mconConnection ', adOpenForwardOnly

to

mrsMisc.CursorLocation = adUseClient
mconConnection.Open
mrsMisc.Open lstrSqlMiscQuery, mconConnection ', adOpenForwardOnly

you must OPEN the connection to the databse BEFORE you can use it to open the recordset.

AW

kanithi

ASKER

Hi,

There is no prblem what so ever in the code or in the query. It was working when i use ODBC.
It is causing the problem when i use OLEDB.

Thanks

Richie_Simonetti

"There is no prblem what so ever in the code or in the query."
80

ASKER CERTIFIED SOLUTION

kanithi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CleanupPing

kanithi:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
Experts: Post your closing recommendations! Who deserves points here?

DanRollins

kanithi, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer

YensidMod

Question is PAQ'd and points refunded.

YensidMod
Community Support Moderator @Experts Exchange