Results 1 to 3 of 3
  1. #1
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480

    Oracle SQL statement, creating an IQY file to run query...

    Hello, So I'm running into some issues here. I have a SQL statement that I need to put into VBA, the vba is going to pass the statement into an iqy file, that will make the oracle connection and run the query from excel.

    How it was previously done...

    Code:
    Dim escapedSQL as string
    Dim MyVariable as string
     Dim SQLA        As String
    
    My Variable = "STUFF"
     escapedSQL = "SQL=select+" & Chr(34) & _  
    "DatabaseName" & Chr(34) & "." & Chr(34) & "TableName" & Chr(34) & "." & Chr(34) & "WantedField" & Chr(34) & " s_2,   " & Chr(34) & _
                "DatabaseName" & Chr(34) & "." & Chr(34) & "TableName" & Chr(34) & "." & Chr(34) & "WantedField1" & Chr(34) & " s_3,   " & Chr(34) & _
                "DatabaseName" & Chr(34) & "." & Chr(34) & "TableName" & Chr(34) & "." & Chr(34) & "WantedField2" & Chr(34) & " s_8,   " & Chr(34) & _
                "DatabaseName" & Chr(34) & "." & Chr(34) & "TableName" & Chr(34) & "." & Chr(34) & "TWantedField3" & Chr(34) & " s_18+from+" & Chr(34) & "DatabaseName" & Chr(34) & "+where+((" & Chr(34) & "TableName" & Chr(34) & "." & Chr(34) & "WantedField1" & Chr(34) & " IN ("
                SQLA = ""
                
                SQLA = SQLAccounts & "'" & MyVariable & "', "
                
                SQLA = VBA.Left(SQLAccounts, (Len(SQLA) - 2)) & ")))"
                
                escapedSQL = escapedSQL & SQLA
    
    
    Then it writes 4 lines to an iqy file and tells excel to open it... showing the results.



    I have the normal SQL for another query I need to run, its a little bit more complex and I'm struggling converting it... Is there a better way to write a sql statement in vba? I don't know why I'm struggling so much with this, but any advice is greatly appreciated!

    I'm not working in access, but in a system called reflections so I need the query to be opened/returned in excel.....

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I try to break the lines at a keyword. I don't like the line continuation method (& _). For me, a long line to too hard to read.

    This is what my SQL query in VBA looks like:
    Code:
       Dim escapedSQL As String
        Dim MyVariable As String
        Dim SQLA As String
    
        MyVariable = "STUFF"
        escapedSQL = "SQL=SELECT+" & Chr(34)
        escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "WantedField" & Chr(34) & " s_2,   " & Chr(34)
        escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "WantedField1" & Chr(34) & " s_3,   " & Chr(34)
        escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "WantedField2" & Chr(34) & " s_8,   " & Chr(34)
        escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "TWantedField3" & Chr(34) & " s_18+FROM+" & Chr(34)
        escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "+WHERE+((" & Chr(34)
        escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
        escapedSQL = escapedSQL & "WantedField1" & Chr(34) & " IN ("
    
        '    Debug.Print escapedSQL
    
        SQLA = ""
    
        SQLA = SQLAccounts & "'" & MyVariable & "', "
    
        SQLA = VBA.Left(SQLAccounts, (Len(SQLA) - 2)) & ")))"
    
        escapedSQL = escapedSQL & SQLA
    This makes it easy to comment out a line or to duplicate a line and change part of the line (comment out the original line) as a test.

    I had to comment out everything after the Debug statement, but the escapedSQL string looks like
    Code:
    SQL=SELECT+"DatabaseName"."TableName"."WantedField" s_2,   "DatabaseName"."TableName"."WantedField1" s_3,   "DatabaseName"."TableName"."WantedField2" s_8,   "DatabaseName"."TableName"."TWantedField3" s_18+FROM+"DatabaseName"+WHERE+(("TableName"."WantedField1" IN (

  3. #3
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    This is a much better approach! easier to read anyway and I think that's a big part of my problem. I appreciate the formatting tip.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  2. Help Converting Oracle PL/SQL Query into Access
    By RThomas321 in forum Queries
    Replies: 5
    Last Post: 11-15-2014, 12:35 PM
  3. Creating Batch File Running Access Query
    By tcheck in forum Access
    Replies: 1
    Last Post: 12-20-2012, 01:50 PM
  4. oracle Link table query
    By mapache in forum Queries
    Replies: 1
    Last Post: 11-19-2012, 07:38 AM
  5. Replies: 1
    Last Post: 10-15-2012, 02:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums