• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1001
  • Last Modified:

Excel VBA Error Run time error '3709':" The connection cannot be used to perform this operation. "

I am trying to connect to Oracle DB and execute SQL dynamically, but getting following error at run time:
Run-time error '3709':
The connection cannot be used to perform this operation.  It is either closed or invalid in this context."

PLease find the logic below

Sub Button1_Click()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlstr As String
Dim strconn As String
Dim ref_no As String

    ' open connection
    Set con = New ADODB.Connection
    strconn = "Driver={Oracle in OraClient 11g_home2}; Data Source = ARGPRO; uid=TCS_SUKAST; pwd=tcs_sukant;"
    con.Open strconn
    'MsgBox (con.State)
    ref_no = Sheet1.Cells(2, 1).Value
        sqlstr = "SELECT  cm.case_num, to_char(cm.init_rept_date,'DD-MON-YYYY') init_rept, cp.product_name,  "
        sqlstr = sqlstr & "         (SELECT  type_desc FROM    argus_app.lm_ref_types lrt WHERE   lrt.ref_type_id = cr.ref_type_id ) ref_typ, "
        sqlstr = sqlstr & "         cr.ref_no ref_n, (SELECT  'Code: ' || lat.action_type ||'   Description: '||ca.description  "
        sqlstr = sqlstr & "         ||'   Date Open: '||ca.date_open ||'   Date Completed: '|| ca.date_done "
        sqlstr = sqlstr & "         FROM    case_actions ca, lm_action_item_type lat WHERE   cm.case_id = ca.case_id (+) AND     lat.action_type_id = ca.code "
        sqlstr = sqlstr & "         AND     upper(lat.action_type) = 'DELETION - BEFORE SUBMISSION' ) act_item "
        sqlstr = sqlstr & "      FROM    case_reference cr, case_master cm, v$case_primary_suspect_product cp"
        sqlstr = sqlstr & "         WHERE   cm.case_id = cr.case_id (+) AND     cm.case_id = cp.case_id AND cr.ref_no like '%" & ref_no & "%' "
    Sheet1.Cells(1, 2) = sqlstr
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    'rs.CursorType = adOpenStatic
    'rs.LockType = adLockBatchOptimistic
    rs.Open sqlstr
    MsgBox (rs.MaxRecords)
    'MsgBox (con.State)
End Sub

kindly help me resolve this issue ASAP.

  • 2
1 Solution
Saurabh Singh TeotiaCommented:
You need to change this line in your code...

rs.Open sqlstr

Open in new window

To this...

rs.Open sqlstr,con

Open in new window

Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
Saurabh Singh TeotiaCommented:
Basis of the problem that he specified and the amendments which i gave to him..His code will work for sure as he wasn't using connecting string when he is running the code..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now