Solved

VB6 - Run query from notepad

Posted on 2013-10-22
4
578 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

691 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