Solved

Method not found while hiding table

Posted on 2011-09-23
16
299 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 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