sethiddenattribute - Tables

  • Thread starter Thread starter briank
  • Start date Start date
B

briank

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.
 
Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) <> "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub
 
I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?
 
Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
 
I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?
 
It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables
 
Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.
 
Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
 
I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.
 
hhhhmmmmm,
I had one local table and the rest linked to SQL Server 2005. Let me test
it again, and I'll get back to you.
 
I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) <> "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.
 
I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?
 
The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) <> "Msys" Then
Should be
If Left(strTblName, 4) <> "Msys" Then

' End If
Should be
End If
 
I'm feeling a guilty on taking up your time on this but for the life of me I
just can't see to get this working. If I can indulge your time one more
time. Here is my code. Unless I'm blatantly missing something can you
preview?

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) <> "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing

End Sub

Thx
 
I wrote it to be called. the argument name was blnHide. Since you are using
it in a different place, you need to use an explicit True.

Change this line:
Application.SetHiddenAttribute acTable, strTblName, blnHide
To
Application.SetHiddenAttribute acTable, strTblName, True
 
Well, not sure what cloud of typos is looming over my head but this code just
doesn't seem to work on my end. I have made the change from "blnHide" to
"True" (without quotes) but still get the same error as before.
 
Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) <> "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, True
End If
Next tbl
Set tbl = Nothing

End Sub
 
The code looks fine.
What is not working?
Are you getting an error?
If so, what is the error and on which line does the error occur?
 
Run Time Error 2016: "You can't modify the attributes of Systems Tables"
Upon going into Debug, the vba line: "Application.SetHiddenAttribute
acTable, strTblName, True" is highlighted.

Any thoughts?

Thx.
 
Back
Top