Hide tables programmatically

Topic

New Reply

Dear All,

When I load up my front end on a PC I then have to set the links to the tables on the server. The problem is that this process un-hides the tables.

I saw some code once that would loop through a collection and programmatically reset an attribute for you.

I

Reply | Quote

Replies

Hi Rupert,
I think what you want is something like:
T.attributes = T.attributes and dbhiddenobject
to hide the table.
Hope that helps.

Reply | Quote

Dear Rory,

I changed it slightly by adding a

Reply | Quote

Hi Rupert,
Sadly I don’t know of any way of doing that. I’d be very happy if anyone else does though! You can hide tables by calling them USysTablename (i.e. prefix the name with ‘USys’) or simply hide the database window.
Hope that helps?

Reply | Quote

Dear Rory,

where am I?

1) It works with tables but I can

Reply | Quote

Rupert,
To hide the db window, select Tools-Startup and uncheck Show database window. Then click on Advanced, and uncheck Use Access special keys.
I will look into hiding the linked tables – it’s not something I’ve needed as yet, but might be of use in the future.
Hope that helps.

Reply | Quote

Guys, do NOT try to set the dbHiddenObject attribute of a table in code. If you set a user-created table as hidden anywhere except from the user interface, the table disappears nicely from the database window. When you compact the database, it also disappears from there … permanently. Unfortunately, there is no way to set the UI hidden attribute from code.

Reply | Quote

Sorry, Rupert – I was not aware of that! Charlotte, is that documented somewhere? I’d love to know why that is the case.

Reply | Quote

I can’t remember every finding an article on it, but I learned it the hard way. I did have an exchange with Dev Ashish about it, and he indicated that the dbHidden attribute is used internally by Access when you delete database objects. When you “delete” a local object, it has its dbHidden attribute turned on so that you no longer see it but it actually remains in the database until the next time you compact. Basically, what you’re doing if you set the dbHiddenObject attribute on an object, is telling Access to delete it!

Reply | Quote

nice of them to mention that in the Help files!

Reply | Quote

A general point in what you did. Do not blindly add or subtract like that. Test if the attribute is set before removing or that it is not set before adding otherwise you will not get what you want.
For example attribue XX is 0 for off and 1 for on. You want to turn it on. If it is already on then after addition the value is 2 and is that off, on or the atrribute YY now switched off?

Reply | Quote

Thanks for pointing that out, Andy. Before anyone tries setting attributes, they should lookup “bitwise operations” and try to bend their brains around it.

Reply | Quote

Dear Rory, Charlotte and AndyAinscow,

Thank you all for your help on this. I can see that it’s probably not possible to do things the way I want so I’ll have to work around it, what’s new!

I found this dbHiddenObject thingy quite interesting. I’m using Access 2000 with no SR’s. I hid some tables and then compiled the DB; I found I could unhide them, even after compiling.

I did some other experiments and found that a query based on a table could still “see” and interact with the table after I hid it even though I couldn

Reply | Quote

Back to your original question. Why do you want to hide them?
I suspect you really want an answer concerning security in the database and hiding an object is not really anything to do with security.

Reply | Quote

Compiling shouldn’t affect the tables you hid. Compacting, on the other hand, should zap them. When you set the dbHiddenObject attribute, you are, in effect telling Access you want to delete that object the next time you compact the database. That’s why you can’t see the object from other databases. The query behavior is what I would expect, since you can do the same thing with system tables, which are hidden using the dbHiddenObject attribute but that’s by Access itself. The rules are different if you do it.

If you have Office, you have VBA, because that’s the programming language of Office. Looping through the tabledefs collection in code would locate the tables, but only until the first time you compact the database. Please believe me, this is dangerous and cannot be used the way you want to use it. I agree, it would be handy. That’s how I found out that it bites!

Reply | Quote

I’ve figured out how to hide tables programmatically in Access 2000 (sorry, but this won’t work in 97 at all), but it takes ADO code and a reference to the ADOX library (Microsoft ADO Ext. 2.5 for DDL and Security). Here’s a routine that will loop through the tables in the database and show or hide each linked table based on the optional blnHide argument. Because this actually sets the UI Hidden property that was not accessible with DAO, there is no risk in deleting the tables as there is with the dbHiddenObject attribute.

Public Function HideLinkedTables( _
         Optional blnHide As Boolean)
  'created by Charlotte Foust 2/14/2001
  'loops through the AllTables collection
  'and sets each linked table's "Hidden in Access"
  'property to value of blnHide
  Dim cat As ADOX.Catalog       'holds the ADO catalog object
  Dim dbs As Object             'holds pointer to current database _
                                 there is no Database object in ADO
  Dim objTdf As AccessObject    'used for looping through AllTables
  Dim obj As Object             'holds current table in catalog
  Dim prp As adodb.Property     'used for looping through ADO _
                                 properties collection
   
  'initialize the catalog and
  'make it active
  Set cat = New ADOX.Catalog
  cat.ActiveConnection = CurrentProject.Connection
  'initialize the CurrentData object
  'of the application
  Set dbs = CurrentData
  'loop throught the AccessObjects in AllTables
  For Each objTdf In dbs.AllTables
    'set the obj variable to the catalog table
    Set obj = cat.Tables(objTdf.Name)
    'if it is a linked table, set the _
      Hidden in Access property
    If obj.Properties("Jet OLEDB:Remote Table Name")  "" Then
      With obj
        .Properties("Jet OLEDB:Table Hidden In Access") = blnHide
      End With 'obj
    End If 'obj.Properties("Jet OLEDB:Remote Table Name")  ""
  Next objTdf 'In dbs.AllTables
  'cleanup and exit
  Set prp = Nothing
  Set obj = Nothing
  Set objTdf = Nothing
  Set dbs = Nothing
  Set cat = Nothing
End Function

Reply | Quote