Questions toward the bottom of the post.
Situation is this:
1) Access 97
2) SQL Server 2000
3) The Access app:
a) sets up pass-thru query
b) .SQL property of querydef is a string, the contents of which
comprise the call to a stored proc
c) in the Open event of the Access report object, sets
the .RecordSource property of the report object to the name of the
querydef
4) the stored proc:
a) accepts the parameters passed to it:
b) creates a string, the contents of which is a SQL SELECT
statement...the parameters passed to the stored proc comprise the the
WHERE clause
c) after creating the string (4b) do an exec() on the string; this
produces a result set that gets returned back to calling app
5) the result set produced by the stored proc populates the controls
in the Access report object
The situation described above is working
I would like to make the overall process more robust; to handle the
possibility that an error could occur when
the stored proc gets run...if so, it would be nice if the Access app
could display the message that describes
the error that happened when the stored proc was run.
For example, I deliberately set up the stored proc in such a way that
it would be syntactically correct, but when
it ran, it would produce an error (reminder: the sproc creates dynamic
SQL, then runs that SQL...so, in the
dynamically-created SQL, I deliberately misspelled a reference to a
column in a table. When I use the Query
Analyzer tool in SQL Server to run this stored proc, I see a
meaningful error message...a message along the
lines of "this column does not exist". When I run the exact same
stored proc through the Access app, the
result I see in Access is:
Reserved Error (-7711); there is no message for this error
I'm guessing that what's going on is:
a) the Access app was expecting a result set to get returned
b) the error in the stored proc means that the stored proc did not
return a result set
c) in the absence of a result set getting returned, Access issued the
-7711 error.
It would be great if I could set things up such that:
a) if there were no errors in stored proc, it would return the typical
result set
b) if there were errors in the stored proc, it would return the text
of the error message (e.g. the message "this
column does not exist")
c) the Access app would be smart enough to know when a result set got
returned, and it would then populate the
controls in the report
d) the Access app would be smart enough to recognize that what got
returned from stored proc was an error, and it would then report that
error
I realize that this posting contains some SQL Server elements and some
MS Access elements, and I realize that this is a group that deals with
MS Access-related topics. The questions below attempt to focus on
the MS Access stuff. As you can tell, the issue described above spans
the MS Access and SQL Server environments, so I'm hoping I'm not
violating posting protocol by referring to SQL Server in this post.
Questions:
1) Is it possible to set up set up an Access 97 app combined with a
SQL Server 2000 stored proc in such a way that:
- when stored proc runs without error, the result set that it
returns feeds RecordSource of Access report object
- when stored proc encounters error, it returns that error message
to Access app, and Access app displays that error message
?
2) If the answer to #1 is yes, how does one make the Access app smart
enough to distinguish between when a legitimate result set is getting
returned from stored proc vs when the stored proc is reporting an
error message?
3) If the answer to #1 is yes, is it likely that the stored proc will
have to employ an output parameter and make use of that parameter in
the instances when an error occurs while running the stored proc?
Thank you.
