Hide Table Using VBA

[Lowell]

Is there a way to hide a table using VBA. I know about
Application.SetHiddenAttribute, but the table can still be made visible by
Tools Menu>Options, View tab and selecting to display hidden objects.

Or, is there a way to disable the Options item on the Tools menu?

Thanks,
Lowell

 

[DCPan]

After you set up the way you want things to look in TOOLS -> STARTUP

Run the following piece of VBA...PLEASE NOTE YOU WILL NOT BE ABLE TO UNDO
THIS, SO MAKE A COPY FIRST!!!

Public Sub PreventBypass()

'Don't run unless you are sure you want to lock this out.
'Make a copy first!

Dim dbs As DAO.Database
Dim prp As DAO.Property

On Error GoTo ErrorFlow

Set dbs = CurrentDb
Set prp = dbs.CreateProperty(Name:="AllowByPassKey", Type:=dbBoolean,
Value:=False, DDL:=True)

dbs.Properties.Append prp

Exit Sub

ErrorFlow:

MsgBox "PROCESS ERROR " & Err.Number & " - " & Err.Description & " If
this problem" & _
" continues, contact the database administrator."

End Sub

 

[Lowell]

After you set up the way you want things to look in TOOLS -> STARTUP

Run the following piece of VBA...PLEASE NOTE YOU WILL NOT BE ABLE TO UNDO
THIS, SO MAKE A COPY FIRST!!!

Public Sub PreventBypass()

'Don't run unless you are sure you want to lock this out.
'Make a copy first!

Dim dbs As DAO.Database
Dim prp As DAO.Property

On Error GoTo ErrorFlow

Set dbs = CurrentDb
Set prp = dbs.CreateProperty(Name:="AllowByPassKey", Type:=dbBoolean,
Value:=False, DDL:=True)

dbs.Properties.Append prp

Exit Sub

ErrorFlow:

MsgBox "PROCESS ERROR " & Err.Number & " - " & Err.Description & " If
this problem" & _
" continues, contact the database administrator."

End Sub

Hi and thanks for this code. I needed it too, but it's not exactly what I'm
looking for. I need code to permanently hide a table and it seems your code
locks out the bypass key (holding SHIFT) when a database is being opened.

I think I have found a solution, so if you don't have code for hiding a
table, don't spend time researching it. If what I have does not work, I'll
add another post to this topic and ask for help again.

Thanks,
Lowell

 

[Dirk Goldgar]

Is there a way to hide a table using VBA. I know about
Application.SetHiddenAttribute,

That's the way to do it -- or you could name the table with the "USys"
prefix.

but the table can still be made visible by
Tools Menu>Options, View tab and selecting to display hidden objects.

Or, is there a way to disable the Options item on the Tools menu?

You want to hide the table so that nobody can ever see it? That's not
possible. You can go to greater or lesser lengths to make it hard to see.
For instance, you can (in .mdb files, at least) apply user-level security,
forcing users to login and then not giving them read permissions on the
table (so they can't open it and look inside). That's moderately hard to
break, but far from impossible, and it adds a lot of complexity.

You can hide the table and set startup options to remove the default
toolbars. Then you have to replace them with toolbars of your own. That
will make it harder to make the table visible, especially if you also hide
the database window and disable the bypass key. However, the users can
always link to the database from another database or application and get at
the table that way.

 

[Lowell]

Dirk,

I'm using Access 2002 at the moment. I found a way to hide the table. I
created an MDE file and then tried to import the table from a blank database.
The table is not displayed as an importable object when I try to import
external data from the blank database.

The problem is I'm stuck in the middle. The solution I found has a copyright
notice attached to it. I am going back to the website to see if I can get
permission to post the link here. I would love to share the method but I
don't want to violate the copyright.

Lowell

 

[Dirk Goldgar]

Dirk,

I'm using Access 2002 at the moment. I found a way to hide the table. I
created an MDE file and then tried to import the table from a blank
database.
The table is not displayed as an importable object when I try to import
external data from the blank database.

Even when you have checked the View Hidden/System Objects boxes in the
Options dialog of the database you're importing to?

The problem is I'm stuck in the middle. The solution I found has a
copyright
notice attached to it. I am going back to the website to see if I can get
permission to post the link here. I would love to share the method but I
don't want to violate the copyright.

I'd be very interested indeed to know if you've found a way to truly hide
the table such that a knowledgeable user can't find it.

 

[Lowell]

Dirk,

Yes. In the database I'm developing, the View hidden files box (Tools,
Options, View tab) is checked and the table is not visible. In the database
I'm importing into, I enabled view Hidden and System files. When I tried to
import, the hidden table in the original database was not displayed, but the
system tables were displayed. Since the last post, I learned that if I
compact the database, the hidden table is deleted.

