Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    26

    Question Unanswered: Split sql and populate records

    Here is a tough one.
    Any responses, references and help would be really appreciated as I can’t find any material to help myself.

    I will have these types of SQL text in a memo field, which will be added through a form.
    Now the challenge is this that I need to split the text starting after SELECT and stop before FROM as well as I need to leave out the first two letters (like a.) and need to populate records.
    For example based on the SQL text the records will be like this with the autonumber.
    001 Formno
    002 dateraised
    003 raisedby

    Thanks in advance.


    ````````````````````````SQL``````````````````````` ``````````````````````````
    SELECT
    a.formno,
    a.dateraised,
    a.raisedby,
    a.description,
    a.priority1,
    a.formstatus,
    a.formstat1,
    a.statdate,
    a.formtype1,
    a.issuedto,
    a.issuedate
    FROM
    Adam.schangedoc a

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Don't know if there is some groovy SQL parser you could use (couldn't see anything on web with quick search) but I had a go at a clumsy, simple one to meet your requirements. It will get totally thrown if you have subselects and will warn you of this. It will also warn you (falsley) if you have derived tables or corrolated sub queries.

    Code:
    Sub Splitter()
     
        Dim sSQL As String
        Dim aSQL() As String
        Dim sElement As String
        Dim iCounter As Integer
        
        Dim i As Integer
        
        sSQL = "SELECT a.Col1, a.Col2, b.Col3, Col4 FROM a, b GROUP BY a.Col1, a.Col2, b.Col3"
        
        If Len(sSQL) - 6 <> Len(Replace(sSQL, " FROM ", "")) Then
            
            MsgBox "O dear"
        
        End If
        
        aSQL = Split(Replace(sSQL, "SELECT ", ""), ",")
            
        For i = 0 To UBound(aSQL)
            
            sElement = aSQL(i)
            
            If InStr(sElement, ".") > 0 Then iCounter = 1 Else iCounter = 2
            
            sElement = Mid(sElement, InStr(sElement, ".") + iCounter)
            
            If InStr(sElement, " FROM ") Then
                
                sElement = Mid(sElement, 1, Len(sElement) - InStr(sElement, " FROM ") - 1)
            
            End If
            
            Debug.Print sElement
            
            If InStr(aSQL(i), " FROM ") > 0 Then Exit For
            
        Next i
        
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    26

    Reply

    Thanks for the reply,

    But i have a form in which their is memo field in which the sql will be added

    forms!myfoem!sql

    and then would like to add the code to a command to do type of update query which will add the records in
    table:
    Fields!field

    Any Ideas how can i do that?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The proc is just an example. Instead of sSQL you will feed in the memo field value.

    and then would like to add the code to a command to do type of update query which will add the records in
    table:
    Fields!field
    I'm afraid I'm not with you....could you expand?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2006
    Posts
    26
    Sorry for the confusion,
    Let me give ya bit more detail and background as it got really complicated at least for me.
    First there is a bound form with only two fields from tbl_Main
    LogNO which is a AutoNumber
    SqlText which is a Memo Field

    Users add a New Record and paste the SQL text from their clipboard into
    the Forms!FRMBISQL!Sql

    After that I,m planning to have a cmd button which holds the code to
    prase the SQL in the Memo Field.
    And based on the parsed data after SELECT and before FROM,
    I would like to add records in the tblFields which has three fields

    FieldName (FieldTypes)
    FieldsNo (AutoNumber)
    LogNo (text)
    Field (text)

    The LogNo will be passed from the form current record updated.

    Hope this makes sense and thanks for the reply.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Fot the PM - sorry to leave you dangling....

    So something along the lines of (run in the After Update or After Insert as you see fit (changes in RED) & untested:
    Code:
    Sub Splitter() Dim sSQL As String Dim aSQL() As String Dim sElement As String Dim iCounter As Integer Dim i As Integer 'sSQL = "SELECT a.Col1, a.Col2, b.Col3, Col4 FROM a, b GROUP BY a.Col1, a.Col2, b.Col3" sSQL = Me.SQL If Len(sSQL) - 6 <> Len(Replace(sSQL, " FROM ", "")) Then MsgBox "O dear" End If aSQL = Split(Replace(sSQL, "SELECT ", ""), ",") For i = 0 To UBound(aSQL) sElement = aSQL(i) If InStr(sElement, ".") > 0 Then iCounter = 1 Else iCounter = 2 sElement = Mid(sElement, InStr(sElement, ".") + iCounter) If InStr(sElement, " FROM ") Then sElement = Mid(sElement, 1, Len(sElement) - InStr(sElement, " FROM ") - 1) End If Debug.Print sElement Application.CurrentDB.Execute "INSERT INTO tblFields (LogNo, Field) VALUES (" & Me.LogNo & ", '" & sElement & "')", dbFailOnError If InStr(aSQL(i), " FROM ") > 0 Then Exit For Next i End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - it will get thrown by column aliases too. I hope these SQL strings are fairly consistent otherwise you will need to adjust the logic to account for anyone writing fancy queries. Hmmm....functions too....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •