Solved

VB6 - Run query from notepad

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

13 Experts available now in Live!

Get 1:1 Help Now