Interface to allow users to create ad-hoc queries

[DaveJHPT]

Hi All

I'd like to implement something in an app to allow users to create their own ad-hoc queries from a specific number of fields from specific tables, without allowing them full access to the query designer.

I can see how to do it (a form with code that creates the SQL), but I wondered if anyone had done something like this before and had any tips or even any existing code. Have searched and can't find anything, but I suspect I'm searching on the wrong keywords.

Dave

 

[yeatmanj]

Me Too Me Too.

 

[Pat Hartman]

On the surface this sounds innocuous but can in fact be quite dangerous. Once you let the user build a query, he can update the table with it and you can't stop him even if you have implemented Access security. A safer solution is to build a query that selects all columns but asks for criteria to limit the number of rows selected and export that data to Excel where the user can play with it to his heart’s content. I recommend NEVER allowing users access to the underlying objects of a database.

 

[yeatmanj]

I just had a thought. *amazing*

If you set up a table that contains all your field name then build your query through VBA as an SQL string. You can then export the data to excel and the user will not only be able to reduce the number of fields exported, but also be able to play with the data to their hearts content. This will allow us to do a somewhat customized query while maintaining data integrity.