Sean Lange (6/30/2014)
Andrew Kernodle (6/30/2014)
Ah, gotcha! That changes things up a good bit :-).Access may well be a more workable solution, then, provided the DBAs aren't overwhelmed presently. Pass-through queries are literally done the same way as SQL queries, since you're writing the statement that SQL Server will use to return data.
If the DBAs have the time, users could submit query requests much like they submit report requests; if they need dropdowns and so on to retrieve data, pass-through queries could be written to pull that data, supply it to Access forms, and the forms would drive the data retrieval.
There would be a bit of a learning curve for the DBAs, but it's at least GUI-based; the pass-throughs would retrieve the data, and they'd be specified as a data source in the Access form designs. There's also the matter of designing the forms, which may or may not be an issue (it's like designing a fairly simplistic GUI, but depending on the complexity needed, there could be lots of moving parts).
Granted, if the DBAs are strained at present, that's going to muddy things up a bit, but if they're willing to work on learning a bit about the functionality of Access, it could be a workable solution.
I would recommend NOT using pass through sql in any application. Otherwise your application needs to be changed when something in the query isn't quite right. I would use stored procedures and/or views so the queries can be modified on the sql side and the application doesn't need to change at all.
Gah, I should've been more specific :-). Pass-through queries that call stored procedures was what I meant, but it wasn't what I said :-D. And being specific is what definitely counts in this sort of a situation! Thanks for the correction.
EDIT: As for the previous post, yes, I was thinking that the DBAs would be doing most of the engineering of the system. However, since they're in a time crunch, that seems like a less usable option.
In that case, we're back to square one; Access would indeed let your end-users write their own queries, but the performance devastation that could occur would be painful.
Also, you lose the benefit of a centralized development location; if everyone's writing their own queries, and something in the data or database needs to be altered, some of those queries will malfunction. If nobody's keeping track of what everybody's writing, it becomes a painstaking process of going to each user, getting their queries, and adjusting them to work with the new format. This may or may not be a common occurrence at your workplace. However, if it does happen, there's going to have to be someone that fixes the problems; it would seem that would be a DBA, which may be problematic, given their limited time.
The Access approach may not be as feasible if the DBAs can't exercise control over the medium; that being the case, I'm not certain of a workable solution for this issue, myself. Hopefully someone else can chime in with more assistance!
- 😀