Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42

    Question Unanswered: How to edit SQL query statement with VB code?

    I don't know how to edit the query with VB.
    That query is a parameter query, so i have to change the value of parameter everytime.
    So I delete the existing query and create new query like this ...


    Function CreateListByStrQueryDef(strStrNo As String)

    Dim strStructureNumber As String
    Dim strSQL As String

    strStructureNumber = strStrNo

    strSQL = "SELECT tblMainD.* FROM tblMainD WHERE (((tblMainD.DNo) Like '" & strStructureNumber & "'));"

    DoCmd.DeleteObject acQuery, "qByStrNo"

    Set qdfTemp = CurrentDb.CreateQueryDef("qByStrNo", strSQL)

    End Function


    If anybody have the easyest way to handel a parameter query or to edit the SQL statement via VB code, let me guide.

    thanks in advance

    mmlatt

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Look up QueryDef you have nice examples getting the what you want.

  3. #3
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42

    Unhappy

    Originally posted by hammbakka
    Look up QueryDef you have nice examples getting the what you want.
    Thanks for your advice, but I had'd seen any example to edit existing query by useing any method.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by mmlatt
    Thanks for your advice, but I had'd seen any example to edit existing query by useing any method.
    here is on example I made for you:


       Dim db As Database
       Dim qdf As QueryDef, qdfLoop As QueryDef
        Set db = CurrentDb

        For Each qdfLoop In db.QueryDefs
         MsgBox (qdfLoop.Name & Chr(13) & Chr(13) & qdfLoop.sql)
        Next

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    I forgot to say, this example, shows you all names of your Quries in current DB, and its SQL string. Hope that waht you want.

    Salam

  6. #6
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42
    Originally posted by hammbakka
    I forgot to say, this example, shows you all names of your Quries in current DB, and its SQL string. Hope that waht you want.

    Salam
    Thanks you very much for your useful code.
    I'm sorry for my late response.
    Cyber : The easiest place to make huge mistake.

  7. #7
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42
    Originally posted by hammbakka
    I forgot to say, this example, shows you all names of your Quries in current DB, and its SQL string. Hope that waht you want.

    Salam
    Thanks you very much for your useful code.
    And very sorry for my late response.
    Cyber : The easiest place to make huge mistake.

  8. #8
    Join Date
    Jan 2004
    Posts
    3

    Re: How to edit SQL query statement with VB code?

    Originally posted by mmlatt
    I don't know how to edit the query with VB.
    That query is a parameter query, so i have to change the value of parameter everytime.
    So I delete the existing query and create new query like this ...


    Function CreateListByStrQueryDef(strStrNo As String)

    Dim strStructureNumber As String
    Dim strSQL As String

    strStructureNumber = strStrNo

    strSQL = "SELECT tblMainD.* FROM tblMainD WHERE (((tblMainD.DNo) Like '" & strStructureNumber & "'));"

    DoCmd.DeleteObject acQuery, "qByStrNo"

    Set qdfTemp = CurrentDb.CreateQueryDef("qByStrNo", strSQL)

    End Function


    If anybody have the easyest way to handel a parameter query or to edit the SQL statement via VB code, let me guide.

    thanks in advance

    mmlatt
    It seems to me that you are trying something that can be done in a more simple way:

    1) You can declare a global variable, which you use as the variable for the querie. Prior to calling the query, you just fill the global variable.

    2) You can build the qeury in VB-code, thus not using the original qury at all.

    3) (this one I found somewhere on the web, but never tried it you can give a variable to the query:

    Dim dbs As Database, rst As Recordset, qdfParam As QueryDef
    Set dbs = DBEngine(0)(0)
    Set qdfParam = dbs.QueryDefs("qryFilterStaffByTitle")
    qdfParam![Please enter the required job title here] = "Manager"
    Set rst = qdfParam.OpenRecordset()

    good luck!

  9. #9
    Join Date
    Jan 2004
    Location
    Myanmar (Burma)
    Posts
    42
    Thanks for your codes odijkje!
    Cyber : The easiest place to make huge mistake.

Posting Permissions

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