How to hide/show a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to be able to programatically show and hide the queries that appear in the database window

I do this with tables by setting the Attributes property, but queries don't have this property

I assume there is a way to do this since I can right-click on the query in the database window and then select properties and hidden -- but I would like to do this in code

Does anyone know how?
 
Glenn,

Try this:

Call Application.SetHiddenAttribute(acQuery, "myQuery", True)

Dan

Glenn Stein said:
I'd like to be able to programatically show and hide the queries that appear in the database window.

I do this with tables by setting the Attributes property, but queries don't have this property.

I assume there is a way to do this since I can right-click on the query in
Click to expand...
the database window and then select properties and hidden -- but I would
like to do this in code.
 
Glenn said:
I'd like to be able to programatically show and hide the queries that appear in the database window.

I do this with tables by setting the Attributes property, but queries don't have this property.
Click to expand...


I don't remember for sure, but doesn't the dbHiddenObject
attribute mark the table to be deleted the next time the db
is compacted?
 
It used to, but I think they fixed that. But, use at your
own risk.....


Chris Nebinger

-----Original Message-----
Click to expand...
property, but queries don't have this property.
 
Marsh,

I did preface it with I THINK.

But, I just tested it on my computer, with:
Office 2000 SP 3, DAO 3.6
and I could not delete the table by setting the flag. The
code I used was:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("table1")
tdf.Attributes = tdf.Attributes Or dbHiddenObject


I even set the flag back by:

tdf.Attributes = tdf.Attributes And Not dbHiddenObject

I tried everything to get the table to be deleted, but
nothing.

Interesting to note that setting that flag causes the
table to not be visible by setting the View/Option to
Hidden, but it is still in MsysObjects and you can query
on that table.

This is by no means for certain, and I have never used
that code in a production application, but it LOOKS like
it's been fixed.

Chris Nebinger


-----Original Message-----
I missed that one, what version was that change made in?
--
Marsh
MVP [MS Access]


Chris said:
It used to, but I think they fixed that. But, use at your
own risk.....

the
queries that appear in the database window.
property, but queries don't have this property.
Click to expand...

.
Click to expand...
 
Hmmm, I think(?) that's interesting, but I will have to set
up some extensive tests before I can put any eggs in that
basket ;-)

You did Compact your test db after setting the attribute,
right?

Maybe the reason that I never took note of a change in this
attribute is because I never really wanted to use code to
hide a table?? I did a quick KB search for dbHiddenObject
and did not find any reference to either a bug or a fix in
any version, so I guess it's OK for me to be ignorant of a
change to its behavior ;-\

Anyway, Chris, thanks for bringing this to my attention.
--
Marsh
MVP [MS Access]



Chris said:
Marsh,

I did preface it with I THINK.

But, I just tested it on my computer, with:
Office 2000 SP 3, DAO 3.6
and I could not delete the table by setting the flag. The
code I used was:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("table1")
tdf.Attributes = tdf.Attributes Or dbHiddenObject


I even set the flag back by:

tdf.Attributes = tdf.Attributes And Not dbHiddenObject

I tried everything to get the table to be deleted, but
nothing.

Interesting to note that setting that flag causes the
table to not be visible by setting the View/Option to
Hidden, but it is still in MsysObjects and you can query
on that table.

This is by no means for certain, and I have never used
that code in a production application, but it LOOKS like
it's been fixed.

Chris Nebinger


-----Original Message-----
I missed that one, what version was that change made in?
--
Marsh
MVP [MS Access]


Chris said:
It used to, but I think they fixed that. But, use at your
own risk.....


-----Original Message-----
Glenn Stein wrote:

I'd like to be able to programatically show and hide the
queries that appear in the database window.

I do this with tables by setting the Attributes
property, but queries don't have this property.


I don't remember for sure, but doesn't the dbHiddenObject
attribute mark the table to be deleted the next time the
db is compacted?
Click to expand...

.
Click to expand...
Click to expand...
 
I couldn't find anything in the KB either, even checking
the JET updates.

If the deletion occurs occasionally, I wouldn't want to
use the dbHiddenObject. But, after compacting it many
times, I couldn't delete the table.

I wish we could get an answer from MS about it.


Chris Nebinger

-----Original Message-----
Hmmm, I think(?) that's interesting, but I will have to set
up some extensive tests before I can put any eggs in that
basket ;-)

You did Compact your test db after setting the attribute,
right?

Maybe the reason that I never took note of a change in this
attribute is because I never really wanted to use code to
hide a table?? I did a quick KB search for dbHiddenObject
and did not find any reference to either a bug or a fix in
any version, so I guess it's OK for me to be ignorant of a
change to its behavior ;-\

Anyway, Chris, thanks for bringing this to my attention.
--
Marsh
MVP [MS Access]



Chris said:
Marsh,

I did preface it with I THINK.

But, I just tested it on my computer, with:
Office 2000 SP 3, DAO 3.6
and I could not delete the table by setting the flag. The
code I used was:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("table1")
tdf.Attributes = tdf.Attributes Or dbHiddenObject


I even set the flag back by:

tdf.Attributes = tdf.Attributes And Not dbHiddenObject

