Hide all tables and queries with VBA

[saqassemi]

Hi, this is a sample access file that hide and unhide one table by clicking commands button in frmSetting. Don't forgot to press F5 to refresh after hide or unhide.
Please complete the code to hide all the tables and queries by one click.
Thanks for your help.

 

[isladogs]

Hi
Attached is an updated version to hide or show all tables at once
The nav pane refreshes automatically.

It uses a query qryTables which uses the MSysObjects system table to list all non-system tables
The HideTables procedure then loops through each table in turn to hide them
The ShowHiddenTables procedure does the same in reverse to display them again

To do the same with queries, create a query qryQueries in the same way using the MSysObjects table. The type field value = 5 for queries. Then create new procedures HideQueries / ShowHiddenQueries

I'll leave this part for you to do.....

HTH

 

[saqassemi]

Thanks Colin for your help.
Your attached file work good and hide all tables but it is possible to unhide with Navigation Options.
The file that I attached work for one table and it is impossible to unhide with Navigation Option. So thanks if you help me and complete the code in my attached file at the first of thread.

 

[isladogs]

Hmmm... that's 'interesting' - I've never seen code like that before.

I've managed to combine your code & mine to hide all tables in one go
BUT when I tried doing the same in reverse, only tblUser is restored!
At the moment I can't see how to get them all back which is no use at all.
They also can't be seen from an external database where hidden tables are visible ... again something I've never seen before.
If a method for restoring them all exists, this could be very useful but at the moment I don't know the solution

Suggest you look at the source for this code & see if they have a solution

In the meantime, I have a different suggestion....
To secure my databases, I hide all tables together with hiding the navigation pane & ribbon together with other security measures. I then distribute as an ACCDE so users cannot look at tables or code. Would that work for you?

 

[The_Doc_Man]

I took a third approach. I used a "switchboard/dispatcher" type of form that was always maximized to the Access window and for which the maximize, normalize, minimize controls were disabled. So it always covered up all of those pesky details. My other forms didn't need that kind of attention - but the main form was my "watchdog."

I just about NEVER allowed my users to see the details behind the scenes, but that is what seems to be happening with your DB, saqassemi. It is not considered to safe to let users see ANYTHING directly that they don't need to do their jobs.

 

[saqassemi]

Mr. Colin thanks for your attention. Yes I do too, I hide all access options, disable bypass and save as ACCDE. But it is easy to import hidden tables in a blank database. I want to make my db one level more secure by completely hide tables and queries with the code attached at the start if thread. It is able to write the code for every table but I wand to find a way to loop the code for all tables.
Mr. Doc Man,
Forms, reports, codes are secure when a database is saved with ACCDE format but every one can import your tables in other databse even tables are hidden by right click.

 

[isladogs]

OK - here's my unfinished version from yesterday.
It loops through all tables and hides them completely so that they cannot be restored from the nav pane or another database.
The second button 'recovers' the first table tblUser ONLY. For some reason the loop doesn't work for the remaining tables but I haven't had time to work out why. The only way I could get all your tables back was by starting again with a fresh copy.

I didn't look any further at the query part of the original request

However if the idea is to make it very secure, I assume you wouldn't have this form in the distributed version anyway

As I won't have time to look into this further for a couple of days at least, I'll pass this back to you with a 'health warning' about not using it on your actual database at this point.

If you do solve it yourself , please post the solution

Also, a reminder that no matter what you do, no database can be absolutely secure. A determined hacker will be able to break any database security. All that is possible is to deter 'casual hacking'

You may find the lock database example I've attached has some useful ideas.
It was done last year as an example of a 'kiosk' style database for another forum user. Its also a 'work in progress' as I never got round to finishing it.

HTH

 

[The_Doc_Man]

saqassemi, you are correct about importing. Access is not 100% impervious to people getting data. You need something like an SQL Server to make it really tough for someone to import stuff you didn't want seen. But there is a difference between wide-open visibility and limited visibility. Nobody can change infrastructure on .ACCDE files - but can they still just open the table and make an unaudited data change? And do you really want that to happen?

If you block off visibility of the table, then they have to "hack" using a method that only exports data without changing it. People will do what they please anyway, but you don't have to make it easy for them. That is just this man's philosophy so don't take it as more than a difference in viewpoint.

 

[AlbertWorld]

Hmmm... that's 'interesting' - I've never seen code like that before.

I've managed to combine your code & mine to hide all tables in one go
BUT when I tried doing the same in reverse, only tblUser is restored!
At the moment I can't see how to get them all back which is no use at all.
They also can't be seen from an external database where hidden tables are visible ... again something I've never seen before.
If a method for restoring them all exists, this could be very useful but at the moment I don't know the solution

Suggest you look at the source for this code & see if they have a solution

In the meantime, I have a different suggestion....
To secure my databases, I hide all tables together with hiding the navigation pane & ribbon together with other security measures. I then distribute as an ACCDE so users cannot look at tables or code. Would that work for you?

Method for restoring all hidden tables

Function UnHideAllTables()
 Dim tdf  As TableDef

For Each tdf In CurrentDb.TableDefs
If tdf.Name Like "msys*" Then
Else
tdf.Attributes = 0
End If
Next tdf
   Set tdf = Nothing
End Function

 

[isladogs]

Hi Albert
Welcome to AWF.
For info, I added code tags to your post to make it easier to read.

I was being slightly disingenuous when I responded to the original thread before.
That code will indeed unhide most tables (and I use it myself) but there are some system tables not beginning with MSys that it won't make visible.
A different approach is needed for to view those and, as a matter of policy, i won't divulge that in a public forum.

However, I never did find a way of making queries completely hidden and don't believe its possible.,
Nevertheless, I did manage to recover a badly corrupted database for a client where all the tables and queries somehow all became 'deep hidden'.
That really was a first for me