Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Getting SQL from a query

    Hi,

    I have an access database, and at the moment, I'm busy writing code to copy some of the queries I have. The problem is that I need to access the SQL of these queries so that I can automatically update the selection criteria and the like (replace existing selection criteria with new criteria).
    Does anyone know how to do this. When replying, please bear in mind that I am not a developer, so a simple solution would be appreciated.

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's simple:

    open your query.
    try the box top-left of the main tool bar: it offers you run-view, design-view, ...and SQL view.

    copy/paste wherever.

    watch out for string criteria when building your SQL from the copy paste e.g.
    strSQL = "SELECT blah FROM blah WHERE TextField = Forms!MyForm.MyBox;" 'wont work if MyBox is text
    you must modify it to get the string criteria in quotes like:
    strSQL = "SELECT blah FROM blah WHERE TextField = '" & Forms!MyForm.MyBox & "';"

    you don't do this for numeric fields/boxes

    izy

  3. #3
    Join Date
    Oct 2003
    Posts
    3

    Getting SQL from a query

    I need to get the SQL whilst running VBA - the code needs to get the SQL for me, and amend it. Here's how it should happen:

    For Each obj In dbs.AllQueries
    If InStr(1, obj.Name, "XX_") <> 0 Then
    ' Get the SQL from the query

    SOMETHING NEEDS TO GO HERE

    ' Find and Replace the text with something else
    End If
    Next obj

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i never tried reading the existing SQL from code: i write it fresh from scratch each time.

    dim dbs as database
    dim qdf as QueryDef
    set dbs = currentdb 'not AS
    if isobject(dbs.QueryDefs("theName")) then 'missing ")"
    dbs.querydefs.delete "theName"
    endif
    set qdf = dbs.CreateQueryDef("theName")
    qdf.SQL = mySQLstring

    sorry can't help with the read.

    izy

    glad you managed to work around my two typos - fixed above
    Last edited by izyrider; 10-15-03 at 10:32.

  5. #5
    Join Date
    Oct 2003
    Posts
    3

    Talking Getting SQL from a query

    You're a star!!!!!

    It's working now...

Posting Permissions

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