none
How to open a temp (no name) QueryDef RRS feed

  • Question

  • the following produces the expected results.

         Set qdfNew1 = db.CreateQueryDef("KKK", "SELECT ContestName FROM Contests where ContestID>20")
         DoCmd.OpenQuery "KKK"
         db.QueryDefs.Delete qdfNew1.Name
         db.Close

    But when I create a TEMP Query Def as in:

    Set qdfNew1 = db.CreateQueryDef(,"SELECT ContestName FROM Contests where ContestID>20")

    the  DoCmd.OpenQuery "qdefNew1"  produces an error.  I have also tried DoCmd.OpenQuery qdefNew1 .

    What is the proper syntax to view the results of a TEMP (not named) Query Def ? 

    Monday, February 22, 2016 12:31 AM

Answers

  • > Set qdfNew1 = db.CreateQueryDef(,"SELECT ContestName FROM Contests where ContestID>20")

    >  DoCmd.OpenQuery "qdefNew1" 

    The problem with that is that you never saved the query as that name. That's why line 2 fails.

    Maybe this will help:
    dim rs as dao.recordset
    set rs = qdefnew1.OpenRecordset(dbOpenDynaset)
    'Use recordset to access data.


    -Tom. Microsoft Access MVP

    Monday, February 22, 2016 4:46 AM
  • I think that you just cannot open a temporary query with DoCmd.OpenQuery. What I do in this case is to create a named query, say qryTemp, and then I reuse it as follows:

    Dim db  As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryTemp")
    qdf.SQL = "SELECT ContestName FROM Contests where ContestID>20"
    qdf.Close
    DoCmd.OpenQuery "qryTemp"
    

    Matthias Kläy, Kläy Computing AG

    Monday, February 22, 2016 12:41 PM
  • I use this function:

    Public Function OpenTempQuery(strSQL As String)

        Dim qdf As DAO.QueryDef
        Static n As Integer
        
        n = n + 1

        ' delete temporary querydef object if exists
        On Error Resume Next
        CurrentDb.QueryDefs.Delete "Temp" & n
        Select Case Err.Number
            Case 0
            ' no error
            Case 3265
            ' temporary querydef does not exist,ignore error
            Case Else
            ' unknown error
            MsgBox Err.Number, vbExclamation, "Error"
        End Select
        ' create temporary querydef object
        Set qdf = CurrentDb.CreateQueryDef("Temp" & n)
        CurrentDb.QueryDefs("Temp" & n).SQL = strSQL
        ' open query and then delete temporary querydef object
        DoCmd.OpenQuery "Temp" & n
        ' delete temporary querydef object
        CurrentDb.QueryDefs.Delete "Temp" & n

    End Function

    Note that by declaring the variable n as Static the function can be called multiple times, leaving the datasheet of each result table open, which is useful for ad hoc 'what if' comparisons.  

    Ken Sheridan, Stafford, England

    Monday, February 22, 2016 12:55 PM

All replies

  • Set db = CurrentDB

    On error resume next

    db.QueryDefs.Delete "KKK"

    Set qdfNew1 = db.CreateQueryDef("KKK", "SELECT ContestName FROM Contests where ContestID>20")

    qdfNew1.Close

    Set qdfNew1 = Nothing

    DoCmd.OpenQuery "KKK"


    Monday, February 22, 2016 2:04 AM
  • Sorry, but I think you missed the point.  The code you suggested is similar to what I have been using and works OK.  The real question is "What is the proper syntax to view the results of a TEMP (not named) Query Def ?"
    Monday, February 22, 2016 2:44 AM
  • > Set qdfNew1 = db.CreateQueryDef(,"SELECT ContestName FROM Contests where ContestID>20")

    >  DoCmd.OpenQuery "qdefNew1" 

    The problem with that is that you never saved the query as that name. That's why line 2 fails.

    Maybe this will help:
    dim rs as dao.recordset
    set rs = qdefnew1.OpenRecordset(dbOpenDynaset)
    'Use recordset to access data.


    -Tom. Microsoft Access MVP

    Monday, February 22, 2016 4:46 AM
  • I think that you just cannot open a temporary query with DoCmd.OpenQuery. What I do in this case is to create a named query, say qryTemp, and then I reuse it as follows:

    Dim db  As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryTemp")
    qdf.SQL = "SELECT ContestName FROM Contests where ContestID>20"
    qdf.Close
    DoCmd.OpenQuery "qryTemp"
    

    Matthias Kläy, Kläy Computing AG

    Monday, February 22, 2016 12:41 PM
  • I use this function:

    Public Function OpenTempQuery(strSQL As String)

        Dim qdf As DAO.QueryDef
        Static n As Integer
        
        n = n + 1

        ' delete temporary querydef object if exists
        On Error Resume Next
        CurrentDb.QueryDefs.Delete "Temp" & n
        Select Case Err.Number
            Case 0
            ' no error
            Case 3265
            ' temporary querydef does not exist,ignore error
            Case Else
            ' unknown error
            MsgBox Err.Number, vbExclamation, "Error"
        End Select
        ' create temporary querydef object
        Set qdf = CurrentDb.CreateQueryDef("Temp" & n)
        CurrentDb.QueryDefs("Temp" & n).SQL = strSQL
        ' open query and then delete temporary querydef object
        DoCmd.OpenQuery "Temp" & n
        ' delete temporary querydef object
        CurrentDb.QueryDefs.Delete "Temp" & n

    End Function

    Note that by declaring the variable n as Static the function can be called multiple times, leaving the datasheet of each result table open, which is useful for ad hoc 'what if' comparisons.  

    Ken Sheridan, Stafford, England

    Monday, February 22, 2016 12:55 PM