Running long SQL query using VBA

[kirtesh250187]

Hi,

I have this very long SQL query which runs down to 5 pages. When i run it on TOAD it works fine and gives the results (100rows) but when i try to run using VBA it doesnt give any result. Below are the codes i am using

Function OpenTextFileToString2(ByVal strFile As String) As String
 Dim hFile As Long
 hFile = FreeFile
 Open strFile For Input As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=hFile"]#hFile[/URL] 
 OpenTextFileToString2 = Input$(LOF(hFile), hFile)
 Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=hFile"]#hFile[/URL] 
 End Function

Sub GetData()
Dim iCols
Dim ws
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim tran_key
Dim fname As String
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim strID, strPW As String


    Dim dbDatabase As ADODB.Connection
    Dim snpData As ADODB.Recordset
    Dim strDatabase As String
    Dim strSQL As String
    Dim intResult As Integer


    strID = "*******"
    strPW = "*******"




b = 0
c = 0


    Set dbDatabase = New ADODB.Connection
    Set snpData = New ADODB.Recordset
    
conn = "Provider=OraOLEDB.Oracle;Data Source=PROD.world;User ID=" & strID & ";Password=" & strPW & ""
  
    dbDatabase.ConnectionString = conn
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.Open


'*** Used for Current Pool ****
    
    fname = ThisWorkbook.Path & "\SQL.txt"
    sql = OpenTextFileToString2(fname)
     set cpw1 = New ADODB.Command        
    With cpw1
           .ActiveConnection = conn
           .CommandText = sql
           .CommandType = adCmdText
        End With
        
        Set rs = New ADODB.Recordset
        rs.Open cpw1, , adOpenDynamic, adLockOptimistic
        If Not rs.EOF Then rs.MoveFirst
        
        Do While Not rs.EOF
        Sheets("Data Input").Range("A2").CopyFromRecordset rs
        Loop
        rs.Close
        Set rs = Nothing

I have tried some other soultions like breaking the sql query but no luck. Could you please help me with this. Do let me know if you need anything else

Thanks,
Kirtesh

 

[mole999]

is hfile numeric ?

 

[kirtesh250187]

I have added the SQL query in a text file and that function helps me open and read the text file

 

[mole999]

Long is numeric

 

[kirtesh250187]

But it works fine with other smaller SQL queries. Only this one is a issue