Give users the ability to query a database using SQL

[birchw]

Hi

I have a database in Access 2000, for which I would like to give users with a certain amount of SQL knowledge the ability to query it themselves. This would save me a great deal of time and effort! The way I would like to do this is to have a form with two windows - the top window being where the SQL is written and the bottom window being the output, perhaps in a datasheet format. The permissions will need to be limited, i.e. to SELECT only. Is this possible?
Any advice most appreciated.

[Jim Doherty]

Hi

I have a database in Access 2000, for which I would like to give users with a certain amount of SQL knowledge the ability to query it themselves. This would save me a great deal of time and effort! The way I would like to do this is to have a form with two windows - the top window being where the SQL is written and the bottom window being the output, perhaps in a datasheet format. The permissions will need to be limited, i.e. to SELECT only. Is this possible?
Any advice most appreciated.

IMHO if you don't wish them to have Access then MSQuery (non default installation part of the office package) can query an Access database There a any number of third party apps that do similar and which you could find with a bit of googling.

If you do not mind them 'having' Access but would wish to control it by custom building a form you'd be reinventing the wheel.

To actually and properly custom build a form of the type you describe 'from scratch' requires a significant amount of effort to ensure consistency in the SQL statement they type to ensure it verfified against the datasource not to mention ensuring compliancy of the SQL itself.

The last thing you would want I think? is a user to say "Hey I typed this in and it hasnt done anything" You'd spend a great deal of time and effort running round with replies like "Hey you missed the date # " or no its * for the wildcard only Ansi 92 does % and so on


Jim

[JConsulting]

Hi

I have a database in Access 2000, for which I would like to give users with a certain amount of SQL knowledge the ability to query it themselves. This would save me a great deal of time and effort! The way I would like to do this is to have a form with two windows - the top window being where the SQL is written and the bottom window being the output, perhaps in a datasheet format. The permissions will need to be limited, i.e. to SELECT only. Is this possible?
Any advice most appreciated.

I'd also like to add to what Jim has already stated.

A free form textbox is simple enough to type into, however because of the dynamic nature of SQL and the differing number of fields returned etc..and the static nature of forms, you would be better off just letting them type away, and read the text from the box into a SQL statement to execute it. Select queries can be snapshot format, however action queries that change data will be somewhat harder to manage. You could end up with a user that deletes all records from a table.
Method wise, the set-up for this requirement is simple, but the management, as Jim has already stated, is going to be more difficult.
J

[ADezii]

Hi

I have a database in Access 2000, for which I would like to give users with a certain amount of SQL knowledge the ability to query it themselves. This would save me a great deal of time and effort! The way I would like to do this is to have a form with two windows - the top window being where the SQL is written and the bottom window being the output, perhaps in a datasheet format. The permissions will need to be limited, i.e. to SELECT only. Is this possible?
Any advice most appreciated.

I have an excellent Utility straight from the Access 2002 Developers Handbook called the SQL Scratchpad. This utility consists of 1 Form and 3 Class Modules, and seems to be tailor made for what you are requesting. The Permissions you would have to handle yourself. It uses ANSI 92 Syntax which means that the * and ? are out and % and _ are in. Whether or not this is a problem can only be answered by you. If you are interested, let me know and I can make some arrangements to get it to you.

[missinglinq]

Perhaps the SQL statement could be parsed, looking for certain terms (Delete for example) and allowing/disallowing execution accordingly. Likewise, a search could also be done of * and ? and replace them, if found, with % and _.

I have to say, though, that giving users this kind of access (sorry!) to data gives me the willies!

Linq ;0)>

[ADezii]

Perhaps the SQL statement could be parsed, looking for certain terms (Delete for example) and allowing/disallowing execution accordingly. Likewise, a search could also be done of * and ? and replace them, if found, with % and _.

I have to say, though, that giving users this kind of access (sorry!) to data gives me the willies!

Linq ;0)>

I'm with you on this one, linq! Imagine the old "Delete * From ReallyImportantTable" if it's not caught? (LOL).

[birchw]

Thanks guys for the advice. I think this is going to be more time and effort than it's really worth :-)