Solved

VB6 - Run query from notepad

Posted on 2013-10-22
4
547 Views
Last Modified: 2013-10-22
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

0
Comment
Question by:Wilder1626
  • 2
4 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 39590536
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
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39590578
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/
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39590584
You won't be able to use the exact block as in your code  because it contains elements that need to be interpreted by VB, but if you have reduced the SQL to simple text then this should do it:

Dim oconn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim strSQL As String
    Dim x As Long
    Dim strText As String
    Dim f As Integer
   
    f = FreeFile
    Open "c:\folder1\mysql.txt" For Input As #f
    Do Until EOF(f)
        Line Input #f, strText
        strSQL = strSQL & strText
    Loop
    Close #f
   
    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
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 39590606
Hi

Thanks you so much for your help

It's working great.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Validating VB6 Function 19 56
bit defender blocks good applications 2 79
Copy a row 12 57
Crystal reports - Formula Field code need assistance with code 17 51
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now