Hide Pass-Through QueryDef from Users

Discussion in 'Access VBA Modules' started by Bob St. Aubyn, Aug 5, 2004.

  1. Greetings All,

    I have a PT QueryDef that is created on the fly as the user opens the app.
    I create the qdf on every open because I have a form bound to it and,
    depending on the user (whos identity is captured just prior), the form will
    show a list customized for them.

    The problem is that I'm a bit uncomfortable with the connect property for
    this qdf being readily available to probing users' eyes. The Query can be
    hidden, but only manually through setting the property in the UI (I've
    searched NG's and Google for two days and found no way to do this
    programmatically). I have also tried deleting the query on closing the db,
    but it appears that the QueryDefs collection doesn't know about the
    code-created query until the next compact.

    By the way, I'm obliged to work with Acc97 here... :-(

    1. Does anyone know a way to hide a QueryDef?
    2. The Refresh method on the QueryDefs collection does not seem to add the
    new query to the catalog (although running it does not produce an error).
    Is there another way I can delete the query without having to compact?
    3. The real issue is hiding the connect string. Is there a better way?

    Bob St. Aubyn, Aug 5, 2004
    1. Advertisements

  2. You should be able to compact before being able to delete the query. How are
    you creating it, and how are you trying to delete it?

    Hiding the query isn't going to be that secure, since all they have to do is
    change their options to display Hidden objects (or System objects, depending
    on how you hide it).
    Douglas J. Steele, Aug 5, 2004
    1. Advertisements

  3. How I create it:

    'Build the query with the sMgrIDs string included in the SQL
    Set qdfTemp = db.CreateQueryDef()
    With qdfTemp
    .Name = sMgrMM & "_Collectors"
    .Connect = SQL_ODBC_CONNECT
    .SQL = COLL_PERSNL_SRC_SQL & sMgrIDs & _
    "ORDER BY E.LastName"
    End With
    db.QueryDefs.Append qdfTemp

    Vars in all caps are constants declared & set up top. sMgrIDs is a string
    dynamically created in previous lines and added to the SQL criteria here.

    How I (am attempting to) delete it:

    For Each qdf In db.QueryDefs
    db.QueryDefs.Delete qdf.Name
    Next qdf
    Set qdf = Nothing

    The idea here is that I want to delete ALL queries. There are no queries I
    need saved between sessions.
    Bob St. Aubyn, Aug 5, 2004
  4. Bob St. Aubyn

    Van T. Dinh Guest

    Did your code delete some Queries?

    If yes, the reason is that the QueryDefs Collection is re-indexed as soon as
    you delete a Query/QueryDef and therefore your code leave every 2nd
    Query/QueryDef in your database.

    You need to delete from the last Query/QueryDef to the first where
    re-indexing won't affect the deletion.

    Try something like:

    Dim intIndex as Integer
    Dim colQDFS As DAO.QueryDefs

    Set colQDFS = db.QueryDefs

    For intIndex = colQDFS.Count - 1 To 0 Step -1
    colQDFS.Delete colQDFS(intIndex).Name
    Next intIndex

    Set colQDFS = Nothing
    Set db = Nothing
    Van T. Dinh, Aug 5, 2004
  5. Thanks Van. Your explanation made good sense and your "untested" code
    worked perfectly. Easy to see that as one query is deleted from the
    collection the whole thing is reindexed and thereby creates a new "starting
    point" for the For-Next loop.

    One quick follow-up: Do you think that doing a create/destroy of these
    queries in this way is going to significantly add to 'bloat' of the .mdb
    file? As I mention, the query is created with a SQL statement customized to
    the user as the app is opened and deleted as it closes.
    Bob St. Aubyn, Aug 5, 2004
  6. Bob St. Aubyn

    Van T. Dinh Guest

    Sorry, I am not sure about this since I follow a different path. My users
    don't see the Database Containers Window and I also set Security on the
    Back-End (SQL Server) so I don't have PST Queries that I create at the start
    and delete at the end of the session.

    OTOH, you can use the Compact-On-Close option to prevent the database
    bloating, anyway.

    Van T. Dinh
    MVP (Access)
    Van T. Dinh, Aug 6, 2004
  7. One quick follow-up: Do you think that doing a create/destroy of
    I don't know about 'significant', but when I tested this 5 years
    ago, I decided to just delete and re-create the SQL, rather than
    deleting and re-creating the qdf. The numbers may have changed
    since then.

    BTW, (1) deleting a QDF doesn't really delete it until you do a
    compact, and (2) the simple 'hide' command is:

    application.SetHiddenAttribute objecttype, objectname, True/False

    david epsom dot com dot au, Aug 9, 2004
  8. Bob St. Aubyn


    Mar 19, 2013
    Likes Received:
    Hide QueryDef

    Here is the answer to your original post :D and the answer to similar questions:rolleyes:
    The default is Hide... so pass False as the 2nd parameter to Show
    Works for All members of acObjectType
    You'll also want to be able to HideHiddenObjects


    Public Function HideQueryDef(ByVal str_ThisQuery As String, Optional ByVal boo_HideMe As Boolean = True) As Boolean
    SetHiddenAttribute acQuery, str_ThisQuery, boo_HideMe
    HideQueryDef = GetHiddenAttribute(acQuery, str_ThisQuery)
    End Function

    Public Function HideTableDef(ByVal str_ThisTable As String, Optional ByVal boo_HideMe As Boolean = True) As Boolean
    SetHiddenAttribute acTable, str_ThisTable, boo_HideMe
    HideTableDef = GetHiddenAttribute(acTable, str_ThisTable)
    End Function

    Public Function HideHiddenObjects(Optional ByVal boo_HideMe As Boolean = True)
    Application.SetOption "Show Hidden Objects", boo_HideMe
    End Function

    Public Function HideSystemObjects(Optional ByVal boo_HideMe As Boolean = True, _
    Optional ByVal boo_SystemToo As Boolean = True)
    Application.SetOption "Show System Objects", boo_HideMe
    If boo_SystemToo Then HideHiddenObjects , boo_HideMe
    End Function

    If ever you have an idle moment, Type 'Application.' and browse the Intellisense list
    Or Press F2 for the Object Browser and dive in.
    It is so much easier than waiting until you have a problem to deal with
    Naemoor, Mar 19, 2013
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.