?
Solved

Hiding Queries from another DB

Posted on 2005-05-16
24
Medium Priority
?
458 Views
Last Modified: 2012-06-27
I'll like to hide / unhide all my query objects except qrySample1, qrySample2 and qrySample3 from another DB. How can I achieve this ?.

I tried putting something together but couldnt quite complete it. Any help will be appreciated.
*************************
Function hidequeries()
Dim dbs As Object, tdf As Object, fld As Variant
Dim strDB As String
Dim qry1 As DAO.QueryDef

strDB = "C:\Documents and
Settings\Bill\Desktop\AMENDMENTS.mdb"
Set appAccess =
CreateObject("Access.Application.9")
appAccess.OpenCurrentDatabase strDB
Set dbs = appAccess.CurrentDb
For Each qry1 In appAccess.CurrentDb.QueryDefs
appAccess.SetHiddenAttribute acQuery, qry1.Name,
True
Next
dbs.Close
appAccess.CloseCurrentDatabase
Set appAccess = Nothing
Set dbs = Nothing
End Function
0
Comment
Question by:billcute
  • 12
  • 9
  • 3
24 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 14009022
Same as my other post:

Public Sub HideQueries()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'If you set the Attributes property of a TableDef object
'to dbHiddenObject, Microsoft Access hides the TableDef
'object. The TableDef object exists in the TableDefs
'collection, but you cannot see it in the Database window,
'even if you have selected the Show Hidden Objects option
'in the Options dialog, which is available by clicking on
'the Tools menu. To create a TableDef object that can be
'either hidden or visible, set the Attributes property to
'dbSystemObject. You can then make the TableDef object
'visible in the Database window by selecting Show System
'Objects in the Options dialog.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
    Dim qdf As QueryDef
   
    For Each qdf In CurrentDb.QueryDef
        If qdf.Name Like "yourvalue*" Then     '<---- set you criteria here
        Else
            qdf.Attributes = dbHiddenObject
        End If
    Next qdf
   
    Set qdf = Nothing
   
End Sub
 
 
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 14009023
BTW, hiding tables and queries only hides them in the back end.
Ideally, your users should not have access to them directly anyway.

:-)
0
 
LVL 4

Author Comment

by:billcute
ID: 14009061
Dan,
The queries will be in the backend.

I will need help in placing the three queries (qrySample, qrySample2 and qrySample3) within your suggested code above.

The code you offered above is not complete, it's omitting reference to path to set the functions from a DB other than the one I am trying to hide.

I will appreciate help with full codes in other to avoid errors:

See my code sample above as a template.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:dannywareham
ID: 14009164
Why do you need to do this?
Hiding tables and/or queries has no tangible benfit to the user.
They can still access them. They can still unhide them manually. They can still run queries on the tables.
If you want them to be hidden, simply prefix them with "usys".

If you are adamant about hiding them, you'll need to trap for Error 7866, which happens when mdb is exclusively opened.

Also, don't use dbHiddenObject from code. If you add dbHiddenObject to any table's Attributes, the table will be deleted during a compact operation.

0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14009193
check with the following modified code :

Function hidequeries()
Dim dbs As Object, qry1 As AccessObject
Set dbs = Application.CurrentData

For Each qry1 In dbs.AllQueries
 Application.SetHiddenAttribute acQuery, qry1.Name, True
Next


End Function
0
 
LVL 4

Author Comment

by:billcute
ID: 14041502
Dan,
I tried your suggested code but didnt work. After placing your suggested code, I used this code to call the function: "Call HideQueries". I received a compile error: Method or data member not found. Debug highlighted "QueryDef" in your code.

I also tried  ? HideQueries in the immediate Window. I received a compile error: expected function or variable.
0
 
LVL 4

Author Comment

by:billcute
ID: 14041523
Sayedaziz,
I also tried your code in my sample of DB. I also used the following codes to test. There were no errors and none of my queries were hidden.
 ? HideQueries
Call HideQueries
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14047576
This function will not fire unless u call it from any event on ur form say

Sub CommandButton_OnClick event.
 Call HideQueries
End Sub
0
 
LVL 4

Author Comment

