Results 1 to 7 of 7

Thread: OpenQuery

  1. #1
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Unanswered: OpenQuery

    All,

    The normal use for this command is for and existing query.

    I have an Append (table building) query "qryBLDfnr" and need to mod it for filtering by date with:
    Code:
    Sub Sav_Tmp(MyTable)
        ' Save values to the Temp Table
        Dim DATbeg, DATend, SQLstr, WHRstr
        DATbeg = TargetForm![tboxSDT]
        DATend = TargetForm![tboxEDT]
        WHRstr = "WHERE (([tmp_wdt] <= DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))) AND " & _
                 "([tmp_wdt] >= DateSerial(Year(DATend), Month(DATend), Day(DATend))));"
        SQLstr = "SELECT * FROM qryBLDfnr " & WHRstr
        DoCmd.SetWarnings False
        DoCmd.OpenQuery SQLstr
        DoCmd.SetWarnings True
    End Sub
    Since it is an Append query the DoCmd****nSQL will not work and with the SQL string I get errors.

    I know somehow I need to assign this to an object, based on the error message of:
    Run-time error '7874':

    Microsoft Office Access can't find the object 'SELECT * FROM qryBLDfnr WHERE (([tmp_wdt] <= DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))) AND ([tmp_wdt] >= DateSerial(Year(DATend), Month(DATend), Day(DATend))));.'
    But do not know which object type, or syntax.

    Have not found any help in the HOWTOs.

    Thanks!

    DBS4M

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try the ADO execute method

    CurrentProject.Connection.Execute MySQL
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    ? Ado ?

    H,

    Do not understand your reply?

    ADO has nothing to do with this, neither does any other method, this is at the doCMD level.

    DBS4M

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by dbsupport4me
    H,

    Do not understand your reply?

    ADO has nothing to do with this, neither does any other method, this is at the doCMD level.

    DBS4M
    and as you yourself report it isnt working using docmd,
    hence why I'm suggesting you should try the connection.execute method.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    mind you as the error refers to access not finding the object it suggests that you may have a typo or mispelling in your SQL
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Working but not saving

    All,

    This code is working making query and all but not saving:
    Code:
    Sub Sav_Tmp(MyTable)
        ' Save values to the Temp Table
        Dim dbs As DAO.Database, WSp As DAO.Workspace, RSc As DAO.Recordset, RSs As DAO.Recordset
        Dim DATbeg, DATend, FMTbeg, FMTend, SQLstr, WHRstr
        Set dbs = CurrentDb
        DATbeg = TargetForm![tboxSDT]
        DATend = TargetForm![tboxEDT]
        FMTbeg = DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))
        FMTend = DateSerial(Year(DATend), Month(DATend), Day(DATend))
        WHRstr = "WHERE (([tmp_wdt] >= #" & FMTbeg & "#) AND ([tmp_wdt] <= #" & FMTend & "#));"
        SQLstr = "SELECT * FROM tmpREPfnr " & WHRstr
    '    On Error Resume Next
        With dbs              '  it would be better to check to see if the
            .QueryDefs.Delete ("qryMODfnr")
            Set TBLobj = .CreateQueryDef("qryMODfnr", SQLstr)
            DoCmd.SetWarnings False
            DoCmd.OpenQuery TBLobj.Name
            DoCmd.SetWarnings True
            .Close
        End With
    End Sub
    Needs to save as table in currentDB.

    If you can help with that would appreciate.

    Thanks!

    DBS4M

  7. #7
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Resolved - New Thread Needed

    All,

    Will close this with code of:
    Code:
    Sub Sav_Tmp_Qry(MyTable)
        ' Save values to the Temp Table
        Dim DATbeg, DATend, SQLstr, WHRstr, TBLobj As New QueryDef, dbs As DAO.Database, FMTbeg, FMTend
        Set dbs = CurrentDb
        DATbeg = TargetForm![tboxSDT]
        DATend = TargetForm![tboxEDT]
        FMTbeg = DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))
        FMTend = DateSerial(Year(DATend), Month(DATend), Day(DATend))
        WHRstr = "WHERE (([tmp_wdt] >= " & FMTbeg & ") AND ([tmp_wdt] <= " & FMTend & "));"
        SQLstr = "SELECT * FROM qryBLDfnr " & WHRstr
    '    On Error Resume Next
        With dbs              '  it would be better to check to see if the
            .QueryDefs.Delete ("qryMODfnr")
            Set TBLobj = .CreateQueryDef("qryMODfnr", SQLstr)
            DoCmd.SetWarnings False
            DoCmd.OpenQuery TBLobj.Name
            DoCmd.SetWarnings True
    '        .saveas "tblMODfnr"
            .Close
        End With
    End Sub
    And will open another thread as I need to save my results in a table, but that is another issue.

    DBS4M

Posting Permissions

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