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
Bookmarks