Solved

VB6 - Run query from notepad

Posted on 2013-10-22
4
554 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 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

816 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

11 Experts available now in Live!

Get 1:1 Help Now