asked on

VB6 - Run query from notepad

Hi all

I have this below macro in my vb6 app that runs an SQL quey. But what i would like to do is to put the SQL directly in a notepad file on my desktop but the macro would pick from there.

How can i do that?

Thanks again for your help

 Dim oconn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim strSQL As String
    Dim x As Long



    strSQL = "SELECT COUNT (TLR.CARRIER_ID)as Count " & vbCrLf & _
             "FROM " & _
             "TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN " & vbCrLf & _
             "WHERE " & _
             "TLR.CARRIER_ID = TRD.CARRIER_ID (+) " & vbCrLf & _
             "AND TLR.LANE_ID = TRD.LANE_ID (+) " & vbCrLf & _
             "AND TLR.TARIFF_CLASS_ID = TRD.TARIFF_CLASS_ID (+) " & vbCrLf & _
             "AND TLR.EFFECTIVE = TRD.EFFECTIVE (+) " & vbCrLf & _
             "AND TLR.LANE_ID = LRN.ID " & vbCrLf & _
             "AND TLR.CARRIER_ID = '" & carrier_id.Text & "'" & vbCrLf & _
             "AND LRN.FROM_LOW_KEY_VALUE = '" & origin_id.Text & "' " & vbCrLf & _
             "AND LRN.TO_LOW_KEY_VALUE = '" & destination_id.Text & "'" & vbCrLf & _
             "AND TLR.DATE_INVALID > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD')-365 " & vbCrLf & _
             "GROUP by TLR.CARRIER_ID, LRN.FROM_LOW_KEY_VALUE,LRN.TO_LOW_KEY_VALUE "
    Set oconn = New ADODB.Connection
    oconn.Open "Provider=OraOLEDB.Oracle.1;Data Source=G_ENVIRONMENT;User Id=TMSFUNC;Password=TMSFUNC;"
    RS.CursorType = adOpenStatic
    RS.CursorLocation = adUseClient
    RS.LockType = adLockOptimistic
    RS.Open strSQL, oconn, adCmdText
    Set Form7.MSHFlexGrid_count.DataSource = RS

    RS.Close
    oconn.Close
    Set RS = Nothing
    Set oconn = Nothing

Open in new window

Wilder1626

ASKER

I tried like this but nothing happen.

Private Sub Label2_Click(Index As Integer)
Dim RS As New ADODB.Recordset
Dim strSQL As String
Dim x As Long
Dim oconn As New ADODB.Connection

strSQL = "C:\Documents and Settings\Marc\Desktop\SQL_RUN.txt"
Set oconn = New ADODB.Connection
oconn.Open "Provider=OraOLEDB.Oracle.1;Data Source=xxxx;User Id=xxxx;Password=xxxx;"
RS.CursorType = adOpenStatic
RS.CursorLocation = adUseClient
RS.LockType = adLockOptimistic
RS.Open strSQL, oconn, adCmdText
Set Form7.MSHFlexGrid_count.DataSource = RS

RS.Close
oconn.Close
Set RS = Nothing
End Sub

Éric Moreau

you need to open the file, read it and store it in your strSQL variable. Check http://www.dreamincode.net/forums/topic/66911-open-text-file-and-dump-text-into-textbox/

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Hi

Thanks you so much for your help

It's working great.