Hiding Access Table using VBA
Paul Clement
¤ Does anyone know how to access the properties of a table
¤ in Access through VBA? I need to hide certain tables so
¤ the user cannot see them. Any help or suggestions would
¤ be appreciated.
You can use the DAO TableDefs collection:
TableDefs("Table3").Attributes = dbHiddenObject
Paul ~~~ pcle...@ameritech.net
Microsoft MVP (Visual Basic)
david epsom dot com dot au
It is possible that the meaning of this has been changed
in Access 2002 or recent versions of Jet - in previous
versions this mis-named attribute indicated temporary
objects that were deleted by compacting.
Of course the 'hidden' is meaningless when you talk
about DAO - you can't 'see' DAO objects because there
is no DAO GUI -- so you are actually asking if you
can change an Access property using DAO. Generally
the answer is no: the access objects are stored in
an undocumented format understood only by Access.
Generally, the only way to change Access properties
is by using Access. In this case, the Access
'visible' property could be changed by using
application.SetHiddenAttribute
or you could indicate to Access that you want the
table to be hidden by giving the table a name that
begins with 'Usys'
(david)
"Paul Clement" <UseAdddressA...@swspectrum.com> wrote in message
news:bto8nvcmi5m9ekcoj...@4ax.com...
david epsom dot com dot au
Compacting does not work correctly in Access 2K+,
DAO 3.6, Jet 4. One of the problems is that, at
least with some versions, tables marked for deletion
with the dbHiddenObject attribute are not deleted
correctly.
(david)
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:...
david epsom dot com dot au
Compacting does not work correctly in Access 2K+,
DAO 3.6, Jet 4. One of the problems is that, at
least with some versions, tables marked for deletion
deleted correctly.
(david)
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:OIMVK9yh...@TK2MSFTNGP11.phx.gbl...
Paul Clement
¤ > TableDefs("Table3").Attributes = dbHiddenObject
¤
¤ It is possible that the meaning of this has been changed
¤ in Access 2002 or recent versions of Jet - in previous
¤ versions this mis-named attribute indicated temporary
¤ objects that were deleted by compacting.
Yes, my understanding was the side affect was removed in Access 2000. I know that Access 97 and
prior versions will remove the TableDef permanently (as you indicated) when compacting.
¤
¤ Of course the 'hidden' is meaningless when you talk
¤ about DAO - you can't 'see' DAO objects because there
¤ is no DAO GUI -- so you are actually asking if you
¤ can change an Access property using DAO. Generally
¤ the answer is no: the access objects are stored in
¤ an undocumented format understood only by Access.
¤ Generally, the only way to change Access properties
¤ is by using Access. In this case, the Access
¤ 'visible' property could be changed by using
¤ application.SetHiddenAttribute
¤ or you could indicate to Access that you want the
¤ table to be hidden by giving the table a name that
¤ begins with 'Usys'
¤
I'm fairly certain he wanted to remove it from the GUI. Setting the dbHiddenObject attribute doesn't
remove it from the TableDefs collection and it's still available when executing QueryDefs.
david epsom dot com dot au
OK, there is only ONE Access property that is controlled by a DAO attribute,
and it has a 'DAO side effect'!
All other Access properties, collections etc, can only be accessed through
the Access application object, and are created only when Access opens the
database. DAO can create, use, delete a database, but no other Access
attributes, system tables etc can be created by DAO: only this one solitary
'Access display attribute', which happens to have a DAO 'side effect'. For
some reason, the designers of DAO (a general purpose Data Object, for use in
VB C++ and third party environments) thought that they would include one
unique feature, a lone control of the Access GUI that Access GUI users would
not be able to override. After all, good design rules demand that code
should ALWAYS include bi-directional dependencies....
-------------------
A side effect of marking an object as temporary/deleted, was that the object
was hidden in Access. The DAO attribute was named after the side effect.
There are still temporary objects, (including tables that have been deleted
but not yet compacted). That capability was not removed in Access 2000.
But setting the DAO attribute was no longer reliable after Access 2000. And
the effect of a JET/DAO compact now depends on which version of JET/DAO you
use: Sometimes stuff is deleted, sometimes not. Sometimes autonumbers are
reset: sometimes not.
The current version of JET/DAO may be completely reliable, or completely
broken when it comes to deleting temporary objects. Certainly the first
release of DAO 3.6/JET 4 did a very poor job of compacting Access 2000
databases. Not reliable at deleting temporary objects: not reliable at
keeping temporary objects.
I haven't tested recently, but perhaps the intention was to break this
mis-named DAO attribute, and if so, perhaps MS has achieved that with the
current DAO/JET release.
(david)
"Paul Clement" <UseAdddressA...@swspectrum.com> wrote in message
news:ir4jnvo0j61i9amln...@4ax.com...
Paul Clement
¤ > Yes, my understanding was the side affect was removed in Access 2000.
¤
From what I've been able to determine, Jet/DAO ignores this attribute when compacting. I don't have
Access 2000 installed so I can't verify whether the application removes the table when compacting.
The Access XP application does not remove a table marked as hidden via DAO.
Perhaps Microsoft changed the behavior in the GUI to be consistent with the behavior in DAO.