Solved

Method not found while hiding table

Posted on 2011-09-23
16
254 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
  • 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
 
LVL 57
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now