• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Method not found while hiding table

Option Compare Database
Option Explicit

Function HideTables(tvalue As String)

 Dim tdf As QueryDef
   
   For Each tdf In CurrentDb.QueryDefs
       If tdf.Name Like "tvalue" Then
       Else
           tdf.Attributes = dbHiddenObject

       End If
   Next tdf
   Set tdf = Nothing

End Function

Open in new window


Hi, I have the above function to hide my tables :o I got the code from here via search

but its saying method not found on this like:

tdf.Attributes =

Any help is much appreciated!

I am calling function like this:

Call HideTables("GetT")

Thanks!
0
Shanan212
Asked:
Shanan212
  • 8
  • 3
  • 2
  • +2
1 Solution
 
datAdrenalineCommented:
>>  Dim tdf As QueryDef <<

A QueryDef is not what you want to be working with, you need your declaration to look like this:

Dim tdf As TableDef


0
 
Jeffrey CoachmanCommented:
Why are you using the syntax for a TableDef for a query?

Dim tdf As QueryDef


This is where my confusion starts

0
 
dqmqCommented:
And, this line should not have quotes:

If tdf.Name Like "tvalue" Then


Also, to make sure we are on the same page: that function hides all tables EXCEPT the ones matching the tvalue mask.  Kind of odd...
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Here's a MSKB article that will help:

ACC: How to Use the Attributes Property for TableDef Objects
http://support.microsoft.com/kb/117536

 But you really don't want to do this.  A table flagged this way will be removed when a compact and repair is done.

Jim.
0
 
datAdrenalineCommented:
As a point of note, I would suggest your code be modified a bit too.  I would use:

tdf.Attributes = (tdf.Attributes Or dbHiddenObject)

I would do this because .Attributes is a series of bit fields and each bit represents something, so by using the Or you retain the bitfields that where previously set.
0
 
datAdrenalineCommented:
>> A table flagged this way will be removed when a compact and repair is done. <<

That is incorrect.  In Jet 3.5 (Access 97) that WAS true, however subsequent versions of the database engine do not delete tables with the hidden attribute set.

I use this obfuscating technique quite often ...
http://www.utteraccess.com/forum/Hide-table-names-code-t1526084.html&p=1527390#entry1527390
0
 
Shanan212Author Commented:
Function HideTables(tvalue As String)

 Dim tdf As TableDef
   
   For Each tdf In CurrentDb.TableDefs
       If tdf.Name Like tvalue Then

           tdf.Attributes = dbHiddenObject

       End If
   Next tdf
   Set tdf = Nothing

End Function

Open in new window


Ok I have the above. Which works...the table disappears from the side. So I go into Navigation Options to unhide it, its not there!

I tried refreshing, copy paste, etc and the table is not there! Any input?

Appreciate your help so far!
0
 
datAdrenalineCommented:
After a re-read ... I may have sounded harsh in my last post ... it was not my intent if interpretted that way! ... please accept my apologies if that was the impression gathered. :-s
0
 
Shanan212Author Commented:
Simple and does the trick!

Thanks a ton!
0
 
datAdrenalineCommented:
>> Ok I have the above. Which works...the table disappears from the side. So I go into Navigation Options to unhide it, its not there! <<

You really need to read the thread I linked to.  But in short, you are dealing with the dbHiddenObject bit of the DAO.TableDef.Attributes, which REALLY hides your table.  You can still "see" it by looping the .TableDefs collection or by examining the MSysObjects table.  If you want to control the visibility of the Access UI Table Object from the NavPane, then you can do that in a few ways.  1) Change the name of your table to have a prefix of USys, which stands for "User System". or 2) do a right click on the Table object, choose properties, then place a check by the Hidden check box. or 3) in code execute the following line of code:

SetHiddenAttribute acTable, "YourTable", True

Remember setting the DAO.TableDef("someTable").Attributes property is different than setting the attributes of the Applications visual representation of that TableDef (a Table object in the NavPane).
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<That is incorrect.  In Jet 3.5 (Access 97) that WAS true, however subsequent versions of the database engine do not delete tables with the hidden attribute set.>>

 Hum, don't remeber that bug being fixed, but then I am getting older<g>.

 Even so, I would still avoid setting it directly and instead use:

Application.SetHiddenAttribute acTable,"<table name>", True

Jim.
0
 
dqmqCommented:
>tdf.Attributes = (tdf.Attributes Or dbHiddenObject)

Very good advice.  

Also, add this to end of the procedure:
application.refreshdatabase window



>Ok I have the above. Which works...the table disappears from the side. So I go into Navigation Options to unhide it, its not there!

Yikes!  Running V2007 here and I'm seeing crazy results when manipulating the tabledef attributes in code.  Rest assured, the tables are not deleted...they can be restored to view with
            currentdb().tabledefs("tablename").Attributes = 0


However, when I set the hidden attribute in code, tables seem to vanish.  The do mysteriously reappear, for example, if you try to create a new table using the name of a hidden table.  Seems very, very buggy to me.  Anybody else?







0
 
datAdrenalineCommented:
Jim ...

Application.SetHiddenAttribute acTable,"<table name>", True

The attribute set with SetHiddenAttribute is different that setting the .Attributes of a TableDef object.  In the former, you are setting an attribute on the object that Access uses to map to the TableDef object -- its basically a "short cut" that points to the TableDef object.
0
 
datAdrenalineCommented:
dqmq:

>> Seems very, very buggy to me.  Anybody else? <<

This has not been my experience.  Have you checked out the linked I pointed to in post 36587897?

I can not make the Table object re-appear.  If I try to create a table of the same name as the hidden one, I am given the option to override the existing object.  If I do, then the object that the same name indeed does appear, but that is a new object since the hidden one was overwritten.

You may be experiencing issues because you are not manipulating the bit, but the whole value of the .Attributes property.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Application.SetHiddenAttribute acTable,"<table name>", True

The attribute set with SetHiddenAttribute is different that setting the .Attributes of a TableDef object.  In the former, you are setting an attribute on the object that Access uses to map to the TableDef object -- its basically a "short cut" that points to the TableDef object. >>

  All I remembered was that one was safe to use and the other not.  Thanks for the info.

Jim.
0
 
datAdrenalineCommented:
>> All I remembered was that one was safe to use and the other not. <<

<chuckle> ... :)  {I disagree ... but in a jovial/agreeable way, of course!}
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 8
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now