Hide a Table in Access 97

[Janie]

need to programmatically hide or unhide table. Unfortunately,
Application.SetHiddenAttribute acTable, "NameOfTAble", True
is not available in Access 97.

Right answer, please?

Mille grazie.

 

[David H]

Prefix the table name with 'USys'. Access will think that its a system table
and hide it. Unfortunately if the db is already built you may have to go back
and update all of the forms, queries, reports and modules. Given that '97 is
'97, I would *seriously* look at upgrading. I've upgraded between several
versions of Access and have never seen any problems with it.

 

[David H]

If the database is so developed that renaming the table isn't a viable
option. You'll need to go the route of hiding the database window. Google
around and you'll find plenty of information on it, however given that you're
dealing with '97, the solutions out there may not work.

 

[Janie]

will the hidden table be deleted on Repair/Compact? I definitely do NOT want
that to happen.

By the way, you should realize upgrading is not always an option. I am
amazed at how many major corporations (including the one I work for) have not
upgraded Access due to the number of existing applications which would come
to a screeching halt if that did.

 

[tina]

the following code will hide/show the tables in a db by changing each
table's attributes to make it a system table - or not. for obvious reasons,
the MSys tables are explicitly exempted from being changed in the code. if
you only want to show/hide specific tables, you can modify the code to use a
collection, where you list specific table names, instead of looping through
all the tables in the db.

Public Function isSetTableProperties(ByVal str As String)

Dim dbs As DAO.Database, tbl As DAO.TableDef

Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Not Left(tbl.Name, 4) = "MSys" Then
If str = "hide" Then
If tbl.Attributes = 0 Then tbl.Attributes = dbSystemObject
If (tbl.Attributes And dbAttachedTable) Then
If Not (tbl.Attributes And dbSystemObject) Then
tbl.Attributes = dbSystemObject
End If
ElseIf str = "show" Then
If tbl.Attributes = dbSystemObject Then tbl.Attributes = 0
If (tbl.Attributes And dbAttachedTable) Then
If (tbl.Attributes And dbSystemObject) Then
tbl.Attributes = 0
End If
End If
End If
Next

End Function

call the function wherever you need it, example

isSetTableProperties "hide"

you can also run code when the database opens, to set the ShowSystemObjects
option to False, as follows


Public Function isSetAttributes(ByVal str As String, ByVal bln As Boolean)

If Application.GetOption(str) = (Not bln) Then Application.SetOption
str, bln

End Function

and call it as

isSetAttributes "Show System Objects", False

so you can make Access treat the tables as system objects, and then make
sure the database opens with system objects hidden. i didn't write this
code, btw, i got it from Garry Robinson's book Real World Microsoft Access
Database Protection and Security. and it does work in A97; i tested it again
(since i haven't been using it) and then copied it, from my own working A97
db. and one more note: when a local table attribute is set to make it a
"system object", the table becomes read-only *when you open the table
directly*. but you can open a query based on the table and add/edit/delete
records, and do the same in a form based on the query. this "read-only"
status does not apply to linked tables, only to local tables.

hth

 

[tina]

btw, i noticed a couple line-wraps in my posted code ( the two places where
the code line starts at the left margin, instead of neatly aligned like the
rest of the code). those mis-aligned lines should be tacked on at the end of
the previous line, rather than linewrapped or moved in.

hth

 

[David H]

No.

Also, I believe that you can run multiple versions of Access on the same
machine.

 

[David H]

I hit post too soon.

Applications built on earlier versions of Access do typically easily upgrade
to newer versions without major issues. I wouldn't quickly state that they
come to a skrecthing halt.

 

[David H]

That works for ACCESS '97?

the following code will hide/show the tables in a db by changing each
table's attributes to make it a system table - or not. for obvious reasons,
the MSys tables are explicitly exempted from being changed in the code. if
you only want to show/hide specific tables, you can modify the code to use a
collection, where you list specific table names, instead of looping through
all the tables in the db.

Public Function isSetTableProperties(ByVal str As String)

Dim dbs As DAO.Database, tbl As DAO.TableDef

Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Not Left(tbl.Name, 4) = "MSys" Then
If str = "hide" Then
If tbl.Attributes = 0 Then tbl.Attributes = dbSystemObject
If (tbl.Attributes And dbAttachedTable) Then
If Not (tbl.Attributes And dbSystemObject) Then
tbl.Attributes = dbSystemObject
End If
ElseIf str = "show" Then
If tbl.Attributes = dbSystemObject Then tbl.Attributes = 0
If (tbl.Attributes And dbAttachedTable) Then
If (tbl.Attributes And dbSystemObject) Then
tbl.Attributes = 0
End If
End If
End If
Next

End Function

call the function wherever you need it, example

isSetTableProperties "hide"

you can also run code when the database opens, to set the ShowSystemObjects
option to False, as follows


Public Function isSetAttributes(ByVal str As String, ByVal bln As Boolean)

If Application.GetOption(str) = (Not bln) Then Application.SetOption
str, bln

End Function

and call it as

isSetAttributes "Show System Objects", False

so you can make Access treat the tables as system objects, and then make
sure the database opens with system objects hidden. i didn't write this
code, btw, i got it from Garry Robinson's book Real World Microsoft Access
Database Protection and Security. and it does work in A97; i tested it again
(since i haven't been using it) and then copied it, from my own working A97
db. and one more note: when a local table attribute is set to make it a
"system object", the table becomes read-only *when you open the table
directly*. but you can open a query based on the table and add/edit/delete
records, and do the same in a form based on the query. this "read-only"
status does not apply to linked tables, only to local tables.

hth

 

[tina]

and it does work in A97; i tested it again

(since i haven't been using it) and then copied it, from my own working A97
db.

hth

 

[david]

You have to set the table to System, or use the Msys or Usys prefix.

There is no programmatic method to set the 'visible'
attribute of a table, which is setting the 'flags' field
in MsysObjects to 8. Not even a RunCommand method.

There is also a DAO property for temporary or deleted
tables, which has the name dao.dbHiddenObject, which
you can use for temporary or deleted objects. It marks
objects for removal, and they are invisible until they are
gone. This property is not available in A2000+, a new
and different property was given the old name.

(david)