[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Method not found while hiding table

Posted on 2011-09-23
16
Medium Priority
?
308 Views
Last Modified: 2012-05-12
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
Comment
Question by:Shanan212
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +2
16 Comments
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36587745
>>  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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36587763
Why are you using the syntax for a TableDef for a query?

Dim tdf As QueryDef


This is where my confusion starts

0
 
LVL 42

Expert Comment

by:dqmq
ID: 36587789
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58
ID: 36587810
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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36587846
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
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 2000 total points
ID: 36587897
>> 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
 
LVL 13

Author Comment

by:Shanan212
ID: 36587902
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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36587923
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
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36587995
Simple and does the trick!

Thanks a ton!
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36588014
>> 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
 
LVL 58
ID: 36588017
<<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
 
LVL 42

Expert Comment

by:dqmq
ID: 36588182
>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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36588226
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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36588286
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
 
LVL 58
ID: 36588492
<<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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36588569
>> 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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question