Software

Office challenge: How would you hide an Access query

In this week's query, learn how to create and group a pivot table and test your Access skills.

Access developers often hide objects from users. Access even makes it easy - just-right click the object in the Navigation Pane (or Database window in Access 2003 and earlier) and choose Hide In This Group or Hide. Recently, a reader asked how to hide a query generated by VBA code. How would you do it and why might you want to?

Last week we asked… How would you improve this Excel spreadsheet? When I responded to this particular reader, I suggested a pivot table and sent her an example. She'll have to generate a new pivot table as needed, but it's easier than maintaining the CountIf() table. To create a pivot table, based on the example sheet from the challenge, do the following:

  1. Select the data and headings. In this case, that's A1:D7.
  2. Click the Insert tab.
  3. Click PivotTable in the Tables group and click OK. Excel will generate a blank pivot table.
  4. From the field list to the right, drag the fields and data to the pivot table. Use the following figure as your guide.

At this point, the pivot table isn't summarizing the titles by month though. You could create a Month column in the original data, but that isn't necessary—and you'll probably be surprised just how easy it is to group those dates! Here's how:

  1. Right-click any date in the Date column (in the pivot table, not the original data).
  2. Choose Group from the resulting context menu.
  3. In this case, you want to group by the month and that's what Excel chooses, so click OK.

Robert was the first to suggest a pivot table, as did many of you. Some of you mentioned advanced CountIf() functions and even the SumProduct() and Offset() functions. Those are interesting ideas, but the pivot table is quick and easy! I like Jkiernan's idea of a dynamic range and a pivot table—I haven't tried it yet, but it sounds intriguing! Thanks for another interesting challenge. If any of you have an Office document that you'd like some help with, just let me know. I'd be glad to use it in a challenge.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
doug
doug

As for understanding the challenge; I'm not sure if we are to hide a Query Object through VBA; or hide the SQL syntax of the query itself. I've ran into many database where the code or other objects have been "hidden". Personally, I'm not a big fan of "hiding" for the sake of "hiding." As with everything else; there are times when its necessary to hide an object(s); the best practice is to document the techniques and (just as importantly) the reasons. As for the reasons; they can be numerous. Some of the first ones off the top of my head include: 1. Hiding "intellectual property" 2. Preventing those experimental users from viewing syntax and/or modifying query objects that are necessary for the Access application (does this sound familiar? "hey, I don't know why the form [or report] doesn't work anymore...all I did was changed one thing in a query I needed for a spreadsheet".) 3. Protecting/Hiding external data; also limiting data from a sensitive table (e.g. (e.g. SELECT T1.EmpName, T1.HireDate FROM HREmployees AS T1 IN "C:\HRDatabase.mdb"; salary, DOB and other sensitive information is not exposed.) ************************* Hiding Query Objects: 1. Rename object with "USYS" prefix (mentioned above) PROS: Very easy to implement CONS: Requires changes to any objects that may reference the query. Users can potentially stumble upon the object by changing their navigation settings. 2. Change object's property so that its hideen. (as mentioned in the challenge) PROS: Very easy to implement CONS: Requires changes to any objects that may reference the query. Users can potentially stumble upon the object by changing their navigation settings. 3. Implement MSAccess Security; create a developer/superuser that has priveleges to view certain objects; limit the view of objects from all others including "Admin" the default user. PROS: Can be implemented at a intermediate level (no VBA expertise required) CONS: Access security isn't for everyone and requires overhead for maintenance. 4. Another way to "hide" action query objects, is to create another database which contains linked tables to the first database. The second database contains the action queries (or even other VBA code that may act upon the data; e.g. custom import/export; functions based on tables etc.) Then using VBA, the first database can create a Database object referencing the second database. Open the second database, and viola; execute queries or code as needed. PROS: Using VBA and another database opens a world of possibilities of security and "hiding" techniques. Users aren't likely to stumble on these queries when browsing the database window. CONS: Requires a higher skillset; knowledge of VBA and other advance techniques. ************************* Hiding SQL Syntax I've seen and heard of a plethora of ways of hiding SQL syntax. Most of the time it involves saving the SQL text in a file, a field in a hidden table (or external linked table); then referenced in VBA and executed in a QueryDef object, Docmd.RunSQL, or CurrentDB.Execute statement. A step further is to create a second database, with linked tables and queries. Then compile the database as a MDE and then reference it in the first database's VBA code. (Note: The most clever technique, I ran across while analyzing another person's database/application, (okay, actually it was incredibly annoying ). The previous developer had converted the SQL text using simple encryption by means of the ASC() and Chr() functions. Essentially, he/she took each character of the SQL text, got its ASCII value and stored the numbers as one long string in a memo field of a hidden table. I guess it was great way to "hide" the SQL, but painful for ongoing maintenance.) Sorry to be wordy.

kathy.wagner
kathy.wagner

Just base a report on a table...but in the Properties window...open up the Query Builder window by clicking on the ellipsis next to "Record Source". Create the query there, then just close it and answer "Yes" when it says, "Do you want to update the property?". Then you have a query hidden in a report.

nickdangerthirdi
nickdangerthirdi

and i would hide the MSaccess.exe file someplace else thats not on the hard drive of the computer, access it horrible at everything, and always a headache for me come upgrade time, because I have to spend time with the fools who write ridiculous apps in access that take 10-15 to compile the same data that a SQL based app could pull in a minute or so...

amasa
amasa

If I wanted to keep users from being able to see a query generated with VBA code, I might add code to run once the query data is closed such that the code deletes the query. for example: I let the user define criteria, specify fields, etc. which I build into a query through code. Then it runs a report based on that query. I could put code into the report to run when the report closes. That code might either delete the query or alter the query to have no fields and no criteria. I might do this if the query accesses data in another file or down a path which I don't want the users to know about. For example: in an Access database I did some years ago I had it pull out information and put it in a file on the user's PC. Then it mapped a drive to a server and copied that file to that server. Once it finished it deleted the drive mapping. I did that because we didn't want the general user to know about that server or that they could reach it. I'm always writing queries which access data in another file. If the user needs data from a file which I don't want them to try to access on their own, I could give them access to it through a code generated query and then delete the query when they close the data display.

Quizmaster
Quizmaster

You can hide a query (or any object) in Access by prefixing its name with "uSys" - this turns it into a system object which is hidden by default

steven@r
steven@r

Within a VBA module, I would create a query object, set it's SQL property to whatever it needed to be including whatever variable information was passed. Then run the Query processing the data as needed. Then close the query object (.Close), and it is gone.

Editor's Picks