+ Reply to Thread
Results 1 to 5 of 5

Runtime Error 3709

  1. #1
    Registered User
    Join Date
    08-24-2016
    Location
    lagos
    MS-Off Ver
    office 2013
    Posts
    3

    Question Runtime Error 3709

    Please I need assistance on the above error. I have left programming for a while now but I need to connect to an oracle DB on another system to extract information into Ms Excel 2013. I did some VBA macros but I got the error 3709(The connection cannot be used to perform this operation. It is either closed or invalid in this context) and the debugger points to "Set RS = Cmd.Execute".

    Kindly can anyone assist me. I am pasting my code below:

    Sub Main()
    Dim Conn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim Cmd As ADODB.Command
    Set Cmd = New ADODB.Command
    Dim sqlText As String
    Dim Row As Long
    Dim Findex As Long
    Dim Data As Worksheet
    Dim X As Long
    Dim UID As String
    Dim PWD As String
    Dim strWkno As String
    Dim strYear As String
    Dim Server As String


    Application.Calculation = xlCalculationManual
    UID = "dbr" 'Enter the User ID
    PWD = "dbr" 'Enter the password
    strWkno = InputBox("Enter Week Number")
    strYear = InputBox("Enter Year")
    Host = "ORESAPP20013.S2.MS.UNILEVER.COM"
    Server = "orclsap" 'This comes from your TNSNames.ora file
    Set Data = Sheets("Sheet1") 'Change this to the name of the sheet you want to return data to
    Data.Select
    Range("A:F").ClearContents 'Change A:F to the range you expect data to return to
    ConString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=dbr;Data Source=DBR_DNS;Extended Properties=" & "DSN=DBR_DNS;UID=dbr;PWD=dbr;DBQ=ORCLSAPREMOTE ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;" & ";Initial Catalog=(Default)"
    Conn.Open (ConString)
    Cmd.CommandType = adCmdText
    ' Put your query next
    sqlText = " SELECT DBR.cust_id, customer.Cust_desc, DBR.item_id, Items.item_desc,SUM(DBR.last_sales) , SUM(DBR.aws) , SUM(DBR.safety_stocks) ,SUM(DBR.last_stocks) , SUM(DBR.dbr_qty) , SUM(DBR.next_order) ,DBR.week, DBR.week_year FROM customer INNER JOIN DBR ON customer.cust_id = DBR.cust_id INNER JOIN items ON DBR.item_id = items.item_id GROUP BY DBR.cust_id, customer.cust_desc, DBR.item_id, items.item_desc, DBR.week, DBR.week_year HAVING (DBR.week = " & strWkno & ") AND (DBR.week_year = " & strYear & ")"

    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    For X = 0 To 11 'Change to the number of columns you are selecting MINUS 1. this loops through the column names
    ' in the query and puts them in the spreadsheet
    Data.Cells(1, X + 1) = RS.Fields(X).Name
    Next
    Do While Not RS.EOF 'this loops through the data and puts it in the spreadsheet
    Row = Row + 1
    For Findex = 0 To RS.Fields.Count - 1
    Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
    Next Findex
    RS.MoveNext
    Loop
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate

    End Sub

    Thanks

  2. #2
    Registered User
    Join Date
    08-24-2016
    Location
    lagos
    MS-Off Ver
    office 2013
    Posts
    3

    Re: Runtime Error 3709

    Thanks @Goose

    The debug is pointing this command "Set RS = Cmd.Execute"

    Kunle

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,739

    Re: Runtime Error 3709

    Why are you trying to execute a SELECT query?

    If you want to run the SELECT query use the Open method of the recordset and specify to use the connection you make earlier in the code.

    PS Can you add code tags when posting code?
    If posting code please use code tags, see here.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,739

    Re: Runtime Error 3709

    PS If you did want to use Execute you would need to specify the ActiveConnection for the Command object.

  5. #5
    Registered User
    Join Date
    08-24-2016
    Location
    lagos
    MS-Off Ver
    office 2013
    Posts
    3

    Re: Runtime Error 3709

    Thank you Norie, the "ActiveConnection" did the magic. I really appreciate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1