<br><font size=2 face="sans-serif">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. </font>
<br>
<br><font size=2 face="sans-serif">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. </font>
<br>
<br><font size=2 face="sans-serif">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. </font>
<br>
<br><font size=2 face="sans-serif">To run the report you would </font>
<br>
<br><font size=2 face="sans-serif"> a) remove any existing record for the user in the parameter table</font>
<br><font size=2 face="sans-serif"> b) insert a new record with the username and the desired parameter values</font>
<br><font size=2 face="sans-serif"> c) run the report</font>
<br><font size=2 face="sans-serif"> </font>
<br><font size=2 face="sans-serif">Note that with this method, the user can only have one 'parameter' record in the table at a time. </font>
<br>
<br><font size=2 face="sans-serif">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.</font>
<br>
<br><font size=2 face="sans-serif">- Ken</font>
<br>
<br>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>
<td><font size=1 face="sans-serif"><b>Brett Barabash <BBarabash@TappeConstruction.com></b></font>
<br><font size=1 face="sans-serif">Sent by: accessd-bounces@databaseadvisors.com</font>
<p><font size=1 face="sans-serif">09/09/2003 10:52 AM</font>
<br><font size=1 face="sans-serif">Please respond to Access Developers discussion and problem solving </font>
<br>
<td><font size=1 face="Arial"> </font>
<br><font size=1 face="sans-serif"> To: "'accessd@databaseadvisors.com'" <accessd@databaseadvisors.com></font>
<br><font size=1 face="sans-serif"> cc: </font>
<br><font size=1 face="sans-serif"> Subject: [AccessD] Access 2000 Pass-Through Query Reports</font></table>
<br>
<br>
<br><font size=2 face="Arial">What is the best approach to using a parameterized SQL Server stored procedure as a recordsource for an A2k report?</font>
<br><font size=2 face="Arial">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.</font>
<p><font size=2 face="Arial">Brett Barabash, MCP <br>
Tappe Construction, Co. <br>
Eagan, MN <br>
bbarabash@tappeconstruction.com <br>
(651) 256-6831 </font>
<p><font size=2 face="Arial">"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</font>
<p><font size=3 face="Times New Roman"> </font>
<br><font size=3 face="Times New Roman"><br>
<br>
--------------------------------------------------------------------------------------------------------------------<br>
This email and any files transmitted with it are confidential and<br>
intended solely for the use of the individual or entity to whom<br>
they are addressed.<br>
If you have received this email in error please notify the<br>
originator of the message. This footer also confirms that this<br>
email message has been scanned for the presence of computer viruses.<br>
<br>
Any views expressed in this message are those of the individual<br>
sender, except where the sender specifies and with authority,<br>
states them to be the views of Tappe Construction Co.<br>
<br>
Scanning of this message and addition of this footer is performed<br>
by SurfControl E-mail Filter software in conjunction with <br>
virus detection software.</font><font size=2 face="Courier New">_______________________________________________<br>
AccessD mailing list<br>
AccessD@databaseadvisors.com<br>
http://databaseadvisors.com/mailman/listinfo/accessd<br>
Website: http://www.databaseadvisors.com<br>
</font>
<br>
<br>