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?

    TIA
     
    Bob St. Aubyn, Aug 5, 2004
    #1
    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).

    --
    Doug Steele, Microsoft Access MVP

    (No private e-mails, please)


    "Bob St. Aubyn" <> wrote in message
    news:#...
    > 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?
    >
    > TIA
    >
    >
     
    Douglas J. Steele, Aug 5, 2004
    #2
    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.

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:...
    > 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).
    >
    > --
    > Doug Steele, Microsoft Access MVP
    >
    > (No private e-mails, please)
    >
    >
    > "Bob St. Aubyn" <> wrote in message
    > news:#...
    > > 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?
    > >
    > > TIA
    > >
    > >

    >
    >
     
    Bob St. Aubyn, Aug 5, 2004
    #3
  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:

    ****Untested****
    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
    ********

    --
    HTH
    Van T. Dinh
    MVP (Access)




    "Bob St. Aubyn" <> wrote in message
    news:...
    > 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.
    >
     
    Van T. Dinh, Aug 5, 2004
    #4
  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.

    "Van T. Dinh" <> wrote in message
    news:...
    > 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:
    >
    > ****Untested****
    > 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
    > ********
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    >
    > "Bob St. Aubyn" <> wrote in message
    > news:...
    > > 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
    #5
  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.

    --
    HTH
    Van T. Dinh
    MVP (Access)




    "Bob St. Aubyn" <> wrote in message
    news:...
    > 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.
    >
    > "Van T. Dinh" <> wrote in message
    > news:...
    > > 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:
    > >
    > > ****Untested****
    > > 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
    > > ********
    > >
    > > --
    > > HTH
    > > Van T. Dinh
    > > MVP (Access)
    > >
    > >
    > >
    > >
    > > "Bob St. Aubyn" <> wrote in message
    > > news:...
    > > > 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.
    > > >

    > >
    > >

    >
    >
     
    Van T. Dinh, Aug 6, 2004
    #6
  7. > One quick follow-up: Do you think that doing a create/destroy of
    > queries in this way is going to significantly add to 'bloat' of the


    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)


    "Bob St. Aubyn" <> wrote in message
    news:...
    > 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.
    >
    > "Van T. Dinh" <> wrote in message
    > news:...
    > > 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:
    > >
    > > ****Untested****
    > > 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
    > > ********
    > >
    > > --
    > > HTH
    > > Van T. Dinh
    > > MVP (Access)
    > >
    > >
    > >
    > >
    > > "Bob St. Aubyn" <> wrote in message
    > > news:...
    > > > 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.
    > > >

    > >
    > >

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

    Naemoor

    Joined:
    Mar 19, 2013
    Messages:
    1
    Likes Received:
    0
    Location:
    Glasgow
    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

    Naemoor...

    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
    #8
    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.