Create Query and open in Design mode

[rfoye]

I'm probably missing something really simple here. I want to create a new Query and open that in Design mode with a source table/query already chosen, but no fields yet added to the query. The wizards do something similar to this, but I can't figure it out how to create the query without selecting any fields.

Sub sOpenQryDesign()
    Dim stQuery As String
    'Replace with msgbox to prompt for name
    stQuery = "RF-test"
    sCreateNewQuery stQuery
    
    DoCmd.OpenQuery (stQuery), acViewDesign
End Sub

Sub sCreateNewQuery(stQname As String)
    Dim db      As DAO.Database
    Dim qdf     As DAO.QueryDef
    Const stSQL As String = "SELECT * FROM qry1;"
    
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(stQname, stSQL)
    qdf.Close
    Set db = Nothing
End Sub

-------------------
Rob Foye
Database Management
Regions Bank

 

[idbr]

Try this Rob:

Sub sCreateNewQuery(stQname As String, stTname As String)

    Dim db      As DAO.Database
    Dim qdf     As DAO.QueryDef
    Dim stSQL As String
    
    stSQL = "SELECT * FROM " & stTname & ";"
    
    
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(stQname, stSQL)
    qdf.Close
    Set db = Nothing
    
    DoCmd.OpenQuery stQname, acViewDesign
    
End Sub

Allows you to pre-determine the table used too.

Iain

 

[idbr]

Ah, spotted my own schoolboy error - returns all fields despite them not being visible in the design view grid. Have played about with it and can't find a method that adds the table without fields, by definition your QueryDef has to have an SQL string. Best I could come up with is replacing the SQL string with:

stSQL = "SELECT [Delete This] AS 1st FROM " & stTname & ";"

This pops up the query with one field that tells the user to delete it as first action. Not pretty though...

Iain