hide a query in ms access 2010

[Varadu]

HI,

I have developed an access database in 2010 version. I have created some tables and created a form for validating the user.

Based on the user role (for ex. end user), I want to hide all the queries.

I have used the following command to hide the tables.

CurrentDb.TableDefs("test table").Attributes = dbHiddenObject

Kindly let me know do we have a similar command for hiding the queries created or is there any other option for hiding the queries ?

Thanks in advance.

 

[jdraw]

I don't have access 2010, but based on your post, I'd try

CurrentDb.QueryDefs("YourQueryNameHere").Attributes = dbHiddenObject

I just found this on Google

http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/25d9dafd-b446-40ba-8dbd-a0efa983f2ff/

 

[Varadu]

Hi,

Thanks for your inputs. I have already tried this. It is not working.

I got the following error :

method or data member not found.

 

[Galaxiom]

The page linked in jdraw's post says that the provided code does not work for queries.

CurrentDb.QueryDefs("YourQueryNameHere").Attributes = dbHiddenObject

It suggests the following alternative:

SetHiddenAttribute acQuery, strcQueryName, True

 

[Varadu]

Hi,

Thanks for the inputs. I trid this as well. Issue here is even though it is shown as hidden, but it still allows the user to change the design and execute the query.

I don't want this to appear to users or users should not not be able to edit or run this query.

Kindly let me know your inputs.

 

[Guus2005]

You could save the query into a table and create it when you need it.
You could save the encrypted query to a table so when the users see it, they don't know what to do with it. They can't read it.
You could store the whole query in code and distribute the application as an MDE or ACCDE

or you can hide the query (rightclick query, options and select hidden), rightclick shutter and select Navigation Options. Remove the check: Show Hidden Objects.

HTH

 

[Khalid_Afridi]

Hi,

I don't want this to appear to users or users should not not be able to edit or run this query.

Kindly let me know your inputs.

The best solution is to hide all your queries etc is to Split your database (with fe and be) and make the accde front end file to give your user to work with it. They will not be allowed to work directly with your queries and form design.

Hope this make sense.

 

[Varadu]

Hi,

Thanks for the update. I have created accde file. I tried to run the application from that. I am having a login form for validating users. I got two command buttons one for validation and one for quit. If I click on validation it is not executing the code. Using that code I am trying to hide the tables. Kindly let me know what could be the issue.

 

[Khalid_Afridi]

Hello varadu,
I guess you want to hide the database window from your users so nobody can access the tables etc?

Here is a useful thread which solve this problem.

This is ghudson's post...

The below function and command button code will allow you to use a password
protected input box to determine if the Shift key can be disabled or not.

You might have to set your "References" to DAO 3.6. When you are viewing
the module, click the Tools menu >>> References >>> and Browse for Microsoft
DAO 3.6 >>> Select "Files of type: Executable Files (*.exe; *.dll)"
My DLL was located @ C:\Program Files\Common Files\Microsoft Shared\DAO.

Copy this function into a new public module.

Public Function SetProperties(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
On Error GoTo Err_SetProperties
    
    'Dim db As Database, prp As Property
    Dim db As DAO.Database, prp As DAO.Property
    
    Set db = CurrentDb
    db.Properties(strPropName) = varPropValue
    SetProperties = True
    Set db = Nothing
    
Exit_SetProperties:
    Exit Function
    
Err_SetProperties:
    If Err = 3270 Then 'Property not found
        Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
        db.Properties.Append prp
        Resume Next
    Else
        SetProperties = False
        MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf & Err.Description
        Resume Exit_SetProperties
    End If
    
End Function

Assign this to the OnClick event of a command (transparent?) button named "bDisableBypassKey".
Change the "TypeYourPasswordHere" default password.
This sub ensures the user is the programmer needing to disable the Bypass Key.
You can not format an Input Box!

Private Sub bDisableBypassKey_Click()
On Error GoTo Err_bDisableBypassKey_Click
    
    Dim strInput As String
    Dim strMsg As String
    
    Beep
    strMsg = "Do you want to enable the Bypass Key?" & vbCrLf & vbLf & "Please key the programmer's password to enable the Bypass Key."
    strInput = InputBox(Prompt:=strMsg, Title:="Disable Bypass Key Password")
    
    If strInput = "TypeYourPasswordHere" Then
        SetProperties "AllowBypassKey", dbBoolean, True
        Beep
        MsgBox "The Bypass Key has been enabled." & vbCrLf & vbLf & "The Shift key will allow the users to bypass the startup options the next time the database is opened.", vbInformation, "Set Startup Properties"
    Else
        Beep
        SetProperties "AllowBypassKey", dbBoolean, False
        MsgBox "Incorrect ''AllowBypassKey'' Password!" & vbCrLf & vbLf & "The Bypass Key was disabled." & vbCrLf & vbLf & "The Shift key will NOT allow the users to bypass the startup options the next time the database is opened.", vbCritical, "Invalid Password"
        Exit Sub
    End If
    
Exit_bDisableBypassKey_Click:
    Exit Sub
    
Err_bDisableBypassKey_Click:
    MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf & Err.Description
    Resume Exit_bDisableBypassKey_Click
    
End Sub

This might help.

 

[Varadu]

Hi

My requirement is to hide the queries from users. I manually tried to hide the queries but users can view the design and execute the query.

I used the following command. It shows query has hided but still users can execute or change the design of the query.

SetHiddenAttribute acQuery, "KPI Active Users Trend Graph", True

My requirement is that users should not be able to run the query manually or change the design of the query.

I got reports which is calling these queries to get the desired output.

I want users to execute the reports and not to run the query or change the design of the query.

Kinldly let me know how to achieve this.

 

[Galaxiom]

Enter the sql text of the query directly into the RecordSource property of the report.

 

[Khalid_Afridi]

This is also a good approach! usually if your queries are simple and not nested queries than the record source of the form/report/combo/listbox will provide you a this facility, and you also can write it in VBA with currentdb.openrecordset SQL statement if you have good grip on your VBA codding.

 

[Galaxiom]

Doing in VBA is a good alternative. Really big sql text queries can sometimes go bad in the Recordsource when you try to edit them.

Another alternative is to store the SQL string in the VBA and write it to the Recordsource property in the OnLoad event.

Further to Khalid's suggestion about openrecordset.

Create the recordset in the OnLoad procedure of the Report and set it as the report's recordset like this:

Set Me.Recordset = rsMyQuery