by:billcute
ID: 14051107
Sayedaziz,
I placed the On Click Event code as suggested, it works  within my current DB. Thanks

Assumming I have a another DB on my Desktop "Amendments.mdb". What can I adapt your code to "unhide / unhide"
"Amendments.mdb"
*******************
strDB = "C:\Documents and Settings\Bill\Desktop\AMENDMENTS.mdb"
Set appAccess = CreateObject("Access.Application.9")
appAccess.OpenCurrentDatabase strDB
Set dbs = appAccess.CurrentDb

0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14051506
here is the code
~~~~~~~~~~~`

Option Compare Database
Dim appAccess As Access.Application
__________________________________________________________
Private Sub Command0_Click()
 Call hidequeries
End Sub
_________________________________________________________
Function hidequeries()
 Dim strDB As String
 Dim qry1 As DAO.QueryDef

    Const strConPath = "C:\Documents and Settings\Aziz\Desktop\"

    strDB = strConPath & "Accounting.mdb"
    Set appAccess = CreateObject("Access.Application")
   
    appAccess.OpenCurrentDatabase strDB
   
    For Each qry1 In appAccess.CurrentDb.QueryDefs
     If Left(qry1.Name, 2) = "MS" Or Left(qry1.Name, 1) = "~" Then
     Else
      appAccess.SetHiddenAttribute acQuery, qry1.Name, True
     End If
    Next
     appAccess.CloseCurrentDatabase
    Set appAccess = Nothing

End Function

0
 
LVL 4

Author Comment

by:billcute
ID: 14051612
Sayedaziz,
I received the error below:

Compile error:
"User-defined type not defined. "

Debug highlighted "appAccess As Access.Application"

Also what do I call to "unhide" the queries?

Regards
Bill
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14051632
Retype the line and when u reach at as Access.   check if u get Application in dropdown or not.

if not in ur VB project Click Tools ----> Reference and put check mark on the following references :

VB for Application
Microsoft Access 11.0 Object Library
Ole Automation
DBNamespace 7.0 Type Lib
Microsoft DAO 3.6 Object Library

in reference if u find Missing/Broken reference then try to register the .dll/.ocx file if available in the Windows\System32 directory.

To unhide Queries just change this code

appAccess.SetHiddenAttribute acQuery, qry1.Name, False   'previous it was True

Aziz

0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14051633
i m leaving now so if it is not solved i can only make it tomm.

Aziz
0
 
LVL 4

Author Comment

by:billcute
ID: 14051689
Thanks for your assistance. I followed your steps and got rid of the previous error. After compiling there were no more errors but after executing the On Click Event to Hide queries, two things hapened:

(1). The Db I was trying to hide its' queries was launched (apparently opened by your code). I really dont want to open
       the DB I was trying to hide its queries.
(2). There was another error message: Run-time error '48': "Error in loading DLL" and the following line code was
       highlighted:
       " For Each qry1 In appAccess.CurrentDb.QueryDefs"
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14054177
(1)  Replace following code with the appropriate previous code place :

    appaccess.OpenCurrentDatabase strDB ' Under this u have to add the following code
    appaccess.Visible = False                    ' This line will hide ur database whose objects
                                                             ' u want to hide.

(2) This indicates that ur DAO.3.6 dll file is corroupt ... perhaps ... I m not sure.

     To ensure this in access menu click ActiveX control and then Register
     Navigate to c:\Program Files\Common File\Microsoft Shared\DAO and then at down u will find Files Of Type ......... select from drop down Libraries ... Now u will have DAO360.dll

Double click the file and close the ActiveX window now restart access and see if it solved ur problem .... otherwise u need to reload ur office.

if u can upload ur file to public access web then it would be more convenient to chk at our end and provide solution accordingly.

Aziz
0
 
LVL 4

Author Comment

by:billcute
ID: 14054453
After implementing your suggestions above, I was able to hide the queries in the Window however, your code still open an closes the db I was trying to hide its' queries.

Is it now possible to add to your code to hide / unhide All Queries EXCEPT qrySample1, qrySample2 and qrySample3.

In other words, I want to be able to hide all queries except the three mentioned queries above

Thanks for your assistance.
0
 
LVL 12

Accepted Solution

by:
Sayedaziz earned 2000 total points
ID: 14054473
Here is the code to hide all queries except the three referred.

Function hidequeries()
 Dim strDB As String
 Dim qry1 As DAO.QueryDef

    Const strConPath = "C:\Documents and Settings\Aziz\Desktop\"

    strDB = strConPath & "Accounting.mdb"
    Set appaccess = CreateObject("Access.Application")
    appaccess.OpenCurrentDatabase strDB
    appaccess.Visible = False
   
    For Each qry1 In appaccess.CurrentDb.QueryDefs
    If Left(qry1.Name, 2) = "MS" Or Left(qry1.Name, 1) = "~" Then
     Else
      appaccess.SetHiddenAttribute acQuery, qry1.Name, True
     End If
    Next
      appaccess.SetHiddenAttribute acQuery, "qrySample1", False
      appaccess.SetHiddenAttribute acQuery, "qrySample2", False
      appaccess.SetHiddenAttribute acQuery, "qrySample3", False
End Function

It is necessary to open the database and then set its visible property to false to get access to the objects.

Aziz
0
 
LVL 4

Author Comment

by:billcute
ID: 14054588
Aziz,
Thanks for your efforts. I tried your last code. It hides the queries but have some flaws.

(1). Your code launches the DB I was trying to hide its' queries and then close it back - this not good as this may
       introduce corruption into the DB
(2). Upon hiding the query, it was easy for me to unhide them all from Ms Access --->Tools--->Options---> "set Hidden
       Objects" & "set System Objects"
       You might want to take a look at the code below which I use in hiding tables. After implementing the code, no one
        can unhide the tables from: Ms Access --->Tools--->Options---> "set Hidden Objects" & "set System Objects"

        Is it possible for your code to behave like the one below?

         Public Function HideAllTables(Optional strDB As String, Optional blHide As Boolean = True)
         Dim db As DAO.Database
         Dim tbl As TableDef

         Set wksp = DBEngine.Workspaces(0)
         strDB = "C:\Documents and Settings\Bill\Desktop\AMENDMENTS.mdb"
         Set db = DBEngine.Workspaces(0).OpenDatabase(strDB)
         For Each tbl In db.TableDefs
         ' **********************
         Debug.Print tbl.Name
 
         If blHide = False Then
         tbl.Attributes = 0
         Else
         If tbl.Name = "TBLSample2" Or tbl.Name = "TBLSample1" Then
            tbl.Attributes = 0
         Else
            tbl.Attributes = 1
         End If
         End If
        Next
        Set tbl = Nothing
        db.Close
        Set db = Nothing

        End Function
0
 
LVL 4

Author Comment

by:billcute
ID: 14055192
Aziz,
I want to thank you for trying the Hide / Unhide queries code. It's appreciated. I will award you ful points for your efforts. However, if you are able to figure out how to make it impossible for Access "Tools" ---> to unhide the queries, I'll appreciate it.

Cheers
Bill
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14058180
I will give u code for using ur own menu in which u will include only restricted menu commands not all. i will work on it today.

Thx bill ....

in case u need to have indepth look on complete sales package with loginform u can visit the following URL .... i will be happy to answer ur question in th EE at all the time.

http://www.geocities.com/aziz_abroad

D/L the database file in Aziz.zip and login with userid guest password also guest


Aziz
0
 
LVL 4

Author Comment

by:billcute
ID: 14059015
Thanks
0
 
LVL 4

Author Comment

by:billcute
ID: 14060304
Aziz,
Can you explain better what you meant by this:
---> It is necessary to open the database and then set its visible property to false to get access to the objects <----

What object (Queries) in the DB needs to have it's visible property set to "false" ?
Bill
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 14062459
i mean the objects(querries) can be acccessed only if the databse is open then set its visible property false to access them programmitically.

the database property is set to visible false so that it will not appear to the user. Then all querries were hide using For each in .....

outside the For ... next loop the 3 querries were set hidden false to make them visible.

Aziz
0
 
LVL 4

Author Comment

by:billcute
ID: 14062546
okay
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question