Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    85

    Unanswered: DoCmd****nSQL (SQL_Text), acEdit

    Hello, I am making a form that builds a SQL statement in a text box called txtSQL, then I want run it with the code below under a button, but I keep getting a error saying RunSQL requires a SQL Statement, I have copied and pasted my SQL statement in to the query builder and it works fine.
    I suspect its the " & Me.txtSQL.Value & " and I have tried forms!.... this did not work either. Any insight would be appreciated, Tim

    Private Sub btnRun_Click()
    Dim SQL_Text As String

    SQL_Text = " " & Me.txtSQL.Value & ";"

    DoCmd****nSQL (SQL_Text), acEdit

    Me.Refresh

    End Sub
    Edit/Delete Message

  2. #2
    Join Date
    Apr 2009
    Posts
    85
    I see that

    DoCmd****nSQL (SQL_Text),

    Dose not support SELECT statements (sigh), I will look for the work around.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What do you want to do with the resulting SQL? Display the results to the user? If so, you would need to save the query first. See CreateQueryDef or is it SaveQueryDef... not sure, I hardly ever do it, but I think this is the answer to your issue.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Apr 2009
    Posts
    85
    Hey StarTrekker, I have a mind numming amount of data to scrub gathered from a acrobat program. I found a bit of code that alowed you to build a sql statement and displayed the results in a textbox, this piqued my imagination, I thought to create a sql statement that would

    1. Change some field names from a chosen table(one of twelve)then
    2. SELECT these fields then
    3. DELETE record's were a particular field entry's are null
    4. UNION this with a table for the clean data

    After hours into this I find out that Access dose not support RENAMING a field or SELECT statments using DocmdRun.SQL

    But this following work even better then UNION(a silver lining)

    INSERT INTO mytable(field1, field2, field3) SELECT field1, field2, field3 FROM othertable

    and this worked as a subsuitute for RENAME

    Sub RenameField(strTableName As String, _
    strFieldFrom As String, _
    strFieldTo As String) _



    Dim dbs As DAO.Database
    Dim tDef As DAO.TableDef
    Dim fDef As DAO.Field

    Set dbs = CurrentDb()
    Set tDef = dbs.TableDefs(strTableName)
    Set fDef = tDef.Fields(strFieldFrom)

    fDef.Name = strFieldTo

    Set fDef = Nothing
    Set tDef = Nothing
    Set dbs = Nothing

    'Then just call it like: RenameField "tblRenCol","Jeff","Jeffrey"
    End Sub

    So it all worked out and my form works!
    I,m know going to google
    CreateQueryDef or is it SaveQueryDef
    and see what comes up.
    Thank's for your reply, I always appriciate the responce's I get. Tim
    Last edited by Timothyl; 06-19-09 at 09:59.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Glad you got it sorted
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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