Can I code a module to automate hiding all tables & queries?

[BMeek]

I have an Access program with about 20 tables and about 60 queries. I have created a user-friendly interface that allows the users to point and click to create reports and/or charts from the data. I have to create about 40 variations of the program based on geographic locations and as part of our security protocols we run the one-step security wizard, then hide all the tables and queries before we make the main file a .MDE.

I am looking for code to automate the hidden object property of all tables and queries so we do not have to manually hide each object. Can someone help me with the code?

Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A TC in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[MichaelRed]

You can use the Hide method. Well described in the Help system.

I usually just hide the database window. All of the forms navigation is done via programatic control, sometimes via a "SwitchBoard" but more often by including a subform which is something like a mini-switchboard for the (valid) operations continuing from the (Current) form. Reports are also selected from a form, with various control groups set up for the options which are valid for the selected report.

The entire user interface is from forms which are included in the app. There is NEVER a time when the USER is exposed/required to use the database window, so he/she NEVER sees it. ALL objects are 'hidden' from the user - unless they are currently using the object - and then, only if it is a form (they may 'see' a report, but only if it is set to preview).



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[BMeek]

Thanks Michael,
We are using the "hide objects" to keep people with Access from importing the tables and queries into a new database. What I am really looking for is a way to "select all" of the tables and queries without having to hardcode all the names in the module.

In Excel, it is possible to Dim a variable as a "worksheet" the use a "For each" loop to hide all worksheets. Is there a similar possibility in Access?

Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A TC in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[TheInsider]

Hi,
Yes, there is a Tabledefs and Querydefs collection in Access. Just Dim a table object then use the For Each loop to loop through the collection.

Rob Marriott
rob@career-connections.net

 

[WP]

Bryan,

I have a similar need to do this for some demo sofware I have. I'd be interested in your final code.



Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[MichaelRed]

Bryan,

Your situation is not clear to me. Are you attempting to prevent users from importing tables/queries into the database by hiding those object types in that database? This doesn't really make sense. Are you attempting to keep them from importing these objects into another database for their own use?

Why is it "O.K." to import/export forms/modules/reports - but not tables/queries? It is none of 'my business', I'm just trying to understand - potentially to help.

Rob Marriott's suggestion will work for any of the document collections, however this will not prevent a user from simply doing the menu "UnHide" command and perusing any/all objects hidden from view (including my suggestion of just hiding the database window). To prevent all view of the objects, you would need to remove the unhide command from the menu structure.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[BMeek]

Michael,
I did not go into the gory details of our complete security process, but we are using the one-step security wizard to allow the "admin" default user access to only run/view the database objects. When I create the database, I am using a "SuperAdmin" user as the owner and eliminating the "Admin" user from the Admin Group. Then we are creating an .MDE file that is compiled to keep people from viewing the code, reports, and forms. When I set the user environment during startup, I also have a line of code that sets the Hide Objects checkbox value to false.

Although I know that experienced Access users can overcome the "Hide Objects" process, just as easily as they can overcome the "ByPassKey" to prevent the autorun macro from starting, my boss likes the fact that for 95% of our users, they will not see any tables or queries if they try to import them to a new database. Since the database is being shipped as an .MDE, the forms, reports, and code modules are all 'grayed' out and do not allow an import as well.

I have created custom menus and toolbars that do not allow the user entry into the "Tools" area when they run our program, so it will take an experienced user to even understand where to try and get past our security. So mainly all I am looking for is a quick way to set the properties of all the tables and queries to 'hidden' so while creating the new databases I don't have to manually set each one, or type all the names of each table and query in my module.

I have the code for the TableDef, but am still working on the QueryDef. Here is what I have so far:

Function ShowTablesQueries()
Dim AllTables As TableDef

For Each AllTables In CurrentDb.TableDefs
If Not AllTables.Attributes Mod 2 = 0 Then
On Error Resume Next
AllTables.Attributes = AllTables.Attributes - dbHiddenObject
On Error GoTo 0
End If
Next AllTables


End Function

QueryDefs has a different set of attributes, so I can't use the same code for them, but I am working on it.

Thanks for everyone's assistance.

Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A TC in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[GordyCoale]

Bryan,

Just out of interest did you manage to write some code to hide your queries?
I'm trying to do roughly the same thing as you but since the querydef object doesn't have an attribute property I can't seem to crack it.

Regards


Gordon