[AccessD] Access 2000 Pass-Through Query Reports

We have a number of forms and reports that use passthru and require 
modifying SQL first.   Unless I have missed something basic, this method 
is not all that efficient and is tough to maintain if you have multi-user 
requirements.   In addition, the frequent mods to the query def seem to 
bloat the databases quickly.   Somebody is probably gonna mop the floor 
with me for this, but here's what we have been doing when we have such a 
report or form.   It might make sense for you.   It might not. 

Anyway, how you do this would depend upon your security model.  First, you 
create a table that has a key column that uniquely identifies the user - 
we use the value returned by suser_sname on the SQL Server side and use a 
WinAPI function to get the name on the Access side.    I believe you can 
also use the client machine name or IP address. 

Construct the table with additional columns to hold your parameter values, 
as well.   Then, you can create a stored proc that reads the parameters 
from this SQL table (using the user name in your WHERE clause) and then 
calls the desired stored procedure with those parameters. 

To run the report you would 

        a) remove any existing record for the user in the parameter table
        b) insert a new record with the username and the desired parameter 
values
        c) run the report
 
Note that with this method, the user can only have one 'parameter' record 
in the table at a time. 

I confess this is kludgy and works best if done up front and not as an 
afterthought.   It does work well for us, though.   Also, I am kinda in a 
rush, so if this needs clarification, please let me know.

- Ken






Brett Barabash <BBarabash at TappeConstruction.com>
Sent by: accessd-bounces at databaseadvisors.com
09/09/2003 10:52 AM
Please respond to Access Developers discussion and problem solving 

 
        To:     "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
        cc: 
        Subject:        [AccessD] Access 2000 Pass-Through Query Reports


What is the best approach to using a parameterized SQL Server stored 
procedure as a recordsource for an A2k report?
I was thinking of basing the report on a pass-through query (containing an 
EXEC statement), but it appears that I would need to modify the 
pass-through SQL each time the report is run, to set the parameter values.
Brett Barabash, MCP 
Tappe Construction, Co. 
Eagan, MN 
bbarabash at tappeconstruction.com 
(651) 256-6831 
"One thing a computer can do that most humans can't is be sealed up in a 
cardboard box and sit in a warehouse."  -Jack Handey
 


--------------------------------------------------------------------------------------------------------------------
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this email in error please notify the
originator of the message. This footer also confirms that this
email message has been scanned for the presence of computer viruses.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

Scanning of this message and addition of this footer is performed
by SurfControl E-mail Filter software in conjunction with 
virus detection software._______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030909/723772e8/attachment-0001.html>