Hide Pass-Through QueryDef from Users

[Bob St. Aubyn]

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]

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).

 

[Bob St. Aubyn]

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]

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

 

[Bob St. Aubyn]

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]

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)

 

[david epsom dot com dot au]

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)

 

[Naemoor]

Hide QueryDef

Here is the answer to your original post and the answer to similar questions
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