I tried everything to get the table to be deleted, but
nothing.

Interesting to note that setting that flag causes the
table to not be visible by setting the View/Option to
Hidden, but it is still in MsysObjects and you can query
on that table.

This is by no means for certain, and I have never used
that code in a production application, but it LOOKS like
it's been fixed.

Chris Nebinger


-----Original Message-----
I missed that one, what version was that change made in?
--
Marsh
MVP [MS Access]


Chris Nebinger wrote:

It used to, but I think they fixed that. But, use at your
own risk.....


-----Original Message-----
Glenn Stein wrote:

I'd like to be able to programatically show and hide the
queries that appear in the database window.

I do this with tables by setting the Attributes
property, but queries don't have this property.


I don't remember for sure, but doesn't the dbHiddenObject
attribute mark the table to be deleted the next time the
db is compacted?

.
Click to expand...
Click to expand...

.
Click to expand...
 
Hi all,

Thanks for posting in the group! I believe Solex's suggestion is helpful to
implement the task of hiding/showing a QUERY. The SetHiddenAttribute method
sets the hidden attribute available in the properties sheet of the object.
For more information, refer to the following article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac10/htm
l/acmthGetHiddenAttribute.asp

Prior to Jet 4.0 (Jet 3.5/3.0...), we can set a TABLE's attribute property
through code using either of the following lines of code, which will cause
the table to be hidden at the Jet Database Engine level.

CurrentDb.TableDefs("TableName").Attributes = 1
CurrentDb.TableDefs("TableName").Attributes = dbHiddenObject
CurrentDb.TableDefs("TableName").Properties(5) = 1
CurrentDb.TableDefs("TableName").Properties(5) = dbHiddenObject

The table object will be hiden from the Microsoft Access Database
Container, and it is marked as a temporary table in the Jet Database
Engine. When it is compacted it is deleted and removed from the MSysObjects
table. There is no reference to the table anymore and no way to get the
table back. It appears that there exists two different bit values to mean
hidden in the Microsoft Jet Database Engine. JET_bitFHidden and
JET_bitFHiddenUsr. On Compact, we are blowing away those tables that are
marked JET_bitFHidden.

In Jet 4.0 (Access 2000, 2002, 2003), the dbHiddenObject method no longer
works. It is recommended to use the SetHiddenAttribute method to hide the
table/query:
Application.SetHiddenAttribute acTable/acQuery,"myTable/myQuery", True

Or, use one of the following workarounds to hide the table:

1. Setting the Tables Attributes property to hidden through the User
Interface(UI) by right clicking on the table name and going to properties
and checking 'Hidden'.



2. Setting the Tables Attributes property through code to make the table a
System Table.



CurrentDb.TableDefs("TableName").Attributes = 2

CurrentDb.TableDefs("TableName").Attributes = dbSystemObject



CurrentDb.TableDefs("TableName").Properties(5) = 2

CurrentDb.TableDefs("TableName").Properties(5) = dbSystemObject


3. Rename the table to USysTableName to make it a User System Table



Best regards,

Billy Yao
Microsoft Online Support
 
Thanks for unraveling the confusion re this complicated
issue with your thorough explanation.

Very well done, Billy.
--
Marsh
MVP [MS Access]

PS
Considering the number of times this question has come up
over the years, would you consider submitting your post to
the KB folks as the basis of a new article? Maybe someone
in Help content could incorporate this info into the
appropriate topics too.
 
Thanks Marsh and Chris for raising this issue! I have appreciated your
professionalism and kindness to share this issue in the community. Also,
thanks for your comments.

Also, I have noticed many community members discussed on this topic in
multiple posts. Some one said a by-design issue, and others persisted in a
bug. Here I'd not like to debate with this. The fact is that the issue
really happens in the previous Jet engine version.

Believe it or not, there are lots of KB articles that provide the
workarounds to hide an object in Access database:

140213 ACC95: Constants in the Microsoft DAO 3.0 Object Library
http://support.microsoft.com/?id=140213

117536 ACC: How to Use the Attributes Property for TableDef Objects
http://support.microsoft.com/?id=117536
210362 HOW TO: Use the Attributes Property for TableDef Objects in Access
2000
http://support.microsoft.com/?id=210362


188801 ACC: Using Visual Basic to Hide a Link to an External Table
http://support.microsoft.com/?id=188801
209841 ACC2000: How to Use Visual Basic to Hide a Link to an External Table
http://support.microsoft.com/?id=209841

I'm not sure if the KB folks will pay more attention to comment on this
known issue. I regret to say that I have no privilege to document such an
official KB article (even I'd love to). Anyway, I will report this issue
and hope the KB folks will incorporate the information to clear up the
confusion.

Hopefully, Microsoft offers several ways for you and all community members
to send comments or suggestions about Microsoft products, documents, expand
feature etc. It's also recommended that you forward the recommendation to
the Microsoft Wish Program via:

http://register.microsoft.com/mswish/suggestion.asp


Respectfully,

Billy Yao
Microsoft Online Support
 
Thanks again Billy.

I guess I'm not so good at searching the KB :-(
But, it's good to know that the information is there for
those that can locate it.

Good job! A very infomative thread.
 
Back
Top