Question Can users write queries without access to db window?

SacValGal

Registered User.
Local time
Yesterday, 18:15
Joined
Jun 16, 2005
Messages
18
I have a database (A2003) being used by an off-site client. In order to reasonably assure that all data changes are recorded in a programmed audit trail, I have shut the users out of the db window: main menu form opens on startup and can't be unloaded unless db is shut down (form is modal and minimize is disabled). I am not using Access workgroup security because I don't want to administer it remotely.

So: naturally enough, they want to be able to query their data. (They have prewritten queries that run off of buttons, but of course they have new questions all the time.) I'm wondering, is there a way that I can give them a QBE interface without letting them into the actual db window?
 
Thanks for drawing my attention to a useful resource. I don't think this will work for me because I didn't follow the presupposed table naming convention. Maybe I can have a look at it though.
 
The user mentioned Crystal reports. I've never used this software and don't have the hang of how it works. I thought of it as an expensive developer tool, not something an end user could manipulate. Am I confused? (I thought of posting a separate question in the Crystal Reports forum, but think this question makes more sense in the context of my main question.)
 
I don't know much about crystal reports, other than as far as I am aware the access reports are nearly as good in some people's opinion.
 
If you are going to let a little thing like the table naming convention stop you, then you're going to have problems!
 
Good point. I'm just a little deterred by the description from starting the download and pay up process. (I let my PayPal account lapse....) But I will have a look.
 
What about a macro or code to open the query in Design view.

You could perhaps make a few base queries and from a drop down list or a tabular form that opens have the code or macro open the selected query in design view.

I use to do that when I first started with Access 95 because I did not know how to base query criteria on data on a form.
 
If you give the users access to the QBE (which you can open for them via VBA), you are giving them free access to the data. They can change anything they want and circumvent your audits. If you use ULS and add "With Owner Access" to each query, you can control when they have update access to the data. But free access to the QBE is a license to corruption.

If they want to do queries, create an export form that provides them with basic selection criteria to wittle down the data and then export the selected data to Excel where they can't do any harm.

The additional benefit of this is that they don't create their own objects in the production database. You don't want to have to worry about saving their stuff if you have to distribute a new front end.
 
Another option - but it still requires security:

Create a "mirror" database. Start a new db, link the tables they want to query to it. Set up a generic user and an administrator. The user account has read-only access to the tables and the ability to create queries and reports.
 
Most off-site vendors that manage data for larger clients use the model of sending out flattened delimited files periodically or on request.

If your office has a something in writing, you could point to that and say, "Sorry, policy doesn't allow me to give you that access; however .... "

and set up some sort of bi-weekly, monthly, data dump to them.

-dK
 
Pat Hartman, I'm interested in the approach you suggest but don't understand what the "basic selection criteria" would be like, to cover anything they'd want to query for, or how I'd be providing these criteria. Sorry to be slow, but can you maybe point me in a direction where I can learn more about this possibility?
 
Hi -

A modification of something like this: http://www.access-programmers.co.uk/forums/showthread.php?p=737059#post737059

Although, as written, it's called from the debug (immediate) window, it could be easily adapted to a form. It prompts the user for table/query, field, and items to search on, then creates/opens a temporary SELECT QueryDef based on the user input. Once again, as written it posts the QueryDef to the debug window, allowing the user to copy/paste it to a new query -- but this could be easily omitted. The temporary querydef is automatically deleted as soon as the query opens.

Idea being that the user gets to see the query results, but has no actual access to the data, and has no ability to modify the underlying query.

Food for thought? - Bob
 
Most applications have "key" elements that would be used for selection. I don't know what yours might be but they could include things like State, OrderType, CustomerType, TransactionDate, etc. You would create a form that allows the user to select one or more of the specified values. You could have two or three standard sets of data columns. So the user would enter criteria values and select the "standard field" list and you would run a query that creates an excel file with the extracted data. This method is simple to create. You could get more sophisticated and let them select a field list but this will require more complex programming since you would need to figure out what joins are required based on which fields were selected or you would have to limit them to one table at a time. I think that the first suggestion will probably satisfy 90% of their querying needs. Once they get the data into Excel, they can do finer filtering if necessary.

Or, just try the method Bob suggested.
 

Users who are viewing this thread

Back
Top Bottom