Looking for 3rd party Query Builder that doesnt allow edits

[Greybeard]

Hi All
I am looking for a 3rd party query builder that can connect with several Access back-end databases simultaneously, that allows creation of queries that don't allow editing of data.
This is for a client that wants to allow employees to create queries into data, but without the ability to modify data. Put simply, they can view but not touch, even accidentally.
Does anybody know of a way that this can be done, short of building a custom builder.

[ranman256]

In access,You can set user passwords and rights.
set users to make select queries, but not action queries.

[JoeM]

set users to make select queries, but not action queries.

That doesn't prevent users from manually updating data through Select queries!

Greybeard,

Most if the articles regarding security have to do with protecting the Objects and Code in the database, not the data.

Unfortunately, Access is not the most secure, in this regard, in preventing people from updating data.
You could make the their front-end database read-only, but it is very easy to change that.
You could also make it Form-driven, and try to control everything through Forms, but it is pretty easy to get around that too.

I have been reading on the internet to see if anyone had any good solutions, and someone recommended burning the database to a CD! I guess that would work, but would be a pain everytime you had to refresh the data. But that does bring up an idea, not give them access to the actual database, but instead make a copy of their C drive whenever they want to access it. Then any changes that they make will be to their personal copy and not the production data.

[aytee111]

All I found are workarounds too! Such as on open/refresh make local copies of the tables instead of linking to them. Then you have to prevent them linking to the tables themselves.

[AccessPower]

How do folder/file permissions interact with access? That might be something to explore. If it's possible: Set everything to read only for every user but yourself. Have your backend sitting in that folder, mark it as read-only. Have the front end sitting with each user and set the file permissions so that they can only read data. If you are setup on a domain, this may work. Unfortunately I have no one with whom i can test this right now.

[CJ_London]

This is for a client that wants to allow employees to create queries into data, but without the ability to modify data.

What you are really looking for is a report writer.

depends on how good the employees are at writing sql queries - At it's simplest level this could be a form with a large textbox for the employees to write the code which is then executed using currentdb.openrecordset. You can include listboxes on the form to display fields in tables. Or you can create a form which acts in a similar way to the query builder but without the facilities to append/update/delete.

Otherwise, try googling for report writers

[Greybeard]

Working on a copy was a solution we discussed but it became unusable because they want immediate access to current data all the time. Having to copy the data each time wouldn't be workable.

[Greybeard]

File permissions won't work with Access for what I need to do, unless I am missing something.

[Greybeard]

What you are really looking for is a report writer.

depends on how good the employees are at writing sql queries - At it's simplest level this could be a form with a large textbox for the employees to write the code which is then executed using currentdb.openrecordset. You can include listboxes on the form to display fields in tables. Or you can create a form which acts in a similar way to the query builder but without the facilities to append/update/delete.

Otherwise, try googling for report writers

Ajax, a report writer that doesn't allow any type of query that modifies data would be good, I think, but the ones I looked at seem to allow you to write even delete queries. I will look further.

[Micron]

It might be possible with network permissions if the IT guy knows how to permit creating files but not altering them. This should allow a user to create a query in a fe that is located in a place they have the necessary permissions and create the locked db file, but not to alter the data in the be. Problem could be that they need to be able to delete files in the BE folder as well, or the ldb file will remain when they disconnect from the be. At least they won't be able to edit!

There are tricks you can employ to hide stuff, fake connections, use read only connections, etc., but the nature of Access is that in order to be able to create, one generally gets to edit or delete as well. If you cannot build a set of search forms for whatever reason, the only other option I can think of is to create the query for them and set it to Snapshot. That will lock the records from edits, but if they're determined to the point of being surreptitious about it, they can alter that if they can open a fe database. If you don't want the job of creating their queries, what if you allow them to construct the sql by designing a query on a limited, non-current data set, which they then submit for dumping into a new query that a db admin type person sets to snapshot?

[Greybeard]

It might be possible with network permissions if the IT guy knows how to permit creating files but not altering them. This should allow a user to create a query in a fe that is located in a place they have the necessary permissions and create the locked db file, but not to alter the data in the be. Problem could be that they need to be able to delete files in the BE folder as well, or the ldb file will remain when they disconnect from the be. At least they won't be able to edit!

There are tricks you can employ to hide stuff, fake connections, use read only connections, etc., but the nature of Access is that in order to be able to create, one generally gets to edit or delete as well. If you cannot build a set of search forms for whatever reason, the only other option I can think of is to create the query for them and set it to Snapshot. That will lock the records from edits, but if they're determined to the point of being surreptitious about it, they can alter that if they can open a fe database. If you don't want the job of creating their queries, what if you allow them to construct the sql by designing a query on a limited, non-current data set, which they then submit for dumping into a new query that a db admin type person sets to snapshot?

They want to be able to create the queries themselves. I am not on site and hence not always available. They may need data at a moments notice that they don't have existing reports for. The permissions problem, as I understand, is that the users need full rights to the BE for normal operations where they need to modify data through the FE. The FE controls what they can modify and enforces business rules. But reporting can be on any of the data and direct queries allow any editing of data and has few abilities to control what they do.

This is a big ask I think. Giving access to create and modify queries without having access to create data modifying queries. I have built a FE that gives access to queries in read-only mode that uses the query builder so they can modify those queries for thing like adding tables, fields, etc, to the query, but the problem is that some people like to play around and the query builder allows them to do some dangerous things. My answer was to 'sack those people that do this' but that didn't go down well.

If I could fiund a report writer that didn't allow users to create and data modifying queries that would be the answer, I think.

[JoeM]

Working on a copy was a solution we discussed but it became unusable because they want immediate access to current data all the time. Having to copy the data each time wouldn't be workable.

It all depends on how you do it. You could create a macro or script to do a "refresh" with a single click.

[CJ_London]

but the ones I looked at seem to allow you to write even delete queries.

I suppose for the purposes of performance sometimes you need to create a temporary table which then needs to be deleted.

You should clarify with your client exactly what they mean by 'wants to allow employees to create queries into data'. Many of my clients don't want to pay me to write their 'ad hoc' queries for them, but also don't know/understand the sql language or how the tables are related to each other, so I provide them with something (similar to the 'search form' examples you will find on this and other forums) which enables them to build an 'excel type' datasheet which they can filter and sort using excel type functionality and then export to excel. Providing the navigation window is not available to users there is no risk of users modifying data - although sophisticated users will know how to access the back end from another db (or even excel) and do their damage then - but that is the risk with using an access backend.

The only other alternative is use something like sql server where each users read/write rights can be specified. But even that would not be able to say 'this user can modify data if they are using a form, but not if they are using a home grown query'

[Micron]

I forgot to mention a db server in my post. Where I worked, all such table connections were through ODBC where I had read permissions on many tables, but not all. I could create whatever Access queries I wanted without affecting the data. Data was entered into those tables via the applications that populated them. BUT with Access alone, you cannot have your cake and eat it too. One either has the ability to create queries (along with the ability to edit data with the usual network permissions) or not for either. If data creation and edit in a somewhat controlled environment is not viable while performing nightly backups of that data onto a set of tables people can play with, then what? Yeah, what's being asked for is a tall order. Maybe impossible with Access - like handing a kid a loaded water pistol and saying "but don't get ME wet".

If a reporting system like Crystal Reports is not in the budget, I have no other ideas.

[AccessPower]

The only way that I can see this being possible without third-party software is through some very complicated file permissions. If you lock the back end to read only (no modify/execute/anything else) for everyone but yourself, then lock the front end to modify. Users should be able to create select queries, but won't be able to add/update/remove data.