How to Hide Queries Using VBA

[Musoke]

Hullo friends , i would like some one to help me with a VBA Code of how to Hide Queries in Access Database
Thanks

 

[CJ_London]

You mean so users cant tamper with them?

And what level of security do you want?

At a basic level,

hide the navigation window
Or right click on the query and select hidden
Or prefix the query name with ‘usys’
Or Rename your file .accdr

None are particularly secure as all can be easily overcome by a knowledgeable user

Other options

Create your queries in vba as required and destroy after use
Some queries can be put in the BE

 

[Musoke]

You mean so users cant tamper with them?

And what level of security do you want?

At a basic level,

hide the navigation window
Or right click on the query and select hidden
Or prefix the query name with ‘usys’
Or Rename your file .accdr

None are particularly secure as all can be easily overcome by a knowledgeable user

Other options

Create your queries in vba as required and destroy after use
Some queries can be put in the BE

Thanks Moderator

 

[Pat Hartman]

No user should ever have access to the Navigation Pane so you need to shut that down also. All employees should also be given a firm warning that attempting to crack the application and/or modifying any objects in the database is a firing offense. Then you lock the FE down as best you can and there are several techniques where you can bury the BE so that the users can't see it to link to it from their own FE. @isladogs has written extensively about securing your databases. But, in the end, Access databases are never truly secure.

 

[Musoke]

No user should ever have access to the Navigation Pane so you need to shut that down also. All employees should also be given a firm warning that attempting to crack the application and/or modifying any objects in the database is a firing offense. Then you lock the FE down as best you can and there are several techniques where you can bury the BE so that the users can't see it to link to it from their own FE. @isladogs has written extensively about securing your databases. But, in the end, Access databases are never truly secure.

Thanks alot for that

 

[MajP]

I usually have code to hide the navigation pane and the ribbon, but provide admins a password protected way to get to a form to show or hide these.
This demo provides the code to show and hide.

I want to give access to many of the queries but in a user friendly way. I create a table of viewable queries that includes the real query name, a descriptive title, and a description of what the query does. Then I provide a read only viewer that lets them view or export the queries.

 

[isladogs]

Tables can be deep hidden so they cannot be viewed from the navigation pane, In such cases, the tables are also not listed in the Import Tables dialog nor do they appear as part of the Tables collection. However none of that is possible for queries.

Following the mention in post #4., here is a link to one of my articles on various ways of making Access databases more secure:

Improve Access Security 2

This article explains how to apply various security measures to make your databases as secure as possible. An example app is used to illustrate the possible methods available

www.isladogs.co.uk

The level to which you should decide to lock down your apps will depend on whom / what type of tampering/hacking you are trying to protect them against.

 

[SparklySpartan]

Not sure if this is just a unique quirk of mine, but 99% of the time I need to use SQL I do it all in VBA modules using strings.

'For simple things I just type the string out:
Dim rs as Recordset
Set rs = CurrentDB.OpenRecordset("SELECT * FROM Parties WHERE Full_Name IS NOT Null")

' Or maybe some conditional manipulation of what shows up in a control's rowsource for example:
if Is_User_Admin(Get_User_ID()) then
    me.Cool_Dropown.RowSource = "SELECT ID, Text FROM Options WHERE [Option_Type]=""Cool_Dropdown_Options"" OR [Option_Type]=""Admin_Dropdown_Options"""
else
    me.Cool_Dropown.RowSource = "SELECT ID, Text FROM Options WHERE [Option_Type]=""Cool_Dropdown_Options"""
end if
me.Cool_Dropdown.Requery

Most of the code I find myself writing code that involves fetching data I use these SQL string techniques instead of using query objects. I only use query objects if a query is hard to replicate as string and super foundational (meaning it itself will need to be queried by a lot of other queries). Even then, in some cases I might just have a function return the SQL string for me and insert any desired parameters while I'm at it.

The reason I bring this up is I feel like these encoded query strings could potentially be more easy to secure than query objects. You could at least bury them deep in the code if hiding their implementation details is a concern to you.