I believe the person who wrote the code is a Microsoft MVP, so perhaps he
will be willing to allow me to post his solution.

 

[Dirk Goldgar]

Dirk,

Yes. In the database I'm developing, the View hidden files box (Tools,
Options, View tab) is checked and the table is not visible. In the
database
I'm importing into, I enabled view Hidden and System files. When I tried
to
import, the hidden table in the original database was not displayed, but
the
system tables were displayed. Since the last post, I learned that if I
compact the database, the hidden table is deleted.

Oh, dear. I hope you didn't do this by setting the DAO dbHiddenObject
attribute. Doing that marks the table for deletion, and as you mention, the
table will be deleted the next time the database is compacted. Are you
willing to assume that your database will never be compacted? I certainly
wouldn't be.

 

[Tony Toews [MVP]]

The problem is I'm stuck in the middle. The solution I found has a copyright
notice attached to it. I am going back to the website to see if I can get
permission to post the link here. I would love to share the method but I
don't want to violate the copyright.

Posting a link to some code isn't a violation of copyright.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Is there a way to hide a table using VBA. I know about
Application.SetHiddenAttribute, but the table can still be made visible by
Tools Menu>Options, View tab and selecting to display hidden objects.

I vaguely recall reading that with some hidden tables compacting will
remove them permanently. So I'd test that just to be sure.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Lowell]

Tony,

I agree that posting a URL should not violate copyright. It's just that the
guy has some wording in his notice about posting on websites, so I'm being
extra carefule - perhaps too careful. I've written him but do not have a
response.

Lowell

 

[Lowell]

Dirk,

I tried posting this last night but the post failed. I am using the
dbHiddenObject attribute. I have compacted the MDE file, opened the compacted
database, and the table "seems" to be there because my app works as expected.
Here's a rundown of what I've done:

1. The table in question is used to prevent unauthorized access to the
database when the customer does not pay for my development. I allow a few
weeks for the client to test and "play with" the app.

2. I created the single record table, created a form bound to the table, and
deleted the table.

3. Hard coded in VBA is the trigger date on which the table gets re-created
and the form is opened to ask for the password. The pasword is hard coded in
a hidden, disabled field on the form.

4. The user gets 3 chances to enter the correct password. The result of the
attempt(s) is stored in the table. On each subsequent launch of the database,
the table is checked for the indicator of successful or unsuccessful
password. If there was a success, the database opens. If there was a failure,
the database closes.

5. I have tested both the password success and failure routines after
creating an MDE and compacting it, and the behavior is as expected.

6. It occurred to me that the success/failure indicator may actually be
stored in the form, rather than in the table. If so, this makes my idea
simpler to implement. I will test that later today by not creating the table.

7. My method is somewhat convoluted, so if you have a better idea on
preventing unauthorized access, I would be very interested.

I really appreciate your help - Lowell

 

[Lowell]

Tony/Dirk,

I hid my table using dbHiddenObject, compacted the MDB database, and then
made the table visible again. It was still in the database and had not been
deleted. Now I'm wondering if this is normal behavior. I am assuming it is,
even though we all thought the table may be deleted when the database was
compacted.

Lowell

 

[Lowell]

Dirk/tony,

Here's the link to VBForums wit the info for hiding a table. Again, in my
testing, I not seen the hidden table get deleted when the MDB or MDE file is
compacted.

http://www.vbforums.com/showthread.php?t=350669

Lowell

 

[Dirk Goldgar]

I am using the
dbHiddenObject attribute. I have compacted the MDE file, opened the
compacted
database, and the table "seems" to be there because my app works as
expected.

