Help again! Hiding Tables
Sam Profit
Well, I thought I was there, but I'm not. Below is the code I've used.
I'm trying to cycle through my tables and hide them all. As you can see
by the commented out line, I even tried defining ONE table and hiding
it, but that didn't work, either.
I keep getting an error code 3001 "invalid argument" on the line:
tdf.Attributes = tdf.Attributes - dbHiddenObject
Can anyone help me, please? Seems like this should be easier than this!
Sub HideTables()
Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb
'Set tdf = dbs.TableDefs("tblClinics")
For Each tdf In dbs.TableDefs
tdf.Attributes = tdf.Attributes - dbHiddenObject
Next
End Sub
Thanks in advance for any help, and thanks to the people who helped me
get THIS far with it!!
Samantha
Bill McKnight
Dim tdf As TableDef
Next
Dev Ashish
I use the following code and it works for me.
dim T as tabledef, db as database, tname as string
dim i as integer
set db=currentdb()
For I = 0 To DB.TableDefs.Count - 1
Set T = DB.TableDefs(I)
If T.Name= "tblRuns" Then
'To hide the table
T.attributes= T.Attributes OR dbHiddenObject
'To unhide the table
T.Attributes = T.Attributes - dbHiddenObject
Next I
HTH
--
Dev Ashish
Sam Profit wrote in message <34993A...@psimso.com>...
Sam Profit
Thanks Bill!!
It works GREAT!! In fact it hides them so well that I can't see them,
and I have my options set to see hidden objects. But they're there, and
the application picks up the data in the tables just fine!
Since this is a sub-set of another table, I won't need it in this case,
but what would I use if I wanted to UNhide them?
Thanks again! This is so much easier than going through them one by one
and clicking on the "Hidden" box!!
Samantha
Bill McKnight wrote:
>
> To Hide :
>
> Function HideTables()
>
> Dim dbs As Database
> Dim tdf As TableDef
>
> Set dbs = CurrentDb
>
> For Each tdf In dbs.TableDefs
> tdf.Attributes = dbHiddenObject
> Next
>
> End Function
>
> Bill McKnight
> PCS
> bm...@iname.com
>
> Sam Profit wrote in message <34993A...@psimso.com>...
> >Well, I thought I was there, but I'm not. Below is the code I've
> used.
> >I'm trying to cycle through my tables and hide them all. As you can
> see
> >by the commented out line, I even tried defining ONE table and hiding
> >it, but that didn't work, either.
> >
Terry Kreft
Erm No, this is wrong, this will turn off all attributes except the hidden
one, which is not usually what is required.
The following procedure turns the hidden attribute on or off depending on
the value of blnHide, without affecting the other attributes.
'************** Code Start *******************
Sub HideTables(blnHide As Boolean)
Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If bnHide Then
tdf.Attributes = tdf.Attributes Or dbHiddenObject
Else
tdf.Attributes = tdf.Attributes And Not dbHiddenObject
End If
Next
End Sub
'************** Code Start *******************
Bill McKnight wrote in message <88251137...@moon.aa.net>...
Simon Moore
If you rename your database objects so that they start with "usys" they are
automatically hidden. It does however create difficulties when you want to
manipulate them
Sam Profit <cpr...@psimso.com> wrote in article
Sam Profit
These are tables that are used extensively in queries, forms, reports
and modules, Simon, so that wouldn't work in this case. Thanks for the
thought and taking the time to answer, though.
Samantha
Sam Profit
Thanks for the correction, Terry. The database I'm using this on is a
subset of my main database. I copy the database, delete tables of
sensitive information, hide the tables, and rename the database and it's
accessed by an application that allows the whole office to look up this
information in directories. Since it's just a temporary copy, no real
harm is done no matter what I or anyone else does to it. I'm glad I
read your reply before I used this on something else, though.
What are some of the other attributes that are turned off? The tables
seem to work okay in the application, although they don't appear in the
database at all, even with my default set to see hidden objects.
Thanks again for your help.
Samantha
Terry Kreft wrote:
>
> Erm No, this is wrong, this will turn off all attributes except the hidden
> one, which is not usually what is required.
>
> The following procedure turns the hidden attribute on or off depending on
> the value of blnHide, without affecting the other attributes.
>
> '************** Code Start *******************
> Sub HideTables(blnHide As Boolean)
> Dim dbs As Database, tdf As TableDef
> Set dbs = CurrentDb
>
> For Each tdf In dbs.TableDefs
> If bnHide Then
> tdf.Attributes = tdf.Attributes Or dbHiddenObject
> Else
> tdf.Attributes = tdf.Attributes And Not dbHiddenObject
> End If
> Next
> End Sub
> '************** Code Start *******************
>
> Bill McKnight wrote in message <88251137...@moon.aa.net>...
> To Hide :
>
> Function HideTables()
>
> Dim dbs As Database
> Dim tdf As TableDef
>
> Set dbs = CurrentDb
>
> For Each tdf In dbs.TableDefs
> tdf.Attributes = dbHiddenObject
> Next
>
> End Function
>
> Bill McKnight
> PCS
> bm...@iname.com
>
> Sam Profit wrote in message <34993A...@psimso.com>...
TR
Actually, naming them USys... isn't such a problem. Setting Show System
Objects to True will display them, and you can include them in queries
etc., and refernce them in DAO, etc. The only place I have not seen them
show up is in the query wizards, but they Do show up in the regular Add
Table... dialog.
By the way, using the prefix USys... will also hide forms, queries, and
reports.
But as you stated in an earlier post, setting the attribute REALLY hides
them, so that even Show System Objects doesn't display them.
Sam Profit <cpr...@psimso.com> wrote in article
<349AE0...@psimso.com>...
> These are tables that are used extensively in queries, forms, reports
> and modules, Simon, so that wouldn't work in this case. Thanks for the
> thought and taking the time to answer, though.
>
> Samantha
>
> Simon Moore wrote:
> >
> > If you rename your database objects so that they start with "usys" they
are
> > automatically hidden. It does however create difficulties when you
want to
> > manipulate them
> >
> > Sam Profit <cpr...@psimso.com> wrote in article
Terry Kreft
Hi Sam,
What follows is my reply to your e-mail
=====================================================
The Hidden attribute for the database window is different to the hidden
attribute for the the tabledef object. The first one is an Access attribute
while the second one is an attribute of the tabledef object itself
(therefore ultimately it's set within the Jet database engine).
Attributes of Tabledef object
======================
If you type Attributes in a code module or the debug window, select the word
and press F1 it will take you straight to the Help topic which lists and
explains the Attributes available for all objects under Jet.
The Attributes property (as you no doubt now realise) is a bitwise flag
property, so the attributes for a tabledef are made up of the OR'ed
attribute settings.
You set or unset them in pretty much the same way as with the previous code.
Hidden attribute in database window
============================
With the hidden attribute that you set through the database window, this
sets a flag in the MSysyObjects table, on the Flags field. Any object which
is hidden has bit 3 of the Flags field set, so the following code was put
together for enumerating hidden objects, (this is a refined version of some
code which I bounced off Ken Getz, which he kindly tidied up for me).
The code will work for all Access objects all that needs to be done is to
pass the appropriate constant to the function.
Public Const hoBaseTable = 1
Public Const hoQuery = 5
Public Const hoLinkTable = 6
Public Const hoForms = -32768
Public Const hoReport = -32764
Public Const hoMacro = -32766
Public Const hoModule = -32761
Function HiddenObjects(intObjType As Integer)
Dim lorst As Recordset
Dim strSQl As String
Const HIDDEN_FLAG = 8
strSQl = "select * from [MSysObjects] where [Type]= " & intObjType & "
Order By Name"
Set lorst = CurrentDb.OpenRecordset(strSQl, dbOpenSnapshot)
Do Until lorst.EOF
If (lorst![Flags] And HIDDEN_FLAG) = HIDDEN_FLAG Then
Debug.Print lorst!Name
End If
lorst.MoveNext
Loop
End Function
Sam Profit wrote in message <349AE3...@psimso.com>...
>Thanks for the correction, Terry. The database I'm using this on is a
>subset of my main database. I copy the database, delete tables of
>sensitive information, hide the tables, and rename the database and it's
>accessed by an application that allows the whole office to look up this
>information in directories. Since it's just a temporary copy, no real
>harm is done no matter what I or anyone else does to it. I'm glad I
>read your reply before I used this on something else, though.
>
>What are some of the other attributes that are turned off? The tables
>seem to work okay in the application, although they don't appear in the
>database at all, even with my default set to see hidden objects.
>
>Thanks again for your help.
>
> Samantha
>
>
>
>Terry Kreft wrote:
>>
>> Erm No, this is wrong, this will turn off all attributes except the
hidden
>> one, which is not usually what is required.
>>
>> The following procedure turns the hidden attribute on or off depending on
>> the value of blnHide, without affecting the other attributes.
>>
>> '************** Code Start *******************
>> Sub HideTables(blnHide As Boolean)
>> Dim dbs As Database, tdf As TableDef
>> Set dbs = CurrentDb
>>
>> For Each tdf In dbs.TableDefs
>> If bnHide Then
>> tdf.Attributes = tdf.Attributes Or dbHiddenObject
>> Else
>> tdf.Attributes = tdf.Attributes And Not dbHiddenObject
>> End If
>> Next
>> End Sub
>> '************** Code Start *******************
>>
>> Bill McKnight wrote in message <88251137...@moon.aa.net>...
>> To Hide :
>>
>> Function HideTables()
>>
>> Dim dbs As Database
>> Dim tdf As TableDef
>>
>> Set dbs = CurrentDb
>>
>> For Each tdf In dbs.TableDefs
>> tdf.Attributes = dbHiddenObject
>> Next
>>
>> End Function
>>
>> Bill McKnight
>> PCS
>> bm...@iname.com
>>
>> Sam Profit wrote in message <34993A...@psimso.com>...
Pierre DEBRAS
Sam,
... tdf.Attributes = tdf.Attributes - dbHiddenObject ...
attributes is actually an array of bits and dbHiddenObject is only
position one bit to true or false in the array. Thus you cannot use
minus. You must perform a bitwize comparison. Something like
tdf.Attributes = tdf.Attributes AND NOT dbHiddenObject
Hope this help,
Pierre
TR wrote:
>
> Actually, naming them USys... isn't such a problem. Setting Show System
> Objects to True will display them, and you can include them in queries
> etc., and refernce them in DAO, etc. The only place I have not seen them
> show up is in the query wizards, but they Do show up in the regular Add
> Table... dialog.
> By the way, using the prefix USys... will also hide forms, queries, and
> reports.
>
> But as you stated in an earlier post, setting the attribute REALLY hides
> them, so that even Show System Objects doesn't display them.
>
> Sam Profit <cpr...@psimso.com> wrote in article
> <349AE0...@psimso.com>...
> > These are tables that are used extensively in queries, forms, reports
> > and modules, Simon, so that wouldn't work in this case. Thanks for the
> > thought and taking the time to answer, though.
> >
> > Samantha
> >
> > Simon Moore wrote:
> > >
> > > If you rename your database objects so that they start with "usys" they
> are
> > > automatically hidden. It does however create difficulties when you
> want to
> > > manipulate them
> > >
> > > Sam Profit <cpr...@psimso.com> wrote in article