I'm running the following and trying to get data back. For some reason the recordset only seems to populate the field names and not the records.
Not sure how to fix this, any Ideas?
Thanks,
Jeff
Code
Sub FinalQuery(wsName As Worksheet, Analysis As String, ItemReq As Variant)
Dim Conn1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
'Dim Errs1 As Errors
Dim Rs1 As New ADODB.Recordset
Rs1.CursorLocation = adUseClient
Dim i As Integer
Dim AccessConnect As String
Dim x As String
Dim ORACLE_USER_NAME As String
Dim ORACLE_PASSWORD As String
Dim sql As String
Dim sConnect As String
Dim myPort As String
Dim myHost As String
Dim OracleSID As String
Dim SERVICE_NAME As String
Dim rcnt As Long
Dim StartDate As Date
Dim EndDate As Date
Analysis = Analysis
Dim Comp As String
With Conn1
.CursorLocation = adUseClient
End With
'Set Sheet1 RowHeight
wsName.Rows("1:1").RowHeight = 24
x = wsName.Range("C2")
ORACLE_USER_NAME = "LWPROD_USER_RO"
ORACLE_PASSWORD = "SB17#adlims4prd"
myPort = "1521"
myHost = "gojoracb-scan.gojo.net"
OracleSID = "10.6.4.142"
SERVICE_NAME = "csprod_primary"
'Connection String
AccessConnect = "Provider=OraOLEDB.Oracle;" & _
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=" & myHost & ")(Port=1521))(CONNECT_DATA=" & _
" (SERVICE_NAME=" & SERVICE_NAME & ")));User ID=" & ORACLE_USER_NAME & ";Password=" & ORACLE_PASSWORD
On Error GoTo ADOError ' Full Error Handling which traverses Connection object
Conn1.ConnectionString = AccessConnect
Comp = VBA.Join(ItemReq, "','")
Analysis = "'" & Analysis & "'"
Comp = "'" & Comp & "'"
Dim sd As String
Dim ed As String
StartDate = wsName.Range("J1").Value
sd = Format(StartDate, "dd/mmm/yyyy")
EndDate = wsName.Range("L1").Value
ed = Format(EndDate, "dd/mmm/yyyy")
sql = ""
sql = sql & "Select r.analysis, r.formatted_entry, r.changed_on, r.sample_number, s.lot, l.work_center, l.C_PRODN_RESOURCE"
sql = sql & " from LWPROD.result r left join LWPROD.sample s ON r.sample_number = s.sample_number left join LWPROD.lot l ON l.lot_number = s.lot"
sql = sql & " WHERE analysis IN " & "(" & Analysis & ")" & " and r.name IN " & "(" & Comp & ")" & ""
sql = sql & " and r.Entry IS NOT NULL"
sql = sql & " and r.changed_on >= '" & sd & "' and r.changed_on <= '" & ed & "'"
sql = sql & " order by 1,3"
Set Rs1 = New ADODB.Recordset
Rs1.Open sql, AccessConnect, adOpenDynamic, adLockReadOnly, adCmdText
'Clear worksheet Range G3:G1xx
wsName.Range("G3:P100000").Clear
'Make sure autofilter is removed
If wsName.AutoFilterMode Then
wsName.Cells.AutoFilter
End If
'loop to get column headings.
For i = 0 To Rs1.Fields.Count - 1
wsName.Cells(3, i + 7).Value = Rs1.Fields(i).Name
Next i
'Rs1.MoveFirst
wsName.Range("G4").CopyFromRecordset Rs1
If Not ActiveSheet.AutoFilterMode Then
wsName.Range("G3").AutoFilter
End If
'Close Connections
Rs1.Close
Conn1.Close
Conn1.ConnectionString = ""
Done:
Set Rs1 = Nothing
Set Cmd1 = Nothing
Set Conn1 = Nothing
'Prompt
MsgBox " Data Returned "
Exit Sub
ADOError:
'to be added later
End Sub
Display More