I've been doing a little research and refreshing my memory about this. As
it turns out, in Jet 3.5 and before -- used by Access 97 and its
predecessors -- tables with the dbHiddenObject attribute set would be
deleted on compact. But in Jet 4.0 -- used by Access 2000, 2002, and
2003 -- they changed that (possibly because so many people were having their
tables unexpectedly deleted). I can't say for sure whether that change was
made in the very first release of Jet 4, or whether it came in a service
pack. I also can't say whether the new database engine used by Access 2007
supports this attribute or how it behaves.

So you can probably get away with hiding your table this way if your
application will only be used with Access 2000, 2002, or 2003, and also with
Access 2007 if it behaves the same way. I still don't recommend it, though.
The dbHiddenObject attribute is intended for internal Jet use and could
conceivably be changed again.

Bear in mind, also, that your table, even hidden, could still be discovered
programmatically. Anyone using VB/VBA and DAO could find it by walking
through the TableDefs collection.

7. My method is somewhat convoluted, so if you have a better idea on
preventing unauthorized access, I would be very interested.

This is not something I've thought a lot about, so I don't have any
meaningful advice to offer. If you're interested, Tony Toews has published
some of his ideas about copy protecting Access applications here:

http://www.granite.ab.ca/access/demo.htm

I haven't worked through those ideas, either.

 

[David W. Fenton]

I hid my table using dbHiddenObject, compacted the MDB database,
and then made the table visible again. It was still in the
database and had not been deleted. Now I'm wondering if this is
normal behavior. I am assuming it is, even though we all thought
the table may be deleted when the database was compacted.

I tested in A2K3 and it no longer seems to delete the table upon
compact.

However, the table and its data are still accessible through the
TableDefs collection -- anyone enumerating that table would be able
to find it and unhide it.

 

[Lowell]

Thanks, David. I plan to distribute an MDE database. When I tried to enter
the VBA project from the MDE, I was blocked. When I created a blank database
and tried to import tables from the MDE file, the hidden table was not listed
on the table import tab. So, does an MDE keep someone from writing code that
will enumerate the tabledefs?

My client is not knowledgeable enough to do this, but I suppose he could
hire someone.

Lowell

 

[Tony Toews [MVP]]

I've been doing a little research and refreshing my memory about this.

Ah, thanks for doing the research.

If you're interested, Tony Toews has published
some of his ideas about copy protecting Access applications here:

http://www.granite.ab.ca/access/demo.htm

I've been quite happily distributing an encrypted key file for some
time now. And every once in a while I think of new information to put
in there. For example I'm now thinking that one of my apps will be
sold on a one year free updates deal. After that an annual fee to get
the updates.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Thanks, David. I plan to distribute an MDE database. When I tried to enter
the VBA project from the MDE, I was blocked. When I created a blank database
and tried to import tables from the MDE file, the hidden table was not listed
on the table import tab. So, does an MDE keep someone from writing code that
will enumerate the tabledefs?

No because you can setup a workspace referencing another MDB file and
read the tabledefs collection that way.

My client is not knowledgeable enough to do this, but I suppose he could
hire someone.

I vaguely recall a movie where the executive type dumped his 25 year
wife for a trophy model. She used her portion of the divorce
settlement to hire a "hacker" who got a job as an accountant at her
husbands firm. The "hacker" was able to cook the books, electronic
and paper trail, in such a way that, when the ex wife blew the whistle
to the Inland Revenue (British movie) he was found guilty of various
tax frauds and such. And thus he spent time in jail for crimes he
didn't commit.

So ever since then I've been of the opinion that you can never assume
someone is a moron about something. They could easily set something
up as a challenge to thier 19 year old nephew who has an interest in
such and knows how to search in newsgroups or ask questions.

For all we know you could be that 19 year old nephew trying to crack
someone else's scheme. Oh man, now my brain hurts.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Lowell]

Tony,

Although I am someone' s nephew, I'm much closer to 60 than 19!! This is my
first ever Access project for someone else and I want to protect it as much
as is feasible.

I don't quite understand the concept of setting up a workspace referencing
another MDB and reading the TableDefs, since I won't be distributing the MDB
to my customer. I'll have to study on that one a little.

Thanks for your help. I've learned a few things from you and the otheres
that responded.

Lowell