Hide Table Using VBA

[Tony Toews [MVP]]

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.

It's a fairly advanced topic and one I've only need to use a few
times.

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

You're quite welcome.

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/

 

[David W. Fenton]

I plan to distribute an MDE database. When I tried to enter
the VBA project from the MDE, I was blocked.

What happens if you type Ctrl-G and then in the immediate window
type type:

?CurrentDB.TableDefs("tblTable").RecordCount

where "tblTable" is replaced by the name of your hidden table.

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?

Of course not. They can't write code in the MDE, but they can use
the Immediate Window to access the TableDefs collection. Just
checking CurrentDB.TableDefs.Count and comparing it to the number of
visible tables (including system tables) could make someone
suspicious that something is hidden.

And they could also open your MDE from their own MDB and write code
there to pull out all kinds of information about the TableDefs
collection. And they could use DAO to edit that data. In fact, they
could likely create a link to the hidden table in code, using
TransferDatabase. I haven't tried it myself, but I bet it's
completely doable.

 

[David W. Fenton]

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

Why do you need a workspace?
DBEngine.OpenDatabase("MyMDEWithHiddenTable.MDE") will do the job
for you, no?

And you can poke around in the TableDefs collection from the
Immediate Window, even in an MDE.

And I speculated in my other post that you could likely create a
table link to the hidden table from a different MDE using
TransferDatabase. I just tested, and, yes, you can use
TransferDatabase to link to a hidden table.

So, I really think this hidden table thing is a waste of time. Much
better is to simply encrypt the data you store in a visiable table
so that only you can decrypt it with your private encryption key.
This will mean that you'll need to encrypt your MDE, because
otherwise, your key will be visible in the compiled code.

 

[Tony Toews [MVP]]

Why do you need a workspace?
DBEngine.OpenDatabase("MyMDEWithHiddenTable.MDE") will do the job
for you, no?

Hmm, yeah, you're right. I was just going from my hazy memory on a
similar problem.

So, I really think this hidden table thing is a waste of time. Much
better is to simply encrypt the data you store in a visiable table
so that only you can decrypt it with your private encryption key.
This will mean that you'll need to encrypt your MDE, because
otherwise, your key will be visible in the compiled code.

Or you could use strange characters in your encryption key, @#$)(&^@#$
so that's not completely obvious when viewing the code in hex. Also
you could concatenate constants placed in different modules so as to
ensure the @# is not immediately adjacent to $) and far away from (& ,
etc, etc.

Not that I've thought about this much. <smile>

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/

 

[David W. Fenton]

Or you could use strange characters in your encryption key,
@#$)(&^@#$ so that's not completely obvious when viewing the code
in hex. Also you could concatenate constants placed in different
modules so as to ensure the @# is not immediately adjacent to $)
and far away from (& , etc, etc.

Not that I've thought about this much. <smile>

Why bother with all of that when encryption is a built-in function
available with an MDB/MDE?

 

[Tony Toews [MVP]]

Why bother with all of that when encryption is a built-in function
available with an MDB/MDE?

It doesn't compress at all and I don't know how secure that is.

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/

 

[David W. Fenton]

It doesn't compress at all and I don't know how secure that is.

Er, what? It's very secure -- it's strong encryption. That it
doesn't compress is a problem for whom? Anyone who has used Jet ULS
should have been encrypting, so should be used to the lack of
compressibility.